MySQL Optimisation

[charliekapper]

Hi there,

I would like some advice on optimising MySQL for a 1gb virtuozzo vps.

The vps is currently hosted at hostdime which I believe they set a proc limit to around 180 (not sure if this plays a part in the problem).

The problem I face is the server is running low on memory around 80-120mb causing the whm panel to show "fatal error occured" messages.

I see mysql is using a lot of memory in the top command and in whm.

# top

1060 mysql     20   0 3425m 148m 7004 S  2.7 14.5 240:51.66 mysqld

# free -m

             total       used       free     shared    buffers     cached
Mem:          1024        927         96          0          0        927
-/+ buffers/cache:          0       1024
Swap:            0          0          0

# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl line 148.

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 104M (Tables: 258)
[--] Data in InnoDB tables: 67M (Tables: 69)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 22

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 0h 13m 53s (49M q [81.502 qps], 4M conn, TX: 32B, RX: 4B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
[!!] Maximum possible memory usage: 1.5G (150% of installed RAM)
[OK] Slow queries: 0% (0/49M)
[OK] Highest usage of available connections: 8% (42/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/143.4M
[OK] Key buffer hit rate: 100.0% (39M cached / 10K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 81K sorts)
[!!] Temporary tables created on disk: 28% (3M on disk / 11M total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 77% (400 open / 515 opened)
[OK] Open file limit used: 23% (596/2K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
[OK] InnoDB data size / buffer pool: 67.4M/128.0M

-------- 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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

[mysqld]
max_connections=500
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=2060

I have no idea if the server is using innodb or myisam, although I hear myisam can cut ram down a lot.

I have no real idea of configuring mysql so that may be why the my.cnf file is short and unconfigured.

I've been told the problem could possibly be due to php.

I use this web server just for a game hosting panel called Multicraft and have various dedicated servers which connect to the mysql to access the daemon.

Let me know if you need any further info.

Thanks in advance.
Charlie

 

[Aaron.Edwards]

Hi Charlie,

How many hits does your server gets usually at the peak time of the server ? How many domains hosted in the server ?

Determine your maximum connections value by reducing it.

 

[charliekapper]

Hi Charlie,

How many hits does your server gets usually at the peak time of the server ? How many domains hosted in the server ?

Determine your maximum connections value by reducing it.

Thanks for the reply.

Around 40 hits afaik. How could I check this?

6 domains are hosted on the server, 5 are sitting with no traffic and 1 (the main one) has the panel on with the databases.

If I reduce the amount, how do I tell if the connections get rejected because it's too low?
Also if I set the wrong settings (low values) will mysql not work and fail to write changes?

 

[thinkbot]

change my.cnf to

[mysqld]
max_connections=75
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=5000
myisam_use_mmap=1

wait_timeout = 60
connect_timeout = 2

query_cache_type = 1
query_cache_size = 20M
query_cache_limit = 1M

thread_cache_size = 20
table_open_cache = 512
key_buffer_size = 150M

innodb_buffer_pool_size = 100M