Hi,
i have a big problem with my mysql 5.5 load is up to 95% of cpu and i need some one to help me optimize my.cnf file
here is my VPS information:
root@vn [~]#
mysqltuner
i have a big problem with my mysql 5.5 load is up to 95% of cpu and i need some one to help me optimize my.cnf file
here is my VPS information:
Code:
Total processors: 4
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Speed
2400.085 MHz
Cache
12288 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Speed
2400.085 MHz
Cache
12288 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Speed
2400.085 MHz
Cache
12288 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Speed
2400.085 MHz
Cache
12288 KB
Current Memory Usage
total used free shared buffers cached
Mem: 1938900 1079784 859116 0 39116 658052
-/+ buffers/cache: 382616 1556284
Swap: 4161528 0 4161528
Total: 6100428 1079784 5020644
mysqltuner
Code:
perl mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
>> 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.68-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 190M (Tables: 428)
[--] Data in InnoDB tables: 4M (Tables: 26)
[--] Data in MEMORY tables: 0B (Tables: 24)
[!!] Total fragmented tables: 3
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 32m 42s (65K q [33.243 qps], 3K conn, TX: 93M, RX: 17M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 74.0M global + 3.6M per thread (500 max threads)
[!!] Maximum possible memory usage: 1.8G (97% of installed RAM)
[OK] Slow queries: 0% (0/65K)
[OK] Highest usage of available connections: 7% (39/500)
[!!] Key buffer size / total MyISAM indexes: 16.0M/26.0M
[!!] Key buffer hit rate: 85.8% (226K cached / 32K reads)
[OK] Query cache efficiency: 54.2% (23K cached / 42K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 310 sorts)
[OK] Temporary tables created on disk: 25% (150 on disk / 596 total)
[OK] Thread cache hit rate: 91% (300 created / 3K connections)
[OK] Table cache hit rate: 44% (501 open / 1K opened)
[OK] Open file limit used: 36% (902/2K)
[OK] Table locks acquired immediately: 97% (24K immediate / 25K locks)
[OK] InnoDB data size / buffer pool: 4.6M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size (> 26.0M)