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