My.cnf config
Hello,
I have Xeon 1230 v2 with 8GB of Ram, we are having Wordpress having about 800MB Database
my.cnf configuration is :
PHP handler is fcgi and we do use cache plugin and eaccelerator, also nginxcp is running. Also we are using MariaDB worth checking root@server [/usr/local/src/publicnginx]# free -m total used free shared buffers cached Mem: 7958 7869 89 0 415 4099 -/+ buffers/cache: 3354 4603 Swap: 2047 48 1998 root@server [/usr/local/src/publicnginx]# Support suggesting to add more Ram. please help me figuring some value at my.cnf
[mysqld]
back_log=300
local-infile=0
datadir=/var/lib/mysql
long_query_time=2
;log_output=file
slow_query_log
slow_query_log_file=/var/log/mysql-slow-queries.log
;log_queries_not_using_indexes
join_buffer_size=32M
query_cache_limit=256M
query_cache_size=192M
query_cache_type=1
max_user_connections=200
max_connections=150
thread_cache_size=128
table_cache=6196
key_buffer_size=32M
#max_connect_errors=20
max_allowed_packet=16M
sort_buffer_size=2M
read_buffer_size=8M
read_rnd_buffer_size=1M
thread_concurrency=4
myisam_sort_buffer_size=16M
concurrent_insert=2
local-infile=0
table_definition_cache=2500
max_heap_table_size=256M
tmp_table_size=256M
open_files_limit=10632
#####fixed innodb options
innodb_buffer_pool_size = 1G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
innodb_file_per_table
#####for 5.5 trees ## Uncomment for 5.5
innodb_buffer_pool_instances=16
PHP handler is fcgi and we do use cache plugin and eaccelerator, also nginxcp is running. Also we are using MariaDB worth checking root@server [/usr/local/src/publicnginx]# free -m total used free shared buffers cached Mem: 7958 7869 89 0 415 4099 -/+ buffers/cache: 3354 4603 Swap: 2047 48 1998 root@server [/usr/local/src/publicnginx]# Support suggesting to add more Ram. please help me figuring some value at my.cnf
-
Hello :) Please see the following thread: mysqlmymonlite.sh server stats gathering tool for cPanel Server It describes how to provide the data necessary to help offer you advice. Thank you. 0 -
root@server [~]# ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.5.32-MariaDB-log x86_64 Uptime = 1 days 0 hrs 26 min 43 sec Avg. qps = 86 Total Questions = 7586035 Threads Connected = 3 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: [url=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html]MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is enabled. Current long_query_time = 2.000000 sec. You have 16 out of 7586056 that take longer than 2.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log WORKER THREADS Current thread_cache_size = 128 Current threads_cached = 25 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 150 Current threads_connected = 4 Historic max_used_connections = 29 The number of used connections is 19% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 526 M Current InnoDB data space = 746 M Current InnoDB buffer pool free = 39 % Current innodb_buffer_pool_size = 1.00 G Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 2.46 G Configured Max Per-thread Buffers : 6.34 G Configured Max Global Buffers : 1.23 G Configured Max Memory Limit : 7.57 G Physical Memory : 7.77 G Max memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 129 M Current key_buffer_size = 32 M Key cache miss rate is 1 : 24976 Key buffer free ratio = 39 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 192 M Current query_cache_used = 182 M Current query_cache_limit = 256 M Current Query cache Memory fill ratio = 94.94 % Current query_cache_min_res_unit = 4 K However, 827553 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 2 M Current read_rnd_buffer_size = 1 M Sort buffer seems to be fine JOINS ./tuning-primer.sh: line 402: export: `2097152': not a valid identifier Current join_buffer_size = 32.00 M You have had 29616 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 12553 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 6196 tables Current table_definition_cache = 2500 tables You have a total of 1620 tables You have 1685 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 256 M Current tmp_table_size = 256 M Of 248505 temp tables, 41% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 1924 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 4001189 Your table locking seems to be fine root@server [~]# root@server [~]# ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.5.32-MariaDB-log x86_64 Uptime = 1 days 0 hrs 27 min 23 sec Avg. qps = 86 Total Questions = 7589628 Threads Connected = 5 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: [url=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html]MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is enabled. Current long_query_time = 2.000000 sec. You have 16 out of 7589649 that take longer than 2.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log WORKER THREADS Current thread_cache_size = 128 Current threads_cached = 26 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 150 Current threads_connected = 3 Historic max_used_connections = 29 The number of used connections is 19% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 526 M Current InnoDB data space = 746 M Current InnoDB buffer pool free = 38 % Current innodb_buffer_pool_size = 1.00 G Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 2.46 G Configured Max Per-thread Buffers : 6.34 G Configured Max Global Buffers : 1.23 G Configured Max Memory Limit : 7.57 G Physical Memory : 7.77 G Max memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 129 M Current key_buffer_size = 32 M Key cache miss rate is 1 : 24981 Key buffer free ratio = 39 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 192 M Current query_cache_used = 182 M Current query_cache_limit = 256 M Current Query cache Memory fill ratio = 94.99 % Current query_cache_min_res_unit = 4 K However, 827660 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 2 M Current read_rnd_buffer_size = 1 M Sort buffer seems to be fine JOINS ./tuning-primer.sh: line 402: export: `2097152': not a valid identifier Current join_buffer_size = 32.00 M You have had 29616 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 12553 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 6196 tables Current table_definition_cache = 2500 tables You have a total of 1620 tables You have 1685 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 256 M Current tmp_table_size = 256 M Of 248904 temp tables, 41% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 1924 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 4003176 Your table locking seems to be fine root@server [~]#
well we do have tuning-primer sh script installed I just run that and above is the output. Please suggest.0 -
nobody to help :( 0 -
correct those max_user_connections=75 max_connections=150 key_buffer_size = 150M join_buffer_size = 4M table_definition_cache = 1500 table_open_cache = 2500 sort_buffer_size=256K read_buffer_size=128K read_rnd_buffer_size=8M query_cache_limit=2M query_cache_size=100M long_query_time=0.1 thread_concurrency=4 - remove that line innodb_write_io_threads = 8 - remove that line innodb_read_io_threads = 8 - remove that line innodb_thread_concurrency = 16 - remove that line innodb_log_buffer_size = 8M innodb_max_dirty_pages_pct = 90 - remove that line innodb_buffer_pool_instances=8 add innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_file_size = 64M before restarting rm -rf /var/lib/mysql/ib_logfile* to remove old innodb log files you dont need any more RAM, its enough now 0
Please sign in to leave a comment.
Comments
4 comments