Bug #64538 Data base crashes....t_cpe_parameters_l;
Submitted: 4 Mar 2012 13:59 Modified: 5 Apr 2012 16:18
Reporter: Todd Ruelle Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.5.21 OS:Linux (Red Hat 5)
Assigned to: CPU Architecture:Any
Tags: "Select*From" statements in code, active queries drop, crashing at1mb per sec

[4 Mar 2012 13:59] Todd Ruelle
Description:
when I start the daemon the database traffic goes to 30MB/sec but while it is running, this slowly drops to less then 1MB/sec before crashing.
I have a long list of threads (about 500 ) in the database, but the number of threads with active queries drops continuously to reach very low numbers after some time ( 5-10 threads)

There are really still many "SELECT * FROM" statements in the code. Particularly the t_cpe_parameters_xx table worries me because that has changed several times over the last months.

mysql> describe t_cpe_parameters_1;
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field                  | Type         | Null | Key | Default             | Extra                       |
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| parameter_id           | bigint(20)   | NO   | PRI | NULL                | auto_increment              |
| parameter_pr_id        | bigint(20)   | NO   | MUL | NULL                |                             |
| parameter_name         | varchar(512) | NO   | MUL | NULL                |                             |
| parameter_value        | mediumtext   | YES  |     | NULL                |                             |
| parameter_type         | varchar(16)  | NO   |     | unknown             |                             |
| parameter_writable     | int(11)      | NO   |     | 0                   |                             |
| parameter_notification | int(11)      | NO   |     | 0                   |                             |
| parameter_access_list  | mediumtext   | YES  |     | NULL                |                             |
| cpe_id                 | bigint(20)   | NO   | MUL | NULL                |                             |
| updated_tm             | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| created_tm             | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
+------------------------+--------------+------+-----+---------------------+-----------------------------+
11 rows in set (0.00 sec)

Innodb_buffer_pool_pages_data............. 256329
Innodb_buffer_pool_pages_dirty............ 8547
Innodb_buffer_pool_pages_flushed.......... 31458
Innodb_buffer_pool_pages_free............. 253478
Innodb_buffer_pool_pages_misc............. 14481
Innodb_buffer_pool_pages_total............ 524288
Innodb_buffer_pool_read_ahead_rnd......... 0
Innodb_buffer_pool_read_ahead............. 665
Innodb_buffer_pool_read_ahead_evicted..... 0
Innodb_buffer_pool_read_requests.......... 136318051
Innodb_buffer_pool_reads.................. 252771
Innodb_buffer_pool_wait_free.............. 0
Innodb_buffer_pool_write_requests......... 739280
Innodb_data_fsyncs........................ 69574
Innodb_data_pending_fsyncs................ 1
Innodb_data_pending_reads................. 3
Innodb_data_pending_writes................ 0
Innodb_data_read.......................... 4178595840
Innodb_data_reads......................... 254915
Innodb_data_writes........................ 85123
Innodb_data_written....................... 597281792
Innodb_dblwr_pages_written................ 15729
Innodb_dblwr_writes....................... 322
Innodb_have_atomic_builtins............... ON
Innodb_log_waits.......................... 0
Innodb_log_write_requests................. 95884
Innodb_log_writes......................... 68800
Innodb_os_log_fsyncs...................... 69013
Innodb_os_log_pending_fsyncs.............. 1
Innodb_os_log_pending_writes.............. 0
Innodb_os_log_written..................... 81806848
Innodb_page_size.......................... 16384
Innodb_pages_created...................... 1422
Innodb_pages_read......................... 254904
Innodb_pages_written...................... 15729
Innodb_row_lock_current_waits............. 0
Innodb_row_lock_time...................... 356
Innodb_row_lock_time_avg.................. 4
Innodb_row_lock_time_max.................. 145
Innodb_row_lock_waits..................... 89
Innodb_rows_deleted....................... 6692
Innodb_rows_inserted...................... 26027
Innodb_rows_read.......................... 338350149
Innodb_rows_updated....................... 61274

