October 31, 2014

MySQL Server Variables – SQL layer or Storage Engine specific.

MySQL Server has tons of variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as on new page Jay has created. Still I see constant confusion out where which of variables apply to storage engines only and which are used on SQL layer and apply to all storage engines.

Lets start with a bit of history. When MySQL was just written there was not that many storage engines – there was MyISAM for disk tables and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer” for example. The other excurse Monty used to make was – it is not really MyISAM specific. key_buffer was also used by now retired ISAM storage engine and could be used by something else. True in theory but in practice for years now when we speak about key_buffer we mean MyISAM.

The other important thing to consider is which storage engines you’re really using. So for example if you only have Innodb tables in your system does it mean MyISAM variables are not important ? Not really. You still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM if they are large enough. So even though you do not want to allocate many resources to MyISAM tables if you do not use them actively you might with to allocate some.

List of variables which are often confused.

bulk_insert_buffer_size – Used by MyISAM tables only to optimize bulk inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement.

concurrent_insert – Enables concurrent insert (while Selects are running) for MyISAM storage engine. Other Storage engine ether always have it (ie Innodb) or do not support it (HEAP/MEMORY). Note in 5.0 you now have very nice value of 2 which allows concurrent insert to work also if tables have “holes” (deleted rows) which can help significantly.

delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables will be badly corrupted on crash.

delayed_insert_limit, delayed_insert_timeout, delayed_queue_size Delayed inserts configuration. Technically it is not storage engine dependent, in practice however not all storage engines support it. For example Innodb tables do not support them. So in practice it is MyISAM related.

expire_logs_days This is replication/binlogging related options – when to delete binary logs from master. It does not affect Innodb and other storage engines transaction logs, slow query log or general log.

flush, flush_time Flushing (closing) of tables after query is complete or every flush_time seconds. This is technically variable which affects all tables but it was designed for MyISAM tables. It makes little sense to use it with Innodb tables for example. Note “flush” means “close file” in this case it does not do fsync() or anything like it to make sure data hits the disk

ft_boolean_syntax, ft_max_word_len, ft_min_word_len, ft_query_expansion_limit, ft_stopword_file FullText search related variables. As FullText currently works with MyISAM tables these are MyISAM related.

join_buffer_size Buffer used for joins without indexes and few other cases. It is used by SQL layer so applies to all storage engines.

key_buffer_size Key Buffer used to buffer Index blocks (row data is not cached). Used only for MyISAM tables. If you do not use MyISAM tables still keep it 4-32MB to be used for temporary tables.

key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm configuration variables. Obviously only apply to MyISAM as key_buffer itself.

large_pages – Use of Large Pages for allocation of large global areas such as Innodb Buffer Pool, Key Buffer etc. It works for certain storage engines which support large pages in general. Currently MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature.

low_priority_updates If enabled selects will get higher priority than updates and waiting update will not stall all new selects to the table until it is executed. It works for storage engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables.

max_write_lock_count Variable which may save you from read starvation if there are constant writes to the table. Works with table locks so again practically applies to MYISAM and MEMORY tables.

preload_buffer_size – Buffer used for Key preloading, which works for MYISAM tables only.

read_buffer_size, read_rnd_buffer_size – Variables used as read buffer for MyISAM tables for Full Table Scan (read_buffer) and for reading rows in sorted order (read_rnd_buffer_size). Other storage engines such as Innodb to not use this variable.

sort_buffer_size Buffer used to sort result set (allocated by each thread once sorting needs to be done) SQL Layer so works for all storage engines.

timed_mutexes Show statistics by mutexes in SHOW MUTEX STATUS. Was intended to work for all storage engines but currently implemented only for Innodb.

