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
- Top most overlooked MySQL Performance Optimizations
- MySQL scaling and high availability – production experience from the last decade(s)
- How to analyze and tune MySQL queries for better performance
Blog Posts
- Best practices for configuring optimal MySQL memory usage
- MySQL query performance – not just indexes
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
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.
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?
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.
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
Is table_cache important for InnoDB setups?
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 🙂
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
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.
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.
I should probably mention that I’ve noticed this limit on a Linux 2.6.18 x86_64.
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.
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.
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.
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.
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.
Jaiko, consider multiple key buffers to get around the 4GB limit if you are using MySQL 4.1 or later.
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.
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.
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. 🙂
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.
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.
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?
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.
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 😉
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
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.
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.
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.
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.
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
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.
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”.
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.
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..
Flasher,
I did not exactly get your question. Also it is best to ask unrelated questions at forum.mysqlperformanceblog.com
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?
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.
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?
It could be helpful to show example my.cnf files for sample configurations.
James, Thanks for translation. very cool job.
I am working building a simple table based on a very large sequential file of about 50 million titles that are in random order. I plan two indexes, one of just the title and one composed of only a date. Can anyone suggest if it would be better to use MyISAM or InnoDB?
James, Thanks for translation. very cool job.
sometimes i got an error like this .. “mysql max_connection_user has a lot connection” .. what does it mean ?.. when i restart the httpd and mysql it will be resolve ..
someone can help me about that .
Thnks..
Hi every1,
Database size and workload define how much memory you’ll need.
any formula from this?? I really have problem on this now.
pls help !!! extremely urgent.
tqvm.
hello,
thank you for all that information, i’ve used the defauls all the time on a mysql-server with heavy load… now i’ll see how the changes affect everyday-operation
Hello every one
Que:I create webapplication of company and i use mysql server
but after some trasaction application sql server speed much down
so i am not get reply sooon
i send request through Ajax
Hi friends,iam using MySQL5.0.
I have a database with 2mb size.There are more than 30 tables.
In a query, iam using joins to retrieve records from db.which joins 10 tables.Iam using windows 2003 server with 1GB RAM.Please specify the optimum settings for this hardware.Data will be bulk.
If I have an insert/update and a delete statement in a mysql stored procedure,does it mean that the second statement(delete) is executed only after the inserts/updates are completed ? Or do they all run concurrently.If yes,how to make them run one after another?
Anil, major optimum setting: move off of windows anything, to anything else..anything is an improvement. All the rest of us already know this. Why don’t you?
Hi Peter, this article actual a 2008 year, thanks