Slow system performance

MySQL & MariaDB Other MySQL® Questions
1

So I’m having lots of problems. Seems that traffic reaches very high midday (. For most of the day, traffic is good, load average around 2-3. But at peak times, I guess it starts using Swap or something. Also as you can see mysql is using 55% of cpu, this number jumps around so much that it sometimes reaches 90%. Is there something I can do. I have 2 large databases used by 2 subdomains (whose tables are listed at the end), which get about +/-150 new records every 6 hours, and then 1 vbulletin running with usually less than 20 guests (30+ if peaking). Am I overloading this one server, becuase I really thought a dedicated server with 1 gb ram and 1 Ghz would be able to hold all this together.

top command

top - 10:43:51 up 13 days, 16:31, 1 user, load average: 5.65, 3.36, 4.95Tasks: 157 total, 4 running, 153 sleeping, 0 stopped, 0 zombieCpu(s): 51.8%us, 21.6%sy, 0.0%ni, 24.3%id, 0.7%wa, 0.3%hi, 1.3%si, 0.0%stMem: 1035324k total, 828400k used, 206924k free, 12260k buffersSwap: 2040244k total, 1895320k used, 144924k free, 335292k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND10011 mysql 15 0 72192 18m 3916 S 55.1 1.8 7:31.45 mysqld14628 apache 16 0 55260 9.9m 6328 S 1.6 1.0 0:00.89 httpd15414 apache 15 0 55036 9348 5796 S 1.6 0.9 0:00.15 httpd11056 apache 15 0 56804 14m 9504 S 1.0 1.4 0:02.86 httpd13168 apache 15 0 55332 11m 7608 S 1.0 1.1 0:01.37 httpd13183 apache 15 0 55996 12m 7968 S 1.0 1.2 0:01.49 httpd14632 apache 15 0 55428 11m 7716 S 1.0 1.1 0:01.06 httpd15438 apache 15 0 54188 7784 5188 S 1.0 0.8 0:00.05 httpd10274 apache 15 0 56348 14m 9772 S 0.7 1.4 0:03.84 httpd11598 apache 15 0 56052 12m 7864 S 0.7 1.2 0:01.68 httpd11604 apache 15 0 56044 12m 8596 S 0.7 1.3 0:01.99 httpd13170 apache 15 0 55500 11m 7676 S 0.7 1.1 0:02.14 httpd14044 apache 15 0 55160 10m 7016 S 0.7 1.0 0:01.21 httpd14938 apache 15 0 55016 10m 6812 S 0.7 1.0 0:00.46 httpd15404 root 15 0 2304 1048 788 R 0.7 0.1 0:00.06 top15413 apache 15 0 55016 9208 5664 S 0.7 0.9 0:00.07 httpd15435 apache 15 0 54184 6688 4080 S 0.7 0.6 0:00.02 httpd15439 apache 15 0 54052 6604 4112 S 0.7 0.6 0:00.02 httpd11996 apache 16 0 56376 13m 9332 S 0.3 1.4 0:03.05 httpd12016 apache 15 0 56448 13m 9200 S 0.3 1.4 0:02.83 httpd15151 apache 15 0 55040 10m 6856 S 0.3 1.0 0:00.24 httpd15166 apache 21 0 55036 9408 5848 S 0.3 0.9 0:00.18 httpd15409 apache 15 0 53920 6884 4468 S 0.3 0.7 0:00.02 httpd15441 apache 15 0 53920 6280 3876 S 0.3 0.6 0:00.01 httpd 1 root 15 0 2040 388 360 S 0.0 0.0 0:01.94 init 2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 3 root 34 19 0 0 0 S 0.0 0.0 0:03.63 ksoftirqd/0 4 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0 5 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 events/0 6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper 7 root 11 -5 0 0 0 S 0.0 0.0 0:00.03 kthread 10 root 10 -5 0 0 0 S 0.0 0.0 0:02.13 kblockd/0 11 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid 87 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0 90 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd 92 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod 155 root 10 -5 0 0 0 S 0.0 0.0 7:35.45 kswapd0 156 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0 313 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused 330 root 10 -5 0 0 0 S 0.0 0.0 2:57.90 kjournald 362 root 16 -5 0 0 0 S 0.0 0.0 0:00.14 kauditd 396 root 18 -4 2764 176 172 S 0.0 0.0 0:00.36 udevd 537 apache 25 0 136m 9428 8560 S 0.0 0.9 1:02.09 httpd 1034 root 18 -5 0 0 0 S 0.0 0.0 0:00.00 kmpathd/0 1059 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kjournald 1440 apache 25 0 136m 9344 8436 S 0.0 0.9 1:02.07 httpd 1627 root 16 -3 12076 352 268 S 0.0 0.0 0:02.75 auditd 1629 root 12 -3 10092 244 240 S 0.0 0.0 0:02.67 python 1648 root 16 0 10128 232 200 S 0.0 0.0 0:00.07 restorecond 1663 root 18 0 1700 260 216 S 0.0 0.0 0:17.26 syslogd

mysql> show processlist;±-----±----------±--------------------±--------±-----±------±-----------------+| Id | Host | db | Command | Time | State | Info |±-----±----------±--------------------±--------±-----±------±-----------------+| 4832 | localhost | punjasingh2 | Sleep | 125 | NULL | NULL || 5221 | localhost | punjasingh2 | Sleep | 51 | NULL | NULL || 5285 | localhost | dataprog_punjasingh | Sleep | 46 | NULL | NULL || 5378 | localhost | punjasingh2 | Sleep | 33 | NULL | NULL || 5432 | localhost | punjasingh2 | Sleep | 23 | NULL | NULL || 5456 | localhost | punjasingh2 | Sleep | 19 | NULL | NULL || 5481 | localhost | punjasingh2 | Sleep | 15 | NULL | NULL || 5501 | localhost | NULL | Query | 0 | NULL | show processlist || 5527 | localhost | dataprog_punjasingh | Sleep | 8 | NULL | NULL || 5539 | localhost | punjasingh2 | Sleep | 7 | NULL | NULL || 5549 | localhost | punjasingh2 | Sleep | 5 | NULL | NULL || 5552 | localhost | punjasingh2 | Sleep | 0 | NULL | NULL || 5553 | localhost | punjasingh2 | Sleep | 4 | NULL | NULL || 5559 | localhost | punjasingh2 | Sleep | 0 | NULL | NULL || 5562 | localhost | dataprog_punjasingh | Sleep | 3 | NULL | NULL || 5563 | localhost | punjasingh2 | Sleep | 2 | NULL | NULL || 5566 | localhost | punjasingh2 | Sleep | 2 | NULL | NULL || 5574 | localhost | punjasingh2 | Sleep | 0 | NULL | NULL || 5576 | localhost | punjasingh2 | Sleep | 0 | NULL | NULL |±-----±----------±--------------------±--------±-----±------±-----------------+19 rows in set (4.27 sec)

mysql> show status;±----------------------------------±---------+| Variable_name | Value |±----------------------------------±---------+| Aborted_clients | 0 || Aborted_connects | 0 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 112 || Bytes_sent | 1246 || Com_admin_commands | 0 || Com_alter_db | 0 || Com_alter_table | 0 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 0 || Com_change_master | 0 || Com_check | 0 || Com_checksum | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_dealloc_sql | 0 || Com_delete | 0 || Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_drop_user | 0 || Com_execute_sql | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 0 || Com_insert_select | 0 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 0 || Com_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 0 || Com_rename_table | 0 || Com_repair | 0 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_restore_table | 0 || Com_revoke | 0 || Com_revoke_all | 0 || Com_rollback | 0 || Com_savepoint | 0 || Com_select | 0 || Com_set_option | 0 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_charsets | 0 || Com_show_collations | 0 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 0 || Com_show_databases | 0 || Com_show_errors | 0 || Com_show_fields | 0 || Com_show_grants | 0 || Com_show_innodb_status | 0 || Com_show_keys | 0 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_ndb_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_privileges | 0 || Com_show_processlist | 1 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 1 || Com_show_storage_engines | 0 || Com_show_tables | 0 || Com_show_triggers | 0 || Com_show_variables | 0 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_stmt_close | 0 || Com_stmt_execute | 0 || Com_stmt_fetch | 0 || Com_stmt_prepare | 0 || Com_stmt_reset | 0 || Com_stmt_send_long_data | 0 || Com_truncate | 0 || Com_unlock_tables | 0 || Com_update | 0 || Com_update_multi | 0 || Com_xa_commit | 0 || Com_xa_end | 0 || Com_xa_prepare | 0 || Com_xa_recover | 0 || Com_xa_rollback | 0 || Com_xa_start | 0 || Compression | OFF || Connections | 5770 || Created_tmp_disk_tables | 0 || Created_tmp_files | 3406 || Created_tmp_tables | 1 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 0 || Handler_discover | 0 || Handler_prepare | 0 || Handler_read_first | 0 || Handler_read_key | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 || Handler_rollback | 0 || Handler_savepoint | 0 || Handler_savepoint_rollback | 0 || Handler_update | 0 || Handler_write | 130 || Innodb_buffer_pool_pages_data | 0 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_pages_flushed | 0 || Innodb_buffer_pool_pages_free | 0 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 0 || Innodb_buffer_pool_pages_total | 0 || Innodb_buffer_pool_read_ahead_rnd | 0 || Innodb_buffer_pool_read_ahead_seq | 0 || Innodb_buffer_pool_read_requests | 0 || Innodb_buffer_pool_reads | 0 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 0 || Innodb_data_fsyncs | 0 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 0 || Innodb_data_reads | 0 || Innodb_data_writes | 0 || Innodb_data_written | 0 || Innodb_dblwr_pages_written | 0 || Innodb_dblwr_writes | 0 || Innodb_log_waits | 0 || Innodb_log_write_requests | 0 || Innodb_log_writes | 0 || Innodb_os_log_fsyncs | 0 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 0 || Innodb_page_size | 0 || Innodb_pages_created | 0 || Innodb_pages_read | 0 || Innodb_pages_written | 0 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 0 || Innodb_row_lock_time_avg | 0 || Innodb_row_lock_time_max | 0 || Innodb_row_lock_waits | 0 || Innodb_rows_deleted | 0 || Innodb_rows_inserted | 0 || Innodb_rows_read | 0 || Innodb_rows_updated | 0 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 0 || Key_blocks_used | 14 || Key_read_requests | 41208954 || Key_reads | 1932246 || Key_write_requests | 617 || Key_writes | 521 || Last_query_cost | 0.000000 || Max_used_connections | 39 || Not_flushed_delayed_rows | 0 || Open_files | 8 || Open_streams | 0 || Open_tables | 4 || Opened_tables | 0 || Qcache_free_blocks | 798 || Qcache_free_memory | 43080304 || Qcache_hits | 36017 || Qcache_inserts | 12560 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 13694 || Qcache_queries_in_cache | 3040 || Qcache_total_blocks | 7032 || Questions | 136967 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 1 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 1 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 0 || Sort_rows | 0 || Sort_scan | 0 || Ssl_accept_renegotiates | 0 || Ssl_accepts | 0 || Ssl_callback_cache_hits | 0 || Ssl_cipher | || Ssl_cipher_list | || Ssl_client_connects | 0 || Ssl_connect_renegotiates | 0 || Ssl_ctx_verify_depth | 0 || Ssl_ctx_verify_mode | 0 || Ssl_default_timeout | 0 || Ssl_finished_accepts | 0 || Ssl_finished_connects | 0 || Ssl_session_cache_hits | 0 || Ssl_session_cache_misses | 0 || Ssl_session_cache_mode | NONE || Ssl_session_cache_overflows | 0 || Ssl_session_cache_size | 0 || Ssl_session_cache_timeouts | 0 || Ssl_sessions_reused | 0 || Ssl_used_session_cache_entries | 0 || Ssl_verify_depth | 0 || Ssl_verify_mode | 0 || Ssl_version | || Table_locks_immediate | 42828 || Table_locks_waited | 2002 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 0 || Threads_cached | 0 || Threads_connected | 16 || Threads_created | 5769 || Threads_running | 1 || Uptime | 1275 |±----------------------------------±---------+245 rows in set (0.02 sec)

mysql> show table status;±-------±--------±-----------±-------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |±-------±--------±-----------±-------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+| MyISAM | 10 | Dynamic | 6 | 20 | 120 | 281474976710655 | 2048 | 0 | 7 | 2008-03-24 21:38:30 | 2008-03-24 21:38:30 | 2008-03-31 11:20:57 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 250204 | 325 | 81560548 | 281474976710655 | 8690688 | 0 | 250214 | 2008-03-24 21:38:30 | 2008-04-04 11:04:13 | 2008-04-04 10:35:01 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 461 | 55 | 25684 | 281474976710655 | 13312 | 0 | 462 | 2008-03-24 21:38:53 | 2008-04-04 07:35:43 | 2008-03-31 11:22:23 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Fixed | 467917 | 9 | 4211253 | 2533274790395903 | 6708224 | 0 | NULL | 2008-03-26 23:24:41 | 2008-04-03 12:06:00 | 2008-03-31 11:23:19 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Fixed | 500 | 13 | 6630 | 3659174697238527 | 16384 | 130 | 560574 | 2008-03-24 21:38:59 | 2008-04-04 11:04:12 | 2008-04-02 11:38:00 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 19 | 137 | 2612 | 281474976710655 | 9216 | 0 | 20 | 2008-03-24 21:38:59 | 2008-04-03 00:07:20 | 2008-03-31 11:23:59 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 44569 | 25 | 1155764 | 281474976710655 | 1509376 | 0 | 44594 | 2008-03-24 21:38:59 | 2008-04-04 11:04:05 | 2008-03-31 11:24:14 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0 | NULL | 2008-03-24 21:39:00 | 2008-03-31 11:24:42 | 2008-03-31 11:24:42 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 93560 | 26 | 2443016 | 281474976710655 | 1625088 | 0 | 93561 | 2008-03-24 21:39:00 | 2008-04-03 00:07:20 | 2008-03-31 11:25:47 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Fixed | 342 | 9 | 3078 | 2533274790395903 | 7168 | 0 | NULL | 2008-03-24 21:39:03 | 2008-04-02 12:57:10 | 2008-03-31 11:25:36 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 4481 | 117 | 528580 | 281474976710655 | 186368 | 0 | 4482 | 2008-03-24 21:39:03 | 2008-04-04 11:03:42 | 2008-03-31 11:25:36 | latin1_swedish_ci | NULL | NULL | || MyISAM | 10 | Fixed | 6811 | 10 | 68110 | 2814749767106559 | 169984 | 0 | NULL | 2008-03-24 21:39:03 | 2008-04-04 10:42:18 | 2008-03-31 11:25:56 | latin1_swedish_ci | NULL | NULL | |---------------±-------±--------±-----------±-------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+12 rows in set (0.02 sec)mysql> show table status;±-------±--------±-----------±--------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |±-------±--------±-----------±--------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+| MyISAM | 10 | Dynamic | 6 | 20 | 120 | 281474976710655 | 2048 | 0 | 21 | 2008-03-22 16:53:34 | 2008-03-22 16:53:34 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 281887 | 479 | 135063932 | 281474976710655 | 14056448 | 0 | 290410 | 2008-03-22 16:53:34 | 2008-04-04 11:05:13 | 2008-03-22 16:54:06 | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Fixed | 1291028 | 9 | 11619252 | 2533274790395903 | 39286784 | 0 | NULL | 2008-03-22 16:54:06 | 2008-04-04 09:00:32 | 2008-03-22 16:54:25 | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Fixed | 500 | 13 | 6578 | 3659174697238527 | 9216 | 78 | 411800 | 2008-03-22 16:54:25 | 2008-04-04 11:04:24 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 19 | 136 | 2596 | 281474976710655 | 5120 | 0 | 36 | 2008-03-22 16:54:25 | 2008-04-04 09:00:32 | 2008-03-22 16:54:25 | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 32655 | 28 | 928444 | 281474976710655 | 395264 | 0 | 32674 | 2008-03-22 16:54:25 | 2008-04-04 11:04:48 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Fixed | 0 | 0 | 0 | 3096224743817215 | 1024 | 0 | NULL | 2008-03-22 16:54:26 | 2008-03-22 16:54:26 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 107118 | 26 | 2806496 | 281474976710655 | 1102848 | 0 | 107119 | 2008-03-22 16:54:26 | 2008-04-04 09:00:32 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Fixed | 126 | 9 | 1134 | 2533274790395903 | 4096 | 0 | NULL | 2008-03-22 16:54:28 | 2008-04-01 09:37:34 | NULL | utf8_general_ci | NULL | NULL | || MyISAM | 10 | Dynamic | 1489 | 120 | 178740 | 281474976710655 | 17408 | 0 | 1490 | 2008-03-22 16:54:28 | 2008-04-04 10:51:22 | NULL | utf8_general_ci | NULL | NULL | |±-------±--------±-----------±--------±---------------±------------±-----------------±-------------±----------±---------------±--------------------±--------------------±--------------------±------------------±---------±---------------±--------+13 rows in set (0.02 sec)

2

Its hard to tell whats the root cause.

Can you post mysql settings here (show global variables, or your my.cnf file)?

3

Just a note, I do skip-networking because I dont need to connect from outside and skip-innodb because I don’t use innodb tables. Although I do have frequent index corruptions on one of my tables that gets updated very frequently, so I’ve been suggested to start using innodb? Or I think maybe if i use transactions for myisam, it might stop corruption, what do you think?

mysql> show global variables;±--------------------------------±-------------------------------------------------------+| Variable_name | Value |±--------------------------------±-------------------------------------------------------+| auto_increment_increment | 1 || auto_increment_offset | 1 || automatic_sp_privileges | ON || back_log | 50 || basedir | /usr/ || bdb_cache_size | 8388600 || bdb_home | || bdb_log_buffer_size | 0 || bdb_logdir | || bdb_max_lock | 10000 || bdb_shared_data | OFF || bdb_tmpdir | || binlog_cache_size | 32768 || bulk_insert_buffer_size | 8388608 || character_set_client | latin1 || character_set_connection | latin1 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ || collation_connection | latin1_swedish_ci || collation_database | latin1_swedish_ci || collation_server | latin1_swedish_ci || completion_type | 0 || concurrent_insert | 1 || connect_timeout | 5 || datadir | /var/lib/mysql/ || date_format | %Y-%m-%d || datetime_format | %Y-%m-%d %H:%i:%s || default_week_format | 0 || delay_key_write | ON || delayed_insert_limit | 100 || delayed_insert_timeout | 300 || delayed_queue_size | 1000 || div_precision_increment | 4 || engine_condition_pushdown | OFF || expire_logs_days | 0 || flush | OFF || flush_time | 0 || ft_boolean_syntax | + -><()~*:“”&| || ft_max_word_len | 84 || ft_min_word_len | 4 || ft_query_expansion_limit | 20 || ft_stopword_file | (built-in) || group_concat_max_len | 1024 || have_archive | NO || have_bdb | DISABLED || have_blackhole_engine | NO || have_compress | YES || have_crypt | YES || have_csv | NO || have_example_engine | NO || have_federated_engine | NO || have_geometry | YES || have_innodb | DISABLED || have_isam | NO || have_ndbcluster | NO || have_openssl | DISABLED || have_query_cache | YES || have_raid | NO || have_rtree_keys | YES || have_symlink | YES || init_connect | || init_file | || init_slave | || innodb_additional_mem_pool_size | 1048576 || innodb_autoextend_increment | 8 || innodb_buffer_pool_awe_mem_mb | 0 || innodb_buffer_pool_size | 8388608 || innodb_checksums | ON || innodb_commit_concurrency | 0 || innodb_concurrency_tickets | 500 || innodb_data_file_path | || innodb_data_home_dir | || innodb_doublewrite | ON || innodb_fast_shutdown | 1 || innodb_file_io_threads | 4 || innodb_file_per_table | OFF || innodb_flush_log_at_trx_commit | 1 || innodb_flush_method | || innodb_force_recovery | 0 || innodb_lock_wait_timeout | 50 || innodb_locks_unsafe_for_binlog | OFF || innodb_log_arch_dir | || innodb_log_archive | OFF || innodb_log_buffer_size | 1048576 || innodb_log_file_size | 5242880 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | || innodb_max_dirty_pages_pct | 90 || innodb_max_purge_lag | 0 || innodb_mirrored_log_groups | 1 || innodb_open_files | 300 || innodb_support_xa | ON || innodb_sync_spin_loops | 20 || innodb_table_locks | ON || innodb_thread_concurrency | 8 || innodb_thread_sleep_delay | 10000 || interactive_timeout | 28800 || join_buffer_size | 131072 || key_buffer_size | 16384 || key_cache_age_threshold | 300 || key_cache_block_size | 1024 || key_cache_division_limit | 100 || language | /usr/share/mysql/english/ || large_files_support | ON || large_page_size | 0 || large_pages | OFF || license | GPL || local_infile | ON || locked_in_memory | OFF || log | OFF || log_bin | OFF || log_bin_trust_function_creators | OFF || log_error | || log_slave_updates | OFF || log_slow_queries | OFF || log_warnings | 1 || long_query_time | 10 || low_priority_updates | OFF || lower_case_file_system | OFF || lower_case_table_names | 0 || max_allowed_packet | 1047552 || max_binlog_cache_size | 4294967295 || max_binlog_size | 1073741824 || max_connect_errors | 10 || max_connections | 300 || max_delayed_threads | 20 || max_error_count | 64 || max_heap_table_size | 16777216 || max_insert_delayed_threads | 20 || max_join_size | 4294967295 || max_length_for_sort_data | 1024 || max_prepared_stmt_count | 16382 || max_relay_log_size | 0 || max_seeks_for_key | 4294967295 || max_sort_length | 1024 || max_sp_recursion_depth | 0 || max_tmp_tables | 32 || max_user_connections | 0 || max_write_lock_count | 4294967295 || multi_range_count | 256 || myisam_data_pointer_size | 6 || myisam_max_sort_file_size | 2147483647 || myisam_recover_options | OFF || myisam_repair_threads | 1 || myisam_sort_buffer_size | 8388608 || myisam_stats_method | nulls_unequal || net_buffer_length | 2048 || net_read_timeout | 30 || net_retry_count | 10 || net_write_timeout | 60 || new | OFF || old_passwords | OFF || open_files_limit | 1510 || optimizer_prune_level | 1 || optimizer_search_depth | 62 || pid_file | /var/run/mysqld/mysqld.pid || prepared_stmt_count | 0 || port | 3306 || preload_buffer_size | 32768 || protocol_version | 10 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 52428800 || query_cache_type | ON || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || range_alloc_block_size | 2048 || read_buffer_size | 258048 || read_only | OFF || read_rnd_buffer_size | 258048 || relay_log_purge | ON || relay_log_space_limit | 0 || rpl_recovery_rank | 0 || secure_auth | OFF || server_id | 1 || skip_external_locking | ON || skip_networking | ON || skip_show_database | OFF || slave_compressed_protocol | OFF || slave_load_tmpdir | /tmp/ || slave_net_timeout | 3600 || slave_skip_errors | OFF || slave_transaction_retries | 10 || slow_launch_time | 2 || socket | /var/lib/mysql/mysql.sock || sort_buffer_size | 65528 || sql_mode | || sql_notes | ON || sql_warnings | ON || storage_engine | MyISAM || sync_binlog | 0 || sync_frm | ON || system_time_zone | CDT || table_cache | 4 || table_lock_wait_timeout | 50 || table_type | MyISAM || thread_cache_size | 0 || thread_stack | 131072 || time_format | %H:%i:%s || time_zone | SYSTEM || timed_mutexes | OFF || tmp_table_size | 33554432 || tmpdir | || transaction_alloc_block_size | 8192 || transaction_prealloc_size | 4096 || tx_isolation | REPEATABLE-READ || updatable_views_with_limit | YES || version | 5.0.22 || version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006) || version_comment | Source distribution || version_compile_machine | i686 || version_compile_os | redhat-linux-gnu || wait_timeout | 28800 |±--------------------------------±-------------------------------------------------------+218 rows in set (0.05 sec)

