Optimization Request my.cnf for 4 core + 3gb Ram of Vps
Guide my.cnf for 4 core + 3gb Ram of Vps
I have SMF
150 Tables
550 mb Database of MYSQL
Please see my.cnf and guide for optimization
[mysqld]
wait_timeout=50
interactive_timeout=100
connect_timeout = 10
key_buffer_size=512M
query_cache_size=30M
query_cache_limit=1M
tmp_table_size=50M
table_cache = 2048
max_heap_table_size=50M
thread_cache_size=64
long_query_time = 5
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"
[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
-
run mysqltuner.pl and post results here 0 -
The following thread is a good place to start in order to obtain the statistics/reports to provide: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you. 0 -
How to run mysqltuner.pl 0 -
>> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: realinfo Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.0.96 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: ERROR 1109 (42S02) at line 1: Unknown table 'ENGINES' in information_schema Use of uninitialized value in concatenation (.) or string at ./mysqltuner.pl line 547, <> line 2 (#1) (W uninitialized) An undefined value was used as if it were already defined. It was interpreted as a "" or a 0, but maybe it was a mistake. To suppress this warning assign a defined value to your variables. To help you figure out what was undefined, perl tells you what operation you used the undefined value in. Note, however, that perl optimizes your program and the operation displayed in the warning may not necessarily appear literally in your program. For example, "that $foo" is usually optimized into "that " . $foo, and the warning will refer to the concatenation (.) operator, even though there is no . in your program. [--] Data in MyISAM tables: 468M (Tables: 111) [--] Data in InnoDB tables: 30M (Tables: 34) [--] Data in MEMORY tables: 124K (Tables: 1) [!!] BDB is enabled but isn't being used [!!] Total fragmented tables: 5 -------- Security Recommendations ------------------------------------------- ERROR 1142 (42000) at line 1: SELECT command denied to user 'realinfo'@'localhost' for table 'user' [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 3h 47m 56s (667K q [48.812 qps], 37K conn, TX: 3B, RX: 200M) [--] Reads / Writes: 60% / 40% [--] Total buffers: 852.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 1.1G (37% of installed RAM) [OK] Slow queries: 0% (1K/667K) [!!] Highest connection usage: 100% (101/100) [OK] Key buffer size / total MyISAM indexes: 712.0M/75.4M [OK] Key buffer hit rate: 100.0% (186M cached / 50K reads) [OK] Query cache efficiency: 48.0% (209K cached / 436K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 9% (1K temp sorts / 15K sorts) [OK] Temporary tables created on disk: 11% (581 on disk / 4K total) [OK] Thread cache hit rate: 99% (143 created / 37K connections) [!!] Table cache hit rate: 9% (1K open / 11K opened) [OK] Open file limit used: 13% (667/5K) [OK] Table locks acquired immediately: 99% (662K immediate / 662K locks) [!!] InnoDB buffer pool / data size: 8.0M/30.2M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-bdb to MySQL configuration to disable BDB 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 Reduce or eliminate persistent connections to reduce connection usage Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: max_connections (> 100) wait_timeout (< 90) interactive_timeout (< 90) table_cache (> 2500) innodb_buffer_pool_size (>= 30M)0 -
Try letting MySQL run for at least 24 hours before running the tuner to ensure the most accurate results. Thank you. 0 -
in general its good, except InnoDB buffer, replace my.cnf with [mysqld] # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql max_connections = 150 max_user_connections = 75 wait_timeout=50 interactive_timeout=100 connect_timeout = 10 key_buffer_size=200M query_cache_type = 1 query_cache_size = 30M query_cache_limit = 1M tmp_table_size=50M max_heap_table_size=50M table_cache = 1000 thread_cache_size=64 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time = 0.1 innodb_buffer_pool_size = 75M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid0
Please sign in to leave a comment.
Comments
7 comments