dos_santos_rj

Member
Apr 12, 2012
9
0
51
cPanel Access Level
Root Administrator
Hello

I need help to optimize MySQL

I have a dedicate server
CPU GenuineIntel, Intel(R) Xeon(R)CPU L5630 @ 2.13GHz
Version Parallels Plesk Panel v10.1.1_build1010110120.18 os_CentOS 5
OS Linux 2.6.18-028stab099.3
2Gb Memory

my.cnf

Code:
[mysqld]
query-cache-type = 1
query-cache-size = 8M
tmp_table_size=64M
key_buffer_size = 256M
innodb_buffer_pool_size=2M
join_buffer_size=1M
max_heap_table_size=32M
thread_cache_size=24


set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# To enable the InnoDB Plugin, uncomment the 2 next lines
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so

# To enable InnoDB-related INFORMATION_SCHEMA tables
# Join the following options to above directive
  ;innodb_trx=ha_innodb_plugin.so
  ;innodb_locks=ha_innodb_plugin.so
  ;innodb_cmp=ha_innodb_plugin.so
  ;innodb_cmp_reset=ha_innodb_plugin.so
  ;innodb_cmpmem=ha_innodb_plugin.so
  ;innodb_cmpmem_reset=ha_innodb_plugin.so

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

Code:
 >>  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.1.54
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 614M (Tables: 418)
[--] Data in InnoDB tables: 2M (Tables: 178)
[!!] Total fragmented tables: 179

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 36s (48K q [121.841 qps], 222 conn, TX: 132M, RX: 5M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 299.0M global + 3.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 846.4M (28% of installed RAM)
[OK] Slow queries: 0% (0/48K)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 256.0M/233.9M
[OK] Key buffer hit rate: 99.8% (3M cached / 7K reads)
[OK] Query cache efficiency: 65.5% (28K cached / 43K selects)
[!!] Query cache prunes per day: 1551709
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 332 sorts)
[!!] Joins performed without indexes: 5
[!!] Temporary tables created on disk: 47% (3K on disk / 8K total)
[OK] Thread cache hit rate: 95% (11 created / 222 connections)
[!!] Table cache hit rate: 19% (64 open / 330 opened)
[OK] Open file limit used: 10% (104/1K)
[OK] Table locks acquired immediately: 100% (15K immediate / 15K locks)
[!!] InnoDB data size / buffer pool: 3.0M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 32M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 2M)