2

My site is generating error 500s to users when load is high ("cannot allocate memory" errors appear in my logs). I have a VPS with GoDaddy, 8GB RAM (on average only about 40% of RAM is being used at any time, which is really weird given the "out of memory" errors). CPU usage can get pretty high during peak times (> 90%). Main site is using an InnoDB database. Below is the content of my.cnf file (results of MySQLTuner follow). I'm not a professional DBA by any means, and am over paying GoDaddy for their useless tuning services. Any help would be greatly appreciated.

[mysql]         

# CLIENT #          
port      = 3306            
socket    = /var/lib/mysql/mysql.sock           

[mysqld]            

# GENERAL #         
user      = mysql           
default-storage-engine  = InnoDB            
socket    = /var/lib/mysql/mysql.sock           
pid-file         = /var/lib/mysql/mysql.pid         

# MyISAM #          
key-buffer-size  = 512M         
myisam-recover   = FORCE,BACKUP         

# SAFETY #          
max-allowed-packet      = 256M          
max-connect-errors      = 1000          

# DATA STORAGE #            
datadir   = /var/lib/mysql/         

# CACHES AND LIMITS #           
tmp-table-size   = 256M         
max-heap-table-size     = 256M          
query-cache-type        = 1         
query-cache-size        = 512M          
query-cache-limit       = 8M            
query-cache-min-res-unit       = 2048           
max-connections  = 1000         
thread-cache-size       = 128M          
open-files-limit        = 65535         
table-definition-cache  = 1024          
table-open-cache        = 2048M         
wait-timeout               = 300    

# INNODB #          
innodb-flush-method     = O_DIRECT          
innodb-log-files-in-group      = 2          
innodb-log-file-size    = 256M          
innodb-flush-log-at-trx-commit = 2          
innodb-file-per-table   = 1         
innodb-buffer-pool-size = 6G            
innodb-read-io-threads  = 16            
innodb-write-io-threads = 16            
innodb-buffer-pool-instances   = 6          
join-buffer-size        = 256M          
sort-buffer-size        = 256M          

# LOGGING #         
log-error        = /var/lib/mysql/mysql-error.log           
log-queries-not-using-indexes  = 1          
slow-query-log   = 1            
slow-query-log-file     = /var/lib/mysql/mysql-slow-new.log         
long_query_time = 1         
innodb_file_per_table=1         
open_files_limit=10000          

Output of MySQLTuner:

-------- Storage Engine Statistics -------------------------------------------          
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV  [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m           
[[0;34m--[0m] Data in MyISAM tables: 60M (Tables: 485)          
[[0;34m--[0m] Data in InnoDB tables: 284M (Tables: 720)         
[[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 17)            
[[0;31m!![0m] Total fragmented tables: 26           

-------- Performance Metrics -------------------------------------------------          
[[0;34m--[0m] Up for: 1d 2h 35m 10s (4M q [47.935 qps], 58K conn, TX: 7B, RX: 541M)         
[[0;34m--[0m] Reads / Writes: 82% / 18%         
[[0;34m--[0m] Binary logging is disabled            
[[0;34m--[0m] Total buffers: 7.3G global + 512.6M per thread (1000 max threads)         
[[0;31m!![0m] Maximum reached memory usage: 156.4G (1955.59% of installed RAM)          
[[0;31m!![0m] Maximum possible memory usage: 507.9G (6348.45% of installed RAM)         
[[0;32mOK[0m] Slow queries: 2% (111K/4M)            
[[0;32mOK[0m] Highest usage of available connections: 29% (298/1000)            
[[0;32mOK[0m] Aborted connections: 0.23%  (133/58103)           
[[0;32mOK[0m] Query cache efficiency: 91.5% (3M cached / 4M selects)            
[[0;32mOK[0m] Query cache prunes per day: 0         
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 132K sorts)          
[[0;31m!![0m] Temporary tables created on disk: 96% (95K on disk / 98K total)           
[[0;32mOK[0m] Thread cache hit rate: 99% (298 created / 58K connections)            
[[0;32mOK[0m] Table cache hit rate: 99% (2K open / 2K opened)           
[[0;32mOK[0m] Open file limit used: 12% (1K/10K)            
[[0;32mOK[0m] Table locks acquired immediately: 99% (640K immediate / 640K locks)           

-------- MyISAM Metrics -----------------------------------------------------           
[[0;31m!![0m] Key buffer used: 18.6% (99M used / 536M cache)            
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 512.0M/12.0M          
[[0;32mOK[0m] Read Key buffer hit rate: 98.7% (1M cached / 13K reads)           
[[0;31m!![0m] Write Key buffer hit rate: 86.7% (152K cached / 20K writes)           

-------- InnoDB Metrics -----------------------------------------------------           
[[0;34m--[0m] InnoDB is enabled.            
[[0;32mOK[0m] InnoDB buffer pool / data size: 6.0G/284.1M           
[[0;32mOK[0m] InnoDB buffer pool instances: 6           
[[0;31m!![0m] InnoDB Used buffer: 4.35% (17122 used/ 393213 total)          
[[0;32mOK[0m] InnoDB Read buffer efficiency: 100.00% (2425099009 hits/ 2425112323 total)            
[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)           
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 82535 writes)          

-------- AriaDB Metrics -----------------------------------------------------           
[[0;34m--[0m] AriaDB is disabled.           

-------- Replication Metrics -------------------------------------------------          
[[0;34m--[0m] No replication slave(s) for this server.          
[[0;34m--[0m] This is a standalone server..         

-------- Recommendations -----------------------------------------------------          
General recommendations:            

Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

MySQLDumpSlow Summary:

Count: 135883  Time=0.14s (19134s)  Lock=0.00s (129s)  Rows=1.0 (135883), 2users@localhost
  SELECT COUNT(*) as `listings2` FROM `ppb_listings` WHERE end_time > NOW() AND deleted = N AND start_time < NOW()

Count: 164403  Time=0.03s (4211s)  Lock=0.00s (57s)  Rows=1.0 (164400), auction_777onlin[auction_777onlin]@localhost
  SELECT `value` as `file_path` FROM `ppb_listings_media` WHERE `value` = 'S'
  UNION
  SELECT `logo_path` as `file_path` FROM `ppb_categories` WHERE `logo_path` = 'S'
  UNION
  SELECT `content` as `file_path` FROM `ppb_advertising` WHERE `content` = 'S' AND `type` = 'S'
  UNION
  SELECT `value` as `file_path` FROM `ppb_settings` WHERE `value` = 'S' AND `name` = 'S'
  UNION
  SELECT `store_settings` as `file_path` FROM `ppb_users` WHERE `store_settings` LIKE 'S'

Count: 5840  Time=0.07s (421s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  UPDATE ppb_listings SET closed = 'S', updated_at = now() WHERE (closed = N) AND (deleted = N) AND (draft = N) AND (start_time < now()) AND (end_time > now() OR end_time is null)

Count: 5840  Time=0.07s (390s)  Lock=0.00s (1s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  SELECT listings.* FROM ppb_listings AS listings WHERE (closed = N) AND (deleted = N) AND (draft = N) AND (start_time < now()) AND (end_time < now()) AND (end_time is not null) LIMIT N FOR UPDATE

Count: 5840  Time=0.07s (386s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  UPDATE ppb_listings SET deleted = 'S' WHERE (end_time is not null) AND (deleted = N) AND (closed = N) AND (end_time < (now() - interval N day))

Count: 5527  Time=0.06s (335s)  Lock=0.00s (0s)  Rows=1.7 (9418), auction_777onlin[auction_777onlin]@localhost
  SELECT listings.* FROM ppb_listings AS listings WHERE (counted_at is null OR counted_at < IF(updated_at is null, created_at, updated_at)) LIMIT N

Count: 84705  Time=0.00s (55s)  Lock=0.00s (9s)  Rows=28.0 (2371737), 2users@localhost
  SELECT categories.* FROM ppb_categories AS categories WHERE (enable_auctions = N) AND (user_id is null) AND (parent_id is null) ORDER BY order_id ASC, name ASC

Count: 579217  Time=0.00s (45s)  Lock=0.00s (25s)  Rows=1.0 (579217), auction_777onlin[auction_777onlin]@localhost
  SELECT bid_increments.amount FROM ppb_bid_increments AS bid_increments WHERE (tier_from <= 'S') AND (tier_to > 'S')

Count: 36456  Time=0.00s (37s)  Lock=0.00s (27s)  Rows=1.0 (36456), 2users@localhost
  SELECT COUNT(*) as `users` FROM `ppb_users`

Count: 115005  Time=0.00s (31s)  Lock=0.00s (13s)  Rows=0.9 (108415), 2users@localhost
  SELECT users_statistics.* FROM ppb_users_statistics AS users_statistics WHERE (remote_addr = 'S') AND (http_user_agent = 'S')

Count: 36208  Time=0.00s (29s)  Lock=0.00s (4s)  Rows=28.0 (1013818), 2users@localhost
  SELECT categories.* FROM ppb_categories AS categories WHERE (parent_id IS NULL) AND (enable_auctions = N) AND (user_id IS NULL) ORDER BY order_id ASC, name ASC

Count: 49482  Time=0.00s (26s)  Lock=0.00s (12s)  Rows=156.0 (7719312), 4users@localhost
  SELECT settings.name, settings.value FROM ppb_settings AS settings

Count: 165  Time=0.13s (20s)  Lock=0.00s (0s)  Rows=10.4 (1711), auction_777onlin[auction_777onlin]@localhost
  SELECT listings_watch.*, w.*, l.end_time FROM ppb_listings_watch AS listings_watch
  INNER JOIN ppb_listings_watch AS w
  INNER JOIN ppb_listings AS l ON w.listing_id = l.id WHERE (w.user_id = 'S') AND (l.end_time >= NOW()) GROUP BY w.id ORDER BY l.end_time ASC LIMIT N

Count: 197  Time=0.08s (16s)  Lock=0.00s (0s)  Rows=17.6 (3477), auction_777onlin[auction_777onlin]@localhost
  SELECT COUNT(*) AS nb_rows FROM ppb_listings_watch AS listings_watch
  INNER JOIN ppb_listings_watch AS w
  INNER JOIN ppb_listings AS l ON w.listing_id = l.id WHERE (w.user_id = 'S') AND (l.end_time >= NOW()) GROUP BY w.id

Count: 5840  Time=0.00s (13s)  Lock=0.00s (1s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  SELECT users.* FROM ppb_users AS users WHERE (user_verified = N) AND (user_verified_next_payment < (now() + interval N day)) AND (user_verified_next_payment > N) AND (user_verified_email = N) LIMIT N

Count: 5840  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  SELECT users.* FROM ppb_users AS users WHERE (store_active = N) AND (store_next_payment < (now() + interval N day)) AND (store_next_payment > N) AND (store_expiration_email = N) LIMIT N

Count: 5840  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  SELECT users.* FROM ppb_users AS users WHERE (user_verified = N) AND (user_verified_next_payment < now()) AND (user_verified_next_payment > N) LIMIT N

Count: 5840  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  SELECT users.* FROM ppb_users AS users WHERE (store_active = N) AND (store_next_payment < now()) AND (store_next_payment > N) LIMIT N

Count: 42  Time=0.27s (11s)  Lock=0.00s (0s)  Rows=6685.6 (280794), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `ppb_listings`

Count: 45033  Time=0.00s (10s)  Lock=0.00s (6s)  Rows=0.0 (0), 2users@localhost
  SELECT custom_fields.* FROM ppb_custom_fields AS custom_fields WHERE (type = 'S') AND (active = N) AND (searchable = N) AND (category_ids REGEXP 'S' OR category_ids = 'S') ORDER BY active DESC, order_id ASC

Count: 5840  Time=0.00s (10s)  Lock=0.00s (0s)  Rows=0.0 (0), auction_777onlin[auction_777onlin]@localhost
  DELETE FROM ppb_autocomplete_tags WHERE (created_at < now() - interval N month) AND (updated_at < now() - interval N month)

Count: 32  Time=0.27s (8s)  Lock=0.00s (0s)  Rows=12.0 (383), auction_777onlin[auction_777onlin]@localhost
  SELECT listings_watch.*, w.*, l.end_time FROM ppb_listings_watch AS listings_watch
  INNER JOIN ppb_listings_watch AS w
  INNER JOIN ppb_listings AS l ON w.listing_id = l.id WHERE (w.user_id = 'S') AND (l.end_time >= NOW()) GROUP BY w.id ORDER BY l.end_time ASC LIMIT N OFFSET N

Show Variables:

 Variable_name       Variable_name      
 auto_increment_increment   1       
 auto_increment_offset  1       
 autocommit      ON     
 automatic_sp_privileges         ON     
 avoid_temporal_upgrade      OFF        
 back_log   250     
 basedir         /usr       
 big_tables      OFF        
 bind_address        *      
 binlog_cache_size  32768       
 binlog_checksum         CRC32      
 binlog_direct_non_transactional_updates         OFF        
 binlog_error_action         IGNORE_ERROR       
 binlog_format       STATEMENT      
 binlog_gtid_simple_recovery         OFF        
 binlog_max_flush_queue_time    0       
 binlog_order_commits        ON     
 binlog_row_image        FULL       
 binlog_rows_query_log_events        OFF        
 binlog_stmt_cache_size 32768       
 binlogging_impossible_mode      IGNORE_ERROR       
 block_encryption_mode       aes-128-ecb        
 bulk_insert_buffer_size    8388608     
 character_set_client        utf8       
 character_set_connection        utf8       
 character_set_database      latin1     
 character_set_filesystem        binary     
 character_set_results       utf8       
 character_set_server        latin1     
 character_set_system        utf8       
 character_sets_dir      /usr/share/mysql/charsets/     
 collation_connection        utf8_general_ci        
 collation_database      latin1_swedish_ci      
 collation_server        latin1_swedish_ci      
 completion_type         NO_CHAIN       
 concurrent_insert       AUTO       
 connect_timeout    10      
 core_file       OFF        
 datadir         /var/lib/mysql/        
 date_format         %Y-%m-%d       
 datetime_format         %Y-%m-%d %H:%i:%s      
 default_storage_engine      InnoDB     
 default_tmp_storage_engine      InnoDB     
 default_week_format    0       
 delay_key_write         ON     
 delayed_insert_limit   100     
 delayed_insert_timeout 300     
 delayed_queue_size 1000        
 disconnect_on_expired_password      ON     
 div_precision_increment    4       
 end_markers_in_json         OFF        
 enforce_gtid_consistency        OFF        
 eq_range_index_dive_limit  10      
 error_count    0       
 event_scheduler         OFF        
 expire_logs_days   0       
 explicit_defaults_for_timestamp         OFF        
 external_user              
 flush       OFF        
 flush_time 0       
 foreign_key_checks      ON     
 ft_boolean_syntax       + -><()~*:""&|     
 ft_max_word_len    84      
 ft_min_word_len    4       
 ft_query_expansion_limit   20      
 ft_stopword_file        (built-in)     
 general_log         OFF        
 general_log_file        /var/lib/mysql/www.log     
 group_concat_max_len   1024        
 gtid_executed              
 gtid_mode       OFF        
 gtid_next       AUTOMATIC      
 gtid_owned             
 gtid_purged                
 have_compress       YES        
 have_crypt      YES        
 have_dynamic_loading        YES        
 have_geometry       YES        
 have_openssl        DISABLED       
 have_profiling      YES        
 have_query_cache        YES        
 have_rtree_keys         YES        
 have_ssl        DISABLED       
 have_symlink        YES        
 host_cache_size    653     
 hostname        www.777auction.ca      
 identity   0       
 ignore_builtin_innodb       OFF        
 ignore_db_dirs             
 init_connect               
 init_file              
 init_slave             
 innodb_adaptive_flushing        ON     
 innodb_adaptive_flushing_lwm   10      
 innodb_adaptive_hash_index      ON     
 innodb_adaptive_max_sleep_delay    150000      
 innodb_additional_mem_pool_size    8388608     
 innodb_api_bk_commit_interval  5       
 innodb_api_disable_rowlock      OFF        
 innodb_api_enable_binlog        OFF        
 Variable_name       Value      
 innodb_api_enable_mdl       OFF        
 innodb_api_trx_level   0       
 innodb_autoextend_increment    64      
 innodb_autoinc_lock_mode   1       
 innodb_buffer_pool_dump_at_shutdown         OFF        
 innodb_buffer_pool_dump_now         OFF        
 innodb_buffer_pool_filename         ib_buffer_pool     
 innodb_buffer_pool_instances   1       
 innodb_buffer_pool_load_abort       OFF        
 innodb_buffer_pool_load_at_startup      OFF        
 innodb_buffer_pool_load_now         OFF        
 innodb_buffer_pool_size    1073741824      
 innodb_change_buffer_max_size  25      
 innodb_change_buffering         all        
 innodb_checksum_algorithm       innodb     
 innodb_checksums        ON     
 innodb_cmp_per_index_enabled        OFF        
 innodb_commit_concurrency  0       
 innodb_compression_failure_threshold_pct   5       
 innodb_compression_level   6       
 innodb_compression_pad_pct_max 50      
 innodb_concurrency_tickets 5000        
 innodb_data_file_path       ibdata1:12M:autoextend     
 innodb_data_home_dir               
 innodb_disable_sort_file_cache      OFF        
 innodb_doublewrite      ON     
 innodb_fast_shutdown   1       
 innodb_file_format      Antelope       
 innodb_file_format_check        ON     
 innodb_file_format_max      Antelope       
 innodb_file_per_table       ON     
 innodb_flush_log_at_timeout    1       
 innodb_flush_log_at_trx_commit 2       
 innodb_flush_method         O_DIRECT       
 innodb_flush_neighbors 1       
 innodb_flushing_avg_loops  30      
 innodb_force_load_corrupted         OFF        
 innodb_force_recovery  0       
 innodb_ft_aux_table                
 innodb_ft_cache_size   8000000     
 innodb_ft_enable_diag_print         OFF        
 innodb_ft_enable_stopword       ON     
 innodb_ft_max_token_size   84      
 innodb_ft_min_token_size   3       
 innodb_ft_num_word_optimize    2000        
 innodb_ft_result_cache_limit   2000000000      
 innodb_ft_server_stopword_table                
 innodb_ft_sort_pll_degree  2       
 innodb_ft_total_cache_size 640000000       
 innodb_ft_user_stopword_table              
 innodb_io_capacity 200     
 innodb_io_capacity_max 2000        
 innodb_large_prefix         OFF        
 innodb_lock_wait_timeout   50      
 innodb_locks_unsafe_for_binlog      OFF        
 innodb_log_buffer_size 8388608     
 innodb_log_compressed_pages         ON     
 innodb_log_file_size   268435456       
 innodb_log_files_in_group  2       
 innodb_log_group_home_dir       ./     
 innodb_lru_scan_depth  1024        
 innodb_max_dirty_pages_pct 75      
 innodb_max_dirty_pages_pct_lwm 0       
 innodb_max_purge_lag   0       
 innodb_max_purge_lag_delay 0       
 innodb_mirrored_log_groups 1       
 innodb_monitor_disable             
 innodb_monitor_enable              
 innodb_monitor_reset               
 innodb_monitor_reset_all               
 innodb_old_blocks_pct  37      
 innodb_old_blocks_time 1000        
 innodb_online_alter_log_max_size   134217728       
 innodb_open_files  1000        
 innodb_optimize_fulltext_only       OFF        
 innodb_page_size   16384       
 innodb_print_all_deadlocks      OFF        
 innodb_purge_batch_size    300     
 innodb_purge_threads   1       
 innodb_random_read_ahead        OFF        
 innodb_read_ahead_threshold    56      
 innodb_read_io_threads 16      
 innodb_read_only        OFF        
 innodb_replication_delay   0       
 innodb_rollback_on_timeout      OFF        
 innodb_rollback_segments   128     
 innodb_sort_buffer_size    1048576     
 innodb_spin_wait_delay 6       
 innodb_stats_auto_recalc        ON     
 innodb_stats_method         nulls_equal        
 innodb_stats_on_metadata        OFF        
 innodb_stats_persistent         ON     
 innodb_stats_persistent_sample_pages   20      
 innodb_stats_sample_pages  8       
 innodb_stats_transient_sample_pages    8       
 innodb_status_output        OFF        
 innodb_status_output_locks      OFF        
 innodb_strict_mode      OFF        
 innodb_support_xa       ON     
 innodb_sync_array_size 1       
 Variable_name       Value      
 innodb_sync_spin_loops 30      
 innodb_table_locks      ON     
 innodb_thread_concurrency  0       
 innodb_thread_sleep_delay  10000       
 innodb_undo_directory       .      
 innodb_undo_logs   128     
 innodb_undo_tablespaces    0       
 innodb_use_native_aio       ON     
 innodb_use_sys_malloc       ON     
 innodb_version      5.6.28     
 innodb_write_io_threads    16      
 insert_id  0       
 interactive_timeout    28800       
 join_buffer_size   268435456       
 keep_files_on_create        OFF        
 key_buffer_size    104857600       
 key_cache_age_threshold    300     
 key_cache_block_size   1024        
 key_cache_division_limit   100     
 large_files_support         ON     
 large_page_size    0       
 large_pages         OFF        
 last_insert_id 0       
 lc_messages         en_US      
 lc_messages_dir         /usr/share/mysql/      
 lc_time_names       en_US      
 license         GPL        
 local_infile        ON     
 lock_wait_timeout  31536000        
 locked_in_memory        OFF        
 log_bin         OFF        
 log_bin_basename               
 log_bin_index              
 log_bin_trust_function_creators         OFF        
 log_bin_use_v1_row_events       OFF        
 log_error       /var/lib/mysql/mysql-error.log     
 log_output      FILE       
 log_queries_not_using_indexes       OFF        
 log_slave_updates       OFF        
 log_slow_admin_statements       OFF        
 log_slow_slave_statements       OFF        
 log_throttle_queries_not_using_indexes 0       
 log_warnings   1       
 long_query_time    1       
 low_priority_updates        OFF        
 lower_case_file_system      OFF        
 lower_case_table_names 0       
 master_info_repository      FILE       
 master_verify_checksum      OFF        
 max_allowed_packet 268435456       
 max_binlog_cache_size  1.84467E+19     
 max_binlog_size    1073741824      
 max_binlog_stmt_cache_size 1.84467E+19     
 max_connect_errors 1000        
 max_connections    1000        
 max_delayed_threads    20      
 max_digest_length  1024        
 max_error_count    64      
 max_heap_table_size    67108864        
 max_insert_delayed_threads 20      
 max_join_size  1.84467E+19     
 max_length_for_sort_data   1024        
 max_prepared_stmt_count    16382       
 max_relay_log_size 0       
 max_seeks_for_key  1.84467E+19     
 max_sort_length    1024        
 max_sp_recursion_depth 0       
 max_tmp_tables 32      
 max_user_connections   0       
 max_write_lock_count   1.84467E+19     
 metadata_locks_cache_size  1024        
 metadata_locks_hash_instances  8       
 min_examined_row_limit 0       
 multi_range_count  256     
 myisam_data_pointer_size   6       
 myisam_max_sort_file_size  9.22337E+18     
 myisam_mmap_size   1.84467E+19     
 myisam_recover_options      BACKUP,FORCE       
 myisam_repair_threads  1       
 myisam_sort_buffer_size    8388608     
 myisam_stats_method         nulls_unequal      
 myisam_use_mmap         OFF        
 net_buffer_length  16384       
 net_read_timeout   30      
 net_retry_count    10      
 net_write_timeout  60      
 new         OFF        
 old         OFF        
 old_alter_table         OFF        
 old_passwords  0       
 open_files_limit   10000       
 optimizer_prune_level  1       
 optimizer_search_depth 62      
 optimizer_switch        index_merge=on,index_merge_union=on,index_merge_so...      
 optimizer_trace         enabled=off,one_line=off       
 optimizer_trace_features        greedy_search=on,range_optimizer=on,dynamic_range=...      
 optimizer_trace_limit  1       
 optimizer_trace_max_mem_size   16384       
 optimizer_trace_offset -1      
 performance_schema      ON     
 Variable_name       Value      
 performance_schema_accounts_size   100     
 performance_schema_digests_size    10000       
 performance_schema_events_stages_history_long_size 10000       
 performance_schema_events_stages_history_size  10      
 performance_schema_events_statements_history_long_...  10000       
 performance_schema_events_statements_history_size  10      
 performance_schema_events_waits_history_long_size  10000       
 performance_schema_events_waits_history_size   10      
 performance_schema_hosts_size  100     
 performance_schema_max_cond_classes    80      
 performance_schema_max_cond_instances  6148        
 performance_schema_max_digest_length   1024        
 performance_schema_max_file_classes    50      
 performance_schema_max_file_handles    32768       
 performance_schema_max_file_instances  15385       
 performance_schema_max_mutex_classes   200     
 performance_schema_max_mutex_instances 17240       
 performance_schema_max_rwlock_classes  40      
 performance_schema_max_rwlock_instances    8544        
 performance_schema_max_socket_classes  10      
 performance_schema_max_socket_instances    2020        
 performance_schema_max_stage_classes   150     
 performance_schema_max_statement_classes   168     
 performance_schema_max_table_handles   2000        
 performance_schema_max_table_instances 12500       
 performance_schema_max_thread_classes  50      
 performance_schema_max_thread_instances    2100        
 performance_schema_session_connect_attrs_size  512     
 performance_schema_setup_actors_size   100     
 performance_schema_setup_objects_size  100     
 performance_schema_users_size  100     
 pid_file        /var/lib/mysql/mysql.pid       
 plugin_dir      /usr/lib64/mysql/plugin/       
 port   3306        
 preload_buffer_size    32768       
 profiling       OFF        
 profiling_history_size 15      
 protocol_version   10      
 proxy_user             
 pseudo_slave_mode       OFF        
 pseudo_thread_id   7635        
 query_alloc_block_size 8192        
 query_cache_limit  8388608     
 query_cache_min_res_unit   2048        
 query_cache_size   52428800        
 query_cache_type        ON     
 query_cache_wlock_invalidate        OFF        
 query_prealloc_size    8192        
 rand_seed1 0       
 rand_seed2 0       
 range_alloc_block_size 4096        
 read_buffer_size   131072      
 read_only       OFF        
 read_rnd_buffer_size   262144      
 relay_log              
 relay_log_basename             
 relay_log_index                
 relay_log_info_file         relay-log.info     
 relay_log_info_repository       FILE       
 relay_log_purge         ON     
 relay_log_recovery      OFF        
 relay_log_space_limit  0       
 report_host                
 report_password                
 report_port    3306        
 report_user                
 rpl_stop_slave_timeout 31536000        
 secure_auth         ON     
 secure_file_priv               
 server_id  0       
 server_id_bits 32      
 server_uuid         ea294e1a-2583-11e4-bcd2-00185136fbef       
 show_old_temporals      OFF        
 simplified_binlog_gtid_recovery         OFF        
 skip_external_locking       ON     
 skip_name_resolve       OFF        
 skip_networking         OFF        
 skip_show_database      OFF        
 slave_allow_batching        OFF        
 slave_checkpoint_group 512     
 slave_checkpoint_period    300     
 slave_compressed_protocol       OFF        
 slave_exec_mode         STRICT     
 slave_load_tmpdir       /tmp       
 slave_max_allowed_packet   1073741824      
 slave_net_timeout  3600        
 slave_parallel_workers 0       
 slave_pending_jobs_size_max    16777216        
 slave_rows_search_algorithms        TABLE_SCAN,INDEX_SCAN      
 slave_skip_errors       OFF        
 slave_sql_verify_checksum       ON     
 slave_transaction_retries  10      
 slave_type_conversions             
 slow_launch_time   2       
 slow_query_log      ON     
 slow_query_log_file         /var/lib/mysql/mysql-slow-new.log      
 socket      /var/lib/mysql/mysql.sock      
 sort_buffer_size   268435456       
 sql_auto_is_null        OFF        
 sql_big_selects         ON     
 Variable_name       Value      
 sql_buffer_result       OFF        
 sql_log_bin         ON     
 sql_log_off         OFF        
 sql_mode        NO_ENGINE_SUBSTITUTION     
 sql_notes       ON     
 sql_quote_show_create       ON     
 sql_safe_updates        OFF        
 sql_select_limit   1.84467E+19     
 sql_slave_skip_counter 0       
 sql_warnings        OFF        
 ssl_ca             
 ssl_capath             
 ssl_cert               
 ssl_cipher             
 ssl_crl                
 ssl_crlpath                
 ssl_key                
 storage_engine      InnoDB     
 stored_program_cache   256     
 sync_binlog    0       
 sync_frm        ON     
 sync_master_info   10000       
 sync_relay_log 10000       
 sync_relay_log_info    10000       
 system_time_zone        EST        
 table_definition_cache 1024        
 table_open_cache   1000        
 table_open_cache_instances 1       
 thread_cache_size  16384       
 thread_concurrency 10      
 thread_handling         one-thread-per-connection      
 thread_stack   262144      
 time_format         %H:%i:%s       
 time_zone       SYSTEM     
 timed_mutexes       OFF        
 timestamp  1454301257      
 tmp_table_size 67108864        
 tmpdir      /tmp       
 transaction_alloc_block_size   8192        
 transaction_allow_batching      OFF        
 transaction_prealloc_size  4096        
 tx_isolation        REPEATABLE-READ        
 tx_read_only        OFF        
 unique_checks       ON     
 updatable_views_with_limit      YES        
 version         5.6.28-log     
 version_comment         MySQL Community Server (GPL)       
 version_compile_machine         x86_64     
 version_compile_os      Linux      
 wait_timeout   300     
 warning_count  0       

SHOW GLOBAL STATUS:

Reached character limit, had to store it externally:

http://777auction.ca/globstat.txt

Explain Selects:

Explain selects

SHOW CREATE TABLE:

http://777auction.ca/showcreate.txt

3
  • 1
    You almost certainly have too many parameters that are not using default values. Bigger is not always better. See also dba.stackexchange.com/a/45570/11651 for my thoughts and other info on "tuning" your server's performance right into the ground, and a great pull-quote from the book High Performance MySQL about tuning scripts and performance tuning, where the bottom line, according to the authors (with whom I agree but am not affiliated), is this: “Tuning” your server can be a stunning waste of time. – Michael - sqlbot Feb 1 '16 at 1:11
  • I prefer -s t on mysqldumpslow; that orders them by their impact on the system. – Rick James Feb 1 '16 at 18:47
  • @RickJames - I updated the MySQLDumpSlow and Show Table Creates in my post... that small tweak to mysqldumpslow highlighted some nasty queries – Doron Feb 1 '16 at 19:51
2

Memory issues

query-cache-size = 512M

Too large -- tends to take a lot of effort purging entries; drop it to 50M. Or consider setting it to 0.

table-open-cache = 2048M

NO! That is a count, not bytes. 1000 (no M) is probably fine.

innodb-buffer-pool-size = 6G
InnoDB buffer pool / data size: 6.0G/284.1M
InnoDB buffer pool instances: 6

Drop that to 1G to make sure the "out of memory" goes away. And you don't need such a big buffer_pool. However, you should consider converting all non-system tables to InnoDB. (In decreasing the buffer_pool_size, decrease the instances - to 1.)

log-queries-not-using-indexes = 1

clutters the slowlog without providing as much information as the rest of the slowlog. Change to 0.

key-buffer-size = 512M
Key buffer used: 18.6% (99M used / 536M cache)

Since you don't have much in MyISAM tables, lower this to 100M.

Total fragmented tables: 26
Maximum reached memory usage: 156.4G (1955.59% of installed RAM)
Maximum possible memory usage: 507.9G (6348.45% of installed RAM)
Run OPTIMIZE TABLE to defragment tables for better performance

These are bogus; ignore them.

tmp-table-size = 256M
max-heap-table-size = 256M
Temporary table size is already large - reduce result set size

That's important advice -- tmp tables are created for complex selects. Lots of users could create lots of big tmp tables, which could blow out memory. Suggest 64M would be safer.

innodb-file-per-table = 1
Data in InnoDB tables: 284M (Tables: 720)

That says you have 720 small .ibd files. It's not worth changing at this point, but I would recommend turning off file_per_table when creating table that won't get bigger than a megabyte.

CPU advice

Temporary tables created on disk: 96% (95K on disk / 98K total)

This is one strong clue that the queries and/or indexes are not well written.

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow-new.log
long_query_time = 1

Good. You have the info with which to find the naughty queries. Use pt-query-digest to summarize the slowlog. Then present to us a couple of the worst, together with EXPLAIN SELECT... and SHOW CREATE TABLE. We can probably quickly help you get the CPU/load down to a reasonable level.

More advice

I you would like more thorough tuning advice, provide SHOW VARIABLES; and SHOW GLOBAL STATUS;. I'll run them through another script that will probably find a few more things.

3
  • I predict that the memory problems will completely vanish with those tips. However, the CPU issues await details about the naughty queries. – Rick James Jan 31 '16 at 23:47
  • Amazing, I ran some load tests and it looks like the site is far more stable now. I wasn't able to install Percona's toolkit, but I ran and got the following output: – Doron Feb 1 '16 at 4:09
  • I added the slow query summary, show variables and global status output to the original post. Thank you so much for your help! – Doron Feb 1 '16 at 4:49
0

(This is more of a continuation than a new answer. This is a review of the VARIABLES and GLOBAL STATUS.)

Observations: Version: 5.6.28-log
8 GB of RAM
Uptime = 00:51:38; Please rerun SHOW GLOBAL STATUS after several hours.
You are not running on Windows.
Running 64-bit version
It appears that you are running both MyISAM and InnoDB.

The More Important Issues

  • innodb_buffer_pool_size has already been discussed; 1G should be good for now.
  • join_buffer_size, sort_buffer_size should be decreased; see notes below.
  • Created_tmp_disk_tables, Select_scan indicate need for query tuning.
  • thread_cache_size should be no bigger than max_connections; else wasting RAM.

Details and other observations

( innodb_buffer_pool_size / _ram ) = 1024M / 8192M = 12.5% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (100M / 0.20 + 1024M / 0.70) / 8192M = 24.0% -- Most of available ram should be made available for caching.

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 59,452 * 16384 / 1024M = 90.7% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 10,052 / 46262 = 21.7% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size (Probably bogus because of very short Uptime.)

( join_buffer_size ) = 256M -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- Use the default.

( join_buffer_size / _ram ) = 256M / 8192M = 3.1% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- Decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM.

( sort_buffer_size / _ram ) = 256M / 8192M = 3.1% -- Probably should limit to 1% of RAM.

( Key_blocks_used * 1024 / key_buffer_size ) = 331 * 1024 / 100M = 0.32% -- Percent of key_buffer used . High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage. (Probably bogus because of very short Uptime.)

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 8,867 / (8867 + 8999) = 49.6% -- Percent of temp tables that spilled to disk

( tmp_table_size ) = 64M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Select_scan / Com_select ) = 15,296 / 22664 = 67.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( sort_buffer_size ) = 256M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok. -- This may be eating into available RAM; recommend no more than 2M.

( binlog_format ) = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( Uptime ) = 3,098 = 51m 38s -- How long (in seconds) the server has been running. -- The system has not been up long enough to get reliable suggestions for many of the issues. Fix what you can, then come back with fresh values after the system has been running several hours.

( max_connect_errors ) = 1,000 -- A small protection against hackers. -- Perhaps no more than 200.

( thread_cache_size ) = 16,384 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)

( Threads_created / Connections ) = 263 / 7658 = 3.4% -- Rapidity of process creation (Probably bogus because of very short Uptime.)

( thread_cache_size / max_connections ) = 16,384 / 1000 = 1638.4%

http://mysql.rjweb.org/doc.php/myisam2innodb - http://mysql.rjweb.org/doc.php/memory


(This is to address the slow queries.)

SELECT  COUNT(*) as `listings2`
    FROM  `ppb_listings`
    WHERE  end_time > NOW()
      AND  deleted = N
      AND  start_time < NOW()

Needs

INDEX(deleted, end_time, start_time),
INDEX(deleted, start_time, end_time)

Neither is really optimal, but they are 'covering', which helps some. The optimizer will dynamically pick which is likely to be better.

SELECT  `value` as `file_path`
    FROM  `ppb_listings_media`
    WHERE  `value` = 'S'
    UNION 
 SELECT  `logo_path` as `file_path`
    FROM  `ppb_categories`
    WHERE  `logo_path` = 'S'
    UNION 
 SELECT  `content` as `file_path`
    FROM  `ppb_advertising`
    WHERE  `content` = 'S'
      AND  `type` = 'S'
    UNION 
 SELECT  `value` as `file_path`
    FROM  `ppb_settings`
    WHERE  `value` = 'S'
      AND  `name` = 'S'
    UNION 
 SELECT  `store_settings` as `file_path`
    FROM  `ppb_users`
    WHERE  `store_settings` LIKE 'S' 

UNION defaults to UNION DISTINCT; change to UNION ALL if practical, since it is faster.

Do you have simple/composite indexes on each table? For example, ppb_settings needs INDEX(value, name) or INDEX(name, value).

SELECT  listings.*
    FROM  ppb_listings AS listings
    WHERE  (closed = N)
      AND  (deleted = N)
      AND  (draft = N)
      AND  (start_time < now())
      AND  (end_time < now())
      AND  (end_time is not null)
    LIMIT  N FOR 
# and  
UPDATE  ppb_listings SET closed = 'S', updated_at = now()
    WHERE  (closed = N)
      AND  (deleted = N)
      AND  (draft = N)
      AND  (start_time < now())
      AND  (end_time > now()
              OR  end_time is null
           ) 

INDEX(closed, deleted, draft, start_time, end_time) (or any of certain rearrangements of such.)

UPDATE  ppb_listings SET deleted = 'S'
    WHERE  (end_time is not null)
      AND  (deleted = N)
      AND  (closed = N)
      AND  (end_time < (now() - interval N day)) 

INDEX(deleted, closed, end_time) -- note that this index and the previous one cannot be combined because end_time is checked in a "range". Also you can probably remove (end_time is not null) since the other test will fail anyway.

SELECT  listings.*
    FROM  ppb_listings AS listings
    WHERE  (counted_at is null
              OR  counted_at < IF(updated_at is null, created_at, updated_at)
           )
    LIMIT  N

Which N do you want? Perhaps you need an ORDER BY? INDEX(counted_at) might help.

SELECT  categories.*
    FROM  ppb_categories AS categories
    WHERE  (enable_auctions = N)
      AND  (user_id is null)
      AND  (parent_id is null)
    ORDER BY  order_id ASC, name ASC 

INDEX(enable_auctions, user_id, parent_id, order_id, name) -- the first 3 in any order; the last 2 must follow and must be in that order.

Those first few probably cover 99% of the activity on the server. Some of my suggestions will help more than others. If you need to discuss any of them, please start another thread, and supply the query, EXPLAIN SELECT ... and SHOW CREATE TABLE.

Those 7 looked like simple cases of "need the right index". If you already have the right index, then perhaps there is no way to improve it. At that point, you need to ask "Does the application really need this query? Or can we change to something else?"

2
  • (Note: I added index suggestions after posting this "answer".) See Cookbook – Rick James Feb 2 '16 at 0:38
  • 1
    I can't thank you enough. My site is running way better after implementing your suggestions. Had 0 (!!!) error 500s last night (peak time), compared to hundreds the previous week. Aside from 2-3 short periods of slow (but at least stable) performance, everything went smooth. I learned a lot from your insights, and will continue to investigate the script and queries to further boost performance. Thank you so much! – Doron Feb 2 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.