tmp_table_size This variable specifies maximum size implicit temporary table (created during query execution automatically) will grow as MEMORY table before it will be converted to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. jaysandy says:

    Check this Livres Gratuits for more info about SQL Server …..

  2. Any idea how the bulk_insert_buffer_size affects inserts when using INSERT INTO … SELECT versus LOAD DATA INFILE. This is mainly for new tables without existing data.

  3. peter says:

    Bulk insert buffer can speed things significantly for bulk inserts by avoiding random IO for updating index. It is still however less efficient than building indexes by sort which is done both by LOAD DATA INFILE and INSERT INTO … SELECT if target is empty MyISAM table.

    In such case the speed is very close. LOAD DATA INFLE in theory needs to do a bit more job by parsing the file but in practice overhead is minimal:

    mysql> load data infile ‘/tmp/z.txt’ into table two;
    Query OK, 7340032 rows affected (2 min 9.61 sec)
    Records: 7340032 Deleted: 0 Skipped: 0 Warnings: 0

    mysql> truncate table two;
    Query OK, 0 rows affected (0.20 sec)

    mysql> insert into two select * from one;
    Query OK, 7340032 rows affected (2 min 9.24 sec)
    Records: 7340032 Duplicates: 0 Warnings: 0

  4. Anja Gheldof says:

    Hello,

    I’m trying to test mysql with large-pages. I compiled a kernel so it support huge pages. Now I want to use large-pages in mysql. How do I use the large-pages? How do I know that mysql use this huge pages? Is there a way to see that?

    Anja (Student)

  5. Vadim says:

    Anja,

    You need to start mysql with –large-pages options
    http://dev.mysql.com/doc/refman/5.0/en/server-options.html

    But before, you have to configure your Linux to enable huge pages.
    for example you can read hugetlbpage.txt :
    http://www.mjmwired.net/kernel/Documentation/vm/hugetlbpage.txt

    However take into account RedHat uses its own way to enable huge pages, perhaps others vendors too.

  6. Anja Gheldof says:

    Vadim,

    I configured already a kernel to enable huge pages. I can use huge pages with a size of 2MB.

    I made a script that does the following:
    mount none /mnt/hugepages -t hugetlbfs

    I did that because I saw with the monitoring tool strace that mysql uses mmap system calls.
    Here you can see the output of
    strace -c -f -o strace.txt -p 4721 (4721 is the pid of mysql)

    % time seconds usecs/call calls errors syscall

    84.78 18.000.427.471 10362 1737133 144871 futex
    5.11 1.084.404.926 22078 49117 1 select
    4.89 1.037.780.473 393 2637973 274671 read
    1.25 264.358.705 46855 5642 rt_sigtimedwait
    1.07 226.303.746 96 2368066 time
    0.89 189.576.561 294 645392 sched_yield
    0.42 90.140.144 80 1126118 rt_sigprocmask
    0.42 89.731.199 88 1013972 1013972 sched_setscheduler
    0.30 63.858.894 112 572717 4012 write
    0.20 42.552.311 185 230154 munmap
    0.20 42.525.781 75 564870 fcntl
    0.16 32.911.366 135 243164 mmap
    0.11 23.633.754 29 807912 lseek
    0.08 16.839.725 62 271100 open
    0.05 10.935.959 21 530205 pread
    0.02 5.296.405 20 268102 265100 access
    0.02 4.758.093 17 272612 close
    0.01 2.227.735 154 14467 mprotect
    0.00 0.484223 124 3906 pwrite
    0.00 0.445093 404 1102 fsync
    0.00 0.430290 72 6000 1500 lstat
    0.00 0.372081 124 3002 unlink
    0.00 0.347109 61 5701 tgkill
    0.00 0.334789 74 4503 setsockopt
    0.00 0.306899 54 5639 alarm
    0.00 0.269266 60 4500 4500 readlink
    0.00 0.132078 88 1501 getpeername
    0.00 0.113291 75 1501 accept
    0.00 0.112548 75 1504 shutdown
    0.00 0.095832 64 1501 getsockname
    0.00 0.004834 44 109 clone
    0.00 0.001256 60 21 brk
    0.00 0.000094 47 2 gettimeofday
    0.00 0.000046 23 2 rt_sigaction
    0.00 0.000028 14 2 2 rt_sigreturn
    0.00 0.000009 9 1 clock_gettime

    100.00 21.231.713.014 13399213 1708629 total

    I also put the parameter larges-pages in the my.cnf and when I check in mysql I saw the following:

    show variables like ‘%large%';
    +———————+———+
    | Variable_name | Value |
    +———————+———+
    | large_files_support | ON |
    | large_page_size | 2097152 |
    | large_pages | ON |
    +———————+———+
    3 rows in set (0.00 sec)

    Now I have a few questions:

    – Did I do something wrong here? Or am I forgetting something?
    – Can I assume that the large_page_size here is 2097152 kB? So it uses 1048 pages of 2M?
    – Must I use the following or is mysql doing that when it starts:
    Use the following command to dynamically allocate/deallocate hugepages:
    echo 2000 > /proc/sys/vm/nr_hugepages
    – and the most important question for me is: How can i see that mysql use this huge pages.

    Information about the server that I use:

    Linux DL585 2.6.15.1-huge-page #1 SMP Thu Nov 23 22:37:15 CET 2006 x86_64 GNU/Linux

    cat /proc/meminfo
    MemTotal: 8012512 kB
    MemFree: 6006088 kB
    Buffers: 7484 kB
    Cached: 1019872 kB
    SwapCached: 0 kB
    Active: 962876 kB
    Inactive: 1003952 kB
    HighTotal: 0 kB
    HighFree: 0 kB
    LowTotal: 8012512 kB
    LowFree: 6006088 kB
    SwapTotal: 975112 kB
    SwapFree: 975112 kB
    Dirty: 0 kB
    Writeback: 0 kB
    Mapped: 948948 kB
    Slab: 18344 kB
    CommitLimit: 4981368 kB
    Committed_AS: 1555036 kB
    PageTables: 3248 kB
    VmallocTotal: 34359738367 kB
    VmallocUsed: 3956 kB
    VmallocChunk: 34359734159 kB
    HugePages_Total: 0
    HugePages_Free: 0
    Hugepagesize: 2048 kB

    I know I ask a lot, but it should be a great help for me if anyone could help me out.

    Thanks,Anja

  7. Vadim says:

    Anja,

    You do not need to execute mount none /mnt/hugepages -t hugetlbfs
    for mysql

    all what you need is
    echo 2000 > /proc/sys/vm/nr_hugepages

    or equal command for your Linux (what do you use btw ? )

    As I see your command was not executed successfully:

    HugePages_Total: 0
    HugePages_Free: 0
    Hugepagesize: 2048 kB

    There should be
    HugePages_Total: 2000

    There can be several reasons:
    1. Your memory is fragmented, so kernel can not allocated non-fragmented part with requested size,
    can you try allocate just after reboot ?
    2. echo 2000 > /proc/sys/vm/nr_hugepages does not work on your box

    See also Configuring Huge Pages in RHEL 3/4 here:
    http://www.puschitz.com/TuningLinuxForOracle.shtml#ConfiguringHugePagesInRHEL4

  8. Smartcoder says:

    Do you want to learn PHP/.NEt/JAVA/ERP/SAP/…… ???

    Download EBooks Freely in .pdf,.chm formats and ready for interview questions….

    http://hotsoftwareslist.blogspot.com/2007/08/download-ebook.html

    Download Opensource CMS Freely

    http://phpfunda.blogspot.com/

    Project management System in PHP – MySQL – http://phpfunda.blogspot.com/2007/09/project-management-system-in-php-mysql.html

    content management systems – http://phpfunda.blogspot.com/2007/08/content-management-systems.html

  9. Hi

    congratulations by your blog. Is interesting and helpful. I have a question about:

    tmp_table_size

    you said that “will grow as MEMORY table before it will be converted to on disk MyISAM table”. I read more of tmp_table_size, one of another definition was “Maximum size of in-memory tables”. Someone can give more ideas about what is a in-memory tables? are a temporary tables which are created as .MYI file on MySQL sometimes?

    Thanks

  10. Stdranwl says:

    Hi Could anybody please suggest me to what will be the best way to setup config settings (my.cnf) for a extreme high insert (8-10k/per second) queries? I’ll be using innodb on mysql 5.1 but I can change my mind if required?
    Thnx Stdranwl

Speak Your Mind

*