I'm happy to help. We have finished the slow query logging, and I have copied the log to /home/kg55ne6/slowqueries.2. I have run the MySQL tuner utility, which has suggested the following changes:

 

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    When making adjustments, make tmp_table_size/max_heap_table_size equal

    Reduce your SELECT DISTINCT queries without LIMIT clauses

    Increase table_cache gradually to avoid file descriptor limits

 

Variables to adjust:

    tmp_table_size (> 32M)

    max_heap_table_size (> 32M)

    table_cache (> 128)

 

Additionally, you asked us to compare this with the settings your other VPS had. First, I did notice that there were still OOM (or "Out Of Memory") kills happening on MySQL on the other server, though it doesn't appear to be as frequent. Running MySQL tuner on the other VPS gave the following recommendations:

 

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    MySQL started within last 24 hours - recommendations may be inaccurate

    Set thread_cache_size to 4 as a starting value

    Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

    thread_cache_size (start at 4)

    table_cache (> 64)

 

Additionally, here are the two configuration files, as requested:

 

CT-12146-bash-4.1# cat /etc/my.cnf

[mysqld]

max_connections = 100

max_user_connections = 75

query_cache_size = 32M

skip-external-locking

key_buffer_size = 32M

max_allowed_packet = 8M

table_open_cache = 128

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 16M

innodb_file_per_table

tmp_table_size = 32M

max_heap_table_size = 32M

thread_cache_size = 4

 

# This setting ensures that aio limits are not exceeded

# (default is 65536, each instance of mysql takes 2661 with this enabled)

innodb_use_native_aio = 0

open_files_limit=4004

log-slow-queries = /var/log/slowqueries

long_query_time = 3

 

 

Other VPS:

 

[mysqld]

safe-show-database

max_connections=150

max_user_connections=30

query_cache_size=32M

key_buffer_size=256M

open_files_limit=7326

log-slow-queries = /var/log/slowqueries

long_query_time = 3

 

One glaring difference I noticed was your PHP handler; the server with fewer out of memory kills was using DSO and mod_ruid2, compared with vps12146 which is using SuPHP. If you'd like we can switch the PHP handler to DSO on vps12146, although the issue of the slow MySQL queries will still need to be solved.

 

Finally, just looking through the slow query logs, there seem to be a much larger number of rows to analyze on the server with more frequent kills, as well as much larger queries in general. This is a query that was flagged as being slow on vps12146:

 

# Time: 140609  4:08:00

# User@Host: kg55ne6_kg55er[kg55ne6_kg55er] @ localhost [127.0.0.1]

# Query_time: 6.448442  Lock_time: 0.000093 Rows_sent: 5  Rows_examined: 14294

SET timestamp=1402312080;

SELECT `documents`.*  FROM `xe_documents` as `documents` , `xe_modules` as `modules`   WHERE ( `modules`.`module` = 'board' and `modules`.`module_srl` in (178440,178443,178446,85913,140038,139898,548,66172,173113,181546,233243,139520,140039,178561,190341,141432) and `documents`.`module_srl` = `modules`.`module_srl` ) and `documents`.`list_order` <= 2100000000    ORDER BY `documents`.`list_order` asc  LIMIT 5;

 

 

While here is a query from the one that is running smoother:

 

# User@Host: youshi5_kb34mgr[youshi5_kb34mgr] @ localhost []

# Query_time: 5.880789  Lock_time: 0.000083 Rows_sent: 1  Rows_examined: 2759

SET timestamp=1402298176;

SELECT count(`document_srl`) as `count`  FROM `xe_documents` as `documents`   WHERE `module_srl` in (63) and `status` in ('SECRET','PUBLIC') and ( `list_order` <= -66254 );

 

Notice that the number of rows examined is much lower, and the amount of logic in the SQL statement itself appears to be much smaller. Both of these could be contributing greatly towards causing MySQL to crash. If you'd like for us to make any changes to the MySQL configuration file, or change the PHP handler to DSO, we can certainly do so for you; however, as far as the queries themselves go, I would recommend having a developer check over those and see what can be done to optimize those. Please let us know if you have any other questions, or if there's anything else that we can assist you with.

 

Please let us know if you have any further questions; we are more than happy to help.

Inmotionhosting.com support team.