Help to optimize my.cnf file

[Smaily]

Total processors: 8 (2xQuad core Intel(R) Xeon(R) CPU E5405 @ 2.00GHz)
RAM: 32GB (16x2GB)
Interface is running bond mode 6.

Current,
/etc/my.cnf

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 768M
max_allowed_packet = 64M
table_open_cache = 5096
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 1500
log-slow-queries
local-infile=0
innodb_file_per_table=1

server-id       = 1

open_files_limit=46924
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Server used as webservice for domains, apache, mysql, mail.

 

[thinkbot]

run mysqltuner and show the results

 

[Smaily]

run mysqltuner and show the results

[email protected] [~]# cd /usr/local/cpanel/3rdparty/mysqltuner/
[email protected] [/usr/local/cpanel/3rdparty/mysqltuner]# ./mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at ./mysqltun                                                                                        er.pl line 148.

 >>  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.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 72M (Tables: 480)
[--] Data in InnoDB tables: 27M (Tables: 383)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 8)
[!!] Total fragmented tables: 50

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 6h 51m 12s (3M q [8.244 qps], 198K conn, TX: 5B, RX: 383M)
[--] Reads / Writes: 53% / 47%
[--] Total buffers: 1.0G global + 24.4M per thread (1500 max threads)
[!!] Maximum possible memory usage: 36.7G (117% of installed RAM)
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 0% (10/1500)
[OK] Key buffer size / total MyISAM indexes: 768.0M/16.1M
[OK] Key buffer hit rate: 99.8% (2M cached / 4K reads)
[OK] Query cache efficiency: 76.8% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 128K sorts)
[!!] Temporary tables created on disk: 41% (14K on disk / 36K total)
[OK] Thread cache hit rate: 99% (10 created / 198K connections)
[OK] Table cache hit rate: 36% (966 open / 2K opened)
[OK] Open file limit used: 2% (1K/46K)
[OK] Table locks acquired immediately: 99% (935K immediate / 936K locks)
[OK] InnoDB data size / buffer pool: 28.0M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

[email protected] [/usr/local/cpanel/3rdparty/mysqltuner]#

 

[thinkbot]

its good in general, you can lower some values, becouse of too big max RAM usage in case it fills all connections, so

max_connections = 300
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 8M

add this
slow_query_log_file=mysql-slow.log
long_query_time=0.1

this can be removed, not used anymore
thread_concurrency = 8



you barely have any queries, 8 qps very small
[!!] Temporary tables created on disk: 41% (14K on disk / 36K total)
but those quries are not much optimized, they use temporary tables on disk

 

[cPanelMichael]

Hello

The following thread is also useful for gathering information to provide here:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Be sure to update us after making any recommended changes to let us know the result.

Thank you.

 

[Smaily]

With updated suggested configuration,

# ./mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at ./mysqltun                                                                                        er.pl line 148.

 >>  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.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 83M (Tables: 481)
[--] Data in InnoDB tables: 124M (Tables: 405)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 248K (Tables: 8)
[!!] Total fragmented tables: 58

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 42m 7s (1M q [14.171 qps], 83K conn, TX: 2B, RX: 254M)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 1.0G global + 8.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 3.6G (11% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 3% (9/300)
[OK] Key buffer size / total MyISAM indexes: 768.0M/18.0M
[OK] Key buffer hit rate: 99.8% (1M cached / 2K reads)
[OK] Query cache efficiency: 75.0% (767K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 82K sorts)
[!!] Temporary tables created on disk: 44% (6K on disk / 14K total)
[OK] Thread cache hit rate: 99% (9 created / 83K connections)
[OK] Table cache hit rate: 86% (977 open / 1K opened)
[OK] Open file limit used: 2% (1K/46K)
[OK] Table locks acquired immediately: 99% (486K immediate / 486K locks)
[OK] InnoDB data size / buffer pool: 124.7M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

 

[thinkbot]

good, you can add there also

innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2


since your innodb data is growing

about temporary tables created on disk, you would have to check and optimize queries that use temp tables on disk