Skip to content

Instantly share code, notes, and snippets.

@mattmcmanus

mattmcmanus/my.cnf

Created April 11, 2011 14:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mattmcmanus/913565 to your computer and use it in GitHub Desktop.
Save mattmcmanus/913565 to your computer and use it in GitHub Desktop.
Download ZIP
Output from the tuning-primer.sh script
Raw
my.cnf
[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
Raw
sar
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
Raw
tuning-primer.log
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'.
Raw
vmstat 3
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