java - After 9 queries to database, the time to perform the same query is multiplied by 7 -
i using postgresql (9.4.8) on windows 7 32. accessing database via eclipselink 2.6.3 , jdbc driver 9.4.1208 on jdk 8u72.
i performing simple count query (build criteria api). first query taking times, no problem that, after that, next 8 queries taking less time, , great. 9th query response taking 7x times of previous query, , never go down.
here extract of logs :
[el fine]: sql: 2016-07-04 17:19:42.658--serversession(13112008)--connection(27980113)--select now() info - select now() = 2016-07-04 17:19:41.8 [el fine]: sql: 2016-07-04 17:19:42.691--serversession(13112008)--connection(27980113)--select version() info - select version() = postgresql 9.4.8, compiled visual c++ build 1800, 32-bit info - jdbc version = postgresql 9.4.1208 [el fine]: sql: 2016-07-04 17:19:42.738--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:19:42.726, 2016-07-04 17:19:42.726] 1 - count : 788166 - 3974 [el fine]: sql: 2016-07-04 17:19:46.244--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:19:46.241, 2016-07-04 17:19:46.241] 2 - count : 788166 - 1500 [el fine]: sql: 2016-07-04 17:19:49.745--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:19:49.742, 2016-07-04 17:19:49.742] 3 - count : 788166 - 1495 [el fine]: sql: 2016-07-04 17:19:53.242--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:19:53.239, 2016-07-04 17:19:53.239] 4 - count : 788166 - 1481 [el fine]: sql: 2016-07-04 17:19:56.723--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:19:56.72, 2016-07-04 17:19:56.72] 5 - count : 788166 - 1497 [el fine]: sql: 2016-07-04 17:20:00.219--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:00.217, 2016-07-04 17:20:00.217] 6 - count : 788166 - 1484 [el fine]: sql: 2016-07-04 17:20:03.705--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:03.703, 2016-07-04 17:20:03.703] 7 - count : 788166 - 1498 [el fine]: sql: 2016-07-04 17:20:07.203--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:07.201, 2016-07-04 17:20:07.201] 8 - count : 788166 - 1498 [el fine]: sql: 2016-07-04 17:20:10.701--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:10.7, 2016-07-04 17:20:10.7] 9 - count : 788166 - 1491 [el fine]: sql: 2016-07-04 17:20:14.193--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:14.192, 2016-07-04 17:20:14.192] 10 - count : 788166 - 7550 [el fine]: sql: 2016-07-04 17:20:23.742--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:23.741, 2016-07-04 17:20:23.741] 11 - count : 788166 - 7553 [el fine]: sql: 2016-07-04 17:20:33.296--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:33.295, 2016-07-04 17:20:33.295] 12 - count : 788166 - 7567 [el fine]: sql: 2016-07-04 17:20:42.864--serversession(13112008)--connection(27980113)--select count(distinct(id)) recorder.records ((channel_number in (?, ......, ?, ?)) , (rec_start between ? , ?)) bind => [10, 11, 12, ......, 299, 2016-03-04 17:20:42.863, 2016-07-04 17:20:42.863] 13 - count : 788166 - 7545 as can see, first query takes 3974 ms, next ones around 1500ms, > 7500ms !
what go wrong ? database doesn't takes more cpu power when problem occurs, nor program.
this looks postgresql using generic plan performs worse plan used before.
if use java.sql.preparedstatement, jdbc driver substitute parameters first 4 executions , create server-side prepared statement of 5th execution, used ever after (assuming use default value of 5 connection parameter preparethreshold).
for next 5 executions, postgresql server create individual plans statement, substituting actual parameters passed.
then create generic plan (with placeholders instead of query parameters), , if query optimizer estimates generic plan not perform worse specific plans used then, generic plan used ever after.
so in case, first 9 executions use different plan following executions, , if query optimizer made wrong choice, can see performance drop after that.
i see 2 options you:
use
prepareon postgresql command line create prepared statement query, then, usingexplain (analyze) execute, compare execution plans used first 5 executions execution plan generic query used afterwards (you can tell generic query placeholders$1,$2etc.).
can figure out why generic plan worse , improve query.the simple way: read the documentation how disable server side prepare statements query setting
preparethreshold0.
Comments
Post a Comment