maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

A connection pool should not affect query performance.

My guess is that the a) connection pool doesn't have enough connections, so the query is waiting in the pool, or b) that the query is slow under concurrency.

The easiest way to debug this is using the "slow query log".  It will log each connection and the start and execution time of each query.  For best results set long_query_time=0 which will log all queries into the log. Check to see if the actual execution of the query is slower, or if the query is being delayed.  

If the query is actually slower through the pool (not delayed by it), then:
A) ensure innodb_thread_concurrency is not set too incorrectly 
B) ensure that the thread pool plugin is not enabled, if it is, the number of threads may be wrong 

In both cases if the value is too low, queries wait in queue, if too high queries suffer from too much concurrency.

In general:
A) Use EXPLAIN on the query and try to tune it
B) break query up into smaller steps, avoiding subqueries
C) While running tests, collect iostat and vmstat to see what the server is doing.

This might be a good time to engage with MariaDB consulting or support, they are experienced in helping with these kind of issues.

Sent from my iPhone

> On Jun 13, 2016, at 8:36 PM, Stu Smith <stu26code@xxxxxxxxx> wrote:
> 
> Hello,
>   Forgive me for reporting this on the behalf of some other developers, I will gather more details as needed.
> 
> But the basic problem we're having is with a query that:
> 
>    - is always fast the first time ( < 3 seconds )
>    - is very slow the following time, when using a connection pool (~ 30 seconds)
>    - is pretty fast the following time, when not using a connection pool (5 or 6 seconds)
> 
> This is even on a single DB with no other load.
> 
> It's a fairly complicated query with sub queries, grouping, and such.
> I've been told it creates two temp tables.
> 
> I know this is not a whole lot to go one - but are there any pointer for connection pool vs no connection pool on a lightly loaded DB?
> 
> This was actually tried and reproduced with multiple connection pools - hikari-db, c3p0, and apache connection pool.
> 
> Take care,
>   -stu
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp