Re: [PERFORM] Postgresql in a Virtual Machine
On Tue, Nov 26, 2013 at 11:18:41AM -0800, Craig James wrote: - On Tue, Nov 26, 2013 at 10:40 AM, Ben Chobot be...@silentmedia.com wrote: - - On Nov 26, 2013, at 9:24 AM, Craig James wrote: - - So far I'm impressed by what I've read about Amazon's Postgres instances. - Maybe the reality will be disappointing, but (for example) the idea of - setting up streaming replication with one click is pretty appealing. - - - Where did you hear this was an option? When we talked to AWS about their - Postgres RDS offering, they were pretty clear that (currently) replication - is hardware-based, the slave is not live, and you don't get access to the - WALs that they use internally for PITR. Changing that is something they - want to address, but isn't there today. - - - I was guessing from the description of their High Availability option ... - but maybe it uses something like pg-pool, or as you say, maybe they do it - at the hardware level. - - http://aws.amazon.com/rds/postgresql/#High-Availability - - - Multi-AZ Deployments This deployment option for your production DB - Instances enhances database availability while protecting your latest - database updates against unplanned outages. When you create or modify your - DB Instance to run as a Multi-AZ deployment, Amazon RDS will automatically - provision and manage a standby replica in a different Availability Zone - (independent infrastructure in a physically separate location). Database - updates are made concurrently on the primary and standby resources to - prevent replication lag. In the event of planned database maintenance, DB - Instance failure, or an Availability Zone failure, Amazon RDS will - automatically failover to the up-to-date standby so that database - operations can resume quickly without administrative intervention. Prior to - failover you cannot directly access the standby, and it cannot be used to - serve read traffic. - - Either way, if a cold standby is all you need, it's still a one-click - option, lots simpler than setting it up yourself. - - Craig The Multi-AZ deployments don't expose the replica to you unless there is a failover. (in which case it picks one and promotes it) There is an option for Create Read Replica but it's currently not available so we can assume that will eventually be an option. -- 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] COPY TO and VACUUM
Hi Roberto, Yes you could partition by vendor and then truncate the partition before loading. Truncate reclaims space immediately and is generally much faster than delete. On Thu, Sep 05, 2013 at 06:05:08PM +0200, Roberto Grandi wrote: - Hi Jeff, - - the proble is that when continously updloading vendors listing on our big table the autovacuum is not able to free space as we would. - Secondarly, if we launch a Vacuum after each upload we collide with other upload taht are running in parallel. - - Is it possible, form your point of view, working with isolation levels or table partitioning to minimize table space growing? - Thanks again for all your help. - - BR, - Roberto - - - Messaggio originale - - Da: Jeff Janes jeff.ja...@gmail.com - A: Roberto Grandi roberto.gra...@trovaprezzi.it - Cc: Kevin Grittner kgri...@ymail.com, pgsql-performance@postgresql.org - Inviato: Mercoledì, 4 settembre 2013 18:29:13 - Oggetto: Re: [PERFORM] COPY TO and VACUUM - - On Tue, Sep 3, 2013 at 11:15 PM, Roberto Grandi - roberto.gra...@trovaprezzi.it wrote: - Hi kevin - - first of all thanks for your help. I did a mistake we are using postgres 8.3. - - I didn't expect COPY TO frees space but I was wondering Autovacumm delete dead rows as soon as possible, in fact my scenario is: - - - Delete all products record for a vendor - - Reload all products record (from new listing) for the same vendor. - - Obviously we repeat this process continously and table space is growing really fast. - - It isn't obvious to me why you would do this continuously. Surely - your vendors don't change their catalogs exactly as fast as your - database can digest them! - - In any event, I'd probably just incorporate a manual vacuum statement - into the delete/reload cycle. Since delete and copy are not - throttled, while autovacuum is throttled by default to a rather low - level, it is quite possible that default autovacuum can never keep up - with the workload you are generating. Rather than trying to tune - autovacuum to fit this special case, it would be easier to just throw - in some manual vacuuming. (Not instead of autovac, just as a - supplement to it) - - Cheers, - - Jeff - - - -- - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) - To make changes to your subscription: - http://www.postgresql.org/mailpref/pgsql-performance -- 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] Looks like merge join planning time is too big, 55 seconds
On Thu, Aug 01, 2013 at 07:17:27PM +0400, Sergey Burladyan wrote: - Sergey Burladyan eshkin...@gmail.com writes: - - # explain - # select i.item_id, u.user_id from items i - # left join users u on u.user_id = i.user_id - # where item_id = 169946840; - QUERY PLAN - -- - Nested Loop Left Join (cost=0.00..397.14 rows=1 width=16) - - Index Scan using items_item_ux on items i (cost=0.00..358.84 rows=1 width=16) - Index Cond: (item_id = 169946840) - - Index Only Scan using users_user_id_pkey on users u (cost=0.00..38.30 rows=1 width=8) - Index Cond: (user_id = i.user_id) - - time: 55919.910 ms - - While running this EXPLAIN backend use disk for a long time: - TID PRIO USER DISK READ DISK WRITE SWAPIN IOCOMMAND - 21638 be/4 postgres2.10 M/s9.45 M/s 0.00 % 69.04 % postgres: postgres x xxx.xxx.xxx.xxx(50987) EXPLAIN - - Why it read and write to disk 10 megabytes per second for EXPLAIN query? Cannot understand what is going on here :( That sounds familiar - is it possible you're running into this? http://www.postgresql.org/message-id/20120713065122.ga45...@mr-paradox.net -- 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] Seq Scan vs Index on Identical Tables in Two Different Databases
On Wed, Jul 17, 2013 at 07:50:06PM +, Ellen Rothman wrote: - I have the same table definition in two different databases on the same computer. When I explain a simple query in both of them, one database uses a sequence scan and the other uses an index scan. If I try to run the Seq Scan version without the where clause restricting the value of uniqueid, it uses all of the memory on my computer and never completes. - - How can I get the Seq Scan version to use an index scan? - - Explain results - good version: - GroupAggregate (cost=0.00..173.78 rows=1 width=15) - - Index Scan using pubcoop_ext_idx1 on pubcoop_ext (cost=0.00..173.77 rows=1 width=15) - Index Cond: (uniqueid '9'::bpchar) - - Explain results - problem version: - HashAggregate (cost=13540397.84..13540398.51 rows=67 width=18) - - Seq Scan on pubcoop_ext (cost=0.00..13360259.50 rows=36027667 width=18) - Filter: (uniqueid '9'::bpchar) (Assuming that your postgresql.conf is the same across both systems and that you've run vanilla analyze against each table... ) I ran into a similar problem before and it revolved around the somewhat random nature of a vaccum analyze. To solve the problem i increased the statistics_target for the table on the box that was performing poorly and ran analyze. I believe that worked because basically the default_statistics_taget of 100 wasn't catching enough info about that record range to make an index appealing to the optimizer on the new box where the old box it was. -- 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] Process 11812 still waiting for ExclusiveLock on extension of relation
On Jul 18, 2012, at 5:08 AM, Sergey Konoplev wrote: Hi, On Tue, Jul 17, 2012 at 7:57 PM, David Kerr d...@mr-paradox.net wrote: I suspect that this is related to a sustained heavy load that would stop autovacuum from getting at this table... Does that sound plausible? Well, not sure. Let us look at the table's statistics first. \x select * from pg_stat_user_tables where relname = 'yourtablename'; the load is controlled and only lasts a few hours. at this point auto vacuum has gotten to the table and done it's thing. I'm wondering what options I have to smooth over these episodes / speed up the extensions. I'm thinking of something like, CLUSTER or VACUUM FULL (those take quite a run so I'd like some direction on it before i TiaS =) ) Instead of CLUSTER I would suggest you to use one of the tools below. They do not block the table as CLUSTER does. pg_reorg http://reorg.projects.postgresql.org/pg_reorg.html Faster, but requires a lot of IO and additional disk space, also it needs PK on the table. pgcompactor http://code.google.com/p/pgtoolkit/ Allows to smooth IO, auto-determines reorganizing necessity for tables and indexes, no PK restriction. I haven't given these projects much thought in the past, but I guess we're getting to the size where that sort of thing might come in handy. I'll have a look. I suspect that Partitioning would help. Any other ideas? Partitioning is a good thing to think about when you deal with big tables. Yeah. unless you're using hibernate which expects inserts to return the # of rows entered (unless you disable that) which we are. or you have fairly dynamic data that doesn't have a great partition key. thanks -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Process 11812 still waiting for ExclusiveLock on extension of relation
Howdy, I've got a couple of tables that are taking a little longer than normal to extend, resulting in some slow inserts. They're fairly large tables, ~200GB pg_total_relation_size (90GB for just the table) I suspect that this is related to a sustained heavy load that would stop autovacuum from getting at this table... Does that sound plausible? I'm wondering what options I have to smooth over these episodes / speed up the extensions. I'm thinking of something like, CLUSTER or VACUUM FULL (those take quite a run so I'd like some direction on it before i TiaS =) ) I suspect that Partitioning would help. Any other ideas? Jul 17 08:11:52 perf: [3-1] user=test,db=perf LOG: process 11812 still waiting for ExclusiveLock on extension of relation 60777 of database 16387 after 1000.270 ms System resouces were fine: PGDATA -- 07/17/12 08:11:48 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util dm-2 1.20 3085.20 77.20 3994.20 15363.20 56680.0017.69 15.573.82 0.06 26.22 07/17/12 08:11:53 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util dm-2 0.40 2097.20 51.80 2610.20 10344.00 37659.2018.03 5.231.96 0.05 14.28 PGXLOG -- 07/17/12 08:11:48 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util dm-4 0.00 3958.200.00 600.40 0.00 36449.6060.71 0.440.74 0.73 43.54 07/17/12 08:11:53 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util dm-4 0.00 2905.200.00 403.40 0.00 26492.8065.67 0.320.80 0.79 31.96 CPU -- CPU %user %nice %system %iowait%steal %idle 08:11:48all 24.49 0.00 3.19 1.17 0.00 71.15 08:11:53all 17.53 0.00 3.13 0.68 0.00 78.65 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] slow prepare, lots of semop calls.
I think my original post here might have gotten caught in a spamtrap, so
re-trying, I apologize if it ends
up being a duplicate.
I also forgot to mention that I'm on PG9.1.1 / RHEL 6.2 x64
I believe this is the reason for the behavior i was seeing in this post as well.
http://archives.postgresql.org/pgsql-performance/2012-07/msg00035.php
---
Periodically when restarting my database I find that my PREAPRE time goes
through the roof.
This query usually runs in a few ms total. After a recent database restart I
find that it's up to 8 seconds
consistantly just to PREPARE.
Even EXPLAIN ends up taking time.
psql -f tt.sql a
Pager usage is off.
Timing is on.
PREPARE
Time: 7965.808 ms
[...]
(1 row)
Time: 1.147 ms
I did an strace on the backend and saw the below.. it seems like there is a
problem with grabbing
a semaphore?
strace -p 2826
Process 2826 attached - interrupt to quit
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
[snip 1000s of lines]
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
[snip 1000s of lines]
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(21069900, {{10, 1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
semop(20709441, {{5, -1, 0}}, 1)= 0
brk(0x1207000) = 0x1207000
brk(0x1229000) = 0x1229000
brk(0x124a000) = 0x124a000
mmap(NULL, 135168, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x2ac9c6b95000
sendto(8, 134Jul 13 00:21:55 postgres[28..., 934, MSG_NOSIGNAL, NULL, 0) =
-1 ENOTCONN (Transport endpoint is not connected)
close(8)= 0
socket(PF_FILE, SOCK_DGRAM|SOCK_CLOEXEC, 0) = 8
connect(8, {sa_family=AF_FILE, path=/dev/log}, 110) = 0
sendto(8, 134Jul 13 00:21:55 postgres[28..., 934, MSG_NOSIGNAL, NULL, 0) =
934
sendto(8, 134Jul 13 00:21:55 postgres[28..., 61, MSG_NOSIGNAL, NULL, 0) = 61
sendto(8, 134Jul 13 00:21:55 postgres[28..., 89, MSG_NOSIGNAL, NULL, 0) = 89
sendto(8, 134Jul 13 00:21:55 postgres[28..., 172, MSG_NOSIGNAL, NULL, 0) =
172
sendto(8, 134Jul 13 00:21:55 postgres[28..., 96, MSG_NOSIGNAL, NULL, 0) = 96
sendto(8, 134Jul 13 00:21:55 postgres[28..., 157, MSG_NOSIGNAL, NULL, 0) =
157
sendto(8, 134Jul 13 00:21:55 postgres[28..., 106, MSG_NOSIGNAL, NULL, 0) =
106
sendto(8, 134Jul 13 00:21:55 postgres[28..., 185, MSG_NOSIGNAL, NULL, 0) =
185
sendto(8, 134Jul 13 00:21:55 postgres[28..., 93, MSG_NOSIGNAL, NULL, 0) = 93
sendto(8, 134Jul 13 00:21:55 postgres[28..., 143, MSG_NOSIGNAL, NULL, 0) =
143
sendto(8, 134Jul 13 00:21:55 postgres[28..., 176, MSG_NOSIGNAL, NULL, 0) =
176
sendto(8, 134Jul 13 00:21:55 postgres[28..., 168, MSG_NOSIGNAL, NULL, 0) =
168
sendto(8, 134Jul 13 00:21:55 postgres[28..., 53, MSG_NOSIGNAL, NULL, 0) = 53
sendto(8, 134Jul 13 00:21:55 postgres[28..., 100, MSG_NOSIGNAL, NULL, 0) =
100
sendto(8, 134Jul 13 00:21:55 postgres[28..., 96, MSG_NOSIGNAL, NULL, 0) = 96
sendto(8, 134Jul 13 00:21:55 postgres[28..., 96, MSG_NOSIGNAL, NULL, 0) = 96
sendto(8, 134Jul 13 00:21:55 postgres[28..., 160, MSG_NOSIGNAL, NULL, 0) =
160
sendto(8, 134Jul 13 00:21:55 postgres[28..., 98, MSG_NOSIGNAL, NULL, 0) = 98
sendto(8, 134Jul 13 00:21:55 postgres[28..., 85, MSG_NOSIGNAL, NULL, 0) = 85
sendto(8, 134Jul 13 00:21:55 postgres[28..., 53, MSG_NOSIGNAL, NULL, 0) = 53
sendto(7, \2\0\0\0\300\3\0\0\3@\0\0\t\0\0\0\1\0\0\0\0\0\0\0007\n\0\0t_sc...,
960, 0, NULL, 0) = 960
sendto(7, \2\0\0\0\300\3\0\0\3@\0\0\t\0\0\0\0\0\0\0\0\0\0\0w\n\0\0t_sc...,
960, 0, NULL, 0) = 960
sendto(7, \2\0\0\0\300\3\0\0\3@\0\0\t\0\0\0\0\0\0\0\0\0\0\0009\n\0\0t_sc...,
960, 0, NULL, 0) = 960
sendto(7, \2\0\0\0\300\3\0\0\3@\0\0\t\0\0\0\0\0\0\0\0\0\0\0M\354\0\0t_sc...,
960, 0, NULL, 0) = 960
sendto(7,
Re: [PERFORM] Massive I/O spikes during checkpoint
On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? (for 512MB raid controller with cache I would suggest to sometning like vm.dirty_background_bytes = 33554432 vm.dirty_bytes = 268435456 32MB and 256MB respectively) I'll take a look. If youre server doesn't have raid with BBU cache - then you should tune these values to much lower values. Please read http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/ and related posts. yeah, I saw that I guess I didn't put 2+2 together. thanks.
Re: [PERFORM] Massive I/O spikes during checkpoint
On 7/9/2012 11:14 PM, Maxim Boguk wrote: On Tue, Jul 10, 2012 at 4:03 PM, David Kerr d...@mr-paradox.net mailto:d...@mr-paradox.net wrote: On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote: But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. It's not an actual checkpoints. It's is a fsync after checkpoint which create write spikes hurting server. You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values So use bla_bytes instead of bla_ratio? Yes because on 256GB server echo 10 /proc/sys/vm/dirty_ratio is equivalent to 26Gb dirty_bytes and echo 5 /proc/sys/vm/dirty_background_ratio is equivalent to 13Gb dirty_background_bytes It is really huge values. sigh yeah, I never bothered to think that through. So kernel doesn't start write any pages out in background before it has at least 13Gb dirty pages in kernel memory. And at end of the checkpoint kernel trying flush all dirty pages to disk. Even echo 1 /proc/sys/vm/dirty_background_ratio is too high value for contemporary server. That is why *_bytes controls added to kernel. Awesome, Thanks. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Massive I/O spikes during checkpoint
Howdy! I'm trying to figure out why checkpointing it completely pegging my I/O under moderate to high write load, I'm on PG9.1.1, RHEL 6.2 x64 checkpoint_completion_target = 0.7 checkpoint_timeout = 10m Jul 10 00:32:30 perf01 postgres[52619]: [1895-1] user=,db= LOG: checkpoint starting: time [...] Jul 10 00:36:47 perf01 postgres[52619]: [1896-1] user=,db= LOG: checkpoint complete: wrote 119454 buffers (11.4%); 0 transaction log file(s) added, 0 removed Watching my I/O with: iostat -t -d -x dm-2 5 Which is my $PGDATA mount point (ext4). I get the following: Dater/s w/s rsec/s wsec/s await svctm %util [...] 07/10/12 00:35:36 0 69.80 2233.6 0.630.07 0.46 07/10/12 00:35:41 1.2 810 99.222200 4.130.05 4.02 07/10/12 00:35:46 0 111.6 0 5422.4 1.820.08 0.9 07/10/12 00:35:51 0 299.2 0 5670.4 1.270.04 1.24 07/10/12 00:35:56 0.8 176.6 41.63654.4 2.160.07 1.32 07/10/12 00:36:01 0 364.8 0 6670.4 1.1 0.04 1.62 07/10/12 00:36:06 0.8 334.6 12.85953.6 1.180.05 1.64 07/10/12 00:36:11 0 118.6 0 6948.8 1.820.07 0.82 07/10/12 00:36:16 0 8274.6 0 148764.810.55 0.07 61.18 07/10/12 00:36:21 0.2 8577.4 3.2 161806.416.68 0.12 99.62 07/10/12 00:36:26 0.8 9244.6 12.8167841.615.01 0.11 99.82 07/10/12 00:36:31 0.8 9434.2 44.8208156.816.22 0.11 99.7 07/10/12 00:36:36 0 9582.8 0 202508.814.84 0.1 99.72 07/10/12 00:36:41 0 9830.2 0 175326.414.42 0.1 99.5 07/10/12 00:36:46 0 8208.6 0 149372.817.82 0.12 99.64 07/10/12 00:36:51 3 1438.4 102.4 26748.8 8.490.12 18 07/10/12 00:36:56 0.6 2004.6 9.6 27400 1.250.03 5.74 07/10/12 00:37:01 0.6 17239.6 23758.4 1.850.03 5.08 07/10/12 00:37:06 0.4 181.2 35.229281.490.06 1.06 The ramp up is barely using any I/O, but then just before the checkpoint ends I get a flood of I/O all at once. I thought that the idea of checkpoint_completion_target was that we try to finish writing out the data throughout the entire checkpoint (leaving some room to spare, in my case 30% of the total estimated checkpoint time) But what appears to be happening is that all of the data is being written out at the end of the checkpoint. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I don't have data to back that statement up. Any suggestions. I'm willing and able to profile, or whatever. Thanks
Re: [PERFORM] Massive I/O spikes during checkpoint
On Jul 9, 2012, at 10:52 PM, Jeff Janes wrote: On Mon, Jul 9, 2012 at 10:39 PM, David Kerr d...@mr-paradox.net wrote: I thought that the idea of checkpoint_completion_target was that we try to finish writing out the data throughout the entire checkpoint (leaving some room to spare, in my case 30% of the total estimated checkpoint time) But what appears to be happening is that all of the data is being written out at the end of the checkpoint. Postgres is writing data out to the kernel throughout the checkpoint. But the kernel is just buffering it up dirty, until the end of the checkpoint when the fsyncs start landing like bombs. Ahh. duh! I guess i assumed that the point of spreading the checkpoint I/O was spreading the syncs out. This happens at every checkpoint while the system is under load. I get the feeling that this isn't the correct behavior and i've done something wrong. Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I don't have data to back that statement up. Did you have less RAM back when you were running PG 8.3? nope. I was on RHEL 5.5 back then though. Any suggestions. I'm willing and able to profile, or whatever. Who much RAM do you have? What are your settings for /proc/sys/vm/dirty_* ? 256G and I've been running with this for a while now, but I think that's the default in RHEL 6+ echo 10 /proc/sys/vm/dirty_ratio echo 5 /proc/sys/vm/dirty_background_ratio -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] What would effect planning time?
I spent a good chunk of today trying to chase down why a query on one box ran in 110ms and on another, smaller box it ran in 10ms. There was no other activity on either box. Both boxes are PG9.1.1 RHEL 6.2 x64. the faster box is a smallish VM. the other box is a big 40core/256GB box. The plans between both boxes were exactly the same, so it didn't occur to me to run an analyze on the tables. I did a number of things including bouncing the DB and reindexing some of the tables. that didn't help. Eventually i separated the query out to a prepared statement and found that it was spending 100ms in PREPARE on the slow box (I assume it was planning) I left the problem for about 30 minutes and came back and the query started running at normal speed. I suspect an autovacuum kicked in, but would that sort of thing really impact parse/plan time to that degree? any other thoughts as to what it could have been? -- 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] Drop statistics?
On Jul 3, 2012, at 10:16 AM, Bruce Momjian wrote: On Fri, Jun 22, 2012 at 11:04:36AM -0700, David Kerr wrote: On Fri, Jun 22, 2012 at 01:27:51PM -0400, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - I'm trying to work through a root cause on a performance problem. I'd like to - be able to show that a problem was fixed by analyzing the table. - - what i've done is - set default_statistics_target=1 - analyze Table - - That gets rid of most of the rows in pg_stats, but i'm still getting decent performance. - - I usually do something like - - DELETE FROM pg_statistic WHERE starelid = 'foo'::regclass; - - (you need to be superuser to be allowed to do this). - - You may need to keep an eye on whether auto-analyze is coming along and - undoing what you did, too. - -regards, tom lane - Awesome, thanks! One cool trick I have seen is to do the DELETE pg_statistic in a multi-statement transaction and then run query query, and roll it back. This allows the statistics to be preserved, and for only your query to see empty pg_statistic values for the table. Nice! thanks! Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Drop statistics?
I'm trying to work through a root cause on a performance problem. I'd like to be able to show that a problem was fixed by analyzing the table. what i've done is set default_statistics_target=1 analyze Table That gets rid of most of the rows in pg_stats, but i'm still getting decent performance. It's possible that the existing stats were just not optimal, and i won't be able to get that back. But I just want to verify that what i've done is the only real option that I have? am i missing anything else that I could try? (I'm on PG9.1) Thanks. Dave -- 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] Drop statistics?
On Fri, Jun 22, 2012 at 01:27:51PM -0400, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - I'm trying to work through a root cause on a performance problem. I'd like to - be able to show that a problem was fixed by analyzing the table. - - what i've done is - set default_statistics_target=1 - analyze Table - - That gets rid of most of the rows in pg_stats, but i'm still getting decent performance. - - I usually do something like - - DELETE FROM pg_statistic WHERE starelid = 'foo'::regclass; - - (you need to be superuser to be allowed to do this). - - You may need to keep an eye on whether auto-analyze is coming along and - undoing what you did, too. - - regards, tom lane - Awesome, thanks! Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] global/pgstat.stat corrupt
Howdy, I just restored a DB from a cold backup (pg_ctl stop -m fast) When starting the DB I see: LOG: corrupted statistics file global/pgstat.stat When I look at the filesystem I don't see a global/pgstat.stat file but i do see a pg_stat_tmp/pgstat.stat is that PG rebuilding the corrupt file? Are those stats related to pg_stats style statistics? Any idea why that pgstats.stat file would be corrupt after a relativly clean backup like that? -- 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] global/pgstat.stat corrupt
On Fri, Jun 22, 2012 at 03:49:00PM -0400, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - I just restored a DB from a cold backup (pg_ctl stop -m fast) - - When starting the DB I see: - LOG: corrupted statistics file global/pgstat.stat - - Is that repeatable? It wouldn't be too surprising to see this once when - starting from a filesystem backup, if you'd managed to capture a - partially written stats file in the backup. hmm, possibly. it's somewhat worrysome if that file isn't written out what else isn't getting written out. our backups are SAN based snapshots, maybe i need to sync prior to the snapshot even with the DB being down. - When I look at the filesystem I don't see a global/pgstat.stat file but i do see a - pg_stat_tmp/pgstat.stat - - This is normal --- global/pgstat.stat is only supposed to exist when the - system is shut down. Transient updates are written into pg_stat_tmp/ ah ok - Are those stats related to pg_stats style statistics? - - No, this file is for the stats collection subsystem, - http://www.postgresql.org/docs/9.1/static/monitoring-stats.html - - regards, tom lane - Oh, hmm thanks. I had looked at that before but must not have fully understood what was going on. That's interesting! Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] pg_autovacuum in PG9.x
Howdy, What is/is there a replacement for pg_autovacuum in PG9.0+ ? I haven't had much luck looking for it in the docs. Thanks! Dave -- 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] pg_autovacuum in PG9.x
On 04/03/2012 06:40 PM, Brett Mc Bride wrote: Hi Dave, It's part of core now: http://www.postgresql.org/docs/9.1/static /routine-vacuuming.html#AUTOVACUUM AH awesome, thanks. -- 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] Very long deletion time on a 200 GB database
On 02/27/2012 12:08 AM, Reuven M. Lerner wrote: Hi, everyone. I wanted to thank you again for your help on the huge delete problem that I was experiencing. After a lot of trial and error, we finally came to the conclusion that deleting this much data in the time frame that they need, on underpowered hardware that is shared with an application, with each test iteration taking 5-9 hours to run (but needing to run in 2-3), is just not going to happen. We tried many of the options that people helpfully suggested here, but none of them gave us the performance that we needed. (One of the developers kept asking me how it can possibly take so long to delete 200 GB, when he can delete files of that size in much less time. I had to explain to him that deleting rows from a database, is a far more complicated task, and can't really be compared to deleting a few files.) In the end, it was agreed that we could execute the deletes over time, deleting items in the background, or in parallel with the application's work. After all, if the disk is filling up at the rate of 2 GB/day, then so long as we delete 4 GB/day (which is pretty easy to do), we should be fine. Adding RAM or another disk are simply out of the question, which is really a shame for a database of this size. Howdy, I'm coming a little late to the tread but i didn't see anyone propose some tricks I've used in the past to overcome the slow delete problem. First - if you can drop your FKs, delete, re-create your FKs you'll find that you can delete an amazing amount of data very quickly. second - if you can't do that - you can try function that loops and deletes a small amount at a time, this gets around the deleting more data then you can fit into memory problem. It's still slow but just not as slow. third - don't delete, instead, create new_table as select * from old_table where records are not the ones you want to delete rename new_table to old_table; create indexes and constraints drop old_table; fourth - I think some folks mentioned this, but just for completeness, partition the table and make sure that your partition key is such that you can just drop an entire partition. Hope that helps and wasn't redundant. Dave -- 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] Performance issues
On Mon, Mar 07, 2011 at 10:49:48PM +0100, Andreas For Tollefsen wrote: - The synchronous_commit off increased the TPS, but not the speed of the below - query. - - Oleg: - This is a query i am working on now. It creates an intersection of two - geometries. One is a grid of 0.5 x 0.5 decimal degree sized cells, while the - other is the country geometries of all countries in the world for a certain - year. - - priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode, - ST_Intersection(pri - ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land, cshapeswdate WHERE - ST_In - tersects(priogrid_land.cell, cshapeswdate.geom); - QUERY - PLAN - - - -- - Nested Loop (cost=0.00..12644.85 rows=43351 width=87704) (actual - time=1.815..7 - 074973.711 rows=130331 loops=1) -Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom) -- Seq Scan on cshapeswdate (cost=0.00..14.42 rows=242 width=87248) - (actual - time=0.007..0.570 rows=242 loops=1) -- Index Scan using idx_priogrid_land_cell on priogrid_land - (cost=0.00..7.1 - 5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242) - Index Cond: (priogrid_land.cell cshapeswdate.geom) - Total runtime: 7075188.549 ms - (6 rows) Your estimated and actuals are way off, have you analyzed those tables? Dave -- 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] pgbench could not send data to client: Broken pipe
On Thu, Sep 09, 2010 at 10:38:16AM -0400, Alvaro Herrera wrote: - Excerpts from David Kerr's message of mié sep 08 18:29:59 -0400 2010: - - Thanks for the insight. we're currently in performance testing of the - app. Currently, the JVM is the bottleneck, once we get past that - i'm sure it will be the database at which point I'll have the kind - of data you're talking about. - - Hopefully you're not running the JVM stuff in the same machine. Nope, this server is 100% allocated to the database. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] pgbench could not send data to client: Broken pipe
Howdy, I'm running pgbench with a fairly large # of clients and getting this error in my PG log file. Here's the command: ./pgbench -c 1100 testdb -l I get: LOG: could not send data to client: Broken pipe (I had to modify the pgbench.c file to make it go that high, i changed: MAXCLIENTS = 2048 I thought maybe i was running out of resources so i checked my ulimits: ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 2048 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) unlimited real-time priority (-r) 0 stack size (kbytes, -s) unlimited cpu time (seconds, -t) unlimited max user processes (-u) unlimited virtual memory (kbytes, -v) unlimited file locks (-x) unlimited This is Pg 8.3.10. Redhat 64bit, the system has 48 cores, 256G of ram.. Any idea what would be causing the error? thanks Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 03:27:34PM -0400, Greg Smith wrote: - Tom Lane wrote: - As of the 9.0 release, it's possible to run pgbench in a multi thread - mode, and if you forced the subprocess rather than thread model it looks - like the select() limit would be per subprocess rather than global. - So I think you could get above the FD_SETSIZE limit with a bit of - hacking if you were using 9.0's pgbench. No chance with 8.3 though. - - - I believe David can do this easily enough by compiling a 9.0 source code - tree with the --disable-thread-safety option. That's the simplest way - to force the pgbench client to build itself using the multi-process - model, rather than the multi-threaded one. - - It's kind of futile to run pgbench simulating much more than a hundred - or two clients before 9.0 anyway. Without multiple workers, you're - likely to just run into the process switching limitations within pgbench - itself rather than testing server performance usefully. I've watched - the older pgbench program fail to come close to saturating an 8 core - server without running into its own limitations first. - - You might run a 9.0 pgbench client against an 8.3 server though, if you - did the whole thing starting from pgbench database initialization over - again--the built-in tables like accounts changed to pgbench_accounts - in 8.4. That might work, can't recall any changes that would prevent - it; but as I haven't tested it yet I can't say for sure. Thanks, I compiled the 9.0 RC1 branch with the --disable-thread-safety option and ran PG bench on my 8.3 DB it seemed to work fine, However, MAXCLIENTS is still 1024, if i hack it to switch it up to 2048 i get this: starting vacuum...end. select failed: Bad file descriptor --- transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 1900 number of threads: 1 number of transactions per client: 10 number of transactions actually processed: 3723/19000 tps = 52.007642 (including connections establishing) tps = 82.579077 (excluding connections establishing) I'm not sure what Tom is referring to with the select(2) limitation, maybe I'm running into it (where do i find that? /usr/include/sys/select.h? ) should i be running pgbench differently? I tried increasing the # of threads but that didn't increase the number of backend's and i'm trying to simulate 2000 physical backend processes. thanks Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 03:44:36PM -0400, Tom Lane wrote: - Greg Smith g...@2ndquadrant.com writes: - Tom Lane wrote: - So I think you could get above the FD_SETSIZE limit with a bit of - hacking if you were using 9.0's pgbench. No chance with 8.3 though. - - I believe David can do this easily enough by compiling a 9.0 source code - tree with the --disable-thread-safety option. - - It would take a bit more work than that, because the code still tries to - limit the client count based on FD_SETSIZE. He'd need to hack it so - that in non-thread mode, the limit is FD_SETSIZE per subprocess. I was - suggesting that an official patch to that effect would be a good thing. Yeah, that might be beyond me =) Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 04:35:28PM -0400, Tom Lane wrote: - David Kerr d...@mr-paradox.net writes: - should i be running pgbench differently? I tried increasing the # of threads - but that didn't increase the number of backend's and i'm trying to simulate - 2000 physical backend processes. - - The odds are good that if you did get up that high, what you'd find is - pgbench itself being the bottleneck, not the server. What I'd suggest - is running several copies of pgbench *on different machines*, all - beating on the one database server. Collating the results will be a bit - more of a PITA than if there were only one pgbench instance, but it'd - be a truer picture of real-world behavior. - - It's probably also worth pointing out that 2000 backend processes is - likely to be a loser anyhow. If you're just doing this for academic - purposes, fine, but if you're trying to set up a real system for 2000 - clients you almost certainly want to stick some connection pooling in - there. - - regards, tom lane - ah that's a good idea, i'll have to give that a shot. Actually, this is real.. that's 2000 connections - connection pooled out to 20k or so. (although i'm pushing for closer to 1000 connections). I know that's not the ideal way to go, but it's what i've got to work with. It IS a huge box though... Thanks Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 03:56:24PM -0500, Kevin Grittner wrote: - David Kerr d...@mr-paradox.net wrote: - - Actually, this is real.. that's 2000 connections - connection - pooled out to 20k or so. (although i'm pushing for closer to 1000 - connections). - - I know that's not the ideal way to go, but it's what i've got to - work with. - - It IS a huge box though... - - FWIW, my benchmarks (and I've had a couple people tell me this is - consistent with what they've seen) show best throughput and best - response time when the connection pool is sized such that the number - of active PostgreSQL connections is limited to about twice the - number of CPU cores plus the number of effective spindles. Either - you've got one heck of a machine, or your sweet spot for the - connection pool will be well under 1000 connections. - - It is important that your connection pool queues requests when - things are maxed out, and quickly submit a new request when - completion brings the number of busy connections below the maximum. - - -Kevin Hmm, i'm not following you. I've got 48 cores. that means my sweet-spot active connections would be 96. (i.e., less than the default max_connections shipped with PG) and this is a very very expensive machine. Now if i were to connection pool that out to 15 people per connection, that's 1440 users total able to use my app at one time. (with only 96 actually doing anything). not really great for a web-based app that will have millions of users accessing it when we're fully ramped up. I've got a few plans to spread the load out across multiple machines but at 1440 users per machine this wouldn't be sustanable.. I know that other people are hosting more than that on larger machines so i hope i'm ok. Thanks Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 04:51:17PM -0500, Kevin Grittner wrote: - David Kerr d...@mr-paradox.net wrote: - - Hmm, i'm not following you. I've got 48 cores. that means my - sweet-spot active connections would be 96. - - Plus your effective spindle count. That can be hard to calculate, - but you could start by just counting spindles on your drive array. We've got this weird LPAR thing at our hosting center. it's tough for me to do. - Now if i were to connection pool that out to 15 people per - connection, - - Where did you get that number? We routinely route hundreds of - requests per second (many of them with 10 or 20 joins) from five or - ten thousand connected users through a pool of 30 connections. It - started out bigger, we kept shrinking it until we hit our sweet - spot. The reason we started bigger is we've got 40 spindles to go - with the 16 cores, but the active portion of the database is cached, - which reduces our effective spindle count to zero. That's encouraging. I don't remember where I got the number from, but my pooler will be Geronimo, so i think it came in that context. - that's 1440 users total able to use my app at one time. (with - only 96 actually doing anything). not really great for a web-based - app that will have millions of users accessing it when we're fully - ramped up. - - Once you have enough active connections to saturate the resources, - adding more connections just adds contention for resources and - context switching cost -- it does nothing to help you service more - concurrent users. The key is, as I mentioned before, to have the - pooler queue requests above the limit and promptly get them running - as slots are freed. Right, I understand that. My assertian/hope is that the saturation point on this machine should be higher than most. Dave -- 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] pgbench could not send data to client: Broken pipe
On Wed, Sep 08, 2010 at 05:27:24PM -0500, Kevin Grittner wrote: - David Kerr d...@mr-paradox.net wrote: - - My assertian/hope is that the saturation point - on this machine should be higher than most. - - Here's another way to think about it -- how long do you expect your - average database request to run? (Our top 20 transaction functions - average about 3ms per execution.) What does that work out to in - transactions per second? That's the TPS you can achieve *on each - connection* if your pooler is efficient. If you've determined a - connection pool size based on hardware resources, divide your - anticipated requests per second by that pool size. If the result is - less than the TPS each connection can handle, you're in good shape. - If it's higher, you may need more hardware to satisfy the load. - - Of course, the only way to really know some of these numbers is to - test your actual application on the real hardware under realistic - load; but sometimes you can get a reasonable approximation from - early tests or gut feel based on experience with similar - applications. I strongly recommend trying incremental changes to - various configuration parameters once you have real load, and - monitor the impact. The optimal settings are often not what you - expect. - - And if the pooling isn't producing the results you expect, you - should look at its configuration, or (if you can) try other pooler - products. - - -Kevin - Thanks for the insight. we're currently in performance testing of the app. Currently, the JVM is the bottleneck, once we get past that i'm sure it will be the database at which point I'll have the kind of data you're talking about. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] PARSE WAITING
Howdy all, We're doing some performance testing, and when we scaled it our app up to about 250 concurrent users we started seeing a bunch of processes sititng in PARSE WAITING state. Can anyone give me insite on what this means? what's the parse waiting for? Thanks Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Very high effective_cache_size == worse performance?
Howdy all, I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9. 64bit OS. No users currently. I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so i don't think we can use copy. Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box. When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because on a much smaller machine I was able to do that same amount of records in 6 hours. My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give any indication that we had resource issues. So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size from 128GB to 2GB). Now the large box performs the same as the smaller box. (which is fine). incidentally, both tests were starting from a blank database. Is this expected? Thanks! Dave -- 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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 01:44:18PM -0400, Robert Haas wrote:
- On Tue, Apr 20, 2010 at 1:39 PM, David Kerr d...@mr-paradox.net wrote:
- My initial thought was hardware issues so we got sar, vmstat, etc all
running on the box and they didn't give
- any indication that we had resource issues.
-
- So I decided to just make the 2 PG config files look the same. (the only
change was dropping effective_cache_size
- from 128GB to 2GB).
-
- Now the large box performs the same as the smaller box. (which is fine).
-
- incidentally, both tests were starting from a blank database.
-
- Is this expected?
-
- Lowering effective_cache_size tends to discourage the planner from
- using a nested-loop-with-inner-indexscan plan - that's it.
-
- What may be happening is that you may be loading data into some tables
- and then running a query against those tables before the autovacuum
- daemon has a chance to analyze them. I suspect that if you enable
- some logging you'll find that one of those queries is really, really
- slow, and that (by happy coincidence) discouraging it from using the
- index it thinks it should use happens to produce a better plan. What
- you should probably do is, for each table that you bulk load and then
- query, insert a manual ANALYZE between the two.
-
- ...Robert
-
that thought occured to me while I was testing this. I ran a vacuumdb -z
on my database during the load and it didn't impact performance at all.
Incidentally the code is written to work like this :
while (read X lines in file){
Process those lines.
write lines to DB.
}
So i would generally expect to get the benefits of the updated staticis
once the loop ended. no? (would prepared statements affect that possibly?)
Also, while I was debugging the problem, I did load a 2nd file into the DB
ontop of one that had been loaded. So the statistics almost certinaly should
have been decent at that point.
I did turn on log_min_duration_statement but that caused performance to be
unbearable,
but i could turn it on again if it would help.
Dave
--
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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote:
- On Tue, Apr 20, 2010 at 2:03 PM, David Kerr d...@mr-paradox.net wrote:
-
- that thought occured to me while I was testing this. I ran a vacuumdb -z
- on my database during the load and it didn't impact performance at all.
-
- Incidentally the code is written to work like this :
-
- while (read X lines in file){
- Process those lines.
- write lines to DB.
- }
-
- So i would generally expect to get the benefits of the updated staticis
- once the loop ended. no? (would prepared statements affect that possibly?)
-
- Also, while I was debugging the problem, I did load a 2nd file into the DB
- ontop of one that had been loaded. So the statistics almost certinaly
- should
- have been decent at that point.
-
- I did turn on log_min_duration_statement but that caused performance to be
- unbearable,
- but i could turn it on again if it would help.
-
- Dave
-
-
- You can absolutely use copy if you like but you need to use a non-standard
- jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
- past and it worked.
-
- Is the whole thing going in in one transaction? I'm reasonably sure
- statistics aren't kept for uncommited transactions.
-
- For inserts the prepared statements can only help. For selects they can
- hurt because eventually the JDBC driver will turn them into back end
- prepared statements that are only planned once. The price here is that that
- plan may not be the best plan for the data that you throw at it.
-
- What was log_min_duration_statement logging that it killed performance?
-
- --Nik
Good to know about the jdbc-copy. but this is a huge project and the load is
just one very very tiny component, I don't think we could introduce anything
new to assist that.
It's not all in one tx. I don't have visibility to the code to determine how
it's broken down, but most likely each while loop is a tx.
I set it to log all statements (i.e., = 0.). that doubled the load time from
~15 to ~30 hours. I could, of course, be more granular if it would be helpful.
Dave
--
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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 12:23:51PM -0600, Scott Marlowe wrote: - On Tue, Apr 20, 2010 at 12:20 PM, David Kerr d...@mr-paradox.net wrote: - On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote: - - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr d...@mr-paradox.net wrote: - - - - You can absolutely use copy if you like but you need to use a non-standard - - jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the - - past and it worked. - - - - Is the whole thing going in in one transaction? I'm reasonably sure - - statistics aren't kept for uncommited transactions. - - - - For inserts the prepared statements can only help. For selects they can - - hurt because eventually the JDBC driver will turn them into back end - - prepared statements that are only planned once. The price here is that that - - plan may not be the best plan for the data that you throw at it. - - - - What was log_min_duration_statement logging that it killed performance? - - - - --Nik - - Good to know about the jdbc-copy. but this is a huge project and the load is - just one very very tiny component, I don't think we could introduce anything - new to assist that. - - It's not all in one tx. I don't have visibility to the code to determine how - it's broken down, but most likely each while loop is a tx. - - I set it to log all statements (i.e., = 0.). that doubled the load time from - ~15 to ~30 hours. I could, of course, be more granular if it would be helpful. - - So are you logging to the same drive that has pg_xlog and your - data/base directory on this machine? - the db, xlog and logs are all on separate areas of the SAN. separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect contention there. I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng on my dev environments that mostly resoved the probelm for me. but these machines still have vanilla syslog. Dave -- 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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 12:30:14PM -0600, Scott Marlowe wrote: - On Tue, Apr 20, 2010 at 12:28 PM, David Kerr d...@mr-paradox.net wrote: - - I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng - on my dev environments that mostly resoved the probelm for me. but these machines - still have vanilla syslog. - - Yea, I almost always log directly via stdout on production machines - because of that. - Ah well good to know i'm not the only one =) I'll get the query info. I've got a twin system that I can use and abuse. Dave -- 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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 01:17:02PM -0500, Kevin Grittner wrote:
- David Kerr d...@mr-paradox.net wrote:
-
- Incidentally the code is written to work like this :
-
- while (read X lines in file){
- Process those lines.
- write lines to DB.
- }
-
- Unless you're selecting from multiple database tables in one query,
- effective_cache_size shouldn't make any difference. There's
- probably some other reason for the difference.
-
- A couple wild shots in the dark:
-
- Any chance the source files were cached the second time, but not the
- first?
-
- Do you have a large checkpoint_segments setting, and did the second
- run without a new initdb?
-
- -Kevin
no i don't think the files would be cached the 2nd time. I ran it multiple times
and got the same performance each time. It wasn't until i changed the parameter
that performance got better.
I've got checkpoint_segments = 300
Dave
--
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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote: - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr d...@mr-paradox.net wrote: - that thought occured to me while I was testing this. I ran a vacuumdb -z - on my database during the load and it didn't impact performance at all. - - The window to run ANALYZE usefully is pretty short. If you run it - before the load is complete, your stats will be wrong. If you run it - after the select statements that hit the table are planned, the - updated stats won't arrive in time to do any good. right, but i'm loading 20 million records in 1000 record increments. so the analyze should affect all subsequent increments, no? - I did turn on log_min_duration_statement but that caused performance to be unbearable, - but i could turn it on again if it would help. - - I think you need to find a way to identify exactly which query is - running slowly. You could sit there and run select * from - pg_stat_activity, or turn on log_min_duration_statement, or have your - application print out timestamps at key points, or some other - method... I'm on it. Dave -- 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] Very high effective_cache_size == worse performance?
On Tue, Apr 20, 2010 at 04:26:52PM -0400, Greg Smith wrote: - David Kerr wrote: - the db, xlog and logs are all on separate areas of the SAN. - separate I/O controllers, etc on the SAN. it's setup well, I wouldn't - expect - contention there. - - - Just because you don't expect it doesn't mean it's not there. - Particularly something as complicated as a SAN setup, presuming anything - without actually benchmarking it is a recipe for fuzzy diagnostics when - problems pop up. If you took anyone's word that your SAN has good - performance without confirming it yourself, that's a path that's lead - many to trouble. that's actually what I'm doing, performance testing this environment. everything's on the table for me at this point. - Anyway, as Robert already stated, effective_cache_size only impacts how - some very specific types of queries are executed; that's it. If there's - some sort of query behavior involved in your load, maybe that has - something to do with your slowdown, but it doesn't explain general slow - performance. Other possibilities include that something else changed - when you reloaded the server as part of that, or it's a complete - coincidence--perhaps autoanalyze happened to finish at around the same - time and it lead to new plans. Ok that's good to know. I didn't think it would have any impact, and was surprised when it appeared to. I just finished running the test on another machine and wasn't able to reproduce the problem, so that's good news in some ways. But now i'm back to the drawing board. I don't think it's anything in the Db that's causing it. ( drop and re-create the db between tests) I actually suspect a hardware issue somewhere. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Optimizer + bind variables
Does/is it possible for the PG optimizer come up with differnet plans when you're using bind variables vs when you send static values? like if my query was select * from users (add a bunch of complex joins) where username = 'dave' vs select * from users (add a bunch of complex joins) where username = '?' In oracle they are frequently different. if it's possible for the plan to be different how can i generate an xplan for the bind version? Thanks! Dave -- 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] Optimizer + bind variables
On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: - David Kerr wrote: - Does/is it possible for the PG optimizer come up with differnet plans when - you're using bind variables vs when you send static values? - - Yes, if the bind variable form causes your DB access driver to use a - server-side prepared statement. Pg can't use its statistics to improve - its query planning if it doesn't have a value for a parameter when it's - building the query plan. hmm, that's a little unclear to me. let's assume that the application is using prepare: Assuming the database hasn't changed, would: PREPARE bla1 as SELECT * from users where username = '$1'; explain execute bla1 give the same output as explain select * from users where username = 'dave'; ? - Whether a server-side prepared statement is used or not depends on how - you're connecting to the database - ie your DB access driver and - version. If you're using JDBC, I *think* the JDBC driver does parameter - placement client-side unless you're using a JDBC prepared statement and - the JDBC prepared statement is re-used several times, at which point it - sets up a server-side prepared statement. AFAIK otherwise it uses - client-side (or Pg protocol level) parameter placement. that's interesting, i'll need to find out which mine are using, probably a mix of both. - if it's possible for the plan to be different how can i generate an - xplan for the bind version? - - xplan = explain? If so: yeah, sorry. - Use PREPARE to prepare a statement with the params, then use: - - EXPLAIN EXECUTE prepared_statement_name(params); - - eg: - - x= PREPARE blah AS SELECT * FROM generate_series(1,100); - PREPARE - x= EXPLAIN EXECUTE blah; -QUERY PLAN - - Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=4) - (1 row) great thanks! Dave -- 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] Optimizer + bind variables
On Wed, Nov 04, 2009 at 11:02:22AM +1100, Chris wrote: - David Kerr wrote: - On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: - - David Kerr wrote: - No. - - This is explained in the notes here: - - http://www.postgresql.org/docs/current/static/sql-prepare.html sigh and i've read that before too. On the upside, then it behaves like I would expect it to, which is good. Thanks Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Under the hood of views
developer came by and asked me an interesting question. If he has a view with 20 columns in it, and he selects a specific column from the view in his query. Does the engine when accessing the view return all columns? or is it smart enough to know to just retrive the one? example: create view test as select a,b,c,d,e,f,g from testtable; select a from test; (does the engine retrieve b-g?) Thanks Dave -- 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] Under the hood of views
On Thu, Aug 13, 2009 at 05:28:01PM +0100, Richard Huxton wrote: - David Kerr wrote: - - create view test as - select a,b,c,d,e,f,g from testtable; - - select a from test; - - (does the engine retrieve b-g?) - - Shouldn't - the query just gets rewritten macro-style. I don't think it - eliminates joins if you don't need any columns, but that's not possible - without a bit of analysis. Perfect, thanks! Dave -- 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] Looking for installations with a large number of concurrent users
On Wed, Jun 10, 2009 at 11:40:21AM -0500, Kevin Grittner wrote: - We're on SLES 10 SP 2 and are handling a web site which gets two to - three million hits per day, running tens of millions of queries, while - functioning as a replication target receiving about one million - database transactions to modify data, averaging about 10 DML - statements each, on one box with the following hardware: [snip] Thanks! that's all great info, puts me much more at ease. - The connection pool for the web application is maxed at 25 active - connections; the replication at 6. We were using higher values, but - found that shrinking the connection pool down to this improved - throughput (in a saturation test) and response time (in production). - If the server were dedicated to PostgreSQL only, more connections - would probably be optimal. Ok, so it looks ilike I need to focus some testing there to find the optimal for my setup. I was thinking 25 for starters, but I think i'll bump that to 50. - I worry a little when you mention J2EE. EJBs were notoriously poor - performers, although I hear there have been improvements. Just be - careful to pinpoint your bottlenecks so you can address the real - problem if there is a performance issue. Sounds good, thanks for the heads up. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Looking for installations with a large number of concurrent users
Hello all, We're implementing a fairly large J2EE application, I'm estimating around 450,000 concurrent users at high peak. Performing reads and writes and we have a very high performance requirement. I'll be using connection pooling (probably the pooling delivered with Geronimo). I'd like to get an idea of how big can I go. without running into context switch storms, or hiting some other wall. The design actually calls for multiple databases but I'm trying to get a good idea of the max size / database. (I.e., don't want 50+ database servers if i can avoid it) We'll be on 8.4 (or 8.5) by the time we go live and SLES linux (for now). I don't have hardware yet, basically, we'll purchase enough hardware to handle whatever we need... Is anyone willing to share their max connections and maybe some rough hardware sizing (cpu/mem?). Thanks Dave -- 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] Question on pgbench output
Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, 2009-04-03 at 16:34 -0700, David Kerr wrote: 400 concurrent users doesn't mean that they're pulling 1.5 megs / second every second. There's a world of difference between 400 connected and 400 concurrent users. You've been testing 400 concurrent users, yet without measuring data transfer. The think time will bring the number of users right down again, but you really need to include the much higher than normal data transfer into your measurements and pgbench won't help there. Actually pgbench can simulate think time perfectly well: use its \sleep command in your script. I think you can even set it up to randomize the sleep time. I agree that it seems David has been measuring a case far beyond what his real problem is. regards, tom lane Fortunately the network throughput issue is not mine to solve. Would it be fair to say that with the pgbench output i've given so far that if all my users clicked go at the same time (i.e., worst case scenario), i could expect (from the database) about 8 second response time? Thanks Dave Kerr -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Question on pgbench output
Hello! Sorry for the wall of text here. I'm working on a performance POC and I'm using pgbench and could use some advice. Mostly I want to ensure that my test is valid and that I'm using pgbench properly. The story behind the POC is that my developers want to pull web items from the database (not too strange) however our environment is fairly unique in that the item size is between 50k and 1.5megs and i need to retrive the data in less than a second. Oh, and we're talking about a minimum of 400 concurrent users. My intuition tells me that this is nuts, for a number of reasons, but to convince everyone I need to get some performance numbers. (So right now i'm just focused on how much time it takes to pull this record from the DB, not memory usage, http caching, contention, etc.) What i did was create a table temp with id(pk) and content(bytea) [ going to compare bytea vs large objects in this POC as well even though i know that large objects are better for this ] I loaded the table with aproximately 50k items that were 1.2Megs in size. Here is my transaction file: \setrandom iid 1 5 BEGIN; SELECT content FROM test WHERE item_id = :iid; END; and then i executed: pgbench -c 400 -t 50 -f trans.sql -l trying to simulate 400 concurrent users performing 50 operations each which is consistant with my needs. The results actually have surprised me, the database isn't really tuned and i'm not working on great hardware. But still I'm getting: caling factor: 1 number of clients: 400 number of transactions per client: 50 number of transactions actually processed: 2/2 tps = 51.086001 (including connections establishing) tps = 51.395364 (excluding connections establishing) I'm not really sure how to evaulate the tps, I've read in this forum that some folks are getting 2k tps so this wouldn't appear to be good to me. However: When i look at the logfile generated: head -5 pgbench_log.7205 0 0 15127082 0 1238784175 660088 1 0 15138079 0 1238784175 671205 2 0 15139007 0 1238784175 672180 3 0 15141097 0 1238784175 674357 4 0 15142000 0 1238784175 675345 (I wrote a script to average the total transaction time for every record in the file) avg_times.ksh pgbench_log.7205 Avg tx time seconds: 7 That's not too bad, it seems like with real hardware + actually tuning the DB i might be able to meet my requirement. So the question is - Can anyone see a flaw in my test so far? (considering that i'm just focused on the performance of pulling the 1.2M record from the table) and if so any suggestions to further nail it down? Thanks Dave Kerr -- 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] Question on pgbench output
On Fri, Apr 03, 2009 at 04:43:29PM -0400, Tom Lane wrote: - I'm not really sure how to evaulate the tps, I've read in this forum that - some folks are getting 2k tps so this wouldn't appear to be good to me. - - Well, you're running a custom transaction definition so comparing your - number to anyone else's is 100% meaningless. All you know here is that - your individual transactions are more expensive than the default pgbench - transaction (which we could'a told you without testing...) That makes sense. I guess I included it incase there was a community defined sense of what a good TPS for a highly responsive web-app. (like if you're getting 1000tps on your web app then your users are happy) But from the sounds of it, yeah, that would probably be difficult to really measure. - (I wrote a script to average the total transaction time for every record - in the file) - avg_times.ksh pgbench_log.7205 - Avg tx time seconds: 7 - - That squares with your previous results: if you're completing 50 - transactions per sec then it takes about 8 seconds to do 400 of 'em. - So any one of the clients ought to see about 8 second response time. - I think that your test is probably valid. Ok, great. thanks! - That's not too bad, it seems like with real hardware + actually tuning - the DB i might be able to meet my requirement. - - How much more real is the target hardware than what you have? - You appear to need about a factor of 10 better disk throughput than - you have, and that's not going to be too cheap. I suspect that the - thing is going to be seek-limited, and seek rate is definitely the - most expensive number to increase. The hardware i'm using is a 5 or 6 year old POS IBM Blade. we haven't specced the new hardware yet but I would say that it will be sigificantly better. - If the items you're pulling are static files, you should consider - storing them as plain files and just using the DB as an index. - Megabyte-sized fields aren't the cheapest things to push around. I agree 100% and of course the memory allocation, etc from being able to cache the items in httpd vs in the DB is a major consideration. Thanks again. Dave Kerr -- 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] Question on pgbench output
On Fri, Apr 03, 2009 at 06:52:26PM -0400, Tom Lane wrote: - Greg Smith gsm...@gregsmith.com writes: - pgbench is extremely bad at simulating large numbers of clients. The - pgbench client operates as a single thread that handles both parsing the - input files, sending things to clients, and processing their responses. - It's very easy to end up in a situation where that bottlenecks at the - pgbench client long before getting to 400 concurrent connections. - - Yeah, good point. hmmm ok, I didn't realize that pgbouncer wasn't threaded. I've got a Plan B that doesn't use pgbouncer that i'll try. - That said, if you're in the hundreds of transactions per second range that - probably isn't biting you yet. I've seen it more once you get around - 5000+ things per second going on. - - However, I don't think anyone else has been pgbench'ing transactions - where client-side libpq has to absorb (and then discard) a megabyte of - data per xact. I wouldn't be surprised that that eats enough CPU to - make it an issue. David, did you pay any attention to how busy the - pgbench process was? I can run it again and have a look, no problem. - Another thing that strikes me as a bit questionable is that your stated - requirements involve being able to pump 400MB/sec from the database - server to your various client machines (presumably those 400 people - aren't running their client apps directly on the DB server). What's the - network fabric going to be, again? Gigabit Ethernet won't cut it... Yes, sorry I'm not trying to be confusing but i didn't want to bog everyone down with a ton of details. 400 concurrent users doesn't mean that they're pulling 1.5 megs / second every second. Just that they could potentially pull 1.5 megs at any one second. most likely there is a 6 (minimum) to 45 second (average) gap between each individual user's pull. My plan B above emulates that, but i was using pgbouncer to try to emulate worst case scenario. - The point I was trying to make is that it's the disk subsystem, not - the CPU, that is going to make or break you. Makes sense, I definitely want to avoid I/Os. On Fri, Apr 03, 2009 at 05:51:50PM -0400, Greg Smith wrote: - Wrapping a SELECT in a BEGIN/END block is unnecessary, and it will - significantly slow down things for two reason: the transactions overhead - and the time pgbench is spending parsing/submitting those additional - lines. Your script should be two lines long, the \setrandom one and the - SELECT. - Oh perfect, I can try that too. thanks - The thing that's really missing from your comments so far is the cold - vs. hot cache issue: at the point when you're running pgbench, is a lot I'm testing with a cold cache because most likely the way the items are spead out, of those 400 users only a few at a time might access similar items. - Wait until Monday, I'm announcing some pgbench tools at PG East this - weekend that will take care of all this as well as things like - graphing. It pushes all the info pgbench returns, including the latency - information, into a database and generates a big stack of derived reports. - I'd rather see you help improve that than reinvent this particular wheel. Ah very cool, wish i could go (but i'm on the west coast). Thanks again guys. Dave Kerr -- 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] Question on pgbench output
Gah - sorry, setting up pgbouncer for my Plan B. I meant -pgbench- Dave Kerr On Fri, Apr 03, 2009 at 04:34:58PM -0700, David Kerr wrote: - On Fri, Apr 03, 2009 at 06:52:26PM -0400, Tom Lane wrote: - - Greg Smith gsm...@gregsmith.com writes: - - pgbench is extremely bad at simulating large numbers of clients. The - - pgbench client operates as a single thread that handles both parsing the - - input files, sending things to clients, and processing their responses. - - It's very easy to end up in a situation where that bottlenecks at the - - pgbench client long before getting to 400 concurrent connections. - - - - Yeah, good point. - - hmmm ok, I didn't realize that pgbouncer wasn't threaded. I've got a Plan B - that doesn't use pgbouncer that i'll try. - - - That said, if you're in the hundreds of transactions per second range that - - probably isn't biting you yet. I've seen it more once you get around - - 5000+ things per second going on. - - - - However, I don't think anyone else has been pgbench'ing transactions - - where client-side libpq has to absorb (and then discard) a megabyte of - - data per xact. I wouldn't be surprised that that eats enough CPU to - - make it an issue. David, did you pay any attention to how busy the - - pgbench process was? - I can run it again and have a look, no problem. - - - Another thing that strikes me as a bit questionable is that your stated - - requirements involve being able to pump 400MB/sec from the database - - server to your various client machines (presumably those 400 people - - aren't running their client apps directly on the DB server). What's the - - network fabric going to be, again? Gigabit Ethernet won't cut it... - - Yes, sorry I'm not trying to be confusing but i didn't want to bog - everyone down with a ton of details. - - 400 concurrent users doesn't mean that they're pulling 1.5 megs / second - every second. Just that they could potentially pull 1.5 megs at any one - second. most likely there is a 6 (minimum) to 45 second (average) gap - between each individual user's pull. My plan B above emulates that, but - i was using pgbouncer to try to emulate worst case scenario. - - - The point I was trying to make is that it's the disk subsystem, not - - the CPU, that is going to make or break you. - - Makes sense, I definitely want to avoid I/Os. - - - On Fri, Apr 03, 2009 at 05:51:50PM -0400, Greg Smith wrote: - - Wrapping a SELECT in a BEGIN/END block is unnecessary, and it will - - significantly slow down things for two reason: the transactions - overhead - - and the time pgbench is spending parsing/submitting those additional - - lines. Your script should be two lines long, the \setrandom one and - the - - SELECT. - - - - Oh perfect, I can try that too. thanks - - - The thing that's really missing from your comments so far is the cold - - vs. hot cache issue: at the point when you're running pgbench, is a lot - - I'm testing with a cold cache because most likely the way the items are - spead out, of those 400 users only a few at a time might access similar - items. - - - Wait until Monday, I'm announcing some pgbench tools at PG East this - - weekend that will take care of all this as well as things like - - graphing. It pushes all the info pgbench returns, including the latency - - information, into a database and generates a big stack of derived reports. - - I'd rather see you help improve that than reinvent this particular wheel. - - Ah very cool, wish i could go (but i'm on the west coast). - - - Thanks again guys. - - Dave Kerr - - - -- - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) - To make changes to your subscription: - http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
