mysql with pool of threads : explanation on execution priority strategy

MySQL & MariaDB Other MySQL® Questions
1

Hi percona members,

I have one production server with Percona-Server 56-5.6.20-rel68

my configuration is simple :
<my.cnf>
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
#skip_bdb
key_buffer_size = 32M
max_allowed_packet = 32M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 12
query_cache_size =256M
query_cache_limit = 8M
wait_timeout = 604800
max_connections = 1000
tmp_table_size = 64M
max_heap_table_size = 64M
thread_concurrency = 8
lower-case-table-names=1
thread_handling=pool-of-threads
thread_pool_size=8
datadir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 3G
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
innodb_flush_log_at_trx_commit = 2
innodb_lru_scan_depth=2500
innodb_flush_neighbors=0
innodb_io_capacity = 2500
innodb_io_capacity_max= 5000
innodb_file_format = Barracuda
innodb_checksum_algorithm = crc32
innodb_file_per_table = true
innodb_doublewrite=1
innodb_flush_method=O_DIRECT_NO_FSYNC
</my.cnf>

so, I use pool-of-threads with many default values like thread_pool_high_prio_mode = transactions.
at 99%, I’m really happy with this implementation (I hava migrated two month ago from a mysql 5.1) but I encounter a strange behaviour
with one batch that create some short bootleneck on all applications. one time per hour, this batch make many requests (500/1000 rqs) during 30/40s.
These requests are some simple select with where clause on primary key and return one row

# Schema: tatex_agence Last_errno: 0 Killed: 0 # Query_time: 0.000204 Lock_time: 0.000113 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 # Bytes_sent: 2979 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 8CFDA4B9 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 1

what’s I don’t understand when this batch run other client of the database can’t execute some request like
insert or complex select and only simple select (primary key in where clause, no join, no temp, no sort,…) are executed.
So, according to me as I don’t have lock on table, no cpu/ram congestion… my only explanation is
thread_pool_high_prio_mode = transaction in this case is too strict and only process “open transaction” and
simple request.
if my analyze is good, I can put in place some workaround like :

  • manage transaction / commit on the batch to force new transcation
  • just add a small sleep between all select
  • make a big select with a in (key1, key2) in where clause

but, I’m interresting to know if my analyze is good and if there are a pure mysql tuning solution ?

br