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?

MySQL 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!)

• innodb_additional_memory_pool_size
• innodb_log_buffer_size

• query_cache_size
| 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
• read_rnd_buffer_size
• tmp_table_size

Specials ... per thread
• bulk_insert_buffer_size
• myisam_sort_buffer_size

Changing the defaults

use /etc/my.cnf ... add in extra lines in the [mysqld] section.

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
  log-slow-queries=/usr/local/logs/mysql_slow

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:
open (LOGFILE,">>/usr/local/logs/webserver");
  $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.

Individual queries 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.

Further Reading

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

Sort Speed

InnoDb buffer pool size
(written 2009-05-31)

 S153 - Sourcing, Running and Configuring MySQL
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)
  [334] Symbolic links and hard links - (2005-06-02)
  [489] Which MySQL server am I using? - (2005-11-07)
  [515] MySQL - an FAQ - (2005-12-03)
  [535] MySQL permissions and privileges - (2005-12-20)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [907] Browser -> httpd -> Tomcat -> MySQL. Restarting. - (2006-10-28)
  [1095] Apache httpd , browser, MySQL and MySQL client downloads - (2007-02-28)
  [1123] mysqldump and mysqlrestore - (2007-03-30)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [1689] Some sideways thoughts on the news - (2008-06-27)
  [1731] Apache httpd, MySQL, PHP - installation procedure - (2008-08-01)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1935] Summary of MySQL installation on a Linux system - (2008-12-11)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2426] Which version of MySQL am I running? - (2009-09-26)
  [2444] Potted MySQL installation - (2009-10-09)
  [2445] Securing MySQL on a production server - (2009-10-09)
  [2458] Cant connect to local MySQL server through socket /tmp/mysql.sock - (2009-10-17)
  [4390] Checking MySQL database backups have worked (not failed) - (2015-01-10)
  [4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
  [4487] Starting MySQL. ERROR! The server quit without updating PID file - how we fixed it. - (2015-05-06)

Back to A beautiful coastline

Previous and next or Horse's mouth home

Forward to MySQL server replication

Keyboard reading in Perl - character by character not line by line
Dripping taps, Java threads and the single thread model
Tcl/Tk - why does the canvas widget use a display list?
MySQL server replication
Monitoring and Tuning your MySQL installation
A beautiful coastline
North Antrim Coast - a jewel thats well worth a visit
Spot the odd one out
An evening walk in Carrickfergus
Images in a database? How big is a database? (MySQL)