Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
[combining responses to two posts on this thread by lars] lars lhofha...@yahoo.com wrote: On the face of it, though, this looks like Postgres would not be that useful as database that resides (mostly) in the cache. I've mentioned this in a hand-wavy general sense, but I should have mentioned specifics ages ago: for a database where the active portion of the database is fully cached, it is best to set seq_page_cost and random_page_cost to the same value, somewhere in the 0.1 to 0.05 range. (In your case I would use 0.05.) In highly cached databases I have sometimes also found it necessary to increase cpu_tuple_cost. (In your case I might try 0.02.) This won't directly address the specific issue you've been investigating in this thread, but it will tend to give you faster plans for your actual environment without needing to fuss with things on a query-by-query basis. It may indirectly mitigate the problem at hand through heavier use of indexes which would reduce pruning and hint-bit setting by readers. Interesting. When you did you test, did you also find WAL write activity when running x the first time after y? I wasn't able to check that in this quick, ad hoc run. Oh, it's just switched off for testing, so that I can control when vacuum runs and make sure that it's not skewing the results while I am measuring something. Again, that's an impulse I can certainly understand, but the problem is that turning autovacuum off skews results in other ways, such as forcing readers to do maintenance work which might otherwise be done in a cost-limited background process. Or if that didn't happen you would be constantly chasing through lots of dead line pointers which would hurt performance in another way. It's probably best to consider autovacuum an integral part of normal database operations and run benchmarks like this with it operational. This will also give you an opportunity to tune thresholds and costing factors to evaluate the impact that such adjustments have on potential workloads. For a fully cached database I would probably want to switch off HOT pruning and compaction (which from what we see is done synchronously with the select) and leave it up to the asynchronous auto vacuum to do that. But maybe I am still not quite understanding the performance implications. Code comments indicate that they expect the pruning to be a pretty clear win on multiple reads, although I don't know how much that was benchmarked. Jeff does raise a good point, though -- it seems odd that WAL-logging of this pruning would need to be synchronous. We support asynchronous commits -- why not use that feature automatically for transactions where the only writes are this sort of thing. Which raises an interesting question -- what happens to the timings if your SELECTs are done with synchronous_commit = off? I wonder if it would make any sense to implicitly use async commit for a transaction which is declared READ ONLY or which never acquires and XID? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On Tue, Jul 12, 2011 at 6:15 PM, lars lhofha...@yahoo.com wrote: Back to the first case, here's an strace from the backend doing the select right after the updates. write(13, f\320\1\0\1\0\0\0\273\0\0\0\0\340\27\22`\32\0\0002833!000..., 2400256) = 2400256 On Wed, Jul 13, 2011 at 9:46 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Code comments indicate that they expect the pruning to be a pretty clear win on multiple reads, although I don't know how much that was benchmarked. Jeff does raise a good point, though -- it seems odd that WAL-logging of this pruning would need to be synchronous. We support asynchronous commits -- why not use that feature Right -- here are my thoughts. notice the above is writing out 293 pages. this is suggesting to me that Kevin is right and you've identified a pattern where you are aggravating the page cleanup facilities of HOT. What threw me off here (and perhaps bears some additional investigation) is that early on in the report you were claiming an update to an indexed field which effectively disables HOT. The fairly lousy I/O performance of EBS is further hurting you here: you have a very fast computer with lots of memory with a late 90's disk system in terms of performance. This means that AWS is not all that great for simulating load profiles unless you are also highly underweight I/O in your servers. Postgres btw demands (as does Oracle) a decent i/o system for many workloads that might be surprising. A note about HOT: there is no way to disable it (other than updating an indexed field to bypass it) -- HOT was a performance revolution for Postgres and numerous benchmarks as well as anecdotal reports have confirmed this. HOT mitigates the impact of dead tuples by 1. highly reducing index bloat under certain conditions and 2. allowing dead tuples to be more aggressively cleaned up -- a 'page level vacuum' if it were. HOT is an especially huge win when updates are frequent and transactions are small and shortbut maybe in your simulated case it's not helping. hm. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
Kevin Grittner kevin.gritt...@wicourts.gov writes: ... Jeff does raise a good point, though -- it seems odd that WAL-logging of this pruning would need to be synchronous. Yeah, we need to get to the bottom of that. If there's enough shared_buffer space then it shouldn't be. We support asynchronous commits -- why not use that feature automatically for transactions where the only writes are this sort of thing. Which raises an interesting question -- what happens to the timings if your SELECTs are done with synchronous_commit = off? I wonder if it would make any sense to implicitly use async commit for a transaction which is declared READ ONLY or which never acquires and XID? Huh? If there was never an XID, there's no commit WAL record, hence nothing to make asynchronous. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On 07/13/2011 08:17 AM, Tom Lane wrote:
Kevin Grittnerkevin.gritt...@wicourts.gov writes:
... Jeff does raise a good point, though -- it seems odd
that WAL-logging of this pruning would need to be synchronous.
Yeah, we need to get to the bottom of that. If there's enough
shared_buffer space then it shouldn't be.
This thread has gotten long, let me try to compile all the relevant
information in one email.
\d test
Table lars.test
Column| Type | Modifiers
--+---+---
tenant | character(15) |
created_by | character(15) |
created_date | date |
Indexes:
i1 btree (tenant)
i11 btree (created_by)
-- Table is populated like this:
create table test(tenant char(15), created_by char(15), created_date date);
insert into test values('x', 'y','2011-6-30');
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test; -- 256k rows
update test set tenant = lpad((random()*1)::int::text,15,'0'),
created_by = lpad((random()*1)::int::text,15,'0');
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test; -- 32m rows
create index i1 on test(tenant);
create index i11 on test(created_by);
vacuum analyze;
-- I doubt it needs that many rows.
= SELECT
'version'::text AS name,
version() AS current_setting
UNION ALL
SELECT
name,current_setting(name)
FROM pg_settings
WHERE NOT source='default' AND NOT name IN
('config_file','data_directory','hba_file','ident_file',
'log_timezone','DateStyle','lc_messages','lc_monetary',
'lc_numeric','lc_time','timezone_abbreviations',
'default_text_search_config','application_name',
'transaction_deferrable','transaction_isolation',
'transaction_read_only');
name
| current_setting
--+--
version | PostgreSQL 9.1beta2 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.4 20100726 (Red Hat
4.4.4-13), 64-bit
bgwriter_delay | 10ms
bgwriter_lru_maxpages| 1000
checkpoint_completion_target | 0.9
checkpoint_segments | 128
client_encoding | UTF8
effective_cache_size | 64GB
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
log_checkpoints | on
log_line_prefix | %m
maintenance_work_mem | 2GB
max_connections | 100
max_stack_depth | 2MB
server_encoding | UTF8
shared_buffers | 20GB
TimeZone | UTC
wal_buffers | 16MB
work_mem | 1GB
(19 rows)
-- Now:
--
= select count(*) from test where tenant = '001' and
created_date = '2011-6-30';
count
---
3712
(1 row)
= SELECT c.relname, isdirty, count(*) * 8192 / 1024/1024 AS buffers
FROM pg_buffercache b, pg_class c
WHERE b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname =
current_database()))
GROUP BY c.relname,isdirty
ORDER BY 3 DESC
LIMIT 6;
relname| isdirty | buffers
---+-+-
test | t | 14
pg_opclass_oid_index | f | 0
pg_rewrite| f | 0
i11 | t | 0
pg_rewrite_rel_rulename_index | f | 0
pg_constraint | f | 0
-- Just started the server, no nothing else is cached, yet
-- it doesn't matter if that update is executed by the same or another
backend.
= update test set created_by = '001' where tenant =
'001';
UPDATE 3712
= select count(*) from test where tenant = '001' and
created_date = '2011-6-30';
count
---
3712
(1 row)
strace now shows:
-
Q\0\0\0_select count(*) from test w..., 8192, 0, NULL, NULL) = 96
gettimeofday({1310579341,
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On 07/13/2011 07:46 AM, Kevin Grittner wrote: I've mentioned this in a hand-wavy general sense, but I should have mentioned specifics ages ago: for a database where the active portion of the database is fully cached, it is best to set seq_page_cost and random_page_cost to the same value, somewhere in the 0.1 to 0.05 range. (In your case I would use 0.05.) In highly cached databases I have sometimes also found it necessary to increase cpu_tuple_cost. (In your case I might try 0.02.) I've been doing that for other tests already (I didn't want to add too many variations here). The Bitmap Heap scans through the table are only useful for spinning media and not the cache (just to state the obvious). As an aside: I found that queries in a cold database take almost twice as long when I make that change, so for spinning media this is very important. Which raises an interesting question -- what happens to the timings if your SELECTs are done with synchronous_commit = off? Just tried that... In that case the WAL is still written (as seen via iostat), but not synchronously by the transaction (as seen by strace). -- Lars -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
lars lhofha...@yahoo.com wrote: On 07/13/2011 07:46 AM, Kevin Grittner wrote: I've mentioned this in a hand-wavy general sense, but I should have mentioned specifics ages ago: for a database where the active portion of the database is fully cached, it is best to set seq_page_cost and random_page_cost to the same value, somewhere in the 0.1 to 0.05 range. (In your case I would use 0.05.) In highly cached databases I have sometimes also found it necessary to increase cpu_tuple_cost. (In your case I might try 0.02.) I've been doing that for other tests already (I didn't want to add too many variations here). The Bitmap Heap scans through the table are only useful for spinning media and not the cache (just to state the obvious). As an aside: I found that queries in a cold database take almost twice as long when I make that change, so for spinning media this is very important. No doubt. We normally run months to years between reboots, with most of our cache at the OS level. We don't have much reason to ever restart PostgreSQL except to install new versions. So we don't worry overly much about the cold cache scenario. Which raises an interesting question -- what happens to the timings if your SELECTs are done with synchronous_commit = off? Just tried that... In that case the WAL is still written (as seen via iostat), but not synchronously by the transaction (as seen by strace). So transactions without an XID *are* sensitive to synchronous_commit. That's likely a useful clue. How much did it help the run time of the SELECT which followed the UPDATE? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On Wed, Jul 13, 2011 at 1:10 PM, lars lhofha...@yahoo.com wrote: On 07/13/2011 08:17 AM, Tom Lane wrote: Kevin Grittnerkevin.gritt...@wicourts.gov writes: ... Jeff does raise a good point, though -- it seems odd that WAL-logging of this pruning would need to be synchronous. Yeah, we need to get to the bottom of that. If there's enough shared_buffer space then it shouldn't be. This thread has gotten long, let me try to compile all the relevant information in one email. \d test Table lars.test Column | Type | Modifiers --+---+--- tenant | character(15) | created_by | character(15) | created_date | date | small aside here: try to avoid use of character(n) type -- varchar(n) is superior in every way, including performance (although that has nothing to do with your WAL issues on this thread). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
Tom Lane t...@sss.pgh.pa.us wrote:
Huh? If there was never an XID, there's no commit WAL record,
hence nothing to make asynchronous.
If you look at the RecordTransactionCommit() function in xact.c
you'll see that's not correct. Currently the commit record has
nothing to do with whether it synchronizes on WAL writes. In
particular, this section around line 1096 is where the choice is
made:
if ((wrote_xlog synchronous_commit SYNCHRONOUS_COMMIT_OFF)
|| forceSyncCommit || nrels 0)
{
/*
* Synchronous commit case:
In the OP's test case, wrote_xlog is true, while forceSyncCommit is
false and nrels == 0.
It doesn't seem like commit of a read-only transaction should be a
magical time for pruning WAL entries to hit the disk, so it probably
would work to modify that if to not drop into the synchronous
commit code if the transaction is explicitly declared READ ONLY or
if it never acquired an XID; although there's likely some better way
to deal with it.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
Huh? If there was never an XID, there's no commit WAL record,
hence nothing to make asynchronous.
If you look at the RecordTransactionCommit() function in xact.c
you'll see that's not correct.
Oh, hmmm ... that code was written with the idea that things like
sequence XLOG_SEQ_LOG records ought to be flushed to disk before
reporting commit; otherwise you don't have a guarantee that the same
sequence value couldn't be handed out again after crash/restart,
in a transaction that just does something like
SELECT nextval('myseq');
without any updates of regular tables.
It seems like we ought to distinguish heap cleanup activities from
user-visible semantics (IOW, users shouldn't care if a HOT cleanup has
to be done over after restart, so if the transaction only wrote such
records there's no need to flush). This'd require more process-global
state than we keep now, I'm afraid.
Another approach we could take (also nontrivial) is to prevent
select-only queries from doing HOT cleanups. You said upthread that
there were alleged performance benefits from aggressive cleanup, but
IMO that can charitably be described as unproven. The real reason it
happens is that we didn't see a simple way for page fetches to know soon
enough whether a tuple update would be likely to happen later, so they
just do cleanups unconditionally.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On 07/13/2011 11:42 AM, Kevin Grittner wrote: So transactions without an XID *are* sensitive to synchronous_commit. That's likely a useful clue. How much did it help the run time of the SELECT which followed the UPDATE? It has surprisingly little impact on the SELECT side: = set synchronous_commit = on; = update test set created_by = '001' where tenant = '001'; UPDATE 3712 Time: 384.702 ms lars= select count(*) from test where tenant = '001' and created_date = '2011-6-30'; count --- 3712 (1 row) Time: 36.571 ms = select count(*) from test where tenant = '001' and created_date = '2011-6-30'; count --- 3712 (1 row) Time: 5.702 ms = select count(*) from test where tenant = '001' and created_date = '2011-6-30'; count --- 3712 (1 row) Time: 5.822 ms = set synchronous_commit = off; SET Time: 0.145 ms = update test set created_by = '001' where tenant = '001'; UPDATE 3712 Time: 96.227 ms = select count(*) from test where tenant = '001' and created_date = '2011-6-30'; count --- 3712 (1 row) Time: 32.422 ms = select count(*) from test where tenant = '001' and created_date = '2011-6-30'; count --- 3712 (1 row) Time: 6.080 ms I tried it multiple times, and while the numbers change by 5-10ms the relationship is the same. The same results show when I use my JDBC code to run updates/selects as fast as possible. When synchronous_commit is off for the SELECTing process it seems to be slightly faster. -- Lars -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
