Server optimization

nunoxico · Apr 14, 2014

Hi,
This is my first tread in this forum, hopping being at the right place.
I’m trying to optimize my new VPS cPanel purchase:

AMD Opteron(tm) Processor 4284
2Vcores
2GB RAM
Speed
3000.000 MHz
Cache
2048 KB

After install MySQLTuner I get this results:

Code:

[OK] Key buffer hit rate: 99.5% (404K cached / 2K reads)
[OK] Query cache efficiency: 92.3% (735K cached / 797K selects)
[!!] Query cache prunes per day: 7655
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts)
[OK] Temporary tables created on disk: 22% (5K on disk / 23K total)
[OK] Thread cache hit rate: 99% (12 created / 17K connections)
[!!] Table cache hit rate: 4% (665 open / 15K opened)
[OK] Open file limit used: 2% (304/10K)
[OK] Table locks acquired immediately: 99% (188K immediate / 189K locks)
[!!] InnoDB  buffer pool / data size: 8.0M/23.1M
[!!] InnoDB log waits: 18
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: [http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/[/url]
Variables to adjust:
    query_cache_size (> 32M)
    table_cache (> 5000)
    innodb_buffer_pool_size (>= 23M)
    innodb_log_buffer_size (>= 1M)

Can anyone guide me through to optimize my plan?
Thanks in advance,
Francisco

cPanelMichael · Apr 14, 2014

Hello

You may want to post the full output from the MySQL tuner as opposed to just the final results snippet.

Thank you.

nunoxico · Apr 14, 2014

Hi Michael,
Thanks for your reply.
Can you please guide trough the steps.
Witch file should I edit?
I also try mysqlcheck –optimize -A but doesn't work.
Again, thanks for any help,
Francisco

thinkbot · Apr 16, 2014

copy here full my.cnf and full mysqltuner.pl result

for now you can adjust in my.cnf

innodb_buffer_pool_size = 50M
innodb_log_buffer_size = 16M

and restart
service mysql restart

nunoxico · Apr 17, 2014

Hi Thinkbot,

Because I have done some modifications, I prefer show you my actual settings before doing anything else.
Please tell me what should I do to improve my settings and bring back memory usage to 2GB = 100%

Thanks in advance,
Francisco

mysqltuner.pl

Code:

>>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  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
[OK] Currently running supported MySQL version 5.1.73-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 2M (Tables: 120)
[--] Data in InnoDB tables: 29M (Tables: 463)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 14

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 3m 13s (1M q [10.003 qps], 19K conn, TX: 2B, RX: 372M)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 218.0M global + 8.5M per thread (250 max threads)
[!!] Maximum possible memory usage: 2.3G (117% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 8% (20/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.5M
[OK] Key buffer hit rate: 99.4% (227K cached / 1K reads)
[OK] Query cache efficiency: 96.4% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
[OK] Temporary tables created on disk: 17% (11K on disk / 61K total)
[OK] Thread cache hit rate: 99% (20 created / 19K connections)
[!!] Table cache hit rate: 0% (666 open / 330K opened)
[OK] Open file limit used: 1% (295/20K)
[OK] Table locks acquired immediately: 99% (142K immediate / 142K locks)
[!!] InnoDB  buffer pool / data size: 8.0M/29.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_cache (> 10000)
    innodb_buffer_pool_size (>= 29M)

my.cnf

Code:

[mysqld]
innodb_file_per_table=1
bind-address=127.0.0.1
open_files_limit=3474
[mysqld]
    local-infile = 0
    max_connections = 250
    key_buffer = 64M
    myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
max_heap_table_size = 16M
table_cache = 10000
thread_cache_size = 286
interactive_timeout = 50
wait_timeout = 7000
connect_timeout = 15
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 2M
    query_cache_size = 128M
    query_cache_type = 1
    tmp_table_size = 16M


[mysqld_safe]


[mysqldump]
    quick
    max_allowed_packet = 16M
[myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
[mysqlhotcopy]
    interactive-timeout

Any suggestions will be very welcome.

thinkbot · Apr 17, 2014

adjust:

max_connections = 100
key_buffer = 25M
join_buffer_size = 1M
read_buffer_size = 128K
sort_buffer_size = 256K
table_cache = 6000
thread_cache_size = 100
connect_timeout = 5
query_cache_size = 30M

innodb_buffer_pool_size = 50M
innodb_log_buffer_size = 16M

and restart, memory savings would be huge

nunoxico · Apr 17, 2014

Hi thinkbot,
I follow your instructions but the results make me a little bit confuse.

Code:

RAM
Before
[!!] Maximum possible memory usage: 2.3G (117% of installed RAM)
now
[OK] Maximum possible memory usage: 313.0M (15% of installed RAM)

Key buffer
before
[OK] Key buffer hit rate: 99.4% (227K cached / 1K reads)
and now
!!] Key buffer hit rate: 92.1% (355 cached / 28 reads)

Query cache efficiency
before
[OK] Query cache efficiency: 96.4% (1M cached / 1M selects)
now
[OK] Query cache efficiency: 35.3% (65 cached / 184 selects)

Thread cache
before
[OK] Thread cache hit rate: 99% (20 created / 19K connections)
now
[OK] Thread cache hit rate: 90% (1 created / 11 connections)

Table cache
before
[!!] Table cache hit rate: 0% (666 open / 330K opened)
now
[OK] Table cache hit rate: 90% (69 open / 76 opened)

Here the report
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.1.73-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 2M (Tables: 120)
[--] Data in InnoDB tables: 29M (Tables: 463)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 14

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 138.0M global + 1.8M per thread (100 max threads)
[OK] Maximum possible memory usage: 313.0M (15% of installed RAM)
[OK] Slow queries: 0% (0/210)
[OK] Highest usage of available connections: 1% (1/100)
[OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M
[!!] Key buffer hit rate: 92.1% (355 cached / 28 reads)
[OK] Query cache efficiency: 35.3% (65 cached / 184 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 38 sorts)
[OK] Temporary tables created on disk: 25% (5 on disk / 20 total)
[OK] Thread cache hit rate: 90% (1 created / 11 connections)
[OK] Table cache hit rate: 90% (69 open / 76 opened)
[OK] Open file limit used: 0% (38/12K)
[OK] Table locks acquired immediately: 100% (223 immediate / 223 locks)
[OK] InnoDB buffer pool / data size: 50.0M/29.9M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
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

and here the Cron Daemon alert
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space'

Please, kindly support me on that.
Thanks,
Francisco

thinkbot · Apr 22, 2014

You need to run mysql for a little bit more time than 1min, as recommendation says
"MySQL started within last 24 hours - recommendations may be inaccurate"

[--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K)

Previous result
[!!] Maximum possible memory usage: 2.3G (117% of installed RAM)

New one
[OK] Maximum possible memory usage: 313.0M (15% of installed RAM)

So the maximum mysql usage dropped to acceptable level for your server, at this point, your mysql data doesnt require more

In the future adjust only those
[OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M
[OK] InnoDB buffer pool / data size: 50.0M/29.9M

so that MyISAM indexes fits Key buffer size and InnoDB data size fits InnoDB buffer pool, as it does now

Forums

The Community Forums

Interact with an entire community of cPanel & WHM users!

Server optimization

nunoxico Registered

cPanelMichael Forums Analyst
Staff Member

nunoxico Registered

thinkbot Well-Known Member

nunoxico Registered

thinkbot Well-Known Member

nunoxico Registered

thinkbot Well-Known Member