my.cnf optimization request :)

[rabee2006]

Hi,
i have a big problem with my mysql 5.5 load is up to 90% of cpu and i need some one to help me optimize my.cnf file

here is my VPS information:

Processor Information
Total processors: 3

Processor #1

    Vendor
        GenuineIntel

    Name
        Intel(R) Xeon(R) CPU E5506 @ 2.13GHz

    Speed
        2133.408 MHz

    Cache
        4096 KB

Processor #2

    Vendor
        GenuineIntel

    Name
        Intel(R) Xeon(R) CPU E5506 @ 2.13GHz

    Speed
        2133.408 MHz

    Cache
        4096 KB

Processor #3

    Vendor
        GenuineIntel

    Name
        Intel(R) Xeon(R) CPU E5506 @ 2.13GHz

    Speed
        2133.408 MHz

    Cache
        4096 KB


Memory Information

Memory: 4776936k/4907008k available (2577k kernel code, 121408k reserved, 1492k data, 208k init)


System Information

Linux 2.6.18-408.8.2.el5.lve0.8.61.3xen #1 SMP Wed Jul 11 06:58:44 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux


Physical Disks



Current Memory Usage

             total       used       free     shared    buffers     cached
Mem:       4898816    3222228    1676588          0     194456    2097112
-/+ buffers/cache:     930660    3968156
Swap:      1048568          0    1048568
Total:     5947384    3222228    2725156


Current Disk Usage

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             123G   72G   45G  62% /
tmpfs                 2.4G     0  2.4G   0% /dev/shm
/usr/tmpDSK           4.0G  154M  3.6G   5% /tmp

my.cnf right now is:

[mysqld]
log-slow-queries
max_connections=500
default-storage-engine=MyISAM
innodb_file_per_table=1
query_cache_size = 50M

any one can help me?

thanks and best regards

 

[SB-Nick]

Hi,

The my.cnf file isnt optimized at all.
Try downloading MySQLTuner from rackerhacker and try to adjust the config based on the settings provided by the script.

 

[rabee2006]

Hi,

The my.cnf file isnt optimized at all.
Try downloading MySQLTuner from rackerhacker and try to adjust the config based on the settings provided by the script.

thanks a lot for the replay, here is the result of mysqltuner what should i write at the my.cnf ?

perl mysqltuner.pl
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_CTYPE = "UTF-8",
	LANG = "en_US.utf-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3G (Tables: 8131)
[--] Data in InnoDB tables: 12M (Tables: 180)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 35)
[!!] Total fragmented tables: 809

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 20h 33m 21s (117M q [91.261 qps], 1M conn, TX: 584B, RX: 17B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.5G (32% of installed RAM)
[OK] Slow queries: 0% (1K/117M)
[!!] Highest connection usage: 100%  (501/500)
[!!] Key buffer size / total MyISAM indexes: 8.0M/615.5M
[!!] Key buffer hit rate: 86.0% (298B cached / 41B reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (60K temp sorts / 16M sorts)
[!!] Joins performed without indexes: 192849
[!!] Temporary tables created on disk: 35% (7M on disk / 20M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 6M opened)
[OK] Open file limit used: 26% (662/2K)
[OK] Table locks acquired immediately: 99% (141M immediate / 141M locks)
[OK] InnoDB data size / buffer pool: 12.6M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 500)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    key_buffer_size (> 615.5M)
    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_cache (> 400)

thanks for your help

 

[SB-Nick]

Hey,

The Recommendation are pretty much self explanatory, try by adding the following settings to /etc/my.cnf, restart the mysql daemon after,

thread_cache_size=4
wait_timeout = 60
interactive_timeout = 60
key_buffer_size = 256M
table_cache=1024
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
join_buffer_size=2M
tmp_table_size=128M
sort_buffer=2M

- Start a "screen" over SSH and as root run "mysqlcheck --optimize -A"
- Go to Home »Server Status »Service Status and see if you are using Swap.

I would also hire a developer and pass these recommendations so he can tune up your website code,

- Reduce or eliminate persistent connections to reduce connection usage
- Adjust your join queries to always utilize indexes
- Reduce your SELECT DISTINCT queries without LIMIT clauses