mysql optimization suggestions please

[pes]

Hello, I'm receiving an error from tailwatchd in regards to eximstats

"The chkservd sub-process with pid 28164 ran for 301 seconds. This sub-process was terminated when it exceeded the time allowed between checks, which is 300 seconds. To determin why, you can check /var/log/chkservd.log and /usr/local/cpanel/logs/tailwatchd_log"

And see this is a somewhat common problem with sql databases not being optimized. I'd like to optimize my db but I'm not sure of my settings.

I run
mysqltuner/mysqltuner.plom [/usr/local/cpanel/logs]# /usr/local/cpanel/3rdparty/

>> MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.65-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 13M (Tables: 44)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 41d 22h 38m 34s (12M q [3.352 qps], 182K conn, TX: 75B, RX: 1B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (14% of installed RAM)
[OK] Slow queries: 0% (1K/12M)
[OK] Highest usage of available connections: 76% (115/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.2M
[OK] Key buffer hit rate: 99.9% (178M cached / 196K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (19 temp sorts / 672K sorts)
[!!] Temporary tables created on disk: 44% (295K on disk / 658K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 42K opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

And my root.cnf is
[email protected] [/usr/local/cpanel/logs]# less /etc/my.cnf

[mysqld]
innodb_file_per_table=1
local-infile=0
/etc/my.cnf (END)



Can someone please help me with the settings in /etc/my.cnf ? I think I should edit it to be:
query_cache_size = 8M
tmp_table_size = 16M
max_heap_table_size =16M
thread_cache_size = 4
table_cache = 64


I've also edited my hard/soft limits in the past in /etc/security/limits.conf to
* root hard nofile 65536
* root soft nofile 65536

as I've had fork shell errors. (I'm not sure if this is related).

I'm running a VPS with 4GB ram CENTOS 6.3 x86_64 vmware – bass WHM 11.34.0 (build 9) Load Averages: 0.12 0.11 0.04

Thanks for your help!

cheers,
Scott

 

[tank]

Scott,
Here is my setup and it has been working for rather nicely so far.

[mysqld]
set-variable = max_connections=500
safe-show-database
query_cache_size=128M
tmp_table_size=32M
max_heap_table=32M
thread_cache_size=4
table_open_cache=10K
max_allowed_packet=32M
local-infile=0
long_query_time = 4
slow_query_log
key_buffer_size=48M
read_buffer_size=128K
join_buffer_size=128K

I would recommend watching this video:
MySQL OptimizationcPanel Videos | cPanel Videos

They make good recommendations and give you knowledge on what each of the variables do.

 

[sahostking]

I completely forgot about mysqltuner. Thanks for this and the links to docs. Will be using these.

 

[JaredR.]

One thing I would like to mention is that running mysqltuner.pl is not necessarily a one-step process. You need to run it, make the suggested changes, restart MySQL, then wait at least a day or two, and then run mysqltuner.pl again. Gradually, this process will shape and adjust your MySQL configuration to fit your needs. It can be a slow, tedious process, but that is true of many aspects of server administration.

mysqltuner.pl is third party, so it is not our product, but we provide a copy of it as a courtesy at /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl .