my.cnf optimization request

[johnleenas]

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:

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

[email protected] [~]#

mysqltuner

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)

 

[newmanyak]

pls put here pico /etc/my.cnf

 

[johnleenas]

my.cnf right now is:

[mysqld]
thread_cache_size=4
table_cache=512
local-infile=0
innodb_file_per_table=1
query_cache_limit=1M
max_connections=500
key_buffer_size=16M
join_buffer_size=1M
query_cache_type=1
query_cache_size=32M
max_tmp_tables=1
default-storage-engine=MyISAM

 

[newmanyak]

try this

[mysqld]
skip-name-resolve
thread_cache_size=64
table_cache=2048
local-infile=0
innodb_file_per_table=1
query_cache_limit=1M
max_connections=300
key_buffer_size=256M
join_buffer_size=1M
query_cache_type=1
query_cache_size=64M
max_tmp_tables=1
default-storage-engine=MyISAM