Additions:
innodb_file_per_table
Additions:
query-cache-size = 64M
Slow queries
log-slow-queries
long_query_time = 1
innodb_buffer_pool_size = 1G
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
Deletions:
query-cache-size = 32M
Additions:
Additions:
Additions:
Additions:
If the above formula gives you a key_buffer requirement that can't be met on your server, check the cache hit ratio. If it's below 0.01, that means your key_buffer is large enough. cache hit ratio = Key_reads/Key_read_requests. In the following case, the ratio is 588/5042 = 0.1166. Just an illustration, this mysql hardly used, the values are probably of no reference value.
show status like '%key_read%';
+
+
+
| Variable_name | Value |
+
+
+
| Key_read_requests | 5042 |
| Key_reads | 588 |
+
+
+
Deletions:
If the above formula gives you a key_buffer requirement that can't be met on your server, check the cache hit ratio. If it's below 0.01, that means your key_buffer is large enough. cache hit ratio = Key_reads/Key_read_requests.
Additions:
If the above formula gives you a key_buffer requirement that can't be met on your server, check the cache hit ratio. If it's below 0.01, that means your key_buffer is large enough. cache hit ratio = Key_reads/Key_read_requests.
Additions:
qps now: 2 Slow qps: 0.0 Threads: 2 ( 2/ 0) 00/00/00/00
*
MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time,
MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
* A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
* If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
* The default is 64. Each time
MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache.
MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
* The size of the buffer that is allocated when sorting
MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in
MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
* If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
* "Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.
Other interesting tuning parameters
delay_key_write: index in memory are not flushed between writes to a table. Instead, it's flushed when a table is closed. ref:
http://www.petefreitag.com/item/441.cfm∞
Deletions:
qps now: 2 Slow qps: 0.0 Threads: 2 ( 2/ 0) 00/00/00/00
*
MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time,
MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
* A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
* If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
* The default is 64. Each time
MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache.
MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
* The size of the buffer that is allocated when sorting
MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in
MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
* If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
* "Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.
Additions:
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
myisam_sort_buffer
* The size of the buffer that is allocated when sorting
MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
Deletions:
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
Additions:
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
* Tables that are not currently in use are released, beginning with the table least recently used.
* If a new table needs to be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary.
Additions:
open_tables shows you how many tables are opened at the moment. opened_tables tells you how many were opened in the past. So if your mysql has been running for a short period of time, opened_tables should give you a pretty good idea on what value should be set to table_cache.
table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 × N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.
Deletions:
Set it so that it's larger than open_tables
Additions:
table_cache
Set it so that it's larger than open_tables
show status like 'open%tables%';
+
+
+
| Variable_name | Value |
+
+
+
| Open_tables | 128 |
| Opened_tables | 515 |
+
+
+
2 rows in set (0.00 sec)
Additions:
Deletions:
Additions:
Deletions:
Additions:
Deletions:
drontadrondo
Additions:
key_buffer_size
query_cache_size
Deletions:
Additions:
drontadrondo
Additions:
Mysql tuning (displaying usage)
Check your mysql's usage with mtop [
http://mtop.sourceforge.net]∞. For example:
# For illustration, let's do 10+10 9 million times
SELECT BENCHMARK(900000000,10+10);
# Then run mtop
mtop --dbuser=root --host=localhost
will give you
localhost mysqld 5.0.37 up 0 day(s), 0:35 hrs
2 threads: 2 running, 0 cached. Queries/slow: 657/0 Cache Hit: 60.00%
Opened tables: 0 RRN: 16.8K TLW: 0 SFJ: 0 SMP: 0 QPS: 0
ID USER HOST DB TIME COMMAND STATE INFO
45 root localhost test 4 Query executing SELE
44 root localhost Query show
mtop fields
Field Definition
RRN If this value is more than 50k, tables may not be properly indexed (lots of table scan.
SMP If this value is high, sort memory is not enough
TLW Table lock wait. If this is high, optimize queries, then split tables.
SFJ # of joins without keys
QPS Total # of sql handled
COMMAND Most of them are self-explained except - Binlog Dump (Slave thread reading queries from master)
Mytop utility
MyTop is similiar to mtop. It can be obtained from
http://jeremy.zawodny.com/mysql/mytop∞. To install, download the source file from the URL, and then perl Makefile.PL; make && make install. Sample output:
>mytop -uroot -ppasswd
MySQL on localhost (5.0.37-log) up 0+11:29:09 [18:59:44]
Queries: 369.0 qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 2 ( 2/ 0) 00/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.0/ 0.6 Now in/out: 28.3/ 4.4k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3 root localhost test 0 Query show full processlist
2 root localhost test 1 Query SELECT BENCHMARK(900000000,10+10)
No tool
You can always use mysql itself to obtain some statistics
show status like '%cache%';
show status like 'max_used_connections';
show status like 'open%tables';
SHOW STATUS LIKE 'created_tmp%';
Other tools
- mysqlreport
- mysqlard - record statistics every 5 min and give tuning suggestions
Mysql tuning (compile time)
Starting from mysql4, external locking can be disabled by --external-locking and --skip-external-locking. Works when the same data is used by one mysql instance.
Mysql tuning (configuration)
my.cnf offers parameters that affect mysql's performance. mysqld --verbose --help shows the current values, or show status / show variables on running instance. To show running parameters, do
show variables like '%buffer%';
Here're each parameter and their usage
|| Parameter || Explaination ||
|| max_connections || 500, def 100 (set it to
MaxChildren defined in apache, if 1 connection is used per request) ||
|| key_buffer || 384M (set it to half of system memory) ||
|| table_cache || 1000 depends on number of tablest ||
|| wait_timeout || 7200 seconds before mysql dump a connection ||
|| max_allowed_packet || 16M (default 8M) ||
memory_required=global_buffer+(thread_buffers)*max_connections
where thread_buffers includes the following:
sort_buffer
myisam_sort_buffer
read_buffer
join_buffer
read_rnd_buffer
and global_buffers includes:
key_buffer
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer
Parameters
query_cache_size:
*
MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time,
MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
key_buffer_size:
* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
* A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
* If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
table_cache:
* The default is 64. Each time
MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache.
MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
sort_buffer:
* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
read_rnd_buffer_size:
* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in
MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
thread_cache:
* If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
tmp_table_size:
* "Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.
Quickly, if I have a system with 2G memory, I should set these?
key_buffer = 1G
read_buffer = 2M
sort_buffer = 1M
max_connections = 340
# Note 1G + (2M + 1M) * 340 = 2044M
# Other settings
wait_timeout=180
thread_concurrency = 4 # 2x number of CPU