problem in mysql service

tanveer · 03-24-2007, 11:46 PM

Hi all:

I have faced with a sudden problem like this when in my webpage its showing lots of mysql_connect warnings and when I did ps -aux then it shows lots and lots of thses below:-

Code:

mysql 32762 1.1 1.7 92324 36860 ? D 11:45 0:07 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 32763 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 32764 1.2 1.7 92324 36860 ? D 11:45 0:07 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 32765 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 32766 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 32767 1.1 1.7 92324 36860 ? D 11:45 0:07 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 300 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 301 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

mysql 302 1.0 1.7 92324 36860 ? D 11:45 0:06 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

And 7/8 of these , which it normally shows when everything is ok about mysql

Code:

mysql 1172 0.0 0.6 32796 13388 pts/1 S 11:59 0:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/career.pid --skip-locking

Now when I gave the command

# service mysqld restart
It was giving an error about a .pid file. Even it doesn't allowing to kill the mysql porcesses with killall command.

So after giving 2 time the kill command then it became in the position to accept this command
# Service mysqld restart

After that it was ok.

Anyone have any idea why this was happening all of a sudden. Also it happened another time before but still I didn't find out where is the problem.

You will notice that the problematic lines of mysql shows status 'D' but normal ones shows 'S' there.
The PROCESS STATE CODES

D uninterruptible sleep (usually IO)
R runnable (on run queue)
S sleeping
T traced or stopped
Z a defunct ("zombie") process
Anybody ever faced this before as I have to pinpoint the source of this because it can happen again and then users cant even login in the website.

Thanks in advance. Waiting for reply.

zaichik · 03-25-2007, 11:08 PM

Looks like you might be facing some IO blocking due to locked tables. Have you tried to look at the mysql process list
while this is going on? My bet is that you will see a whole bunch of locked tables. Next time, try:

Code:

mysqladmin processlist

If you see a bunch of locked tables, then the issue is that queries are not being processed fast enough, likely due to not
enough memory being allocated to some key mysql memory usage variables, or a lack of proper indexes on the tables.

If that is the case, perhaps you could provide the output of

Code:

mysqladmin status

in this thread, and maybe make the output of

Code:

mysqladmin variables
mysqladmin extended-status

available on a website somewhere (they will be rather long).

tanveer · 03-25-2007, 11:50 PM

hi zaichik,
Thanks for replying.
The time it occured I was sweating on how quickly to resolve this issue and furthermore the command you mentioned haven't seen them before. My mysql knowledge is about running queries and a little bit administration.
Now tell me should I provide you the output of those commands now or when if this situation occurs again(Praying this never happens again).
And how to make the output of

Quote:

mysqladmin variables
mysqladmin extended-status

available on the website and what to look for there?

thanks.

tanveer · 03-27-2007, 01:20 AM

Here is

Code:

mysql> show status;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 2          |
| Aborted_connects         | 4          |
| Binlog_cache_disk_use    | 0          |
| Binlog_cache_use         | 0          |
| Bytes_received           | 115153244  |
| Bytes_sent               | 1672903180 |
| 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            | 957835     |
| 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               | 5109       |
| 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               | 35156      |
| 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               | 967560     |
| 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       | 5          |
| Com_show_errors          | 0          |
| Com_show_fields          | 288        |
| Com_show_grants          | 0          |
| Com_show_innodb_status   | 0          |
| Com_show_keys            | 0          |
| Com_show_logs            | 0          |
| Com_show_master_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          | 2          |
| Com_show_storage_engines | 0          |
| Com_show_tables          | 6          |
| Com_show_variables       | 1          |
| Com_show_warnings        | 0          |
| Com_slave_start          | 0          |
| Com_slave_stop           | 0          |
| Com_truncate             | 0          |
| Com_unlock_tables        | 0          |
| Com_update               | 26823      |
| Com_update_multi         | 0          |
| Connections              | 96916      |
| Created_tmp_disk_tables  | 10553      |
| Created_tmp_files        | 3          |
| Created_tmp_tables       | 21091      |
| Delayed_errors           | 0          |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Flush_commands           | 1          |
| Handler_commit           | 0          |
| Handler_delete           | 22687      |
| Handler_discover         | 0          |
| Handler_read_first       | 2          |
| Handler_read_key         | 953495     |
| Handler_read_next        | 3171120    |
| Handler_read_prev        | 0          |
| Handler_read_rnd         | 483372     |
| Handler_read_rnd_next    | 1984033200 |
| Handler_rollback         | 2027       |
| Handler_update           | 9076       |
| Handler_write            | 914432     |
| Key_blocks_not_flushed   | 0          |
| Key_blocks_unused        | 0          |
| Key_blocks_used          | 7248       |
| Key_read_requests        | 10044262   |
| Key_reads                | 55609      |
| Key_write_requests       | 983287     |
| Key_writes               | 82539      |
| Max_used_connections     | 46         |
| Not_flushed_delayed_rows | 0          |
| Open_files               | 112        |
| Open_streams             | 0          |
| Open_tables              | 64         |
| Opened_tables            | 320        |
| Qcache_free_blocks       | 0          |
| Qcache_free_memory       | 0          |
| Qcache_hits              | 0          |
| Qcache_inserts           | 0          |
| Qcache_lowmem_prunes     | 0          |
| Qcache_not_cached        | 0          |
| Qcache_queries_in_cache  | 0          |
| Qcache_total_blocks      | 0          |
| Questions                | 2089811    |
| Rpl_status               | NULL       |
| Select_full_join         | 35         |
| Select_full_range_join   | 0          |
| Select_range             | 0          |
| Select_range_check       | 0          |
| Select_scan              | 575295     |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 0          |
| Slow_queries             | 11         |
| Sort_merge_passes        | 0          |
| Sort_range               | 18356      |
| Sort_rows                | 10401195   |
| Sort_scan                | 261952     |
| Table_locks_immediate    | 1045385    |
| Table_locks_waited       | 354        |
| Threads_cached           | 0          |
| Threads_connected        | 3          |
| Threads_created          | 96915      |
| Threads_running          | 1          |
| Uptime                   | 519266     |
+--------------------------+------------+
156 rows in set (0.00 sec)

Let me know what you all think of this?