MySql Tuning Optimization Help
Hello all, could somebody help me to tuning mysql? What i have:
Vps with CentOS release 5.10 (Final) i686, 6GB Ram and 4 vCores of Intel(R) Xeon(R) CPU E31270 @ 3.40GHz
My tuning prime:
My my.cnf:
Thanks
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.36-cll i686
Uptime = 9 days 0 hrs 48 min 56 sec
Avg. qps = 10
Total Questions = 8039793
Threads Connected = 2
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.5/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 1134 out of 8039814 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.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 6
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 250
Current threads_connected = 2
Historic max_used_connections = 45
The number of used connections is 18% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 36 M
Current InnoDB data space = 37 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 : 547 M
Configured Max Per-thread Buffers : 2.57 G
Configured Max Global Buffers : 72 M
Configured Max Memory Limit : 2.64 G
Physical Memory : 6.00 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 40 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 22
Key buffer free ratio = 84 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 21 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 67.36 %
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 = 256 K
Current read_rnd_buffer_size = 8 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 5358 queries where a join could not use an index properly
You have had 4903 joins without keys that check for key usage after each row
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 = 13670 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 = 400 tables
You have a total of 2821 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 486591 temp tables, 30% 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 = 2 M
Current table scan ratio = 172 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 352
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=ALWAYS'.My my.cnf:
[myisamchk]
write_buffer=2M
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
[mysqld]
myisam_sort_buffer_size=16M
max_allowed_packet=1M
table_cache=256
local-infile=0
key_buffer=16M
thread_cache_size=8
query_cache_size=32M
innodb_buffer_pool_size=8M
skip-external-locking
max_user_connections=30
read_rnd_buffer_size=8M
wait_timeout=40
read_buffer_size=2M
server-id=1
sort_buffer_size=256K
open_files_limit=13670
max_connections=250
thread_concurrency=8
connect_timeout=10
innodb_file_per_table=1
default-storage-engine=MyISAM
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeoutThanks
-
Not sure if this will help you, this is how we have our my.cnf file setup our machines are very similar spec [mysqld] local-infile=0 query_cache_size=256M query_cache_limit=8M max_connections = 50 thread_cache_size=16 join_buffer_size=3M key_buffer_size=100M sort_buffer_size=4M read_buffer_size=4M read_rnd_buffer_size=8M myisam_sort_buffer_size=64M table_cache=2048 table_definition_cache=800 tmp_table_size = 256M max_heap_table_size = 256M low_priority_updates=1 concurrent_insert=ALWAYS slow_query_log = 1 slow_query_log_file=/var/log/my-slow.log long_query_time=3 open_files_limit=3628 [isamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer_size = 2M write_buffer_size = 2M [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer_size = 2M write_buffer_size = 2M0 -
Hello :) Feel free to update this thread with the outcome should you decide to implement the values suggested by other users. Thank you. 0 -
Hello again, @markvegas, thanks for your suggested values :) i liked it, @cpanelmichael. Im upgrading to mysql 5.6 but it is taking too much time :/ the upgrade has been running for the last 7 hours and still, i think it is at 35%... when the update completes i will apply the suggested values and see how it works and update with my results. Btw, i always see too much values of this on my server with top or with htop, is this normal? 2609 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.27 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 2734 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.17 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14706 mysql 20 0 869M 630M 8796 D 0.0 10.3 0:06.44 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14741 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:06.36 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 2601 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.28 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 15452 mysql 20 0 869M 630M 8796 D 0.0 10.3 0:05.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14754 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:05.84 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14673 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.54 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 30585 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.77 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 30757 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:01.12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14675 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:01.88 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 1575 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 407 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14708 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.16 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14674 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:02.06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 2647 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14696 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.91 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14704 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.34 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14676 mysql 20 0 869M 630M 8796 S 0.6 10.3 0:02.54 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14695 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14671 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.60 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14670 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.61 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14677 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:01.19 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14672 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.55 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14669 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.84 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14668 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14705 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14726 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14697 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14709 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14707 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 14717 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 3724 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 3977 mysql 20 0 869M 630M 8796 S 0.0 10.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql0 -
Do you receive the same result after restarting MySQL? Are those processes listed when using "ps"? You can run a command such as "mysqladmin processlist" to see which databases are actively using MySQL. Thank you. 0 -
The update just finished but now i could not start mysql with my old my.cnf values or @markvegas suggested values, im getting this errror: Starting MySQL...................... ERROR! The server quit without updating PID file. I deleted my.cnf and mysql can start, but i dont have any values now :/ [COLOR="silver">- - - Updated - - - After restarting mysql (with no my.cnf) i see this processes again: 5946 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:01.25 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6050 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.17 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6001 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.23 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6011 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5948 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5949 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5950 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5951 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5952 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5953 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5954 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5955 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5956 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5957 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5985 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5986 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5987 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5990 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5991 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5992 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5993 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5994 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5995 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 5996 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6015 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6029 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6224 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6301 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6308 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6310 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 6448 mysql 20 0 770M 444M 8184 S 0.0 7.2 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
And mysqladmin processlist:+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | 10 | eximstats | localhost | eximstats | Sleep | 8 | | | | 12 | DELAYED | localhost | eximstats | Delayed insert | 8 | Waiting for INSERT | | | 162 | DELAYED | localhost | eximstats | Delayed insert | 17 | Waiting for INSERT | | | 200 | root | localhost | | Query | 0 | init | show processlist | +-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+0 -
You can review the MySQL error log (/var/lib/mysql/$hostname.err) when you attempt to start MySQL with your existing /etc/my.cnf configuration to see what specific value is preventing it from starting. Thank you. 0 -
Is this value: 2014-03-19 12:43:21 5787 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache=2048' [COLOR="silver">- - - Updated - - - Already commented that value and mysql start without problems; But i still see this: 10146 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10225 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10224 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10094 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10095 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10096 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10097 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10098 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10099 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10100 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10101 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10102 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10103 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10120 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10121 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10122 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10132 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10133 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10134 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10135 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10136 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10137 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10138 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10172 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10187 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10208 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10223 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql 10248 mysql 20 0 801M 138M 7980 S 0.0 2.3 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql
and mysqladmin processlist:+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+ | 15 | eximstats | localhost | eximstats | Sleep | 7 | | | | 16 | DELAYED | localhost | eximstats | Delayed insert | 7 | Waiting for INSERT | | | 35 | DELAYED | localhost | eximstats | Delayed insert | 105 | Waiting for INSERT | | | 70 | root | localhost | | Query | 0 | init | show processlist | +----+-----------+-----------+-----------+----------------+------+--------------------+------------------+0 -
I have this "strange" problem, thats why i started to check mysql and my.cnf This problem started like 1 month ago when i noticed that one of our servers starting to have high load for no reason, we have some desks at my support office where i have a lot of displays and i have htop,top, mytop, whm and other tools open almost all time in all my servers so we can detect any problem before my clients complains or before they realized that something is wrong with the service (in case something happen), so we anticipate problems and we can face a lot of problems almost instantly, we like to give a premium service, if a bad script runs or a site is compromised we can identify and kill the script and fix the site almost instantly. So, like 1 month ago one of our servers starting to have high load for no reason, no bad scripts, no compromised sites and no mail spam or big querys on mysql, the server just start rising its load for no reason. we have like 70 small sites on the server, only one of them was having like 1000 visits per day and the other sites are really small, some of them uses wordpress but are really small sites. We started to use nginxcp and the server was stable for 1 week and then the high load start again, so we change to litespeed and move 4 sites (the most visited sites) to a new server and the server becomes stable a little high load but something within normal, 10 days passed without problems and yesterday at off peak hours the server started to have high load for almost 10 hours until i reboot it, then it becomes stable again and today i upgraded it to mysql 5.6 and applied the suggested values of @markvegas, everything was fine but a few minutes ago my server start rising its load apparently for no reason, at the moment of the high load i check the mysqladmin processlist and nothing strange in it: MySQL on localhost (5.6.16-log) up 0+05:57:27 [19:11:01] Queries: 14.0 qps: 0 Slow: 1.0 Se/In/Up/De(%): 00/00/00/00 qps now: 0 Slow qps: 0.0 Threads: 4 ( 3/ 5) 00/00/00/00 Key Efficiency: 99.2% Bps in/out: 0.0/ 3.5 Now in/out: 8.4/ 2.0k Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 10538 root localhost mysql 0 Query show full processlist 10546 roundcube localhost roundcube 3 Query UPDATE session SET changed='2014-03-19 20:10:58' WHERE sess_id='qlmbdfhovcnqrq4mej7nlh6pr2' 10430 eximstats localhost eximstats 8 Query update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=1 where smtp.transport_is_remote=1 and sends.user='cuplaper' and smtp.processed=0; /* Cpanel::Logd::geteximstats - bandwidth processing */ 15 eximstats localhost eximstats 10 Sleep 10371 DELAYED localhost eximstats 10 Delaye Waiting for INSERT 10536 DELAYED localhost eximstats 45 Delaye Waiting for INSERT
I killed all processes by user mysql and the server immediately becomes stable again... ATM of writing this i have this mysql processes running on the server:7501 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:10.59 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7562 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:02.20 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7592 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:02.12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7777 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:02.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7503 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7568 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:02.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7537 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.14 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7548 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7505 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7504 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7508 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7506 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7532 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7535 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7553 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.05 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7510 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7507 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7511 mysql 20 0 805M 146M 8324 S 0.7 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7533 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7512 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7509 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7536 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7534 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7547 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7549 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 7550 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 11067 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 11576 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid 11630 mysql 20 0 805M 146M 8324 S 0.0 2.4 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/$hostname.err --open-files-limit=3628 --pid-file=/var/lib/mysql/$hostname.pid
I think that thats is the origin of my load issues, there is something strange on it.0
Please sign in to leave a comment.
Comments
8 comments