MySQL:选择查询执行，结果获取时间随着连接数的增加而增加 [英] MySQL: Select query execution and result fetch time increases with number of connections

问题描述

我的服务器应用程序通过单独的线程与MySQL建立了多个连接.每个连接都会触发一个SELECT查询并获取结果，然后应用程序迎合其连接的用户.

My server application makes multiple connections to MySQL through separate threads. Each connection fires a SELECT query and fetches result which the application then caters back to its connected users.

我正在使用InnoDB.令我惊讶的是，我发现如果增加与MySQL的连接数会很奇怪，查询性能会下降，结果获取时间也会增加.下表显示了相同的内容.

I am using InnoDB. To my surprise I found it a very weird that if I increase number of connections to MySQL, query performance deteriorates and result fetch time also increases. Below is a table showing same.

当我在MySQL表中有3333条记录，并且基于赋予它的随机参数的SELECT查询从其中提取了大约450条记录时，就会生成此数据.每条记录有大约10个字段，并且它们总共包含1.2 KB数据. (因此，单个SELECT查询总共获取1.2 * 450 = 540 KB数据)

This data is produced when I had 3333 records in MySQL table and the SELECT query based on random parameters given to it fetches around 450 records out of them. Each record has around 10 fields and all of them together contains 1.2 KB of data. (Thus, single SELECT query fetches 1.2 * 450 = 540 KB data in total)

╔═══════════╦═══════════════╦══════════════╗
║ Number of ║Query execution║ Result fetch ║
║connections║  time range   ║  time range  ║
║ to MySQL  ║ (in seconds)  ║ (in seconds) ║ 
╠═══════════╬═══════════════╬══════════════╣
║     1     ║ 0.02 to 0.06  ║ 0.03 to 0.18 ║
║     7     ║ 0.23 to 0.64  ║ 0.54 to 0.74 ║
║    17     ║ 0.32 to 1.71  ║ 0.53 to 1.18 ║
║    37     ║ 0.37 to 2.01  ║ 0.70 to 1.70 ║
║   117     ║ 1.13 to 3.29  ║ 2.48 to 3.25 ║
╚═══════════╩═══════════════╩══════════════╝

我在这里不明白的是为什么当增加与MySQL的连接数时，MySQL需要更多的时间?特别是当没有对表进行更新时，MySQL应该在单独的线程中处理来自每个连接的SELECT请求.从而并发处理查询.因此，理想情况下，性能和获取不应有明显的下降.

What I don't understand here is why does MySQL take more time when number of connections to it are increased? Especially when there are no updates being made to the table, MySQL should process SELECT request from each connection in separate thread. Thus concurrent processing of query. Hence, ideally there shouldn't be significant degrade in performance and fetch.

我不介意与数据库建立单一连接，但发现我的服务器性能因此而大大下降.成千上万的用户(连接到我的服务器)将不得不等待该单线程才能出现.

I won't mind to have single connection to DB but catch is that my server performance significantly degrades with it. Thousands of users (connected to my server) will have to wait for that single thread for their turn to come.

在经历了一些相关的问题之后，我尝试增加 innodb_buffer_pool_size到1 GB，没有运气.

After going through some related questions on SO I tried increasing innodb_buffer_pool_size to 1 GB with no luck.

这是我所有的InnoDB参数:

Here are my all InnoDB parameters:

innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10
innodb_adaptive_hash_index  ON
innodb_adaptive_max_sleep_delay 150000
innodb_additional_mem_pool_size 2097152
innodb_api_bk_commit_interval   5
innodb_api_disable_rowlock  OFF
innodb_api_enable_binlog    OFF
innodb_api_enable_mdl   OFF
innodb_api_trx_level    0
innodb_autoextend_increment 64
innodb_autoinc_lock_mode    1
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 1073741824
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_checksum_algorithm   crc32
innodb_checksums    ON
innodb_cmp_per_index_enabled    OFF
innodb_commit_concurrency   0
innodb_compression_failure_threshold_pct    5
innodb_compression_level    6
innodb_compression_pad_pct_max  50
innodb_concurrency_tickets  5000
innodb_data_file_path   ibdata1:12M:autoextend
innodb_data_home_dir    
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  2
innodb_flush_method normal
innodb_flush_neighbors  1
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_ft_aux_table 
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_server_stopword_table 
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_ft_user_stopword_table   
innodb_io_capacity  200
innodb_io_capacity_max  2000
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  268435456
innodb_log_compressed_pages ON
innodb_log_file_size    262144000
innodb_log_files_in_group   2
innodb_log_group_home_dir   .\
innodb_lru_scan_depth   1024
innodb_max_dirty_pages_pct  75
innodb_max_dirty_pages_pct_lwm  0
innodb_max_purge_lag    0
innodb_max_purge_lag_delay  0
innodb_mirrored_log_groups  1
innodb_monitor_disable  
innodb_monitor_enable   
innodb_monitor_reset    
innodb_monitor_reset_all    
innodb_old_blocks_pct   37
innodb_old_blocks_time  1000
innodb_online_alter_log_max_size    134217728
innodb_open_files   300
innodb_optimize_fulltext_only   OFF
innodb_page_size    16384
innodb_print_all_deadlocks  OFF
innodb_purge_batch_size 300
innodb_purge_threads    1
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  64
innodb_read_only    OFF
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_stats_auto_recalc    ON
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_sample_pages   8
innodb_stats_transient_sample_pages 8
innodb_status_output    OFF
innodb_status_output_locks  OFF
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_array_size  1
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   0
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.6.28
innodb_write_io_threads 16

有人可以扔光吗?这真的困扰了我很长时间.

Can someone please throw light? This is bugging me for really long time.

(注意:在这个问题中我没有提到实际的查询，因为查询有点复杂，并且这个问题与该查询无关.但是，这是关于在查询相同的情况下，随着连接数的增加而导致的性能下降)

(Note: I haven't mentioned actual query in this question because query is little complicated and this question is not about that query. But it's about performance degradation with increasing connections when query is same)

更新1

这是我的表的SHOW CREATE TABLE输出:

CREATE TABLE `profiles` (
  `SRNO` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `HANDLE_FIRST` int(10) unsigned NOT NULL,
  `HANDLE_SECOND` bigint(20) unsigned NOT NULL,
  `USERID` binary(16) NOT NULL,
  `UNIQUESTRING` char(10) NOT NULL,
  `CLIENT_VERSION` smallint(5) unsigned NOT NULL,
  `ISCONNECTED` bit(1) NOT NULL,
  `ISPROFILEPRESENT` bit(1) NOT NULL,
  `USERNAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `GENDER` tinyint(1) DEFAULT NULL,
  `DND` bit(1) DEFAULT NULL,
  `STATUS` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `PROFILE_URL` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`SRNO`),
  UNIQUE KEY `USERID` (`USERID`),
  KEY `USERID_INDEX` (`USERID`),
  KEY `UNIQUESTRING_INDEX` (`UNIQUESTRING`),
  KEY `ISCONNECTED_INDEX` (`ISCONNECTED`),
  KEY `ISPROFILEPRESENT_INDEX` (`ISPROFILEPRESENT`)
) ENGINE=InnoDB AUTO_INCREMENT=9250 DEFAULT CHARSET=utf8


CREATE TABLE `blockers` (
  `BLOCKER_PROFILE_SRNO` bigint(20) unsigned NOT NULL,
  `BLOCKED_PROFILE_SRNO` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `BLOCKER_PROFILE_SRNO` (`BLOCKER_PROFILE_SRNO`,`BLOCKED_PROFILE_SRNO`),
  KEY `BLOCKER_PROFILE_SRNO_INDEX` (`BLOCKER_PROFILE_SRNO`),
  KEY `BLOCKED_PROFILE_SRNO_INDEX` (`BLOCKED_PROFILE_SRNO`),
  CONSTRAINT `fk_BlockedIndex` FOREIGN KEY (`BLOCKED_PROFILE_SRNO`) REFERENCES `profiles` (`SRNO`),
  CONSTRAINT `fk_BlockerIndex` FOREIGN KEY (`BLOCKER_PROFILE_SRNO`) REFERENCES `profiles` (`SRNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

这是我正在运行的查询:

And here is the query I am running:

select  prfls.*
    from  profiles as prfls
    left outer join  blockers as blkr1 on blkr1.blocker_profile_srno = prfls.srno
      and  blkr1.blocked_profile_srno = 6443
    left outer join  blockers as blkr2 on blkr2.blocker_profile_srno = 6443
      and  blkr2.blocked_profile_srno = prfls.srno
    where  blkr1.blocker_profile_srno is null
      and  blkr2.blocker_profile_srno is null
      and  (      (prfls.uniquestring like 'phk5600dcc%')
              or  (prfls.uniquestring like 'phk5600dcf%')
           )
      and  prfls.isconnected=1
      and  prfls.isprofilepresent=1
    limit  450

此查询本质上是一个准备好的语句，其中blocked_profile_srno，blocker_profile_srno和uniquestring参数对于每个查询都会不断变化.但是blocked_profile_srno和blocker_profile_srno始终保持相等(在上面的查询中，它们的值为6443).表blockers为空白(我已准备好以备将来使用，但目前其中没有数据)

This query is essentially a prepared statement where blocked_profile_srno, blocker_profile_srno and uniquestring parameters keep changing for each query. However blocked_profile_srno and blocker_profile_srno always remains equal (in above query their value is 6443). Table blockers is blank (I have it in place for future use but currently it has no data in it)

当同时有117个连接运行查询时，SHOW GLOBAL STATUS LIKE 'Threads_running';的输出大部分时间为1.但是有时它会上升到27.同时，SHOW GLOBAL STATUS LIKE 'Max_used_connections';的输出为130

When 117 connections were simultaneously running queries, output of SHOW GLOBAL STATUS LIKE 'Threads_running'; was most of the time 1. However it sometimes went upto 27. At the same time, output of SHOW GLOBAL STATUS LIKE 'Max_used_connections'; was 130

更新2

我可以从下面的里克·詹姆斯(Rick James)答案中得知，优化查询可以缩短查询执行时间.该时间范围仍会随着连接数的增加而不断增加，但仍在可接受的范围内.这就是为什么我接受了答案.

I can gather from Rick James answer below that optimizing query reduces query execution time range. This time range still keeps increasing with number of connections but within acceptable range. This is why I've accepted the answer.

推荐答案

可能每个连接都在对profiles进行全表扫描.让我们尝试避免这种情况.当有数十个查询命中同一张表时，就会有一些锁导致InnoDB绊倒自己".这些计划中的任何一个都将加快查询速度并减少接触的行数(因此减少锁定).建议使用复合"索引将加快查询速度.但是OR妨碍了您的操作.我看到仍然可以对uniquestring进行索引的两个技巧，但要避免使用某些或全部OR.

Probably each connection is doing a full table scan of profiles. Let's try to avoid that. When there are dozens of queries hitting the same table, there are locks that cause InnoDB to "stumble over itself". Either of these plans will both speed up the query and decrease the number of rows touched (hence decrease the locking). The use of the suggested "composite" index will speed up the query. But the OR gets in the way. I see two tricks to still have an index look at uniquestring, but avoid some or all of the OR.

(      (prfls.uniquestring like 'phk5600dcc%')
   or  (prfls.uniquestring like 'phk5600dcf%')
)

OR难以优化.

添加此内容:

INDEX(isconnected, isprofilepresent, uniquestring)

然后...

计划A:

prfls.uniquestring         like 'phk5600dc%' AND  -- note common prefix
(      (prfls.uniquestring like 'phk5600dcc%')
   or  (prfls.uniquestring like 'phk5600dcf%')
)

这假定您可以构造该公共前缀.

This assumes you can construct that common prefix.

计划B(将OR转换为UNION):

( SELECT ...
    WHERE prfls.uniquestring like 'phk5600dcc%' AND ...
    LIMIT 450 )
UNION ALL    -- ? You may want DISTINCT, if there could be dups
( SELECT ...
    WHERE prfls.uniquestring like 'phk5600dcf%' AND ...  -- the only diff
    LIMIT 450 )
LIMIT 450   -- yes, again

计划A(如果可行)将似乎的优势用作共同的初始值.计划B无论如何都可以运行，但可能会慢一些，尽管仍然比原始计划快很多.

Plan A (if practical) takes advantage of what seems to be a common starting value. Plan B works regardless, but is probably a little slower, although still a lot faster than the original.

其他说明...

标志(其中有两个)的索引几乎从未使用过. EXPLAIN SELECT ...可能表明两者均未使用.请为任何需要讨论的SELECT提供EXPLAIN.

Indexes on flags (of which you have two) are almost never used. EXPLAIN SELECT ... will probably show that neither was used. Please provide the EXPLAIN for any SELECT that needs discussion.

A UNIQUE KEY是KEY，因此不需要USERID上的冗余索引.

A UNIQUE KEY is a KEY, so there is not need for the redundant index on USERID.

limit 450-您想要哪个450?如果没有ORDER BY，则允许查询为您提供任何450.(当然，也许可以.)(而且ORDER BY可能会使查询变慢.)

limit 450 -- Which 450 do you want? Without an ORDER BY, the query is allowed to give you any 450. (Of course, perhaps that is fine.) (And ORDER BY would probably slow down the query.)

我的建议不会解决"问题，但是在速度变慢之前，它们应该增加连接数.

My suggestions will not "solve" the problem, but they should increase the number of connections before the slow-down becomes noticeable.

这篇关于MySQL:选择查询执行，结果获取时间随着连接数的增加而增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！