mysql 根据时间连接,MySQL：选择查询执行，结果获取时间随连接数而增加

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.

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.

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 ║

╚═══════════╩═══════════════╩══════════════╝

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

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)

UPDATE 1

Here is SHOW CREATE TABLE output for my tables:

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

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)

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

UPDATE 2

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.

解决方案

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 is hard to optimize.

Add this:

INDEX(isconnected, isprofilepresent, uniquestring)

Then...

Plan 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.

Plan B (turn OR into 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

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.

Other notes...

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 is a KEY, so there is not need for the redundant index on USERID.

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.