Re: UPDATEDs slowing SELECTs in a fully cached database
От | Kevin Grittner |
---|---|
Тема | Re: UPDATEDs slowing SELECTs in a fully cached database |
Дата | |
Msg-id | 4E1C7B78020000250003F26E@gw.wicourts.gov обсуждение исходный текст |
Ответ на | Re: UPDATEDs slowing SELECTs in a fully cached database (lars <lhofhansl@yahoo.com>) |
Ответы |
Re: UPDATEDs slowing SELECTs in a fully cached database
(lars <lhofhansl@yahoo.com>)
|
Список | pgsql-performance |
lars <lhofhansl@yahoo.com> wrote: > vacuum analyze; I tried this out on a 16 core, 64 GB machine. It was a replication target for a few dozen source databases into a couple 2 TB reporting databases, and had some light testing going on, but it was only at about 50% capacity, so that shouldn't throw this off by *too* much, I hope. Since our data is long-lived enough to worry about transaction ID freezing issues, I always follow a bulk load with VACUUM FREEZE ANALYZE; so I did that here. I also just threw this into the 2 TB database without changing our configuration. Among other things, that means that autovacuum was on. > prepare x as select count(*) from test where tenant = $1 and > created_date = $2; > prepare y as update test set created_by = $1 where tenant = $2 and > created_date = $3; > > execute y('000000000000001', '000000000000001','2011-6-30'); > execute x('000000000000001','2011-6-30'); I ran x a bunch of times to get a baseline, then y once, then x a bunch more times. The results were a bit surprising: cir=> \timing Timing is on. cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 9.823 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 8.481 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 14.054 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 10.169 ms cir=> execute y('000000000000001', '000000000000001','2011-6-30'); UPDATE 3456 Time: 404.244 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 128.643 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 2.657 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 5.883 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 2.645 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 2.753 ms cir=> execute x('000000000000001','2011-6-30'); count ------- 3456 (1 row) Time: 2.253 ms Running the update made the next SELECT slow, then it was much *faster*. My best guess is that the data landed in a more concentrated set of pages after the update, and once autovacuum kicked in and cleaned things up it was able to get to that set of data faster. > On the face of it, though, this looks like Postgres would not be > that useful as database that resides (mostly) in the cache. > autovacuum | off Well, certainly not while under modification without running autovacuum. That's disabling an integral part of what keeps performance up. There are very few, if any, situations where running PostgreSQL in production without autovacuum makes any sense, and benchmarks which disable it don't give a very accurate picture of typical performance. Now, if you're looking to artificially create a worst-case scenario, then it makes sense, but I'm not clear on the point of it. I do understand the impulse, though. When we first started using PostgreSQL there were certain very small tables which were updated very frequently which got slow when autovacuum kicked in. We made autovacuum less aggressive, and found that things go worse! Se we went the other way and made autovacuum much more aggressive than the defaults, and everything was fine. -Kevin
В списке pgsql-performance по дате отправления:
Предыдущее
От: Merlin MoncureДата:
Сообщение: Re: UPDATEDs slowing SELECTs in a fully cached database
Следующее
От: Mario SplivaloДата:
Сообщение: Re: Planner choosing NestedLoop, although it is slower...