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 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. 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.
About Peter Zaitsev
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
how to determine the installed instance name?
can any bosy help me in the form of a query
Great guide! One question, is there any sort of performance gain/penalty from using innodb_file_per_table?
And one small correction, it should be ‘innodb_flush_log_at_trx_commit’ (singular log), rather than ‘innodb_flush_logs_at_trx_commit’ it seems.
innodb_flush_logs_at_trx_commit should be innodb_flush_log_at_trx_commit (no s)
Thanks. Fixed
I am a registered user on a couple of “blogs” (websites) that allow articles to be discussed (by registered users). And the users are given a opportunity to “edit” his/her own post during a 30 minute period following submission of same.
One of the sites where I am encountering a problem is: commondreams.org
Each time I try to EDIT one of my posts, I am allowed (once) to perform the edit, but I then immediately receive this message:
“WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]
SELECT comment_content, comment_author_IP, comment_date_gmt FROM wp_comments
WHERE comment_ID =
You aren’t allowed to edit this comment, either because you didn’t write it
or you passed the 30 minute time limit.”
I am using a PC running Windows XP, and using either Firefox or Internet Explorer
(the problem occurs with either browser).
What is “my SQL syntax”?
Where does it “reside”?
How can I “fix it”?
Is there some nifty “patch” that will fix it for me?
I am not a “tech-type”, so please try not to answer me in “Computereese”
Thanks!
Hi, Peter,
I am new to mysql setting level.
Right now I am working in a PHP-Mysql base project in which we have one job table and one url master table.
Job table contains 15000000 records and url table contains 25000 record. Every day we are crawling all the urls and if there is any new job on those url then we will insert that record in our job table. Show each and every day there are near 100000 new job record inserted into job table.
Right now out both table type is ‘innodb’.
But problem is right now our query takes two much time for duplicate checking and other type processes.
Can you please tell me what are the settings required in mysql for incressing performance of my project.
Also can you please tell me that the type of table ‘innodb’ is OK ro not..
Below are the my server ‘SHOW STATUS’ output;
Key_read_requests 9490927
Key_reads 72073
Key_write_requests 678314
Key_writes 430602
Open_tables 561
Open_files 278
Thanks !
Comment by “Sam Nicolosi” (no.81) is simply spam. WTF would some1 write such a long message in a place that’s completely irrelevant if not for the sole purpose of including a url?
Anyway, very nice and helpful post. I’ve posted my question in your forum.
This blog comes up on almost any mySQL config variable I search on Google
Following some advice given here and in the mysql documentation, I’m setting up 3 keycaches. When I assign tables to a keycache, is that permanent or is that information lost when I restart the server or daemon? Also, is there a way to tell which keycache a table is assigned to currently?
Hi.
I changed the parameter – innodb_log_file_size and many of mysql databases were giving lots of errors
the
mysqlcheck would give errors
db1.civicrm_price_set_entity
error : Incorrect information in file: ‘./db1/civicrm_price_set_entity.frm’
mysql
mysql> desc access;
ERROR 1033 (HY000): Incorrect information in file: ‘./db1/access.frm’
mysql> exit;
mysqldump: Got error: 1033: Incorrect information in file: ‘./db1/access.frm’ when using LOCK TABLES
Surprising thing is that of the above 3, only civicrm_price_set_entity is an innodb table. The other 2 are myisam.
Finally after a lot of searcing i came across this very informative post –
http://forums.mysql.com/read.php?34,177212,234851#msg-234851
i’ll have to study a bit more deeper as to how to change the parameter innodb_log_file_size but it looks like it’s not as easy as
editing the my.cnf file and restarting mysql.
yashesh
You have to move the old log files away. InnoDB is disabled in your server right now.
I am using Mysql server version 4.1.20
and i in my server there are almost 10 databases but 2 databases running frequently. One has 40 tables and other has 120 tables. and they contains 133478 rows in each table.
Mysql configuration is as follows:::
[root@crm ~]$ vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=255
query_cache_size=8M
table_cache=510
tmp_table_size=13M
thread_cache_size=12
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=64M
read_buffer_size=1M
read_rnd_buffer_size=1M
sort_buffer_size=203K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=200M
innodb_log_file_size=10M
innodb_thread_concurrency=8
My Problem is that, day by day i see that the number of slow queries is increasing on.
mysql> \s
————–
mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3
Connection id: 303745
Current database: office_new
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 4.1.20
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 72 days 19 hours 45 min 58 sec
Threads: 1 Questions: 10163302 Slow queries: 149 Opens: 149457 Flush tables: 1 Open tables: 64 Queries per second avg: 1.615
————–
Please suggest me how to increase the performance of my server???? There are almost 20-30 user’s online each time.
Please suggest me
My email address is kuberstha@subisu.net.np
Hi Peter,
These tips definately sound great from the number of responses that you have got ! I am not really a DBA but have been using oracle for 8+ years now, I am more of a BI engineer who knows to use SQL and write fairly complex queries.
I am very intrested in learning MySQL and perfromance tunning in MySQL from a reporting standpoint, ie to optimise the database (datawarehouse) for reporting.
Would you be kind enough to provide me with a starting point ? some website which starts with the basics of tunning ? (once again I am not a DBA but am a BI engineer)
(I am able to google and get way too many links and get easily lost -:)
Thanks in Advance !
For example, I found this link on google http://www.mysql.com/why-mysql/performance/
But it has so many articles and documents within and so was not sure what would be the right start and best bet from a time line perspective.
Thanks again !
Please Anyone suggest me how to increase the performance of my MYSQL server. Day by day the number of slow queries are increasing on!….
Threads: 1 Questions: 10163302 Slow queries: 149 Opens: 149457 Flush tables: 1 Open tables: 64 Queries per second avg: 1.615
max_connections=255
query_cache_size=8M
table_cache=510
tmp_table_size=13M
thread_cache_size=12
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=64M
read_buffer_size=1M
read_rnd_buffer_size=1M
sort_buffer_size=203K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=200M
innodb_log_file_size=10M
innodb_thread_concurrency=8
Kuber,
If I may ask you a basic question please ?
How do you get these stats ? I mean from where ? (I am a newbie-:)
Hope some expert helps you with your question !
Regards,
These are mysql configuration and it is found in my.ini or my.cnf which is the base of mysql configuration.
in linux you can view this file in this location
/etc/my.cnf
Thanks Kuber !
D’you consider “misc” like what, when treat about the functions of innodb_additional_mem_pool_size? I’ve used this variable to tune server to alocate dictionary cache…is this true?
http://blog.taragana.com/index.php/archive/one-mysql-configuration-tip-that-can-dramatically-improve-mysql-performance/
try this
max_write_lock_count = 1
im running my chat server at centos with 3.5 gb ram , i observered there are round about 100 queries per second can you please guess some important parameteres and their values for my server that can increase speed of mysql .. currently it is going slower and slower as the users increasing
Hi all,
I really need your help on finding the solution for my problems. Fyi, i’m using 2 X quad core processor with 8gb of RAM. This server is a dedicated database server. So, my problems are, the mysql process always use 1 cpu core at 1 time and it used 100% of the cpu resource. I’am not sure why it happen like it. Below are my server ‘TOP’ status, as you can see cpu4 use 100% usage while others like at idle state.
top – 18:27:53 up 103 days, 7:14, 3 users, load average: 1.10, 1.07, 1.02
Tasks: 191 total, 1 running, 190 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 99.3% id, 0.7% wa, 0.0% hi, 0.0% si
Cpu2 : 0.3% us, 0.0% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 98.6% id, 1.4% wa, 0.0% hi, 0.0% si
Cpu4 : 100.0% us, 0.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu5 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 8165752k total, 8077196k used, 88556k free, 493192k buffers
Swap: 4096532k total, 276k used, 4096256k free, 6326856k cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
11395 mysql 16 0 100 39700:41 2.2 706m 172m 4764 S mysqld
Below is my.cnf configuration file
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package)
old_passwords=1
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
log_queries_not_using_indexes = On
log_slow_queries=/var/lib/mysql/log-slow-queries.log
innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:100M:autoextend
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=1
#skip-innodb
skip-name-resolve
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 16
set-variable=max_connections=2000
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Odeng, the comments aren’t a place where we answer specific questions like that. If you really need help, we provide consulting on a paid basis. If you are looking for free help, you should post your question in a forum or mailing list.
Hi, I have a question. Does the settings on the post above are up to date ? I didn’t see last update on the article.
Think you
Thanks this has been a big help. So on a dedicated MySQL server with 12 GB of RAM your saying the key_buffer_size should be set to about 3 GB? Right now its set at 512 MB…I’m slowly bumping it up by 512 MB every few days to gage performance. Please advise.
cnizz,
I think that you must fit key_buffer_size memory space with all its tables indexes, as much as you can. SHOW VARIABLES LIKE ‘key%’ will show you all key_buffer_size status variables and than you’ll have an idea as you can proceed to configure key_buffer variable. Attention to a point: increase arbitrarily key_buffer variable will cause bad performance. Study and adjust it better as possible. Max 4GB, ok?
Regards.
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
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.
Be advised,
when changing the values of variables
innodb_log_file_size and innodb_log_buffer_size
you must delete the file ib_logfile0 and probably ib_logfile1 too.
otherwise it will not open the tables notifying: .frm file corrupted
I migrated our myISAM engine to InnoDB Engine and the performance is so poor after that and did some performance tuning based on above guidance. Now our MYSQL database is running faster and have a better performance.
Thank for sharing