MySQL optimize help needed

[koolaquarian]

Hello Everyone,

i dont know much about optimizing mysql.

I need a little help to edit my.cnf i hired 2 different people but none of them has still solved my problem

I have a dedicated server with 4gb Ram :

Total processors: 2

Processor #1

    Vendor
        GenuineIntel

    Name
        Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz

    Speed
        1800.000 MHz

    Cache
        1024 KB

Processor #2

    Vendor
        GenuineIntel

    Name
        Intel(R) Pentium(R) Dual CPU E2160 @ 1.80GHz

    Speed
        1800.000 MHz

    Cache
        1024 KB

my.cnf that i have now is :

[mysqld]
max_connections = 200
#log-slow-queries
#safe-show-database
#port            = 3306
#socket          = /var/lib/mysql/mysql.sock
#skip-locking
table_cache = 12000
read_buffer_size = 8M
skip-networking
skip-federated
#log-bin=mysql-bin
thread_cache_size = 12M
#open_files_limit = 8192
max_allowed_packet = 12M
local-infile=0
#max_user_connections = 5
query_cache_size = 16M
query_cache_type = 1
key_buffer_size = 256M
#query_cache_min_res_unit = 2M

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

MYSQLTUNER RESULTS :

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 390M (Tables: 390)
[--] Data in InnoDB tables: 512K (Tables: 32)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 2)
[!!] Total fragmented tables: 52

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 31m 40s (5M q [33.055 qps], 129K conn, TX: 32B, RX: 459M)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 432.0M global + 10.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 2.5G (66% of installed RAM)
[OK] Slow queries: 0% (0/5M)
[OK] Highest usage of available connections: 23% (46/200)
[OK] Key buffer size / total MyISAM indexes: 256.0M/230.7M
[OK] Key buffer hit rate: 100.0% (58M cached / 22K reads)
[OK] Query cache efficiency: 89.3% (4M cached / 4M selects)
[!!] Query cache prunes per day: 63622
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 123K sorts)
[!!] Joins performed without indexes: 81066
[!!] Temporary tables created on disk: 28% (44K on disk / 156K total)
[OK] Thread cache hit rate: 99% (46 created / 129K connections)
[OK] Table cache hit rate: 96% (538 open / 558 opened)
[OK] Open file limit used: 3% (901/24K)
[OK] Table locks acquired immediately: 99% (751K immediate / 751K locks)
[OK] InnoDB data size / buffer pool: 512.0K/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    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
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

Load averages goes up to 5.84 1.68 1.88
and sometimes more also..


Please help me out

Thanks....

 

[-GR-]

Are you sure mysql is what is causing your load to be high? Only your one minute load average is high, your 5 minute and 15 minute load averages are not bad.

The only things I would change right now on your my.cnf would be:

query_cache_size = 128M
query_cache_limit = 4M
tmp_table_size = 128M
max_heap_table_size = 128M

Judging by the joins performed without indexes you are running some addons with your software that are using joins without indexes. Finding and adding indexes to the bad queries would help with that or disable the mod entirely.

We use Invision Power Board on our main site and the classifieds add on does a lot of joins without indexes.

 

[cPanelTristan]

Also, it is table_open_cache not table_cache under MySQL 5.1 and higher. Next, remove everything with a # on it entirely. It's just filling up the /etc/my.cnf for no reason.

You might want to enable the slow query logging for MySQL 5.5:

MySQL :: MySQL 5.5 Reference Manual :: 5.2.5 The Slow Query Log

This would be slow_query_log in /etc/my.cnf file.

 

[koolaquarian]

Actually I dont know anything about servers or linux.... i know just a bit by searching and stuff. i hired two diff people to work on it but they dont have time and dont reply me at all. so i am trying to do it myself..


can you tell me what do i edit or remove??

Also, it is table_open_cache not table_cache under MySQL 5.1 and higher. Next, remove everything with a # on it entirely. It's just filling up the /etc/my.cnf for no reason.

You might want to enable the slow query logging for MySQL 5.5:

MySQL :: MySQL 5.5 Reference Manual :: 5.2.5 The Slow Query Log

This would be slow_query_log in /etc/my.cnf file.

 

[cPanelTristan]

[mysqld]
max_connections = 200
table_open_cache = 12000
read_buffer_size = 8M
skip-networking
skip-federated
slow_query_log
thread_cache_size = 12M
max_allowed_packet = 12M
local-infile=0
query_cache_size = 128M
query_cache_limit = 4M
query_cache_type = 1
key_buffer_size = 256M
tmp_table_size = 128M
max_heap_table_size = 128M

[mysqldump]
max_allowed_packet = 64M

[mysql]
no-auto-rehash

[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

I don't really like skip-networking being in there. I've seen issues with that setting for certain functions working.