# mysql with pool of threads : explanation on execution priority strategy

**URL:** <https://forums.percona.com/t/mysql-with-pool-of-threads-explanation-on-execution-priority-strategy/3931>\
**Category:** Other MySQL® Questions\
**Created:** [December 4, 2014, 7:58am UTC](https://forums.percona.com/t/mysql-with-pool-of-threads-explanation-on-execution-priority-strategy/3931 "2014-12-04T07:58:20Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![courtos](https://avatars.discourse-cdn.com/v4/letter/c/e19adc/32.png) [@courtos](https://forums.percona.com/u/courtos)\
**Post date:** [December 4, 2014, 7:58am UTC](https://forums.percona.com/t/mysql-with-pool-of-threads-explanation-on-execution-priority-strategy/3931/1 "2014-12-04T07:58:20Z")

</div>

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