Where to start?
I've notice some up's and down's in the load average but I con't find anything strange in the logs!
This is a 768 linode wgere I run a vB+vBseo board 250/300 users online.
I don'd find any relations between the load jumps and any other processes like backups, mysqldump, most users onlie, etc.
I use nginx proxying to apache and mysql:
Timeout 50
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 3
MaxSpareServers 3
MaxClients 25
MaxRequestsPerChild 100
Mysql:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /home/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
bind-address = 127.0.0.1
myisam-recover = BACKUP
skip-innodb
back_log = 50
max_connections = 50
keybuffersize = 64M
myisamsortbuffer_size = 64M
joinbuffersize = 1M
readbuffersize = 1M
sortbuffersize = 2M
tableopencache = 16000
tabledefinitioncache = 8000
threadcachesize = 144
wait_timeout = 3600
connect_timeout = 10
tmptablesize = 32M
maxheaptable_size = 32M
maxallowedpacket = 32M
netbufferlength = 16384
maxconnecterrors = 100000
thread_concurrency = 4
concurrent_insert = 2
tablelockwait_timeout = 30
readrndbuffer_size = 786432
bulkinsertbuffer_size = 8M
querycachelimit = 1M
querycachesize = 48M
querycachetype = 1
querypreallocsize = 262144
queryallocblock_size = 65536
transactionallocblock_size = 8192
transactionpreallocsize = 4096
default-storage-engine = MyISAM
maxwritelock_count = 4
slowquerylog = 1
slowquerylog_file = /var/log/mysql/mysql-slow.log
longquerytime = 3
log-error = /var/log/mysql/mysql-error.log
lowpriorityupdates=1
[mysqld_safe]
nice = -5
openfileslimit = 8192
[mysqldump]
quick
maxallowedpacket = 16M
[myisamchk]
keybuffersize = 64M
sortbuffersize = 16M
readbuffersize = 16M
writebuffersize = 16M
[mysqlhotcopy]
interactive-timeout
Where and what should I start looking?
Thanks
~~![](<URL url=)http://img816.imageshack.us/img816/4864/loadaverage.png
~~![](<URL url=)
![](~~~~
9 Replies
Theres only one spike in slow logs in it's default configuration:
![](
Wast in the cache. Here the new one's:
~~![](<URL url=)http://img24.imageshack.us/img24/3882/mysqlqueries.png
~~![](<URL url=)
![](~~~~
Here is mysqltuner results:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 236M (Tables: 788)
[--] Data in MEMORY tables: 1M (Tables: 3)
[!!] Total fragmented tables: 17
-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 20h 46m 5s (8M q [7.337 qps], 572K conn, TX: 2B, RX: 2B)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 144.0M global + 4.9M per thread (50 max threads)
[OK] Maximum possible memory usage: 390.9M (52% of installed RAM)
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 22% (11/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/51.0M
[OK] Key buffer hit rate: 99.5% (167M cached / 796K reads)
[OK] Query cache efficiency: 64.1% (3M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 712K sorts)
[OK] Temporary tables created on disk: 16% (29K on disk / 182K total)
[OK] Thread cache hit rate: 99% (11 created / 572K connections)
[!!] Table cache hit rate: 0% (186 open / 18K opened)
[OK] Open file limit used: 1% (324/32K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 16000)
… and tuning-primer:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.37-1ubuntu5.4 i486
Uptime = 12 days 20 hrs 47 min 38 sec
Avg. qps = 7
Total Questions = 8156558
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 NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 8156579 that take longer than 10.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 = 144
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 50
Current threads_connected = 1
Historic max_used_connections = 11
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 166 M
Configured Max Per-thread Buffers : 246 M
Configured Max Global Buffers : 112 M
Configured Max Memory Limit : 358 M
Physical Memory : 751 M
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 51 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 209
Key buffer free ratio = 67 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 48 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 41.39 %
Current query_cache_min_res_unit = 4 K
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 = 768 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 1.00 M
You have had 374 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 = 32060 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 = 16000 tables
Current table_definition_cache = 8000 tables
You have a total of 814 tables
You have 857 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 153530 temp tables, 16% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 189 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 13844
Your table locking seems to be fine
Uncomment these lines
slowquerylog = 1
slowquerylog_file = /var/log/mysql/mysql-slow.log
longquerytime = 3
and add
logqueriesnotusingindexes=1
Let it run for a couple of days then check the slow query log again.
Also set up a cron that runs
mysqlcheck -A -o -a -u
where mysql user is a user that has access to the databases. It'll analyse and optimise all databases.
Here are the new results …
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37-1ubuntu5.4-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 249M (Tables: 989)
[--] Data in MEMORY tables: 1M (Tables: 3)
[OK] Total fragmented tables: 0
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 14h 19m 42s (1M q [10.609 qps], 99K conn, TX: 2B, RX: 515M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 144.0M global + 4.9M per thread (50 max threads)
[OK] Maximum possible memory usage: 390.9M (52% of installed RAM)
[!!] Slow queries: 7% (110K/1M)
[OK] Highest usage of available connections: 26% (13/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/52.1M
[OK] Key buffer hit rate: 99.6% (32M cached / 131K reads)
[OK] Query cache efficiency: 66.0% (734K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 113K sorts)
[OK] Temporary tables created on disk: 14% (4K on disk / 27K total)
[OK] Thread cache hit rate: 99% (13 created / 99K connections)
[!!] Table cache hit rate: 1% (91 open / 6K opened)
[OK] Open file limit used: 0% (137/32K)
[OK] Table locks acquired immediately: 99% (966K immediate / 967K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 16000)</major@mhtx.net>
~~![](<URL url=)
~~![](<URL url=)http://img26.imageshack.us/img26/3488/mysqlthreads.png
~~![](<URL url=)
![](~~