texas90

Member
Jun 10, 2014
22
0
1
cPanel Access Level
Root Administrator
My server load is going very high due to to mysql. I have cloudlinux already installed. But it isn't helping much in case of mysql.
Here is my /etc/my.cnf

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=50000
 

texas90

Member
Jun 10, 2014
22
0
1
cPanel Access Level
Root Administrator
Please have a look at this and suggest me how to bring the server load down. I had added the server name in the thread title so that you could better help me. My server is dual xeon 5420

[email protected] [/]# perl mysqltuner.pl
>> MySQLTuner 1.5.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 9G (Tables: 7613)
[--] Data in InnoDB tables: 952M (Tables: 6438)
[--] Data in MEMORY tables: 0B (Tables: 40)
[!!] Total fragmented tables: 746

-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User '[email protected]' has user name as password.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 7h 20m 19s (114M q [250.309 qps], 4M conn, TX: 266B, RX: 28B)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 544.8M (3.42% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (3.67% of installed RAM)
[OK] Slow queries: 0% (703/114M)
[!!] Highest connection usage: 90% (137/151)
[OK] Aborted connections: 0.01% (564/4319956)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (26K temp sorts / 10M sorts)
[!!] Joins performed without indexes: 424659
[!!] Temporary tables created on disk: 61% (3M on disk / 5M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 26M opened)
[OK] Open file limit used: 0% (449/50K)
[OK] Table locks acquired immediately: 99% (99M immediate / 99M locks)

-------- MyISAM Metrics -----------------------------------------------------
[OK] Key buffer used: 99.2% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.5G
[OK] Read Key buffer hit rate: 98.3% (1B cached / 17M reads)
[!!] Write Key buffer hit rate: 73.6% (52M cached / 13M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/953.0M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (8192 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 96.72% (2082148445 hits/ 2152841006 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1886234 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for [email protected]% to [email protected]
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
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_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (50000) variable
should be greater that table_open_cache ( 400)
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 952M) if possible.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463
Hello :)

I've moved this thread to our "Optimization" forum where you may receive more user-feedback to this type of question.

Thank you.