Cannot write to mysql database while select query in process

Question

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

Answer 1

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

• select for update gives error on indexed column
• Concurrently update with limit mysql
• Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
• Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

Answer 2

After a week of dealing with AWS support and running multiple benchmarks, the answer to the question is that RDS has I/O caps through their OS/filesystem that prohibit large queries from properly handling this type of scenario.

We could not recreate this issue on a standard LAMP stack, even on a micro instance. After we were out of options, we turned to AWS for their support.

We provided the exact data set, setup an RDS instance for AWS to test on, and provided the exact instructions how to recreate the problem. RDS was able to recreate the issue, and after their testing, they came back with this answer:

"RDS has investigated this and it's basically using up the disk I/O (writes) which leads to the second query being slow." ... "MySQL uses a temporary table for this which leads to the database performing many writes despite being a SELECT."

So in effect, because this is a bottleneck generated by RDS, this scenario will likely only occur on RDS, and not a regular MySQL installation.

The bottom line: If you have this problem, move to a different database setup.

Answer 3

We had the same problem. But our server was not in cloud.

Our ubuntu os had io-scheduler set to CFQ. This starved the insert/updates on the table being selected from. We switched to DEADLINE option for our io-scheduler and blocking problem was gone.

cat /sys/block/sda/queue/scheduler

noop anticipatory deadline [cfq]

echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler

noop anticipatory [deadline] cfq