MySql Tuning Optimization Help

tui

Well-Known Member
Jun 15, 2007
84
5
58
Mexico
cPanel Access Level
Root Administrator
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:

Code:
        -- 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:

Code:
[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-timeout
Thanks
 

markvegas

Member
Mar 18, 2014
14
1
3
cPanel Access Level
Root Administrator
Not sure if this will help you, this is how we have our my.cnf file setup our machines are very similar spec


Code:
[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 = 2M
 

tui

Well-Known Member
Jun 15, 2007
84
5
58
Mexico
cPanel Access Level
Root Administrator
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?

Code:
 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=mysql
 

cPanelMichael

Technical Support Community Manager
Staff member
Apr 11, 2011
47,911
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
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.
 

tui

Well-Known Member
Jun 15, 2007
84
5
58
Mexico
cPanel Access Level
Root Administrator
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 :/

- - - Updated - - -

After restarting mysql (with no my.cnf) i see this processes again:

Code:
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:
Code:
+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| 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 |
+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
 
Last edited:

cPanelMichael

Technical Support Community Manager
Staff member
Apr 11, 2011
47,911
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
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.
 

tui

Well-Known Member
Jun 15, 2007
84
5
58
Mexico
cPanel Access Level
Root Administrator
Is this value:

2014-03-19 12:43:21 5787 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache=2048'

- - - Updated - - -

Already commented that value and mysql start without problems;

But i still see this:

Code:
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:

Code:
+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| 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 |
+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
 

tui

Well-Known Member
Jun 15, 2007
84
5
58
Mexico
cPanel Access Level
Root Administrator
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:

Code:
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:

Code:
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.