MySQL optimization

[mehnihma]

Hi
Can you help me optimize my database:

now I have :

[mysqld]
innodb_file_per_table=1
open_files_limit=5294


mysqltuner:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 50)
[--] Data in InnoDB tables: 64M (Tables: 1364)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 24

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10d 14h 31m 35s (7M q [8.648 qps], 149K conn, TX: 20B, RX: 1B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (3% of installed RAM)
[OK] Slow queries: 0% (32/7M)
[OK] Highest usage of available connections: 11% (18/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.2M
[OK] Key buffer hit rate: 98.2% (460K cached / 8K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (223 temp sorts / 952K sorts)
[!!] Joins performed without indexes: 2998
[!!] Temporary tables created on disk: 45% (721K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 126K opened)
[OK] Open file limit used: 1% (74/5K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[OK] InnoDB data size / buffer pool: 64.6M/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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    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

- - - Updated - - -

Would this be ok?

[mysqld]
innodb_file_per_table=1
open_files_limit=5294
log-slow-queries=/var/lib/mysql/slow.log
interactive_timeout=300
log-slow-queries=/var/lib/mysql/slow.log
query_cache_size=50M
skip-bdb
query_cache_type=1
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=4
table_cache=600
wait_timeout=300

 

[mehnihma]

When I put this in my.conf


[mysqld]
innodb_file_per_table=1
open_files_limit=5294
log-slow-queries=/var/lib/mysql/slow.log
interactive_timeout=300
query_cache_size=50M
skip-bdb
query_cache_type=1
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=4
table_cache=600
wait_timeout=300

I get:
Waiting for mysql to restart....................................................................finished.


mysql has failed, please contact the sysadmin (result was "mysql is not running").

Can you help me?

 

[mehnihma]

This is making problems:
skip-bdb


Now I have this:
[mysqld]
innodb_file_per_table=1
open_files_limit=6020
log-slow-queries=/var/lib/mysql/slow.log
interactive_timeout=300
query_cache_size=50M
query_cache_type=1
query_cache_type=1
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=4
table_cache=600
wait_timeout=300
query_cache_limit=64M


And the result:
>> MySQLTuner 1.2.0_1 - 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.35-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 50)
[--] Data in InnoDB tables: 64M (Tables: 1364)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 24

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5s (188 q [37.600 qps], 10 conn, TX: 2M, RX: 86K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 502.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 917.2M (5% of installed RAM)
[OK] Slow queries: 0% (0/188)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.2M
[OK] Query cache efficiency: 24.4% (38 cached / 156 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 7% (1 temp sorts / 13 sorts)
[!!] Temporary tables created on disk: 31% (6 on disk / 19 total)
[OK] Thread cache hit rate: 60% (4 created / 10 connections)
[OK] Table cache hit rate: 84% (39 open / 46 opened)
[OK] Open file limit used: 0% (21/6K)
[OK] Table locks acquired immediately: 100% (167 immediate / 167 locks)
[OK] InnoDB data size / buffer pool: 64.6M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

 

[thinkbot]

it's good configuration, don't worry

 

[cPanelMichael]

This is making problems:
skip-bdb

I just wanted to note that this option is not available in your version of MySQL (5.5) in-case others come across this thread with a similar error message.

Thank you.