How to repeat:
when I start the daemon the database traffic goes to 30MB/sec but while it is running, this slowly drops to less then 1MB/sec before crashing.
I have a long list of threads (about 500 ) in the database, but the number of threads with active queries drops continuously to reach very low numbers after some time ( 5-10 threads)

There are really still many "SELECT * FROM" statements in the code. Particularly the t_cpe_parameters_xx table worries me because that has changed several times over the last months.

mysql> describe t_cpe_parameters_1;
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field                  | Type         | Null | Key | Default             | Extra                       |
+------------------------+--------------+------+-----+---------------------+-----------------------------+
| parameter_id           | bigint(20)   | NO   | PRI | NULL                | auto_increment              |
| parameter_pr_id        | bigint(20)   | NO   | MUL | NULL                |                             |
| parameter_name         | varchar(512) | NO   | MUL | NULL                |                             |
| parameter_value        | mediumtext   | YES  |     | NULL                |                             |
| parameter_type         | varchar(16)  | NO   |     | unknown             |                             |
| parameter_writable     | int(11)      | NO   |     | 0                   |                             |
| parameter_notification | int(11)      | NO   |     | 0                   |                             |
| parameter_access_list  | mediumtext   | YES  |     | NULL                |                             |
| cpe_id                 | bigint(20)   | NO   | MUL | NULL                |                             |
| updated_tm             | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| created_tm             | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
+------------------------+--------------+------+-----+---------------------+-----------------------------+
11 rows in set (0.00 sec)

Innodb_buffer_pool_pages_data............. 256329
Innodb_buffer_pool_pages_dirty............ 8547
Innodb_buffer_pool_pages_flushed.......... 31458
Innodb_buffer_pool_pages_free............. 253478
Innodb_buffer_pool_pages_misc............. 14481
Innodb_buffer_pool_pages_total............ 524288
Innodb_buffer_pool_read_ahead_rnd......... 0
Innodb_buffer_pool_read_ahead............. 665
Innodb_buffer_pool_read_ahead_evicted..... 0
Innodb_buffer_pool_read_requests.......... 136318051
Innodb_buffer_pool_reads.................. 252771
Innodb_buffer_pool_wait_free.............. 0
Innodb_buffer_pool_write_requests......... 739280
Innodb_data_fsyncs........................ 69574
Innodb_data_pending_fsyncs................ 1
Innodb_data_pending_reads................. 3
Innodb_data_pending_writes................ 0
Innodb_data_read.......................... 4178595840
Innodb_data_reads......................... 254915
Innodb_data_writes........................ 85123
Innodb_data_written....................... 597281792
Innodb_dblwr_pages_written................ 15729
Innodb_dblwr_writes....................... 322
Innodb_have_atomic_builtins............... ON
Innodb_log_waits.......................... 0
Innodb_log_write_requests................. 95884
Innodb_log_writes......................... 68800
Innodb_os_log_fsyncs...................... 69013
Innodb_os_log_pending_fsyncs.............. 1
Innodb_os_log_pending_writes.............. 0
Innodb_os_log_written..................... 81806848
Innodb_page_size.......................... 16384
Innodb_pages_created...................... 1422
Innodb_pages_read......................... 254904
Innodb_pages_written...................... 15729
Innodb_row_lock_current_waits............. 0
Innodb_row_lock_time...................... 356
Innodb_row_lock_time_avg.................. 4
Innodb_row_lock_time_max.................. 145
Innodb_row_lock_waits..................... 89
Innodb_rows_deleted....................... 6692
Innodb_rows_inserted...................... 26027
Innodb_rows_read.......................... 338350149
Innodb_rows_updated....................... 61274
[4 Mar 2012 14:12] Todd Ruelle
Critical for Customer
[4 Mar 2012 14:12] Todd Ruelle
Critical for Customer
[4 Mar 2012 14:48] Valeriy Kravchuk
What exact version, x.y.z, do you use?

Please, send your my.cnf file content, entire error log (compressed) and the output of:

show global status;
show full processlist;
show innodb status\G

taken at the moment when you see the problem.
[4 Mar 2012 23:51] Todd Ruelle
Version 5.5.21
[5 Mar 2012 16:18] Valeriy Kravchuk
What about the other information requested, error log etc?
[6 Apr 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".