草庐IT

MySQL:选择查询执行和结果获取时间随着连接数的增加而增加

coder 2023-10-12 原文

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

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

此数据是在我在 MySQL 表中有 3333 条记录时生成的，SELECT 查询基于提供给它的随机参数从中提取了大约 450 条记录。每条记录大约有 10 个字段，所有字段加在一起包含 1.2 KB 的数据。 (因此，单个 SELECT 查询总共获取 1.2 * 450 = 540 KB 数据)

╔═══════════╦═══════════════╦══════════════╗
║ 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 ║
╚═══════════╩═══════════════╩══════════════╝

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.

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

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

有人可以投光吗？这困扰了我很长时间。

(注意:我没有在这个问题中提到实际的查询，因为查询有点复杂，这个问题与那个查询无关。但它是关于当查询相同时随着连接的增加性能下降)

更新 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

这是我正在运行的查询:

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 是空白的(我把它准备好以备将来使用，但目前它里面没有数据)

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

更新 2

我可以从下面的 Rick James 回答中了解到，优化查询会减少查询执行时间范围。这个时间范围仍然随着连接数的增加而增加，但在可接受的范围内。这就是我接受答案的原因。

最佳答案

可能每个连接都在对profiles 进行全表扫描。让我们尽量避免这种情况。当有几十个查询访问同一个表时，就会出现导致 InnoDB“绊倒自己”的锁。这些计划中的任何一个都将加快查询速度并减少接触的行数(因此减少锁定)。使用建议的“复合”索引将加快查询速度。但是 OR 妨碍了。我看到两个技巧仍然可以对 uniquestring 进行索引查看，但要避免部分或全部 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%')
)

这假设您可以构建该公共(public)前缀。

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 计划无论如何都有效，但可能会慢一点，但仍然比原来快很多。

其他注意事项...

标志上的索引(您有两个)几乎从未使用过。 EXPLAIN SELECT ... 可能会显示两者都没有被使用。请为任何需要讨论的SELECT提供EXPLAIN。

UNIQUE KEY是KEY，所以USERID上不需要冗余索引。

limit 450 -- 你想要哪个 450？如果没有 ORDER BY，查询可以返回 any 450。(当然，也许这没问题。)(还有 ORDER BY可能会减慢查询速度。)

我的建议不会“解决”问题，但它们应该在速度变慢变得明显之前增加连接数。

关于MySQL:选择查询执行和结果获取时间随着连接数的增加而增加，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34484391/

MySQL增加innodbcodeuniquestringdatabase-performancequery-performance

有关MySQL:选择查询执行和结果获取时间随着连接数的增加而增加的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby - 检查数组是否在增加 - 2

    这个问题在这里已经有了答案:Checktoseeifanarrayisalreadysorted?(8个答案)关闭9年前。我只是想知道是否有办法检查数组是否在增加？这是我的解决方案，但我正在寻找更漂亮的方法:n=-1@arr.flatten.each{|e|returnfalseife

  3. ruby - 续集在添加关联时访问many_to_many连接表 - 2

    我正在使用Sequel构建一个愿望list系统。我有一个wishlists和itemstable和一个items_wishlists连接表(该名称是续集选择的名称)。items_wishlists表还有一个用于facebookid的额外列(因此我可以存储opengraph操作)，这是一个NOTNULL列。我还有Wishlist和Item具有续集many_to_many关联的模型已建立。Wishlist类也有:selectmany_to_many关联的选项设置为select:[:items.*,:items_wishlists__facebook_action_id].有没有一种方法可以

  4. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  5. ruby - 无法在 60 秒内获得稳定的 Firefox 连接 (127.0.0.1 :7055) - 2

    我使用的是Firefox版本36.0.1和Selenium-Webdrivergem版本2.45.0。我能够创建Firefox实例，但无法使用脚本继续进行进一步的操作无法在60秒内获得稳定的Firefox连接(127.0.0.1:7055)错误。有人能帮帮我吗？ 最佳答案 我遇到了同样的问题。降级到firefoxv33后一切正常。您可以找到旧版本here 关于ruby-无法在60秒内获得稳定的Firefox连接(127.0.0.1:7055)，我们在StackOverflow上找到一个类

  6. ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2

    我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby​​是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。 最佳答案 我不太明白你的问题。我假设您想检查

  7. ruby - 简单获取法拉第超时 - 2

    有没有办法在这个简单的get方法中添加超时选项？我正在使用法拉第3.3。Faraday.get(url)四处寻找，我只能先发起连接后应用超时选项，然后应用超时选项。或者有什么简单的方法？这就是我现在正在做的:conn=Faraday.newresponse=conn.getdo|req|req.urlurlreq.options.timeout=2#2secondsend 最佳答案 试试这个:conn=Faraday.newdo|conn|conn.options.timeout=20endresponse=conn.get(url

  8. ruby - 从 Ruby 中的主机名获取 IP 地址 - 2

    我有一个存储主机名的Ruby数组server_names。如果我打印出来，它看起来像这样:["hostname.abc.com","hostname2.abc.com","hostname3.abc.com"]相当标准。我想要做的是获取这些服务器的IP(可能将它们存储在另一个变量中)。看起来IPSocket类可以做到这一点，但我不确定如何使用IPSocket类遍历它。如果它只是尝试像这样打印出IP:server_names.eachdo|name|IPSocket::getaddress(name)pnameend它提示我没有提供服务器名称。这是语法问题还是我没有正确使用类？输出:ge

  9. ruby - 获取模块中定义的所有常量的值 - 2

    我想获取模块中定义的所有常量的值:moduleLettersA='apple'.freezeB='boy'.freezeendconstants给了我常量的名字:Letters.constants(false)#=>[:A,:B]如何获取它们的值的数组，即["apple","boy"]？ 最佳答案 为了做到这一点，请使用mapLetters.constants(false).map&Letters.method(:const_get)这将返回["a","b"]第二种方式:Letters.constants(false).map{|c

  10. ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2

    这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build

随机推荐