Created
April 11, 2011 14:02
-
-
Save mattmcmanus/913565 to your computer and use it in GitHub Desktop.
Output from the tuning-primer.sh script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[mysqld] | |
# SLLLLOOOWWWW Queries | |
log_slow_queries = /var/log/mysql/mysql-slow.log | |
long_query_time = 2 | |
log-queries-not-using-indexes | |
wait_timeout=60 | |
connect_timeout=10 #Increase connect_timeout from 5 to 10 | |
interactive_timeout=120 #Decrease interactive_timeout from 28800 to 100 | |
innodb_buffer_pool_size = 32M | |
join_buffer_size=16M #Increase join_buffer_size from 131072 to 1M | |
key_buffer_size = 64M | |
low_priority_updates=1 #If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting | |
max_allowed_packet=16M #Increase max allowed packet size from 1M to 16M | |
myisam_sort_buffer_size = 64M | |
query_cache_size=128M #Increase query_cache_size from 0 to 128M | |
query_cache_limit=2M #Increase query cache limit from 1048576 to 2M | |
read_buffer_size=2M #Increase read buffer size from 1M | |
read_rnd_buffer_size=4M #Increase read_rnd_buffer_size to 4M | |
sort_buffer_size=2M #Increase sort buffer size from 1M | |
table_cache=1024 #Increase table cache cache from 256 to 1024 | |
table_definition_cache = 1024 | |
table_open_cache = 1024 | |
thread_cache_size = 8 | |
thread_concurrency = 4 # Try number of CPU's*2 for thread_concurrency | |
tmp_table_size = 32M | |
max_heap_table_size = 32M |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Linux 2.6.31-23-generic-pae (sangreal.arcadia.edu) 04/12/2011 _i686_ (2 CPU) | |
12:00:33 AM CPU %user %nice %system %iowait %steal %idle | |
12:06:19 AM all 77.80 0.00 22.20 0.00 0.00 0.00 | |
12:15:48 AM all 82.53 0.00 17.47 0.00 0.00 0.00 | |
12:25:27 AM all 80.88 0.00 19.12 0.00 0.00 0.00 | |
12:35:50 AM all 79.72 0.00 20.28 0.00 0.00 0.00 | |
12:45:02 AM all 21.56 0.00 5.73 1.58 0.00 71.13 | |
12:55:01 AM all 1.76 0.00 3.34 2.36 0.00 92.54 | |
01:05:01 AM all 0.94 0.00 0.34 0.25 0.00 98.46 | |
01:15:01 AM all 4.89 0.00 13.08 0.46 0.00 81.57 | |
01:25:01 AM all 0.27 0.00 0.13 0.07 0.00 99.54 | |
01:35:02 AM all 0.68 0.00 0.29 0.06 0.00 98.97 | |
01:45:01 AM all 1.92 0.00 0.43 0.11 0.00 97.53 | |
01:55:01 AM all 1.84 0.00 0.58 0.05 0.00 97.54 | |
02:05:01 AM all 0.46 0.00 0.19 0.10 0.00 99.25 | |
02:15:01 AM all 0.50 0.00 0.18 0.05 0.00 99.27 | |
02:25:01 AM all 0.65 0.00 0.16 0.05 0.00 99.15 | |
02:35:01 AM all 0.59 0.00 0.21 0.03 0.00 99.17 | |
02:45:18 AM all 4.30 0.00 18.44 0.03 0.00 77.22 | |
02:55:02 AM all 18.75 0.00 29.21 0.05 0.00 51.99 | |
03:05:01 AM all 1.01 0.00 0.28 0.03 0.00 98.68 | |
03:15:01 AM all 1.88 0.00 0.49 0.10 0.00 97.52 | |
03:25:01 AM all 1.07 0.00 0.36 0.02 0.00 98.56 | |
03:35:01 AM all 0.63 0.00 0.18 0.01 0.00 99.18 | |
03:45:01 AM all 1.71 0.00 2.87 0.04 0.00 95.38 | |
03:55:01 AM all 1.07 0.00 0.66 0.02 0.00 98.25 | |
04:05:01 AM all 0.88 0.00 0.28 0.02 0.00 98.82 | |
04:15:01 AM all 0.63 0.00 0.14 0.06 0.00 99.16 | |
04:25:01 AM all 0.66 0.00 0.15 0.01 0.00 99.18 | |
04:35:01 AM all 0.37 0.00 0.09 0.01 0.00 99.53 | |
04:45:01 AM all 0.57 0.00 0.17 0.01 0.00 99.25 | |
04:55:01 AM all 0.84 0.00 0.18 0.01 0.00 98.96 | |
05:05:01 AM all 1.16 0.00 0.24 0.06 0.00 98.55 | |
05:15:01 AM all 1.20 0.00 0.28 0.05 0.00 98.48 | |
05:25:01 AM all 1.26 0.00 0.31 0.12 0.00 98.31 | |
05:35:01 AM all 1.07 0.00 0.21 0.02 0.00 98.70 | |
05:45:01 AM all 1.54 0.00 0.26 0.02 0.00 98.18 | |
05:57:34 AM all 38.36 0.00 51.80 0.00 0.00 9.84 | |
06:05:01 AM all 48.90 0.00 19.39 0.01 0.00 31.70 | |
06:15:01 AM all 2.25 0.00 1.90 0.03 0.00 95.82 | |
06:25:01 AM all 1.44 0.00 0.86 0.02 0.00 97.67 | |
06:35:01 AM all 1.61 0.00 0.66 2.25 0.00 95.49 | |
06:45:01 AM all 1.43 0.00 0.60 0.03 0.00 97.94 | |
06:55:01 AM all 2.24 0.00 3.62 5.97 0.00 88.17 | |
07:05:01 AM all 1.02 0.00 1.34 0.01 0.00 97.62 | |
07:15:01 AM all 1.23 0.00 0.52 0.02 0.00 98.23 | |
07:25:01 AM all 0.68 0.00 0.18 0.01 0.00 99.13 | |
07:35:01 AM all 0.51 0.00 0.28 0.01 0.00 99.19 | |
07:45:01 AM all 1.42 0.00 0.37 0.02 0.00 98.19 | |
07:55:01 AM all 1.11 0.00 0.46 0.05 0.00 98.38 | |
08:05:01 AM all 1.21 0.00 0.69 0.02 0.00 98.08 | |
08:15:02 AM all 2.65 0.00 1.53 0.13 0.00 95.70 | |
08:25:01 AM all 2.21 0.00 2.09 0.03 0.00 95.66 | |
08:35:01 AM all 2.54 0.00 1.36 0.05 0.00 96.05 | |
08:45:01 AM all 1.91 0.00 0.81 0.03 0.00 97.26 | |
08:55:01 AM all 3.07 0.00 1.15 0.11 0.00 95.67 | |
Average: all 5.31 0.00 3.08 0.28 0.00 91.34 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
root@sangreal:~# sh tuning-primer.sh | |
-- MYSQL PERFORMANCE TUNING PRIMER -- | |
- By: Matthew Montgomery - | |
MySQL Version 5.1.37-1ubuntu5.5-log i486 | |
Uptime = 5 days 18 hrs 42 min 12 sec | |
Avg. qps = 7 | |
Total Questions = 3551002 | |
Threads Connected = 1 | |
Server has been running for over 48hrs. | |
It should be safe to follow these recommendations | |
To find out more information on how each of these | |
runtime variables effects performance visit: | |
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html | |
Visit http://www.mysql.com/products/enterprise/advisors.html | |
for info about MySQL's Enterprise Monitoring and Advisory Service | |
SLOW QUERIES | |
The slow query log is enabled. | |
Current long_query_time = 2.000000 sec. | |
You have 3293 out of 3551023 that take longer than 2.000000 sec. to complete | |
Your long_query_time seems to be fine | |
BINARY UPDATE LOG | |
The binary update log is NOT enabled. | |
You will not be able to do point in time recovery | |
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html | |
WORKER THREADS | |
Current thread_cache_size = 8 | |
Current threads_cached = 7 | |
Current threads_per_sec = 0 | |
Historic threads_per_sec = 0 | |
Your thread_cache_size is fine | |
MAX CONNECTIONS | |
Current max_connections = 151 | |
Current threads_connected = 1 | |
Historic max_used_connections = 141 | |
The number of used connections is 93% of the configured maximum. | |
You should raise max_connections | |
INNODB STATUS | |
Current InnoDB index space = 2 M | |
Current InnoDB data space = 7 M | |
Current InnoDB buffer pool free = 0 % | |
Current innodb_buffer_pool_size = 8 M | |
Depending on how much space your innodb indexes take up it may be safe | |
to increase this value to up to 2 / 3 of total system memory | |
MEMORY USAGE | |
Max Memory Ever Allocated : 1.40 G | |
Configured Max Per-thread Buffers : 1.20 G | |
Configured Max Global Buffers : 282 M | |
Configured Max Memory Limit : 1.48 G | |
Physical Memory : 1.96 G | |
Max memory limit seem to be within acceptable norms | |
KEY BUFFER | |
Current MyISAM index space = 19 M | |
Current key_buffer_size = 256 M | |
Key cache miss rate is 1 : 885 | |
Key buffer free ratio = 88 % | |
Your key_buffer_size seems to be fine | |
QUERY CACHE | |
Query cache is enabled | |
Current query_cache_size = 16 M | |
Current query_cache_used = 12 M | |
Current query_cache_limit = 2 M | |
Current Query cache Memory fill ratio = 81.08 % | |
Current query_cache_min_res_unit = 4 K | |
However, 329517 queries have been removed from the query cache due to lack of memory | |
Perhaps you should raise query_cache_size | |
MySQL won't cache query results that are larger than query_cache_limit in size | |
SORT OPERATIONS | |
Current sort_buffer_size = 2 M | |
Current read_rnd_buffer_size = 4 M | |
Sort buffer seems to be fine | |
JOINS | |
Current join_buffer_size = 1.00 M | |
You have had 8652 queries where a join could not use an index properly | |
You should enable "log-queries-not-using-indexes" | |
Then look for non indexed joins in the slow query log. | |
If you are unable to optimize your queries you may want to increase your | |
join_buffer_size to accommodate larger joins in one pass. | |
Note! This script will still suggest raising the join_buffer_size when | |
ANY joins not using indexes are found. | |
OPEN FILES LIMIT | |
Current open_files_limit = 1024 files | |
The open_files_limit should typically be set to at least 2x-3x | |
that of table_cache if you have heavy MyISAM usage. | |
Your open_files_limit value seems to be fine | |
TABLE CACHE | |
Current table_open_cache = 256 tables | |
Current table_definition_cache = 256 tables | |
You have a total of 940 tables | |
You have 256 open tables. | |
Current table_cache hit rate is 0% | |
, while 100% of your table cache is in use | |
You should probably increase your table_cache | |
You should probably increase your table_definition_cache value. | |
TEMP TABLES | |
Current max_heap_table_size = 16 M | |
Current tmp_table_size = 16 M | |
Of 118501 temp tables, 46% were created on disk | |
Perhaps you should increase your tmp_table_size and/or max_heap_table_size | |
to reduce the number of disk-based temporary tables | |
Note! BLOB and TEXT columns are not allow in memory tables. | |
If you are using these columns raising these values might not impact your | |
ratio of on disk temp tables. | |
TABLE SCANS | |
Current read_buffer_size = 1 M | |
Current table scan ratio = 38 : 1 | |
read_buffer_size seems to be fine | |
TABLE LOCKING | |
Current Lock Wait ratio = 1 : 484 | |
You may benefit from selective use of InnoDB. | |
If you have long running SELECT's against MyISAM tables and perform | |
frequent updates consider setting 'low_priority_updates=1' | |
If you have a high concurrency of inserts on Dynamic row-length tables | |
consider setting 'concurrent_insert=2'. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- | |
r b swpd free buff cache si so bi bo in cs us sy id wa | |
25 0 2992 380688 194656 519284 0 0 2 27 32 31 2 2 96 0 | |
20 0 2992 379344 194660 519396 0 0 0 72 142 113 38 63 0 0 | |
40 0 2992 378624 194660 519400 0 0 0 0 57 61 35 65 0 0 | |
40 0 2992 376096 194664 519440 0 0 0 32 142 116 11 89 0 0 | |
26 0 2992 375020 194664 519444 0 0 0 0 67 63 15 85 0 0 | |
19 0 2992 373540 194664 519444 0 0 0 0 139 113 25 75 0 0 | |
22 0 2992 371356 194664 519460 0 0 0 21 271 185 36 64 0 0 | |
23 0 2992 369620 194664 519460 0 0 0 0 163 114 23 77 0 0 | |
24 0 2992 367140 194668 519460 0 0 0 33 195 130 16 84 0 0 | |
38 0 2992 365912 194668 519460 0 0 0 1 220 87 27 73 0 0 | |
26 0 2992 365104 194668 519460 0 0 0 69 108 77 28 72 0 0 | |
16 0 2992 364368 194672 519460 0 0 0 84 64 64 30 70 0 0 | |
35 0 2992 362384 194672 519460 0 0 0 0 162 151 28 72 0 0 | |
33 0 2992 360152 194672 519460 0 0 0 0 172 138 34 66 0 0 | |
21 0 2992 358912 194676 519460 0 0 0 27 121 105 31 69 0 0 | |
35 1 2992 357936 194676 519460 0 0 0 8 113 111 47 53 0 0 | |
39 0 2992 355952 194676 519460 0 0 0 0 133 120 24 76 0 0 | |
25 0 2992 354456 194676 519460 0 0 0 0 132 108 23 77 0 0 | |
14 0 2992 352852 194680 519504 0 0 0 28 123 99 28 72 0 0 | |
10 0 2992 351124 194688 519504 0 0 0 32 183 137 33 67 0 0 | |
27 0 2992 349848 194688 519504 0 0 0 0 119 97 35 65 0 0 | |
18 0 2992 348508 194692 519500 0 0 0 41 137 140 41 59 0 0 | |
36 0 2992 346572 194692 519504 0 0 0 0 181 146 26 74 0 0 | |
38 0 2992 342472 194692 519500 0 0 0 5 295 257 26 74 0 0 | |
39 0 2992 340936 194696 519516 0 0 0 25 111 101 21 79 0 0 | |
26 0 2992 338968 194696 519504 0 0 0 0 132 119 20 80 0 0 | |
25 0 2992 336728 194700 519504 0 0 0 60 171 135 20 80 0 0 | |
34 0 2992 335084 194700 519504 0 0 0 0 134 111 16 84 0 0 | |
24 0 2992 331844 194700 519504 0 0 0 1 179 167 19 81 0 0 | |
16 0 2992 331100 194700 519504 0 0 0 1 56 51 26 74 0 0 | |
18 0 2992 329852 194704 519504 0 0 0 12 139 125 37 63 0 0 | |
15 0 2992 327628 194708 519504 0 0 0 39 159 147 26 74 0 0 | |
39 0 2992 326752 194708 519500 0 0 0 19 128 120 39 61 0 0 | |
35 0 2992 325860 194708 519504 0 0 0 0 74 84 39 61 0 0 | |
27 0 2992 324992 194708 519520 0 0 0 0 84 69 32 68 0 0 | |
25 0 2992 324340 194708 519500 0 0 0 29 80 69 33 67 0 0 | |
24 0 2992 322968 194708 519500 0 0 0 0 119 104 30 70 0 0 | |
39 0 2992 321976 194708 519500 0 0 0 31 56 54 25 75 0 0 | |
27 0 2992 319496 194712 519496 0 0 0 19 149 133 27 73 0 0 | |
48 0 2992 318876 194712 519500 0 0 0 0 48 51 38 62 0 0 | |
31 0 2992 317604 194712 519500 0 0 0 28 86 83 40 60 0 0 | |
29 0 2992 316224 194712 519496 0 0 0 0 80 69 28 72 0 0 | |
28 0 2992 314860 194712 519496 0 0 0 16 146 144 46 54 0 0 | |
24 0 2992 313868 194712 519496 0 0 0 0 138 122 52 48 0 0 | |
59 0 2992 312132 194716 519492 0 0 0 55 115 85 34 66 0 0 | |
24 0 2992 310636 194716 519496 0 0 0 0 100 87 22 78 0 0 | |
37 0 2992 309512 194716 519496 0 0 0 0 79 69 26 74 0 0 | |
16 0 2992 307932 194724 519496 0 0 0 5 99 87 25 75 0 0 | |
16 0 2992 307460 194724 519496 0 0 0 0 131 123 56 44 0 0 | |
19 0 2992 306096 194728 519496 0 0 0 11 93 82 35 65 0 0 | |
16 0 2992 304484 194728 519496 0 0 0 0 135 95 36 64 0 0 | |
22 0 2992 302996 194732 519500 0 0 0 15 115 93 26 74 0 0 | |
33 0 2992 302004 194732 519500 0 0 0 16 70 60 32 68 0 0 | |
24 0 2992 300764 194732 519600 0 0 0 0 113 90 35 65 0 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment