Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
InnoDB will not LOCK rows unless they are in use. Please run the following commands and add the output to your question: SHOW CREATE TABLE tableA\G SHOW CREATE TABLE tableB\G –  randy melder Jul 13 '12 at 17:56
    
I updated the question with the show statements as well as the queries that are running –  Slickrick12 Jul 13 '12 at 18:34
    
Please run SHOW GLOBAL VARIABLES LIKE 'innodb%'; and display that in your question. –  RolandoMySQLDBA Jul 13 '12 at 19:00
    
Updated the question to include the global innodb variables as requested. –  Slickrick12 Jul 13 '12 at 19:08
    
Also added version information –  Slickrick12 Jul 13 '12 at 19:13
show 9 more comments

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
Hi Rolando, thanks for the information. The query I am running above is fictitious, as I was trying to replicate the issue in the simplest form possible. Assuming another similar query would in fact take 10+ seconds to run, is there no way to prevent the system from blocking ALL updates to the db until that query has completed? This seems like a real bottle neck, no? –  Slickrick12 Jul 13 '12 at 19:46
    
I just ran the query you suggested on our test environment (where we are recreating this issue), and the BufferPoolFullPct value comes to 3.50. In production, the value comes to 41 –  Slickrick12 Jul 13 '12 at 19:49
    
Thanks for your answers. Please see the most recent update I posted in the question for more insight, where I believe this is server wide, and not related to any one database. –  Slickrick12 Jul 13 '12 at 20:44
    
From this article, it seems that 128MB is a good size for a database log. Not sure this is the problem. –  Slickrick12 Jul 14 '12 at 5:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.