MySQL Server Optimization - Only using 6% installed RAM

djblamire

Well-Known Member
May 3, 2003
255
1
168
I have just purchased a new dedicated server and currently using the defaults for MySQL and Apache. I'm looking to try and optimize the setup as best I can.

Here is the spec of the machine:

Intel Xeon E3 1225v2 3.25Ghz
4 Cores / 4 Threads
32GB RAM
CentOS 6.5 - 64-Bit

The spec is similar to my previous machine (i7-2600), except that had only 8GB RAM and this one has 32GB of RAM, but the server load seems to be generally higher - I did do some tweaks on the previous machine, and have made the same changes on this server.

Here is the contents of /etc/my.cnf

Code:
Code:
[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10000
myisam_use_mmap=1
connect_timeout = 2
join_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_type = 1
query_cache_size = 100M
query_cache_limit = 30M
max_heap_table_size = 100M
tmp_table_size = 100M
thread_cache_size = 50
table_open_cache = 2000
slow_query_log=0
slow_query_log_file=mysql-slow.log
long_query_time=0.1
key_buffer_size = 500M
concurrent_insert=2
Even though I've made these changes (only recently - so cannot take complete notice of mysqltuner.pl for the results, it is showing:

[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)

I would expect the server should be set to be allowed to use far more than a maxmimum of 2.1GB on a 32GB machine.

I have run /usr/bin/mysqltuner.pl and this is what is showing (for info only, as it has only been running for 45 minutes since I made the changes).


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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 21M (Tables: 185)
[--] Data in InnoDB tables: 1M (Tables: 113)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 46m 16s (9K q [3.418 qps], 2K conn, TX: 21M, RX: 1M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 844.0M global + 8.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
[!!] Slow queries: 14% (1K/9K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 500.0M/8.4M
[!!] Key buffer hit rate: 21.6% (19K cached / 14K reads)
[!!] Query cache efficiency: 18.2% (430 cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 2% (9 on disk / 309 total)
[OK] Thread cache hit rate: 99% (5 created / 2K connections)
[!!] Table cache hit rate: 16% (134 open / 808 opened)
[OK] Open file limit used: 0% (7/10K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 1.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 30M, or use smaller result sets)
    table_cache (> 2000)

Thanks in advance,

Daniel
 
Last edited:

djblamire

Well-Known Member
May 3, 2003
255
1
168
After almost 2 days, this is what it is currently showing on mysqltuner:

Code:
>>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  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.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 31M (Tables: 185)
[--] Data in InnoDB tables: 1M (Tables: 113)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 17h 38m 54s (607K q [4.055 qps], 150K conn, TX: 1B, RX: 94M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 844.0M global + 8.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
[!!] Slow queries: 13% (80K/607K)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Key buffer size / total MyISAM indexes: 500.0M/10.9M
[OK] Key buffer hit rate: 95.1% (335K cached / 16K reads)
[OK] Query cache efficiency: 35.3% (57K cached / 162K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (251 temp sorts / 99K sorts)
[!!] Temporary tables created on disk: 28% (1K on disk / 4K total)
[OK] Thread cache hit rate: 99% (8 created / 150K connections)
[OK] Table cache hit rate: 30% (525 open / 1K opened)
[OK] Open file limit used: 6% (604/10K)
[OK] Table locks acquired immediately: 99% (125K immediate / 125K locks)
[OK] InnoDB data size / buffer pool: 1.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 100M)
    max_heap_table_size (> 100M)
 

gopkris2005

Well-Known Member
Jan 9, 2007
64
0
156
India
cPanel Access Level
Root Administrator
Twitter
Paste here the following command result.

mysqladmin proc

It should be need to change depending on your query and RAM size. Anyway, If possible try the following changes

join_buffer_size=3M
read_rnd_buffer_size=3M
max_heap_table_size = 2048M
tmp_table_size = 2048M
key_buffer_size = 5120M
max_connections =500
sort_buffer_size=3M
thread_stack=1M
innodb_buffer_pool_size=10M
innodb_log_buffer_size=2M
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
Hi,

Thanks for your replies.

I've only just made the changes now so will need to see how it goes, but it does show that it can use up to a maximum of 40% of installed RAM (rather than 6% before). Although there were not issues before, should it have got busy the RAM usage was being heavily restricted.

The /etc/my.cnf file now looks like this:

[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10000
myisam_use_mmap=1
connect_timeout=2
join_buffer_size=3M
read_rnd_buffer_size=4M
query_cache_type=1
query_cache_size=100M
query_cache_limit=2048M
max_heap_table_size=2048M
tmp_table_size=2048M
thread_cache_size=50
table_open_cache=2000
slow_query_log=0
slow_query_log_file=mysql-slow.log
long_query_time=0.1
key_buffer_size=5120M
concurrent_insert=2
max_connections=500
sort_buffer_size=3M
thread_stack=1M
innodb_buffer_pool_size=10M
innodb_log_buffer_size=2M


The result of the command 'mysqladmin proc' as requested above is as follows (Although I'm not sure what help this information will be).


+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| 10 | eximstats | localhost | eximstats | Sleep | 71 | | |
| 11 | DELAYED | localhost | eximstats | Delayed insert | 71 | Waiting for INSERT | |
| 57 | root | localhost | | Query | 0 | | show processlist |
+----+-----------+-----------+-----------+----------------+------+--------------------+------------------+

I'll post back the results of mysqltuner after it has been running for at least 24 hours. Any further comments/suggestions are appreciated.

Thanks
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
After making the above changes, and the server being up and running for 6 days, the following is the results of the mysqltuner.

Any other suggestions or improvements ??

Thanks in advance

Code:
>>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  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.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 209M (Tables: 185)
[--] Data in InnoDB tables: 1M (Tables: 113)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 4

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 12h 28m 6s (2M q [4.273 qps], 528K conn, TX: 4B, RX: 356M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 7.1G global + 11.1M per thread (500 max threads)
[OK] Maximum possible memory usage: 12.5G (40% of installed RAM)
[!!] Slow queries: 11% (274K/2M)
[OK] Highest usage of available connections: 2% (11/500)
[OK] Key buffer size / total MyISAM indexes: 5.0G/56.7M
[OK] Key buffer hit rate: 99.6% (1M cached / 6K reads)
[OK] Query cache efficiency: 51.5% (399K cached / 776K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (749 temp sorts / 329K sorts)
[!!] Temporary tables created on disk: 30% (4K on disk / 14K total)
[OK] Thread cache hit rate: 99% (11 created / 528K connections)
[OK] Table cache hit rate: 50% (525 open / 1K opened)
[OK] Open file limit used: 6% (604/10K)
[OK] Table locks acquired immediately: 99% (537K immediate / 538K locks)
[OK] InnoDB data size / buffer pool: 1.8M/10.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
After MySQL being up now for 33 days, the results of the MySQLTuner are shown below.

I see there are still some areas that can be optimized further.

Any suggestions/ideas on how to improve things further ??

Thanks in advance


Code:
>>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
 >>  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.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 183M (Tables: 185)
[--] Data in InnoDB tables: 1M (Tables: 113)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 33d 23h 42m 27s (13M q [4.589 qps], 3M conn, TX: 26B, RX: 2B)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 7.1G global + 11.1M per thread (500 max threads)
[OK] Maximum possible memory usage: 12.5G (40% of installed RAM)
[!!] Slow queries: 11% (1M/13M)
[OK] Highest usage of available connections: 2% (11/500)
[OK] Key buffer size / total MyISAM indexes: 5.0G/37.2M
[OK] Key buffer hit rate: 97.9% (17M cached / 377K reads)
[OK] Query cache efficiency: 49.0% (2M cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 30% (21K on disk / 70K total)
[OK] Thread cache hit rate: 99% (11 created / 3M connections)
[!!] Table cache hit rate: 3% (153 open / 4K opened)
[OK] Open file limit used: 0% (32/10K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 1.8M/10.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 2000)
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
I was just wondering if anyone had any other suggested improvements ?

Thanks in advance,

Daniel