Hi,

To answer your question about performance using pool versus not using pool : There will be some difference : ideal pool implementation presents a Connection instance to the application that is indistinguishable from a "fresh" Connection obtained directly from the driver, so pool add have some work to do that can lead to extra-queries (Reset Catalog, Read-only, Auto-commit, Transaction Isolation, Clear SQL warnings ...).

But worst case scenario, that time is counted in millisecond, and that's always better than creating a "fresh" connection.

As Justin point it out, if difference is more than 20 seconds when using pool, the pool maximum connection limit must have been reached:

For example: hikariCP default maximum pool size is 10, so if those 10 connections are currently used (= not released), the next connection asked to pool will wait until one is released.

If the maximum connection limit seems appropriate, you might have a connection pool leak : somewhere the application doesn't close connection after using it. Eventually if this keep on happening the pool will be out of connections (pool exhaustion). To continue with the example of HikariCP, you can add an option "leakDetectionThreshold" that will log connection not returned after a defined amount of time. This will confirm the connection leak.

Diego.

On Tue, Jun 14, 2016 at 1:36 PM, Justin Swanhart <greenlion@gmail.com> wrote:

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@gmail.com> 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@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp