My.cnf config

[khanbaba]

Hello,

I have Xeon 1230 v2 with 8GB of Ram, we are having Wordpress having about 800MB Database
my.cnf configuration is :

[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

[email protected] [/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
[email protected] [/usr/local/src/publicnginx]#


Support suggesting to add more Ram.

please help me figuring some value at my.cnf

 

[cPanelMichael]

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.

 

[khanbaba]

[email protected] [~]# ./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[/url]
Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors[/url]
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[/url]

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

[email protected] [~]#
[email protected] [~]# ./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[/url]
Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors[/url]
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[/url]

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

[email protected] [~]#

well we do have tuning-primer sh script installed I just run that and above is the output.

Please suggest.

 

[khanbaba]

nobody to help

 

[thinkbot]

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