current my.cnf configuration

mark_rogers · Aug 19, 2013

Hello cPanel expert community,

I have a medium sized VPS on which I run 2 Magento installations, a few static pages as well as several WordPress / Joomla installation - all personal websites and overall nothing extremely fancy or with extreme traffic.

My VPS Specifications:

Memory:12288 MB
CPU cores: 4 × 2.4 Ghz
OS: CentOS
Extras: WHM / cPanel

Generally the speed on my sites was always there and I could not complain but recently I noticed websites loading slower, to be more specific it's all websites that require a MySQL database.

I asked someone for advice and I was told it's most likely related to the growth of my databases and / or a bad my.cnf configuration file.

The current my.cnf looks as follows:

Code:

    [mysqld]
    open_files_limit=64000
    local-infile=0
    query_cache_size=512M
    query_cache_limit=2M
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    thread_cache_size=4
    join_buffer_size=4M
    key_buffer_size=128M
    table_cache=640
    log-slow-queries=/home/mysql-slow-queries.log
    long_query_time=1

I was recommended to run mysqltuner, which I did with the following output:

Code:

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 165M (Tables: 1034)
    [--] Data in InnoDB tables: 92M (Tables: 1101)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 372K (Tables: 55)
    [!!] Total fragmented tables: 25

    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 39m 42s (571K q [34.034 qps], 15K conn, TX: 786M, RX: 113M)
    [--] Reads / Writes: 54% / 46%
    [--] Total buffers: 800.0M global + 6.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 1.8G (14% of installed RAM)
    [OK] Slow queries: 0% (2/571K)
    [OK] Highest usage of available connections: 7% (12/151)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/40.3M
    [OK] Key buffer hit rate: 96.5% (548K cached / 18K reads)
    [OK] Query cache efficiency: 93.2% (440K cached / 472K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 946
    [OK] Temporary tables created on disk: 11% (2K on disk / 22K total)
    [OK] Thread cache hit rate: 99% (63 created / 15K connections)
    [!!] Table cache hit rate: 0% (634 open / 268K opened)
    [OK] Open file limit used: 1% (980/64K)
    [OK] Table locks acquired immediately: 99% (121K immediate / 121K locks)
    [OK] InnoDB data size / buffer pool: 93.0M/128.0M


    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits

    Variables to adjust:
        join_buffer_size (> 4.0M, or always use indexes with joins)
        table_cache (> 640)

After supplying this output I was recommended to apply the following changes:

Code:

        thread_cache_size=16
        query_cache_size=1024M
        join_buffer_size=8M
        table_cache=4096

My questions:

Are the recommendations I was given safe? I did not apply them yet, but from what I read throughout other my.cnf related posts they seem a little bit over the top.
I reckon that my current my.cnf file is rather "slim" if I am to compare it with other my.cnf files I've stumbled upon the web. Is there anything in particular that you think I am missing out?

I am definitely not an expert or system administrator but merely an amateur, therefore it would be greatly appreciated if someone can share a little insight on how I can improve / enhance my current configuration.

Thank you very much.

cPanelMichael · Aug 19, 2013

Hello

I recommend letting MySQL run for at least 24 hours in order to get accurate results from the MySQL tuner.

Thank you.

mark_rogers · Aug 20, 2013

Thank you very much Mr. Michael,

I will see to report back once I have passed the hour mark.

mark_rogers · Aug 21, 2013

Dear cPanel community and Mr. Michael,

I have made a little further research and now also run tuning primer in addition to mysqltuner. I had MySQL now running for 48hours as per suggested by tuning primer in order to post the results / outcome:

MySQL Tuner:

Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 162M (Tables: 935)
[--] Data in InnoDB tables: 87M (Tables: 758)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 372K (Tables: 38)
[!!] Total fragmented tables: 42

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 1h 21m 45s (10M q [59.984 qps], 103K conn, TX: 16B, RX: 2B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 800.0M global + 6.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.8G (14% of installed RAM)
[OK] Slow queries: 0% (8/10M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/32.0M
[!!] Key buffer hit rate: 93.7% (1M cached / 113K reads)
[OK] Query cache efficiency: 96.4% (9M cached / 9M selects)
[!!] Query cache prunes per day: 27316
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 79K sorts)
[!!] Joins performed without indexes: 3164
[OK] Temporary tables created on disk: 9% (25K on disk / 266K total)
[OK] Thread cache hit rate: 99% (587 created / 103K connections)
[!!] Table cache hit rate: 0% (640 open / 2M opened)
[OK] Open file limit used: 1% (1K/64K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 87.9M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 4.0M, or always use indexes with joins)
    table_cache (> 640)

Tuning Primer:

Code:

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.32-cll x86_64

Uptime = 2 days 1 hrs 22 min 34 sec
Avg. qps = 59
Total Questions = 10660664
Threads Connected = 4

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 8 out of 10660685 that take longer than 10.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 http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 4
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 4
Historic max_used_connections = 11
The number of used connections is 7% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 60 M
Current InnoDB data space = 87 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 128 M
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 : 856 M
Configured Max Per-thread Buffers : 1000 M
Configured Max Global Buffers : 784 M
Configured Max Memory Limit : 1.74 G
Physical Memory : 11.73 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 32 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 15
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 471 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 92.16 %
Current query_cache_min_res_unit = 4 K
However, 56183 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 = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 3164 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 = 64000 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 = 640 tables
Current table_definition_cache = 400 tables
You have a total of 1772 tables
You have 640 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 241510 temp tables, 9% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 240 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 29299
Your table locking seems to be fine

My current my.cnf file:

Code:

    [mysqld]
    open_files_limit=64000
    local-infile=0
    query_cache_size=512M
    query_cache_limit=2M
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    thread_cache_size=4
    join_buffer_size=4M
    key_buffer_size=128M
    table_cache=640

It would be really greatly appreciated if someone can give me any suggestions or help me to apply a proper tuning / configuration on the current my.cnf file as I personally think there are a lot of things wrong / missing in it.

Thank you very much.

thinkbot · Aug 21, 2013

It looks ok, but few things:

- your slow log is set to log queries taking longer than 10 s
for performance optimization you should track all of them or at least 0.1 and then optimize them

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

- you got a lot of opened tables
[!!] Table cache hit rate: 0% (640 open / 2M opened)
even though you got less than 1k tables in MyISAM
[--] Data in MyISAM tables: 162M (Tables: 935)

That's becouse of queries using temporary tables (in memory or on disk)

You can increase table_cache a bit, it's now called table_open_cache
so instead of
table_cache=640
place
table_open_cache = 1000

and review queries with EXPLAIN query, to check if they use temporary tables and if they can be optimized
especially those creating temp tables on disk (those are very slow, and doesn't scale much)
[OK] Temporary tables created on disk: 9% (25K on disk / 266K total)
those would be probably one of the slowest in slow query log

add there in the end
max_heap_table_size = 50M
tmp_table_size = 50M

to increase the size of temporary tables (in memory)

- InnoDB buffer is almost full, can be increased a lot since you got much RAM
innodb_buffer_pool_size = 1G

- query cache doesn't scale well when set high, it's better to lower it, mysqltuner will display "suggestions" about it anyway
query_cache_size=100M
query_cache_limit=1M

you don't have much traffic, so going into details with each mysql variable doesn't make much sense,
the most can be done by tracking and optimizing your current queries (especially the ones creating temp tables on disk, or the ones not utilizing proper indexes), and that is thru slow query log

so after you place above settings in my.cnf, please restart mysql, and it will create mysql-slow.log

so that you can run this

wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest

./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

and in slow.txt you will have a review of slow queries that should be checked and optimized

If you are using custom code, the chances are some indexes needs to be created or some code/queries rewritten
If you use CMS like wordpress/joomla/drupal etc. it's database structure is probably already optimized, but there might be plugins (written by many people around the world, many of them might not be aware of optimizations, or speed/scallability concerns when coding) that got inefficient queries, so they are the ones to be checked

mark_rogers · Aug 21, 2013

Dear thinkbot,

thank you very much for the kind message and thorough details, that's very much appreciated.
Before I actually apply the new settings in the my.cnf file I just wanted to confirm / double check with you if everything seems to be okay. Here as follows:

Code:

    [mysqld]
    open_files_limit=64000
    local-infile=0
    #query_cache_size=512M
    #query_cache_limit=2M
    query_cache_size=100M
    query_cache_limit=1M
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    innodb_buffer_pool_size = 1G
    thread_cache_size=4
    join_buffer_size=4M
    key_buffer_size=128M
    #table_cache=640
    table_open_cache = 1000
    max_heap_table_size = 50M
    tmp_table_size = 50M
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

Please kindly let me know and thank you so much for your help.

thinkbot · Aug 21, 2013

yes correct, you can also increase
thread_cache_size=20
and add

read_rnd_buffer_size=4M

then restart

mark_rogers · Aug 22, 2013

Thank you thinkbot - I have seen to implement all suggestions you have so kindly pointed out and for the time being, everything seems to be safe and working. I will report back here after 48 hours have past with related mysqltuner and tuning primer results.

Thanks again for your suggestions!

thinkbot · Aug 22, 2013

btw. for Magento, Wordpress and Joomla you should also check caching settings (in admin areas of those scripts), which will make great difference
one more thing, which php handler you use ? suPHP ? mod_php ?

also it's good to install WHM Munin plugin, to monitor server resources (CPU, Load etc)

mark_rogers · Aug 22, 2013

Hello thinkbot, thanks for your reply.

Most of my installations have cache settings enabled, for Magento I make additional use of memcached. In relation to PHP I use DSO as PHP5 handler.

Best regards,
Mark

mark_rogers · Aug 23, 2013

Hello Thinkbot and cPanel community,

here are my current results using the newly configured my.cnf configuration:

Code:

[mysqld]
open_files_limit=64000
local-infile=0
query_cache_size=128M
query_cache_limit=1M
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=1G
thread_cache_size=20
read_rnd_buffer_size=4M
join_buffer_size=4M
key_buffer_size=128M
table_open_cache=1000
max_heap_table_size=50M
tmp_table_size=50M
slow_query_log=1
slow_query_log_file=/home/mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

MySQLTuner results:

Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 163M (Tables: 936)
[--] Data in InnoDB tables: 83M (Tables: 759)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 372K (Tables: 38)
[!!] Total fragmented tables: 29

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 21m 45s (7M q [48.223 qps], 67K conn, TX: 12B, RX: 1B)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 1.3G global + 10.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.8G (24% of installed RAM)
[OK] Slow queries: 1% (88K/7M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/31.0M
[COLOR="#FF0000"][!!] Key buffer hit rate: 93.8% (1M cached / 116K reads)[/COLOR]
[OK] Query cache efficiency: 95.6% (6M cached / 7M selects)
[COLOR="#FF0000"][!!] Query cache prunes per day: 88560[/COLOR]
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 72K sorts)
[COLOR="#FF0000"][!!] Joins performed without indexes: 3277[/COLOR]
[OK] Temporary tables created on disk: 9% (22K on disk / 231K total)
[OK] Thread cache hit rate: 99% (12 created / 67K connections)
[COLOR="#FF0000"][!!] Table cache hit rate: 0% (1K open / 1M opened)[/COLOR]
[OK] Open file limit used: 2% (1K/64K)
[OK] Table locks acquired immediately: 99% (941K immediate / 942K locks)
[OK] InnoDB data size / buffer pool: 83.3M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 4.0M, or always use indexes with joins)
    table_cache (> 1000)

Tuning Primer results:

Code:

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 0.100000 sec.
You have 88357 out of 7536421 that take longer than 0.100000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
[COLOR="#FF0000"]The binary update log is NOT enabled.
You will not be able to do point in time recovery[/COLOR]
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 20
Current threads_cached = 9
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 3
Historic max_used_connections = 12
[COLOR="#FF0000"]The number of used connections is 7% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating[/COLOR]

INNODB STATUS
Current InnoDB index space = 60 M
Current InnoDB data space = 83 M
Current InnoDB buffer pool free = 62 %
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 : 1.38 G
Configured Max Per-thread Buffers : 1.52 G
Configured Max Global Buffers : 1.26 G
Configured Max Memory Limit : 2.79 G
Physical Memory : 11.73 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 30 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 16
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 100 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 78.19 %
Current query_cache_min_res_unit = 4 K
[COLOR="#FF8C00"]MySQL won't cache query results that are larger than query_cache_limit in size[/COLOR]

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 3261 queries where a join could not use an index properly
[COLOR="#FF0000"]You have had 16 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised[/COLOR]
You should enable "log-queries-not-using-indexes" [B]<- I have this in my my.cnf though ?![/B]
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 64000 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 = 1000 tables
Current table_definition_cache = 400 tables
You have a total of 1774 tables
You have [COLOR="#FF0000"]1000 open tables.[/COLOR]
Current table_cache hit rate is[COLOR="#FF0000"] 0%[/COLOR]
, while [COLOR="#FF0000"]100% [/COLOR]of your table cache is in use
[COLOR="#FF0000"]You should probably increase your table_cache
You should probably increase your table_definition_cache value.[/COLOR]

TEMP TABLES
Current max_heap_table_size = 50 M
Current tmp_table_size = 50 M
Of 209631 temp tables, 9% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 244 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = [COLOR="#FF0000"]1 : 18134[/COLOR]
Your table locking seems to be fine

I have seen to highlight the issues reported by the related software in red. It would be greatly appreciated if someone can help me with this situation.

Thank you very much and best regards,
Mark

Rhuan · Aug 23, 2013

Hi bro, I will help you

Code:

[!!] Total fragmented tables: 29

Run mysqlcheck -Ao to optimize all databases and tables...

Code:

[!!] Key buffer hit rate: 93.8% (1M cached / 116K reads)

You have to increase key_buffer_size...

Code:

[!!] Query cache prunes per day: 88560

This is a high number, increase query_cache_size

Code:

[!!] Joins performed without indexes: 3277

Increase join_buffer_size

Code:

[!!] Table cache hit rate: 0% (1K open / 1M opened)

Increase table_cache

Another tips...

Code:

innodb_buffer_pool_size=1G

You can change for 512M or 256M because you have only 83.3M of InnoDB data...

Code:

open_files_limit=64000

Change for 40000...

mark_rogers · Aug 24, 2013

Thank you Rhuan for getting back at me in relation to this situation, very much appreciated.

Which values would you recommend for the items you've pointed out?

See my current config for those items:

key_buffer_size=128M
query_cache_size=128M
query_cache_limit=1M
join_buffer_size=4M
table_open_cache=1000

I am clearly not an expert with these settings and would not want to crash my server.

Thank you very much for your help,
Mark

thinkbot · Aug 24, 2013

I totally disagree with Rhuan,
you can't base everything on mysqltuner readings and suggestions without understanding mysql variables and how it works

[!!] Total fragmented tables: 29
Run mysqlcheck -Ao to optimize all databases and tables...

there will almost always be some "fragmented" tables

[!!] Key buffer hit rate: 93.8% (1M cached / 116K reads)
You have to increase key_buffer_size...

if you checked
[OK] Key buffer size / total MyISAM indexes: 128.0M/31.0M
you would see that key_buffer_size is well enough

[!!] Query cache prunes per day: 88560
This is a high number, increase query_cache_size

nope
[OK] Query cache efficiency: 95.6% (6M cached / 7M selects)
Current Query cache Memory fill ratio = 78.19 %
No need to further increase query cache, it's better to prune than to make it too big (since query cache doesn't scale well in mysql)
The best option is to add SQL_NO_CACHE to queries that often change, or that read from tables that are updated often
In Magento you will have tons of queries using query cache, so don't really worry about it, don't make it too big.

[!!] Joins performed without indexes: 3277
Increase join_buffer_size

He gets info about queries not using indexes, so why you suggest to futher increase join_buffer_size ?
it's already big 4MB
If you want to fix this warning check the queries that doesn't utilize indexes, anyways, in most installations you will have those

[!!] Table cache hit rate: 0% (1K open / 1M opened)
Increase table_cache

NO NO NO
[--] Data in MyISAM tables: 163M (Tables: 936)
There is less than 1k MyISAM tables
The rest that are being open are temporary tables

increasing mysql tables cache too much will decrease performance, table cache doesn't scale well, you should update to mysql 5.6 where there are multiple buffers for table cache

Code:
innodb_buffer_pool_size=1G
You can change for 512M or 256M because you have only 83.3M of InnoDB data...

Code:
open_files_limit=64000
Change for 40000...

and 12GB of RAM, so no need
Current InnoDB buffer pool free = 62 %
and innodb buffer is used in almost 40% even though he got 83MB of data only

to sum all, you shouldn't base on your opitions on mysqltuner or other tools results, couse they only take numbers from mysql variables and show previously defined ratios

I would suggest updating to Percona MySQL 5.6

the rest is quite good, of couse it could be better but you would need to go into details, and this can't be done accurately thru forums when you don't have much mysql optimization/benchmarking experience

Rhuan · Aug 24, 2013

Hi thinkbot, thank you for your corrections I will read more about this

mark_rogers · Aug 24, 2013

Thank you both for contributing to this post, very much appreciated.

Thinkbot, do you think I should leave all current values in the my.cnf or is there anything that you think I can further adjust / tweak?

Thank you,
Mark

thinkbot · Aug 24, 2013

well, you can increase the table cache more to 4000
table_open_cache = 4000

but dont go over values like 8000+

other than that,

innodb_log_file_size = 50M

but before restarting the mysql, you need to remove old innodb log files by
rm -rf /var/lib/mysql/ib_logfile*

then
service mysql restart

mark_rogers · Aug 24, 2013

Thank you thinkbot for your expert insight and advises throughout the entire post - I very much appreciated your help.

As per your suggestion I have seen to increase the table_open_cache value from 1000 to 4000

One last question, you've mentioned: "it's better to prune than to make it too big (since query cache doesn't scale well in mysql)" - is there any specific command to get this done?

Thank you,
Mark

thinkbot · Aug 25, 2013

query cache is pruned/cleared automatically

Thread starter	Similar threads	Forum	Replies	Date
N	MariaDB won't start when making change to my.cnf	Workarounds and Optimization	2	Oct 8, 2019
D	CPU and Concurrent Connection Usage	Workarounds and Optimization	2	Oct 22, 2018
	Work around for "Error: Log is currently being processed in the background. Cannot update."	Workarounds and Optimization	1	Jan 7, 2015
E	Concurrent connections	Workarounds and Optimization	7	Sep 19, 2014
	Any tips on getting over 5000 concurrent apache connections?	Workarounds and Optimization	5	Dec 16, 2009

current my.cnf configuration

Member

Administrator

Member

Member

Well-Known Member

Member

Well-Known Member

Member

Well-Known Member

Member

Member

Active Member

Member

Well-Known Member

Active Member

Member

Well-Known Member

Member

Well-Known Member