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.
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.
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
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)
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.
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
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
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
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
Check this Livres Gratuits for more info about SQL Server …..
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
Hi Peter: I read your articles frequently to got mysql depth knowledge. Here my doubt is related with read_buffer_size, read_rnd_buffer_size, as you are saying that it is used only in myisam but not in innodb…while mysql documentation is different as it is also used for all engines in some context like —
-For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
-For bulk insert into partitions.
-For caching results of nested queries.