my.cnf

more /etc/my.cnf# Example MySQL config file for small systems.## This is for a system with little memory (<= 64M) where MySQL is only used# from time to time and it’s important that the mysqld daemon# doesn’t use much resources.## You can copy this file to# /etc/my.cnf to set global options,# mysql-data-dir/my.cnf to set server-specific options (in this# installation this directory is /var/lib/mysql) or# ~/.my.cnf to set user-specific options.## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the “–help” option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /var/lib/mysql/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]query_cache_size = 50Mquery_cache_limit = 1Mset-variable = max_connections=300skip-innodbport = 3306socket = /var/lib/mysql/mysql.sockskip-lockingkey_buffer = 16Kmax_allowed_packet = 1Mtable_cache = 4sort_buffer_size = 64Kread_buffer_size = 256Kread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 64K# Don’t listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (using the “enable-named-pipe” option) will render mysqld useless!#skip-networkingserver-id = 1# Uncomment the following if you want to log updates#log-bin=mysql-bin# Uncomment the following if you are NOT using BDB tablesskip-bdb# Uncomment the following if you are using InnoDB tables#innodb_data_home_dir = /var/lib/mysql/#innodb_data_file_path = ibdata1:10M:autoextend#innodb_log_group_home_dir = /var/lib/mysql/#innodb_log_arch_dir = /var/lib/mysql/# You can set …_buffer_pool_size up to 50 - 80 %# of RAM but beware of setting memory usage too high#innodb_buffer_pool_size = 16M#innodb_additional_mem_pool_size = 2M# Set …_log_file_size to 25 % of buffer pool size#innodb_log_file_size = 5M#innodb_log_buffer_size = 8M#innodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).#old_passwords=1[mysql.server]user=mysqlbasedir=/var/lib[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]key_buffer = 8Msort_buffer_size = 8M[myisamchk]key_buffer = 8Msort_buffer_size = 8M[mysqlhotcopy]interactive-timeout

