mySQL Croaks Every 10 Days Or So?

[adrianchew]

I've had mySQL go nuts twice now - still haven't upgraded, its running 3.23.41... the loads just spiked up, plenty of memory left, restart mySQL, loads drop to normal again.

This is happening without vB seeing any more traffic than usual either. The only thing that happened by chance around the same time was a forum was added, and some threads moved.

I'm going to upgrade tonight, but just wondering if others have seen mySQL just go crazy for no reason? :confused:

[adrianchew]

Here's the my.cnf and extended status (latter is pretty useless since its after I just installed 3.23.52). There 2 vB sites - 50MB and 5MB respectively in database size (small) but the main site has 60-80 users, the smaller site has about 10 max. pconnect is off.

Just upgraded to 3.23.52. Hope it fixes my problems! :D


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=300
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=14400
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable = max_allowed_packet=16M


[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Wed Sep 25 19:58:44 EDT 2002


7:58pm up 10 days, 10:03, 1 user, load average: 0.03, 0.21, 0.25
69 processes: 68 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 524288K av, 155260K used, 369028K free, 18480K shrd, 0K buff
Swap: 524288K av, 0K used, 524288K free 89592K cached



Http processes currently running = 29
Mysql processes currently running = 8

Netstat information summary
3 FIN_WAIT2
4 TIME_WAIT
7 ESTABLISHED
16 LISTEN

+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 339857 |
| Bytes_sent | 4526875 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 86 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 15 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 23 |
| Com_insert_select | 2 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 1671 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 156 |
| Connections | 91 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 47 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4 |
| Handler_read_first | 189 |
| Handler_read_key | 11595 |
| Handler_read_next | 30859 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 18803 |
| Handler_read_rnd_next | 47659 |
| Handler_update | 158 |
| Handler_write | 561 |
| Key_blocks_used | 352 |
| Key_read_requests | 58092 |
| Key_reads | 352 |
| Key_write_requests | 175 |
| Key_writes | 121 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 52 | 5% of table_cache in use
| Open_files | 96 |
| Open_streams | 0 |
| Opened_tables | 58 |
| Questions | 2047 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 367 |
| Select_range_check | 0 |
| Select_scan | 302 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 345 |
| Sort_rows | 20362 |
| Sort_scan | 216 |
| Table_locks_immediate | 2191 |
| Table_locks_waited | 0 |
| Threads_cached | 1 |
| Threads_created | 2 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 276 | 4 mins 36 secs
+--------------------------+---------+


Key Reads/Key Read Requests = 0.006059 (Cache hit = 99.993941%)
Key Writes/Key Write Requests = 0.691429
Connections/second = 0.330 (/hour = 1186.957)
KB received/second = 1.199 (/hour = 4317.391)
KB sent/second = 16.014 (/hour = 57652.174)
Temporary Tables Created/second = 0.170 (/hour = 613.043)
Opened Tables/second = 0.210 (/hour = 756.522)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 7.417 (/hour = 26700.000)

[eva2000]

can you post the url to mysql extended-status as well as whether you have other scripts or sites which use mysql besides vBs ?

[adrianchew]

http://carmen.skatingforums.com/status/myinfo.php

I do have an old archive site on phpBB2 (that pretty much isn't accessed anymore), plus a test board running Invision Board 1.0.1 (conversion using the main vB site database). But both are pretty much 1 user at this point (me) just to play around. :D

The whole mysql data directory is about 190MB for now.

[eva2000]

looks fine to me... what do you mean mysql croaks ? any error messages ?

[adrianchew]

Originally posted by eva2000

looks fine to me... what do you mean mysql croaks ? any error messages ?

It just croaks - no error messages, just one slow server (usual load is 0.x range, loads go up to 10-15 when this happens). Lots of RAM left, and even the mysql processes using top don't seem out of control. Couldn't figure out what was causing the problem - the last time it happened I just rebooted and everything was back to normal. This time, I just restarted mysql and the loads came back to normal.

I'm guessing its something in 3.23.41 that's broken, that's hopefully fixed in 3.23.52 (it will take at least 2 weeks for me to find out for sure though). :(

[Alex]

This has something to do with the log-function from mysql. You should turn it off. We have had the same problem...

Somehow, mysql has a maximum on fliesize with logging. If this filesize exceeds, mysql is almost stop working. We've had the same problem with our server at first.

After figuring this out and disabling the logging it's working smooth without problems.

Sorry, but i can't remember right now, how to disable the logging function. Maybe someone else can help?