What to tune in MySQL Server after installation

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right for your initial MySQL performance tuning, other changes will most commonly offer only incremental performance improvements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

query_cache_size If your application is read intensive and you do not have application level caches this can be great help to optimize your MySQL database. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

More free resources that you might find useful

Webinars

Blog Posts

White Papers & eBooks

Share this post

Comments (107)

  • Rasmus Møller

    Hello,

    our old MySQL 4.1 server has started to fail during a large SELECT INTO OUTFILE 4.5 mio records totalling 1.5GB with an “ORDER BY” statement at the end.

    If we test run without “ORDER BY” there is no error.
    If we select fewer records there is no error.

    We are p.t. not at liberty to upgrade Windows nor MySQL version nor are we allowed to change the SQL statement.

    Are there any MY.INI config parameters which should be tweaked?

    September 29, 2006 at 12:00 am
  • Wagner Bianchi

    Normally, I have been adding skip-name-resolve on both sides, on both MySQL Servers involved on a remote connection.

    ERRATA on my last interaction:

    SHOW VARIABLES LIKE ‘key%’ will show you all variables involved in maintain key_buffer_size internal system and SHOW STATUS LIKE ‘key%’ will show you MySQL key_buffer_size status variables what is the snapshot of key_buffer behavior.

    September 29, 2006 at 12:00 am
  • benhur

    Daer sir,

    Kill 20509 unauthenticated user 89.x.x.x:2501 None Connect Reading from net —

    I have already added the skip-name-resolve, skip-host-cache, and skip-locking option to my.cnf. As a added measure I have already added the IP Address to the /etc/hosts and the privileges for the user and IP address has also been granted in the database already.

    still problem is there can any abody please help

    Please help.

    Regards,
    benhur

    September 29, 2006 at 12:00 am
  • sjc

    Thanks Peter, nice summary of these tuning settings.

    A question: I have a database using only MyISAM with about 6GB of data, 4GB of index files (largest index is nearly 1.7GB) running on a box with 4GB of memory. I’ve currently got the key_buffer_size set to 1.5GB, and I’m finding performance on the large tables can vary hugely, presumably dependent on whether the index for those tables is currently loaded or not. I’m working on a medium-term solution to get rid of those huge indexes entirely, but in the meantime can you suggest a suitable size for the key buffer? My current thinking is to *lower* the size of it in order to give the OS a better chance of caching all the files.

    Thanks,

    – s

    September 29, 2006 at 6:56 am
  • peter

    Surely performance will be very different if data is in memory or not. If you have some particular indexes which you need to have in memory you can use LOAD INDEX INTO CACHE to preload them. You can even have separate keycache for them to ensure they are not replaced by other data.

    Your 1.5GB key buffer looks reasonable but I can’t tell if it is optimal for your workload or 1GB will be better. Run some benchmarks and see yourself.

    September 29, 2006 at 7:05 am
  • Roland Bouman

    Hi Peter,

    What an excellent and valuable overview.

    I sometimes wonder if these are just the teasers and there’s still a box of well hidden secrets that you keep away for consulting gigs 😉

    Anyway, concerning innodb_flush_logs_at_trx_commit:

    “Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.”

    To be clear, we could loose *committed* data in these cases, right?

    September 29, 2006 at 10:43 am
  • peter

    Roland,

    I keep no secrets I do not need it. Normally the problem is not what information is unavailable – with OpenSource you can argue everything is open but applying this information to particular problem and finding bits and pieces relevant for particular case, so are analytical skills. It is impossible to remember everything these days 🙂

    About innodb_flush_logs_at_trx_commit – yes you’re right if you have it set to 0 or 2 you can loose commited data on the crash. The difference is it is MySQL crash for value 0 and full OS crash for value 2, which is why it is better.

    September 29, 2006 at 10:53 am
  • James Day

    Roland, as Peter says, no need to hide, the skill comes in knowing what to apply in a specific context and what to look for first.

    James Day

    September 29, 2006 at 12:31 pm
  • Ruslan Zakirov

    Is table_cache important for InnoDB setups?

    September 29, 2006 at 1:02 pm
  • peter

    James, Thanks for translation 🙂

    Ruslan – it is less of the issue for Innodb but still .frm needs to be read etc. So I prefer to keep it simple 🙂

    September 29, 2006 at 1:28 pm
  • Roland Bouman

    Hi Peter, James

    I trust you understand my remark was meant to be tongue-in-cheek.

    I’m just very much thrilled to see how Peter is openly sharing all this valuable knowledge, and in such a practical manner. I think it’s quite obvious that hiring his consultancy for performance issues is bound to lead to results – a good thing to know when you are hiring a consultant. Some (if not most) consultants might decide to be less detailed in offering such practical tips in fear of devaluating their skills.

    regards,

    Roland Bouman

    September 30, 2006 at 5:00 am
  • peter

    Roland,

    You’re right. I prefer to be open in sharing my knowledge/ideas when it comes to MySQL as well as other areas of expertise. I do not think I will drain me. I think the knowledge is more like the stream than like a lake, and sharing it does not decrease it. Furthermore I learn more by sharing.

    I also try to be very forthcoming in my pre-sales calls. I openly share what I think might be wrong based on information they provide during these 15 minutes. This allows customers to be confident I know what I’m doing and if this little tip was only what they needed to solve their problems I’m fine with having provided it for free.

    September 30, 2006 at 8:24 am
  • Jakiao

    You comment that setting key_buffer to 4G is not uncommon. What if you need to go over 4G? I have noticed that no matter what value you set it at, it will always restrict you to a maximum of 4G.

    October 3, 2006 at 2:10 am
  • Jakiao

    I should probably mention that I’ve noticed this limit on a Linux 2.6.18 x86_64.

    October 3, 2006 at 2:14 am
  • peter

    key_buffer has some internal implementation issues which limits its size to 4G. These are to be fixed in one of next MySQL versions but currently you should stick to this size.

    October 3, 2006 at 2:18 am
  • Mike

    Thanks again – this is excellent information! I’m curious, though, how one would go about setting some of these things on a system with lots of RAM (32G), but with the inherent per-process memory limitations of a 32-bit system. I work with a couple of servers with 8 P3 Xeon processors and 32G of RAM each. I know some buffers are allocated only once for all MySQL threads/processes, and others are per-thread or per-connection.

    October 3, 2006 at 11:06 am
  • peter

    Mike,

    I thought these beasts are all dead by now. But seriously if you are with 32bit and running Linux you can only allocate buffers up to some 2.5GB or so (depending on a lot of factors) – the rest will be only used as OS cache, which is less efficient. On Windows Innodb also can use AWE for caching which you can try to use.

    October 3, 2006 at 11:23 am
  • Jakiao

    Is there anyway I can compile my own MySQL binaries and change this 4GB limit? We won’t be able to use MySQL 5.x for many months, and utilizing RAM is key here. This 4GB limit seems to have no place other than to cause headaches.

    Thanks.

    October 3, 2006 at 3:46 pm
  • peter

    Jakiao,

    MySQL does not have a limit of memory it can use on 64bit systems, it applies to all versions. There is a limit however for particular key_buffer variable which should not be over 4GB. There are some code fixes needed to make it work so it is not simply question of recompiling.

    October 4, 2006 at 6:35 am
  • James Day

    Jaiko, consider multiple key buffers to get around the 4GB limit if you are using MySQL 4.1 or later.

    October 4, 2006 at 12:58 pm
  • peter

    Thanks James. I forgot about that one.

    I do not generally like using MyISAM in cases when more than 4GB of buffer may be required. Tables with such large indexes may take a while to recover.

    October 4, 2006 at 1:36 pm
  • Jakiao

    Hey all,

    I am attempting multiple key_cache’s, it’s just a matter of determining which tables to assign to the alternate cache.

    One thing I noticed is that the primary cache, when set to 4GB, usage never goes over 2GB. Once it reaches 2GB, it drops back to 1.8GB or so. After adding a second key_cache, I’ve notice it go past that 2GB mark.

    Thanks for taking the time to answer my questions. I appreciate it.

    October 4, 2006 at 9:08 pm
  • Caleb

    Great article. Thanks for it.

    I’ve got a situation that I’ve been trying to figure out for some time now and I haven’t really gotten anywhere with it, unfortunately…

    My VPS has 256mb ram and all of mysql resources are fine with the current setting I have, or at least appear to be – except my tmp_table_size. I’ve got 110mb assigned to it and still 50% or more of the tmp tables created are disk based. (as compared to my key_buffer allocation, for instance, which only has 16mb assigned to it but keeps the fill ratio only between 40-50%).

    My current settings:

    [mysqld]
    max_connections = 300
    max_user_connections = 300
    key_buffer = 16M
    myisam_sort_buffer_size = 16M
    read_rnd_buffer_size = 1M
    join_buffer_size = 2M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1000
    thread_cache_size = 200
    interactive_timeout = 25
    wait_timeout = 30
    connect_timeout = 10
    max_allowed_packet = 2M
    max_connect_errors = 1000
    query_cache_limit = 1M
    query_cache_size = 12M
    query_cache_type = 1
    tmp_table_size = 110M
    safe-show-database

    Any light anyone can shed on this would be really awesome. 🙂

    October 6, 2006 at 12:44 pm
  • Shaheryar

    Great information Peter,

    I am facing a problem these days with MySQL. I have a table that has 3.3 million records (size is around 500 MB) that stores records of members. Now I need to update the records of this table by reading data from a file that contains 1 million email addresses and I need to update one field of those records whose email address is in the file. I created a script, reading 1 email address from file and trying to update the table on email field (that is not primary key but an index has been applied to it) and it takes 6-7 seconds in updating one record that means it will require days to update the whole table. I changed it to first select the primary key of the given email and then trying to update it but there was little difference in result. What settings you suggest for MySQL and what approach to update of the two I just described. Table type is MyISAM and MySQL version is 4.1.21-standard.

    October 13, 2006 at 1:45 am
  • peter

    Shaheryar,

    First I should ask you to ask unrelated questions at Forums: http://forum.mysqlperformanceblog.com/s/f/2/ rather than use post comments.

    In your case I would run EXPLAIN for SELECT statement similar to update you’re using, it really feels like it is not using index for some reason.

    Generally your method is fine. Depending on the data you have in the file you might be able to load it in the table and write single query which performs an update, but it will need to do similar operation internally anyway.

    October 13, 2006 at 5:20 am
  • Josh

    I am curious what you think about mixing table types. I have 2 web applications and they are integrated with a site. Now these web apps use MyISAM tables, but I would prefer to use transaction safe tables on the site itself and convert the few web app tables into InnoDB that I insert data into from the site. Then just allow the webapps to use MyISAM on the rest of their tables.

    Is it best to just stick with one table type performance wise?

    October 15, 2006 at 12:13 am
  • peter

    Josh, Same suggestion to you – please use forums for your questions instead of using comments for unrelated posts.

    Mixing table types is OK. I do it all the time. The thing you should care in terms of performance is mainly memory competition – you will need both allocate memory for OS cache and key_buffer for MyISAM tables and innodb_buffer_pool for Innodb tables and ballancing these might be complicated for performance fine tuning.

    October 16, 2006 at 5:29 am
  • Spud

    hi,
    i’ve some two forum tables with abot 700Mb each one, and they was type myisam. I was getting some lock problems and i decided to switch them to innodb, but server load growed from 3 to 20. I followed your steps but i got not any server load improvements.

    should i back to myisam? or is there any way to solve it?

    thanks 😉

    October 19, 2006 at 4:04 pm
  • peter

    Spud,

    Please ask your question at the forum http://forum.mysqlperformanceblog.com/s/f/2/ rather than here if you want to get an answer

    October 20, 2006 at 7:56 am
  • the_dbatrix

    This is great information thank you. I’m getting used to mySQL after many years in Oracle, and I’m curious as to what metrics can be reviewed to iteratively tune this parameters. For instance, INNODB_BUFFER_POOL_SIZE… how can I evaluate hit ratios to determine if this is undersized? Sure, I may see excessive IO in IOStat, but how do I know it’s from this, versus from disk sorts, or myISAM queries not using the query cache and key cache?

    I’ve noticed in the Administrator GUI tool you can see hit ratios for query cache and key cache in myISAM. Are there underlying sql commands I can run to get this to evaluate the efficiency of my variable settings?

    Thanks again, I’ve found reading through your blog quite illuminating.

    October 23, 2006 at 6:29 pm
  • peter

    You can run SHOW INNODB STATUS (it is also available in SHOW STATUS in MySQL 5.0+) – this will show you number of buffer pool pages reads/writes per second which is what you can use to differ Innodb IO from anything else.

    October 24, 2006 at 3:32 am
  • Arul

    Shaheryar,

    You can use the “start transaction + commit” pair to speed up your work. There are different commands for MyISAM and INNODB (can’t remember). Check it out and try it.

    My experience: I once had to load a 1.7 million record table into the DB. Initially the estimated time taken was a few hours. Then I tried the above pair. The upload was completed in 15 minutes! Assuming you are uploading a .sql file, MySQL reads each line and executes it and moves down to the next. With the “Start Transaction” and “Commit” around a chunk of query, SQL will not execute until it reaches the “commit”. So I figure that cuts down drastically on overhead processing.

    October 25, 2006 at 8:34 pm
  • jgabios

    also a good thing is to log your slow queries with:
    log_slow_queries=/var/log/mysqld.slow.log
    and
    long_query_time=1
    I set it to 1 second because my mysql in in web environment and if something takes more than a second, then the users will wait a lot for the page to load and never come back.

    November 17, 2006 at 3:15 am
  • Rohan

    Hi,

    I’m facing problem of slave servers are getting behind master. I’ve one slave and three slave. All slave are getting behind master.
    Server details
    Dual Xeon, 4 GB ram
    All servers are on same gig network.
    All my tables are MyIsam.

    Following is out of mytop

    MySQL on localhost (5.0.22-standard-log) up 12+23:09:57 [13:00:53]
    Queries: 598.6M qps: 560 Slow: 0.0 Se/In/Up/De(%): 03/00/00/00
    qps now: 4928 Slow qps: 0.0 Threads: 37 ( 34/ 2) 01/00/00/00
    Key Efficiency: 100.0% Bps in/out: 0.0/ 0.5 Now in/out: 42.0/18.7k

    qps varies from 3000 to 8000.

    My.cnf

    key_buffer = 4096M
    max_allowed_packet = 32M
    table_cache = 812
    sort_buffer_size = 1024M
    read_buffer_size = 512M
    myisam_sort_buffer_size = 1024M
    thread_cache = 16
    query_cache_size = 512M
    max_connections = 300
    max_user_connections = 300
    max_connect_errors = 99
    wait_timeout=10
    interactive_timeout = 10
    #set-variable=wait_timeout=3000
    # Try number of CPU’s*2 for thread_concurrency
    thread_concurrency = 8

    Please suggest which parameter to tweak. And where to look for the problem

    Waiting for your reply.

    Rohan

    December 7, 2006 at 12:31 am
  • peter

    Rohan,

    Please use forum.mysqlperformanceblog.com for such questions.
    In general I’m surprised it works at all, your sort_buffer_size and read_buffer_size are way off.

    Slaves can get behind master ether because write load is high or if you load slaves with reads too much.

    December 7, 2006 at 2:54 am
  • nustchen

    Deal Peter:
    Hi. First my english is very poor. Please forgive me for my grammar mistake.
    Your article is very useful for me. But I still want to know how can I tune “sort_buffer_size”.
    What status can I use to monitor the performance improvement when I tuned “sort_buffer_size”.

    December 25, 2006 at 10:45 pm
  • peter

    You would normally look at sort_merge_passes status variable. If this variable is increased sort_buffer was not enough to accommodate all data to perform the sort and file based sort was used. Note you do not always have to increase global sort_buffer value. In many cases setting per connection variable before running long queries would be good enough.

    December 28, 2006 at 5:49 am
  • Flasher

    Hi Peter,

    Its realy nice post.

    i want to create a table for templates and replace the type of files .tpl [for Smarty-Light]

    so which is the best type for my table ( MyISAM or InnoDB ) ?

    and Thanks..

    December 29, 2006 at 9:18 pm
  • peter

    Flasher,

    I did not exactly get your question. Also it is best to ask unrelated questions at forum.mysqlperformanceblog.com

    December 30, 2006 at 1:52 am
  • merlin

    I have a mysql server with 300+ workstations connecting to it and my server has only 4 GB. sometimes the server has 3000+ qps. on the average it has 125 qps.

    is 4GB enough since were adding more workstations that will be inserting records to this server? I was told that a standard mysql server must have at least 16 GB ram — true?

    February 7, 2007 at 8:53 am
  • peter

    Merlin,

    MySQL can run with very low amount of memory. 16GB is not required and it is surely not the lowest number.

    Database size and workload define how much memory you’ll need.

    February 8, 2007 at 6:20 am
  • merlin

    I have 65 tables now at 4.3 GB. One table has 6 million rows. Now we average at 300+ queries per second. Average threads at 150+

    How do I optimize my box?

    February 8, 2007 at 7:00 am
  • youtube videos

    It could be helpful to show example my.cnf files for sample configurations.

    March 4, 2007 at 1:46 pm
  • Strafverteidiger München

    James, Thanks for translation. very cool job.

    April 19, 2007 at 11:05 am
  • Ewin Barnett