How much memory does MySQL occupy? Is it efficient / does it need more memory? Can I tune it?
All good questions ... and to make the most of any answers, there's something you'll need to know first about the structure of MySQL. It runs as a series of daemon processes / services, each of which has a size associated with it, and there are also various buffers that are shared by those processes. So its memory footprint is effected by the size of global buffers, by the size of buffers in each thread, and by the number of threads. Changing the size of a global buffer by a megabyte only changes the overall footprint by a megabyte ... but changing the size of a thread buffer changes it in each thread, so if you have 20 threads running, then a change of 1Mb changes the overall memory usage by 20Mb. And limiting the number of threads / closing down threads that have completed quickly can be very significant indeed on systems which are tight for memory.
There is little point in having huge buffers if you're only using part of them - and you can see how you're doing with the MySQL command show status
. This will report on the status of the last operation performed, so that you can run a statement and see how it did. Surprisingly, you may find that increasing buffer sizes beyond a certain point actually reduces performance; the most likely cause of this is that there's extra (pointless!) memory to be handled, and / or you're slowing your system down by increasing swapping.
OK - so what are the buffers?
(Sample data shown is from a show status
on our server)
General ... and global:
• key_buffer_size (default 16 Mb)
| Key_blocks_unused | 5348 |
| Key_blocks_used | 3535 |
Especially used with MyISAM tables.
• innodb_buffer_pool_size (default 8 Mb)
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
Used with innodb tables.
(Note - if you keep half your data in MyISAM and half in InnoDb, you're likely to need two quite substantial sets of buffers - it's possible that you could do better sticking to one table type or the other. You'll see that on my system, we have a big pool that's virtually empty and I'm tempted to cut it to 2 Mbytes ... not that 6 Mbytes is hugely important these days!)
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
These only apply if you have query caching switched on
General ... per thread
• read_buffer_size (default 128k)
Note that for nested queries, MySQL may allocate more than one read_buffer per thread.
• sort_buffer_size (default 2 Mb)
So cutting the sort buffer to 1 Mb if you have 20 threads running will save 20 Mb of memory
Specials ... per thread
Changing the defaults
use /etc/my.cnf ... add in extra lines in the [mysqld]
Many of the values are now dynamic - i.e. they can be changed on a running system. And there are extra controls too, over and above the buffer sizes, which you should not overlook:
max_user_connections (default 0)
connect_timeout (was 5 now 10)
wait_timeout (default 28800)
There is a full list of the system variables here
in the MySQL documentation
How do I find out what's going on?
If you have tuning issues ...
You can do immediate tests as to what settings your server is tuning with using mysql --help
from the command line. And within the mysql
stand alone program, you can run individual commands then do a show status
to see how they did.
Individual logging is available for all queries, or (if you would like them filtered) just for slow queries, and can be set via flags in my.cnf:
log = /var/log/mysql/mysql.log
Remembering that logging itself may have a (slight?) effect on performance, and you'll need to ensure that crontab
jobs cycle the log files if you leave then running over a long period.
Keeping track of the number of MySQL threads over a longer period, I run a crontab job regularly through the day which includes the following .. it's in Perl:
$run_mysql = `ps aux | grep -c mysql`;
$run_httpd = `ps aux | grep -c httpd`;
print LOGFILE "Mysql - $run_mysql";
print LOGFILE "httpd - $run_httpd";
and I also add a datestamp to the file, so that I can correlate the results back with my web (and other) logs if I need to.
can also need attention - have a look at explain
to see how you can ask MySQL what it's doing, and have a look at indexes
(short case study
and introduction to optimising selects via indexes
)to ensure that you're not reading through big tables time and time again.
This really is the most enormous subject ... and the answers to improving MySQL performance will vary for everyone, based on the footprint of your use of the server and how much resources you have available. In fact, I'm in danger of writing a whole book here ;-). But remember
• Study / benchmark before you change settings
• Look at individual thread sizes, then global buffers
• Don't overlook the number of threads you have running and for how long
• And remember to optimise your queries too.
Here are some links away from this site. I make no secret that I use these pages - once you've seen the overview above, they'll fill you in much more and so I'll leave you in the capable writings of the "MySQL Performance Blog" folks.
What to tune after installation
Server memory usage
Optimal buffer sizes
InnoDb buffer pool size (written 2009-05-31)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articlesS153 - Sourcing, Running and Configuring MySQL 
Starting MySQL. ERROR! The server quit without updating PID file - how we fixed it. - (2015-05-06) 
Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25) 
Checking MySQL database backups have worked (not failed) - (2015-01-10) 
Cant connect to local MySQL server through socket /tmp/mysql.sock - (2009-10-17) 
Securing MySQL on a production server - (2009-10-09) 
Potted MySQL installation - (2009-10-09) 
Which version of MySQL am I running? - (2009-09-26) 
MySQL - licensing issues, even with using the name - (2009-03-16) 
Summary of MySQL installation on a Linux system - (2008-12-11) 
More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24) 
Apache httpd, MySQL, PHP - installation procedure - (2008-08-01) 
Some sideways thoughts on the news - (2008-06-27) 
MySQL - Password security (authentication protocol) - (2007-04-02) 
mysqldump and mysqlrestore - (2007-03-30) 
Apache httpd , browser, MySQL and MySQL client downloads - (2007-02-28) 
Browser -> httpd -> Tomcat -> MySQL. Restarting. - (2006-10-28) 
Key facts - SQL and MySQL - (2006-02-04) 
MySQL permissions and privileges - (2005-12-20) 
MySQL - an FAQ - (2005-12-03) 
Which MySQL server am I using? - (2005-11-07) 
Symbolic links and hard links - (2005-06-02) 
Current MySQL and PHP paths and upgrades - (2005-01-28)
Some other Articles
Keyboard reading in Perl - character by character not line by lineDripping taps, Java threads and the single thread modelTcl/Tk - why does the canvas widget use a display list?MySQL server replicationMonitoring and Tuning your MySQL installationA beautiful coastlineNorth Antrim Coast - a jewel thats well worth a visitSpot the odd one outAn evening walk in CarrickfergusImages in a database? How big is a database? (MySQL)