Randomly slow Insert / Update on small table
Posted by: Sebastian Hamann
Date: October 28, 2009 03:04AM
Date: October 28, 2009 03:04AM
Sorry, this post should have gone to InnoDB Board!! (Dear moderator, could you move it? thanks, sebastian)
Good morning everybody!
We got the following situation:
There is a MySQL based Mailingsystem, with a lot of reads on up to 3 joined tables. One of those frequently read tables holds domain informations:
Inserts / updates on this table happen infrequently and randomly via our mailadmins. After some 3-4 hours "non insert / update" such a query run on this table will take significantly longer than it should be: 20 secs to begin with. After a period of 24h "non insert / update" it'll take up to 5 minutes to complete a insert / update. Any consecutive insert / updates after the first one will succeed "normaly" ie. very fast (> 1 sec).
While doing an insert / update, the "whole DB locks": show processlist will work (our monitoring system runs mad), but any other selects are blocked. New connections are available up to the point of max_connections / max_user_connections.
Some additional facts:
* (Mail-) / DB System migrated from 4.0.x via 5.0.x til 5.1.36, the behaviour existed to less or more annoyance on all of those
* (Mail-) / DBSystem was allway Sun Solaris based
* We are running a Master / Slave Setup
* Total Database size about 60MB in dump / 120MB in Memory
* Other tables ("greylisting records") hold many more rows and do run select, update and insert statements w/o any problems.
* "Insert locking" happens from our web-based administration tool as well as from mysql client locally connected on that machine mysql runs.
* Server stats:
:~# prtconf
System Configuration: Sun Microsystems i86pc
Memory size: 16256 Megabytes
:~# uname -a
SunOS XXX 5.10 Generic_141415-07 i86pc i386 i86pc
:~# psrinfo -vp
The physical processor has 1 virtual processor (0)
x86 (AuthenticAMD family 15 model 37 step 1 clock 2792 MHz)
AMD Opteron(tm) Processor 254
The physical processor has 1 virtual processor (1)
x86 (AuthenticAMD family 15 model 37 step 1 clock 2792 MHz)
AMD Opteron(tm) Processor 254
Has anyone here ever happend to stumble upon this or any similiar problem? Anyone any idea whatever problem happens to ache the system?
Kindest regards and thanks in advance,
sebastian
Edited 2 time(s). Last edit at 10/28/2009 03:16AM by Sebastian Hamann.
Good morning everybody!
We got the following situation:
There is a MySQL based Mailingsystem, with a lot of reads on up to 3 joined tables. One of those frequently read tables holds domain informations:
mysql> show create table domain;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| domain | CREATE TABLE `domain` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domainname` varchar(80) NOT NULL DEFAULT '',
`fk_customer_id_default` int(10) NOT NULL DEFAULT '0',
`mail_route_type` set('mailbox','smtp','uucp','uucp_gz') NOT NULL DEFAULT 'mailbox',
`match_subdomains` set('yes','no') NOT NULL DEFAULT 'no',
`obsolete` int(11) NOT NULL DEFAULT '0',
`enable_virusscan` set('yes','no') DEFAULT 'no',
`enable_spamblocker` set('yes','no') DEFAULT 'no',
`enable_greylisting` set('yes','no') DEFAULT 'no',
`suppress_virus_notify` set('yes','no') DEFAULT 'no',
`mailbox_install` set('yes','no') DEFAULT 'no',
`mail_quota_default` varchar(5) NOT NULL DEFAULT '1024M',
`contact_email` varchar(254) DEFAULT NULL COMMENT 'LIste der kommasep. Kontakt-Mailadressen',
PRIMARY KEY (`id`),
UNIQUE KEY `domain_unq_idx` (`domainname`),
KEY `mail_route_type` (`mail_route_type`),
KEY `match_subdomains` (`match_subdomains`),
KEY `obsolete_idx` (`obsolete`),
KEY `domain_idx_customer` (`fk_customer_id_default`)
) ENGINE=InnoDB AUTO_INCREMENT=2554 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show table status like 'domain';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| domain | InnoDB | 10 | Compact | 1315 | 87 | 114688 | 0 | 212992 | 5136973824 | 2554 | 2009-10-20 10:25:34 | NULL | NULL | latin1_swedish_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
Inserts / updates on this table happen infrequently and randomly via our mailadmins. After some 3-4 hours "non insert / update" such a query run on this table will take significantly longer than it should be: 20 secs to begin with. After a period of 24h "non insert / update" it'll take up to 5 minutes to complete a insert / update. Any consecutive insert / updates after the first one will succeed "normaly" ie. very fast (> 1 sec).
mysql> update domain set domainname='sebi4.domain.de' where id = 2553; Query OK, 1 row affected (5 min 56.97 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from domain where id = 2553; +------+--------------+------------------------+-----------------+------------------+----------+------------------+--------------------+--------------------+-----------------------+-----------------+--------------------+---------------+ | id | domainname | fk_customer_id_default | mail_route_type | match_subdomains | obsolete | enable_virusscan | enable_spamblocker | enable_greylisting | suppress_virus_notify | mailbox_install | mail_quota_default | contact_email | +------+--------------+------------------------+-----------------+------------------+----------+------------------+--------------------+--------------------+-----------------------+-----------------+--------------------+---------------+ | 2553 | sebi4.domain.de | 4 | mailbox | no | 1 | no | no | no | no | no | 1024M | NULL | +------+--------------+------------------------+-----------------+------------------+----------+------------------+--------------------+--------------------+-----------------------+-----------------+--------------------+---------------+ 1 row in set (1.44 sec)
While doing an insert / update, the "whole DB locks": show processlist will work (our monitoring system runs mad), but any other selects are blocked. New connections are available up to the point of max_connections / max_user_connections.
Some additional facts:
* (Mail-) / DB System migrated from 4.0.x via 5.0.x til 5.1.36, the behaviour existed to less or more annoyance on all of those
* (Mail-) / DBSystem was allway Sun Solaris based
* We are running a Master / Slave Setup
* Total Database size about 60MB in dump / 120MB in Memory
* Other tables ("greylisting records") hold many more rows and do run select, update and insert statements w/o any problems.
* "Insert locking" happens from our web-based administration tool as well as from mysql client locally connected on that machine mysql runs.
mysql> show variables like '%inno%'; +-----------------------------------------+-------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------+ | have_innodb | YES | | ignore_builtin_innodb | OFF | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 209715200 | | innodb_autoextend_increment | 1000 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 3758096384 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:5G:autoextend:max:25G | | innodb_data_home_dir | /local/rg-mysql_master/ibdata | | innodb_doublewrite | OFF | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 12 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 67108864 | | innodb_log_files_in_group | 48 | | innodb_log_group_home_dir | /local/rg-mysql_master/iblogs | | innodb_max_dirty_pages_pct | 1 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 8192 | | innodb_rollback_on_timeout | OFF | | innodb_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | +-----------------------------------------+-------------------------------+ 37 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%buffer%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 3758096384 | | innodb_log_buffer_size | 8388608 | | join_buffer_size | 1048576 | | key_buffer_size | 134217728 | | myisam_sort_buffer_size | 268435456 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 2097152 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 134217728 | | sql_buffer_result | OFF | +-------------------------+------------+ 12 rows in set (0.00 sec) mysql> show variables like 'version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.1.36-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | pc-solaris2.10 | +-------------------------+------------------------------+ mysql> show profile all for query 8; +----------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+ | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line | +----------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+ | starting | 0.000340 | 0.000066 | 0.000273 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | | checking permissions | 0.000119 | 0.000010 | 0.000109 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 5162 | | Opening tables | 0.000124 | 0.000017 | 0.000107 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_base.cc | 4468 | | System lock | 0.000118 | 0.000007 | 0.000112 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | lock.cc | 258 | | Table lock | 0.000118 | 0.000006 | 0.000112 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | lock.cc | 269 | | init | 0.000184 | 0.000071 | 0.000112 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_update.cc | 235 | | Updating | 0.000266 | 0.000159 | 0.000107 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_update.cc | 535 | | end | 356.565667 | 354.662674 | 1.796702 | 16327 | 49534 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_update.cc | 773 | | query end | 0.001535 | 0.000017 | 0.001516 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 4924 | | freeing items | 0.900950 | 1.559062 | 0.165947 | 8621 | 201 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 5951 | | logging slow query | 0.001618 | 0.001100 | 0.002445 | 7 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 1625 | | logging slow query | 0.006783 | 0.010607 | 0.002655 | 54 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 1635 | | cleaning up | 0.008749 | 0.014716 | 0.002725 | 57 | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | <unknown> | sql_parse.cc | 1592 | +----------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------+---------------+-------------+
* Server stats:
:~# prtconf
System Configuration: Sun Microsystems i86pc
Memory size: 16256 Megabytes
:~# uname -a
SunOS XXX 5.10 Generic_141415-07 i86pc i386 i86pc
:~# psrinfo -vp
The physical processor has 1 virtual processor (0)
x86 (AuthenticAMD family 15 model 37 step 1 clock 2792 MHz)
AMD Opteron(tm) Processor 254
The physical processor has 1 virtual processor (1)
x86 (AuthenticAMD family 15 model 37 step 1 clock 2792 MHz)
AMD Opteron(tm) Processor 254
Has anyone here ever happend to stumble upon this or any similiar problem? Anyone any idea whatever problem happens to ache the system?
Kindest regards and thanks in advance,
sebastian
Edited 2 time(s). Last edit at 10/28/2009 03:16AM by Sebastian Hamann.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.