Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
Monitoring and Tuning your MySQL installation

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)

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


Back to
A beautiful coastline
Previous and next
or
Horse's mouth home
Forward to
MySQL server replication
Some other Articles
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)
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/2209_Mon ... ation.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat