Mysql – Cannot write to thesql database while select query in process

amazon-rdsinnodblockingMySQL

Running Mysql 5.1.57 on InnoDB, and have a database locking issue.

I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a slow query) on table A. Then on the other connection, I run a small UPDATE query on table B.

Edit: As described at the bottom of this topic, this issue also occurs when the tables/queries are in separate databases altogether, and is not limited to tables/queries are in the same database.

For some reason the update on table B will not complete until the long select query finishes, as if they are on the same connection. In addition, the "PROCESS LIST" shows the second query as "freeing items", while it is waiting to execute.

Are there any settings, or configuration issues that would be causing these queries to run sequentially rather than simultaneously?

Thanks in advance for the help.

Table A

CREATE TABLE `history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `log` text NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `history_user_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=364398 DEFAULT CHARSET=utf8

Table B

CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `root_id` int(11) DEFAULT NULL,  
  `salesperson_id` int(11) DEFAULT NULL,
  `name` varchar(127) DEFAULT NULL,
  `notes` text,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `root_id_idx` (`root_id`),
  KEY `salesperson_id_idx` (`salesperson_id`),
  CONSTRAINT `client_root_id_client_id` FOREIGN KEY (`root_id`) REFERENCES `client` (`id`) ON DELETE CASCADE,
  CONSTRAINT `client_salesperson_id_user_id` FOREIGN KEY (`salesperson_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=757 DEFAULT CHARSET=utf8

Query 1: 

SELECT * 
FROM `history` 
WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000; // (fictitious query to replicate the issue)

Takes about 10 seconds to run

Query 2: 

UPDATE `client` SET name = 'Test Name' WHERE id = 24; // hangs until query 1 is complete

MySQL Version Information

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.0.16                       |
| protocol_version        | 10                           |
| version                 | 5.1.57-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | unknown-linux-gnu            |
+-------------------------+------------------------------+

Note: This is an Amazon RDS server (m1.large)

Global InnoDB Variables

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | ON                     |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 2097152                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 5882511360             |
| innodb_change_buffering         | inserts                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            | /rdsdbdata/db/innodb   |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | Barracuda              |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             | O_DIRECT               |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 134217728              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | /rdsdbdata/log/innodb  |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.0.16                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

Connection Variables

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 623   |
| max_user_connections | 0     |
+----------------------+-------+

Issue Persists Across Separate Databases

Further testing has shown that we can recreate this issue on an entirely different database all together, which negates any issue about table locking. To recreate this, we setup Table A / Query 1 in one database, and Table B / Query 2 in an entirely separate database (on the same server). When query 1 is running, Query 2 waits until #1 is complete before finishing.

This leads me to believe there is something server-wide (OS level file locking?) that is causing this issue, not related to any one database or table. Any ideas?

User Grants

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS,
REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.*
TO 'username'@'%' IDENTIFIED BY PASSWORD 'password' WITH GRANT OPTION

Best Answer

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

Related Question