Mysql going down every day now.

[mvigod]

Well...everything was running ok. I was on a 800Mhz server w/1GB RAM running PHP 4.06 and mysql 3.23.33.

I then had upgraded my hardware to a dual processor 1Ghz and 1.5GB RAM. Changed nothing else.

Now, about once a day my server becomes inaccesible. I can get to the other websites I have apache running (very little traffic on these) but not to vbulletin. All I have to do is stop and start mysql for it to come back. Usually I catch this 3-6 hours after it happens so we are down all the time and the 3500+ users are starting to get unruly.

Getting mysql error number 2003 emailed to me...link id can't connect to mydomain.com....

Here is my status output after restarting:

mysql Ver 11.12 Distrib 3.23.33, for pc-linux-gnu (i686)

Connection id: 3302
Current database:
Current user: root@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.33-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 36 min 17 sec

Threads: 2 Questions: 130002 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 68 Queries per second avg: 59.716
--------------



Extended status output:

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 63 |
| Bytes_received | 20112150 |
| Bytes_sent | 271574528 |
| Connections | 4783 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 2717 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 433 |
| Handler_read_first | 3391 |
| Handler_read_key | 765284 |
| Handler_read_next | 7168080 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1040406 |
| Handler_read_rnd_next | 12474800 |
| Handler_update | 26490 |
| Handler_write | 325403 |
| Key_blocks_used | 2838 |
| Key_read_requests | 3630720 |
| Key_reads | 2822 |
| Key_write_requests | 4561 |
| Key_writes | 4213 |
| Max_used_connections | 14 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 69 |
| Open_files | 109 |
| Open_streams | 0 |
| Opened_tables | 75 |
| Questions | 138711 |
| Select_full_join | 4 |
| Select_full_range_join | 0 |
| Select_range | 37498 |
| Select_range_check | 0 |
| Select_scan | 23857 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 31916 |
| Sort_rows | 1057533 |
| Sort_scan | 8879 |
| Table_locks_immediate | 147937 |
| Table_locks_waited | 79 |
| Threads_cached | 10 |
| Threads_created | 15 |
| Threads_connected | 5 |
| Threads_running | 1 |
| Uptime | 2305 |
+--------------------------+-----------+

[mvigod]

| Variable_name | Value |
+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | NO |
| have_gemini | NO |
| have_innobase | NO |
| have_isam | YES |
| have_raid | NO |
| have_ssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 7335936 |
| key_buffer_size | 16773120 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 400 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /var/lib/mysql/www.vlminc.net.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 7335936 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 10485752 |
| table_cache | 1024 |
| table_type | MYISAM |
| thread_cache_size | 256 |
| thread_stack | 65536 |
| timezone | EST |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.33-log |
| wait_timeout | 28800

[mvigod]

Here is my.cnf

www: /etc# more my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=400
set-variable = key_buffer=16M
set-variable = join_buffer=7M
set-variable = record_buffer=7M
set-variable = sort_buffer=10M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
log-slow-queries=/var/log/mysql/slow-queries.log

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

[eva2000]

try removing

log-slow-queries=/var/log/mysql/slow-queries.log

from my.cnf file and restart mysql

also try viewing your mysql error log named /path/to/mysql/data/directory/hostname.err

as root user type

tail -400 /path/to/mysql/data/directory/hostname.err

to see what mysql error log tells you

[mvigod]

Eva,

I pm'd you the url to view my mysql error log...let me know if you find anything ugly in it that could be causing the problem here.

thanks

[eva2000]

not much is shown in there except the usual connections aborted

see how removing slow query log and restarting mysql goes

[mvigod]

Well...what I did last night to ensure the site would stay up was to shut down and restart mysql every 20 minutes. The site was up this morning and then about 30 minutes ago down again.

I went into the server and tried to connect to the database using mysql and it wouldn't connect nor would the mysqladmin extended-status run. I checked the process list and the mysql daemon was still in it.

I then tried to shut it down and restart it but it would not let me. I had to kill -9 it just to get rid of it. Then I tried to restart it and it wouldn't even restart! I used myisamch --recover --quick on all my tables and tried again but it won't start...

Could this have anything to do with the SMP linux kernel that was installed on my server when I upgraded to the dual 1Ghz?

[eva2000]

.are you running the commands as root user ? i.e.

what exact linux kernel are you using as well

[mvigod]

Originally posted by eva2000
.are you running the commands as root user ? i.e.

what exact linux kernel are you using as well

Yes...always as root.

Kernel version 2.2.17-14.1

[eva2000]

not sure you might want to try upgrading to latest mysql version to catch up on all the bugs between 3.23.33 - 3.23.47

[mvigod]

Originally posted by eva2000
not sure you might want to try upgrading to latest mysql version to catch up on all the bugs between 3.23.33 - 3.23.47

OK...I downloaded all the 3.23.47 rpms.

Is there anything I need to do other than run them?

Is this procedure correct below:

Shut down mysqld daemon:

rpm -Uvh mysql*

Check the /etc/my.cnf in case it was saved as my.cnf.rpmsave

Restart the daemon

[eva2000]

Originally posted by mvigod


OK...I downloaded all the 3.23.47 rpms.

Is there anything I need to do other than run them?

Is this procedure correct below:

Shut down mysqld daemon:

rpm -Uvh mysql*

Check the /etc/my.cnf in case it was saved as my.cnf.rpmsave

Restart the daemon that's right was 3.23.33 installed via rpms or compiled from source ? usually upgrade the same method as with the current mysql version - much easier

[mvigod]

Well...heres the strange part...

I think it was installed via rpm since I have no source files laying around and always keep them. If I recall this was the standard mysql install on redhat 6.2. At some point my rpm database file got corrupted and I had to rebuild a new one. The mysql rpm is nowhere to be found in the rpm database.

Could this cause any problems or should I take certain precautions prior to running the rpm -Uvh mysql* command?