Optimization for 4GB Server Dedicated CENTOS 6.5 Xeon 3450

[maisondasilva]

Could you help me with a good optimization?
Server Xeon 3450 4gb RAM

This is the actual my.cnf:
[mysqld]
innodb_file_per_table=1
open_files_limit=50000

mysqltunner:
 >>  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: 1G (Tables: 23008)
[--] Data in InnoDB tables: 5G (Tables: 12626)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 577)
[!!] Total fragmented tables: 231

-------- Performance Metrics -------------------------------------------------
[--] Up for: 52m 19s (310K q [99.009 qps], 11K conn, TX: 703M, RX: 42M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (15% of installed RAM)
[OK] Slow queries: 0% (0/310K)
[OK] Highest usage of available connections: 11% (18/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/471.1M
[OK] Key buffer hit rate: 99.9% (39M cached / 56K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26K sorts)
[!!] Joins performed without indexes: 1268
[OK] Temporary tables created on disk: 25% (14K on disk / 56K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 10% (400 open / 3K opened)
[OK] Open file limit used: 0% (431/50K)
[OK] Table locks acquired immediately: 99% (385K immediate / 386K locks)
[!!] InnoDB data size / buffer pool: 5.7G/128.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
    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)
    thread_cache_size (start at 4)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 5G)

 

[cPanelMichael]

Hello

The following thread is a good place to start when obtaining data for MySQL optimization:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

There is a variant to the MySQL tuner that is more suited towards newer versions of MySQL. In addition, try to let MySQL run at least 24 hours before using a tuner.

Thank you.

 

[thinkbot]

You have too little RAM memory to fit main buffers

replace your my.cnf with this

[mysqld]
skip-name-resolve

myisam_use_mmap=1

max_connections = 100
max_user_connections = 50

join_buffer_size=512K
sort_buffer_size=256K

table_open_cache = 5000
table_definition_cache = 3000
max_allowed_packet = 32M

thread_cache_size = 16

query_cache_type = 1
query_cache_size =50M
query_cache_limit = 1M

max_heap_table_size = 30M
tmp_table_size = 30M

key_buffer_size = 500M

innodb_buffer_pool_size = 1000M
innodb_stats_on_metadata=0

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1



This will make of course huge improvement, but it still won't fit whole innodb_buffer_pool_size in RAM, innodb buffer
Make sure to remove databases that are not used and rerun mysqltuner.pl again

And which tables are used more often, InnoDB or MyISAM ?

 

[maisondasilva]

Hi Thanks Help

My costumer use WordPress Joomla, more MyISAM
What ram for me? 8gb is good?

Process Manager
Mysql
CPU% 5%
Memory% 12%

Thanks
Maison

 

[thinkbot]

If your all innodb databases are not used, so they will probably fit in memory
MyiSAM tables all fits in buffers now, so they will work very fast

You should install munin plugin for WHM to have monitoring of server usage on graphs

please rerun mysqltuner.pl

 

[maisondasilva]

Hi Mysql Use 31,2% RAM 1,9% CPU is correct?

Thanks
Maison

 

[thinkbot]

You should install something more reliable for server monitoring, like munin plugin in WHM
Process Manager is very basic tool for showing server utilization

 

[maisondasilva]

I have 8gb installed on the server now, I need to change some more configuration below?

You have too little RAM memory to fit main buffers

replace your my.cnf with this

[mysqld]
skip-name-resolve

myisam_use_mmap=1

max_connections = 100
max_user_connections = 50

join_buffer_size=512K
sort_buffer_size=256K

table_open_cache = 5000
table_definition_cache = 3000
max_allowed_packet = 32M

thread_cache_size = 16

query_cache_type = 1
query_cache_size =50M
query_cache_limit = 1M

max_heap_table_size = 30M
tmp_table_size = 30M

key_buffer_size = 500M

innodb_buffer_pool_size = 1000M
innodb_stats_on_metadata=0

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1



This will make of course huge improvement, but it still won't fit whole innodb_buffer_pool_size in RAM, innodb buffer
Make sure to remove databases that are not used and rerun mysqltuner.pl again

And which tables are used more often, InnoDB or MyISAM ?

 

[thinkbot]

First please rerun mysqltuner.pl and post results here

 

[Julien Mary]

That changed the server from turtle style to blazing fast : THANKS