4

try below:

join_buffer_size: 131072 → 256Kkey_buffer_size: 16384 → 32Mread_buffer_size: 258048read_rnd_buffer_size: 258048sort_buffer_size: 65528 → 128Ktable_cache: 4 → 512thread_concurrency → 8thread_cache_size → 32wait_timeout: 28800 → 120

restart mysqld and monitor mysql status to fine-tune them then (mysqlreport at [URL]http://hackmysql.com/mysqlreport[/URL] can help you this).

Hope this help )

5

For some reason now my mysql is taking way too much memory, notice how the mysql line in my top command. This may also be responsible for my first crash in 13 days:

top - 16:40:51 up 2:10, 1 user, load average: 0.79, 0.88, 1.13Tasks: 94 total, 3 running, 91 sleeping, 0 stopped, 0 zombieCpu(s): 16.4%us, 1.7%sy, 0.0%ni, 81.6%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%stMem: 1035324k total, 936804k used, 98520k free, 56468k buffersSwap: 2040244k total, 2580k used, 2037664k free, 372752k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3899 apache 15 0 56620 15m 9564 S 10.0 1.5 0:02.51 httpd 2863 mysql 15 0 223m 171m 3412 S 3.0 17.0 30:49.09 mysqld 3957 apache 15 0 56208 16m 11m S 1.3 1.7 0:04.45 httpd 4084 apache 15 0 56404 15m 9548 S 1.3 1.5 0:02.49 httpd 3635 apache 15 0 57216 16m 10m S 1.0 1.7 0:08.31 httpd 4109 apache 15 0 55040 11m 7528 S 1.0 1.2 0:00.81 httpd 4013 apache 15 0 56008 15m 10m S 0.3 1.5 0:04.12 httpd 4080 root 15 0 2172 1000 788 R 0.3 0.1 0:01.61 top 1 root 15 0 2044 636 544 S 0.0 0.1 0:00.57 init 2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0 4 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0 5 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0 6 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 khelper 7 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kthread 10 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kblockd/0 11 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid

6

sure, with the parameter as I suggest you, it must take more memory than before but help to reduce the swap of memory to disk, and also CPU usage.

BTW, I can see there may be a bottle-neck in apache (CPU usage and memory consume) as you can see in the top result. Optimize your apache config (change parameters, remove unused modules) and also the application source-code.
For hardware, upgrade RAM could help.