Optimization for 4GB Server Dedicated CENTOS 6.5 Xeon 3450
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
>> 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)
-
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. 0 -
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 ? 0 -
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 0 -
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 0 -
Hi Mysql Use 31,2% RAM 1,9% CPU is correct? Thanks Maison 0 -
You should install something more reliable for server monitoring, like munin plugin in WHM Process Manager is very basic tool for showing server utilization 0 -
I have 8gb installed on the server now, I need to change some more configuration below? [quote="thinkbot, post: 1571582">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 ? 0 -
First please rerun mysqltuner.pl and post results here 0 -
That changed the server from turtle style to blazing fast : THANKS 0
Please sign in to leave a comment.
Comments
9 comments