[PERFORM] Strange performance degradation
Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? I can post my postgresql.conf if needed. Thank you for your help, -- Lorenzo -- 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] Strange performance degradation
In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] View based upon function won't use index on joins
How about CREATE OR REPLACE VIEW value_codes_view AS select * from ( SELECT value_codes.id_nbr, value_codes.id_qfr, (ARRAY[val_1_cd_1, ... , val_2_cd_12])[i] as value_code, (ARRAY[val_1_amt_1, ... , val_2_amt_12])[i] as value_amount, FROM value_codes, generate_series(1,24) i) a where value_code is not null and value_code != ''; ?
Re: [PERFORM] Strange performance degradation
A. Kretschmer wrote: In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. Why running vacuum by hand is a bad idea? vacuum doesn't solve anyway, it seems only a plain restart stops the performance degradation. So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. below is the output of vacuum analyze verbose (NOTE: I've already run vacuum this morning, this is a second run) DETAIL: A total of 58224 page slots are in use (including overhead). 58224 page slots are required to track all free space. Current limits are: 200 page slots, 1000 relations, using 11784 kB. -- 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] SSD + RAID
Am 13.11.2009 um 14:57 schrieb Laszlo Nagy: I was thinking about ARECA 1320 with 2GB memory + BBU. Unfortunately, I cannot find information about using ARECA cards with SSD drives. They told me: currently not supported, but they have positive customer reports. No date yet for implementation of the TRIM command in firmware. ... My other option is to buy two SLC SSD drives and use RAID1. It would cost about the same, but has less redundancy and less capacity. Which is the faster? 8-10 MLC disks in RAID 6 with a good caching controller, or two SLC disks in RAID1? I just went the MLC path with X25-Ms mainly to save energy. The fresh assembled box has one SSD for WAL and one RAID 0 with for SSDs as table space. Everything runs smoothly on a areca 1222 with BBU, which turned all write caches off. OS is FreeBSD 8.0. I aligned all partitions on 1 MB boundaries. Next week I will install 8.4.1 and run pgbench for pull-the-plug- testing. I would like to get some advice from the list for testing the SSDs! Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- 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] Strange performance degradation
Lorenzo Allegrucci lorenzo.allegrucci 'at' forinicom.it writes: A. Kretschmer wrote: In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. Why running vacuum by hand is a bad idea? It's rather turning autovacuum off which is a bad idea. vacuum doesn't solve anyway, it seems only a plain restart stops the performance degradation. Notice: normally, restarting doesn't help for vacuum-related problems. Your degradation might come from a big request being intensive on PG's and OS's caches, resulting in data useful to other requests getting farther (but it should get back to normal if the big request is not performed again). And btw, 25% is far from 100% so response time should be the same if there are no other factors; you should rather have a look at IOs (top, vmstat, iostat) during problematic time. How do you measure your degradation, btw? So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. below is the output of vacuum analyze verbose (NOTE: I've already run vacuum this morning, this is a second run) DETAIL: A total of 58224 page slots are in use (including overhead). 58224 page slots are required to track all free space. Current limits are: 200 page slots, 1000 relations, using 11784 kB. Which means your FSM settings look fine; but doesn't mean your database is not bloated (and with many UPDATEs and no correct vacuuming, it should be bloated). One way to know is to restore a recent backup, issue VACUUM VERBOSE on a table known to be large and regularly UPDATE's/DELETE'd on both databases (in production, and on the restore) and compare the reported number of pages needed. The difference is the potential benefit of running VACUUM FULL (or CLUSTER) in production (once your DB is bloated, a normal VACUUM doesn't remove the bloat). db_production=# VACUUM VERBOSE table; [...] INFO: table: found 408 removable, 64994 nonremovable row versions in 4395 pages db_restored=# VACUUM VERBOSE table; [...] INFO: table: found 0 removable, 64977 nonremovable row versions in 628 pages In that 628/4395 example, we have 85% bloat in production. -- Guillaume Cottenceau -- 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] SSD + RAID
On Thu, 19 Nov 2009, Greg Smith wrote: This is why turning the cache off can tank performance so badly--you're going to be writing a whole 128K block no matter what if it's force to disk without caching, even if it's just to write a 8K page to it. Theoretically, this does not need to be the case. Now, I don't know what the Intel drives actually do, but remember that for flash, it is the *erase* cycle that has to be done in large blocks. Writing itself can be done in small blocks, to previously erased sites. The technology for combining small writes into sequential writes has been around for 17 years or so in http://portal.acm.org/citation.cfm?id=146943dl= so there really isn't any excuse for modern flash drives not giving really fast small writes. Matthew -- for a in past present future; do for b in clients employers associates relatives neighbours pets; do echo The opinions here in no way reflect the opinions of my $a $b. done; done -- 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] Postgres query completion status?
Richard Neill wrote: As far as I know, the only tools that exist are pg_stat_activity, top, and iotop Have I missed one? The ui for pgTop might be easier for what you're trying to do: http://pgfoundry.org/projects/pgtop/ -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] Postgres query completion status?
2009/11/20 Richard Neill rn...@cam.ac.uk Greg Williamson wrote: Richard -- You might post the results of EXPLAIN ANALYZE your SQL here; ... be sure to run it in a transaction if you want to be able roll it back. Perhaps try EXPLAIN your SQL; first as it is faster, but EXPLAIN ANALYZE shows what the planner is doing. Here's something very very odd. Explain Analyze has now run, in about 4 minutes. (result below) However, I'd be willing to swear that the last time I ran explain on this query about half an hour ago, the final 2 lines were sequential scans. So, I've just terminated the real job (which uses this select for an update) after 77 minutes of fruitless cpu-hogging, and re-started it ...This time, the same job ran through in 24 minutes. [This is running exactly the same transaction on exactly the same data!] Richard It looks like your statistics are way out of sync with the real data. Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual time=248577.879..253168.466 rows=347308 loops=1) This shows that it thinks there will be 8,686 rows, but actually traverses 347,308. Have you manually run a VACUUM on these tables? Preferrably a full one if you can. I notice that you appear ot have multiple sorts going on. Are all of those actually necessary for your output? Also consider using partial or multicolumn indexes where useful. And which version of PostgreSQL are you using? Thom
Re: [PERFORM] Postgres query completion status?
Sorry for top-posting -- challenged mail client. Thom's suggestion that the estimates are off seems like a useful line of inquiry, but ANALYZE is what builds statistics. If it is not run often enough the planner will base its idea of what a good plan is on bad data. So ANALYZE table name; is your friend. You may need to change the statistics for the tables in question if there are odd distributions of data -- as Thom asked -- which version of PostgreSQL ? Stay away from VACUUM FULL ! It will block other activity and will be horribly slow on large tables. It will get rid of bloat but there may be better ways of doing that depending on what version you are using and what you maintenance window looks like. HTH, Greg W. From: Thom Brown thombr...@gmail.com To: Richard Neill rn...@cam.ac.uk Cc: Greg Williamson gwilliamso...@yahoo.com; pgsql-performance@postgresql.org Sent: Fri, November 20, 2009 4:13:03 AM Subject: Re: [PERFORM] Postgres query completion status? 2009/11/20 Richard Neill rn...@cam.ac.uk Greg Williamson wrote: Richard -- You might post the results of EXPLAIN ANALYZE your SQL here; ... be sure to run it in a transaction if you want to be able roll it back. Perhaps try EXPLAIN your SQL; first as it is faster, but EXPLAIN ANALYZE shows what the planner is doing. Here's something very very odd. Explain Analyze has now run, in about 4 minutes. (result below) However, I'd be willing to swear that the last time I ran explain on this query about half an hour ago, the final 2 lines were sequential scans. So, I've just terminated the real job (which uses this select for an update) after 77 minutes of fruitless cpu-hogging, and re-started it ...This time, the same job ran through in 24 minutes. [This is running exactly the same transaction on exactly the same data!] Richard It looks like your statistics are way out of sync with the real data. Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual time=248577.879..253168.466 rows=347308 loops=1) This shows that it thinks there will be 8,686 rows, but actually traverses 347,308. Have you manually run a VACUUM on these tables? Preferrably a full one if you can. I notice that you appear ot have multiple sorts going on. Are all of those actually necessary for your output? Also consider using partial or multicolumn indexes where useful. And which version of PostgreSQL are you using? Thom
Re: [PERFORM] [GENERAL] Strange performance degradation
Lorenzo Allegrucci lorenzo.allegru...@forinicom.it writes: So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? Are you killing off any long-running transactions when you restart? 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] View based upon function won't use index on joins
This seems to result in the same problem; should I attempt to pull for a specific id_nbr/id_qfr, postgres uses the index without a problem. If I try to join the two tables/views however, it insists on doing a sequential scan (actually two in this case) and will not use the index. Any other ideas/explanations? That being said, I probably need to look into arrays more. I haven't used them at all in my relatively brief experience with postgres. More research! 2009/11/20 Віталій Тимчишин tiv...@gmail.com How about CREATE OR REPLACE VIEW value_codes_view AS select * from ( SELECT value_codes.id_nbr, value_codes.id_qfr, (ARRAY[val_1_cd_1, ... , val_2_cd_12])[i] as value_code, (ARRAY[val_1_amt_1, ... , val_2_amt_12])[i] as value_amount, FROM value_codes, generate_series(1,24) i) a where value_code is not null and value_code != ''; ?
Re: [PERFORM] Strange performance degradation
On Fri, 20 Nov 2009, Lorenzo Allegrucci wrote: performance is degrading... In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. You don't really give enough information to determine what is going on here. This could be one of two situations: 1. You have a constant incoming stream of short-lived requests at a constant rate, and Postgres is taking eight times as much CPU to service it as normal. You're looking at CPU usage in aggregate over long periods of time. In this case, we should look at long running transactions and other slowdown possibilities. 2. You are running a complex query, and you look at top and see that Postgres uses eight times as much CPU as when it has been freshly started. In this case, the performance degradation could actually be that the data is more in cache, and postgres is able to process it eight times *faster*. Restarting Postgres kills the cache and puts you back at square one. Which of these is it? Matthew -- Reality is that which, when you stop believing in it, doesn't go away. -- Philip K. Dick -- 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] Postgres query completion status?
Richard Neill wrote: SELECT ( core.demand.qty - viwcs.wave_end_demand.qty_remaining ) FROM core.demand, viwcs.previous_wave LEFT OUTER JOIN viwcs.wave_end_demand USING ( wid ) WHERE core.demand.id = viwcs.wave_end_demand.demand_id; For comparison, how does this do?: SELECT (core.demand.qty - viwcs.wave_end_demand.qty_remaining) FROM core.demand, JOIN viwcs.previous_wave ON (core.demand.id = viwcs.wave_end_demand.demand_id) LEFT OUTER JOIN viwcs.wave_end_demand USING (wid); -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] View based upon function won't use index on joins
20 листопада 2009 р. 17:01 Jonathan Foy the...@gmail.com написав: This seems to result in the same problem; should I attempt to pull for a specific id_nbr/id_qfr, postgres uses the index without a problem. If I try to join the two tables/views however, it insists on doing a sequential scan (actually two in this case) and will not use the index. Any other ideas/explanations? Have you tried to do same (join) when not using the viewes or converting columns into records? May be the problem is not in conversion, but in something simplier, like statistics or index bloat? Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Partitions and max_locks_per_transaction
It was Thursday 19 November 2009 11:08:10 pm that the wise Tom Lane thus wrote: hashincl...@gmail.com writes: To make make the retrieval faster, I'm using a partitioning scheme as follows: stats_300: data gathered at 5 mins, child tables named stats_300_t1_t2 (where t2 - t1 = 2 hrs), i.e. 12 tables in one day stats_3600: data gathered / calculated over 1 hour, child tables similar to the above - stats_3600_t1_t2, where (t2 - t1) is 2 days (i.e. 15 tables a month) stats_86400: data gathered / calculated over 1 day, stored as stats_86400_t1_t2 where (t2 - t1) is 30 days (i.e. 12 tables a year). So you've got, um, something less than a hundred rows in any one child table? This is carrying partitioning to an insane degree, and your performance is NOT going to be improved by it. Sorry I forgot to mention - in the normal case, each of those tables will have a few hundred thousand records, and in the worst case (the tables store info on up to 2000 endpoints) it can be around 5 million. Also, the partitioning is not final yet (we might move it to 6 hours / 12 hours per partition) - which is why I need to run the load test :) I'd suggest partitioning on boundaries that will give you order of a million rows per child. That could be argued an order of magnitude or two either way, but what you've got is well outside the useful range. I'm running into the error ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. No surprise given the number of tables and indexes you're forcing the system to deal with ... How many locks per table/index does PG require? Even with my current state (50 tables, 250 (tables + indexes)) is it reasonable to expect 2000 locks to run out? Thanks, Hrishi -- 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] SSD + RAID
On Wed, Nov 18, 2009 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Carey sc...@richrelevance.com writes: For your database DATA disks, leaving the write cache on is 100% acceptable, even with power loss, and without a RAID controller. And even in high write environments. Really? How hard have you tested that configuration? That is what the XLOG is for, isn't it? Once we have fsync'd a data change, we discard the relevant XLOG entries. If the disk hasn't actually put the data on stable storage before it claims the fsync is done, you're screwed. XLOG only exists to centralize the writes that have to happen before a transaction can be reported committed (in particular, to avoid a lot of random-access writes at commit). It doesn't make any fundamental change in the rules of the game: a disk that lies about write complete will still burn you. In a zero-seek-cost environment I suspect that XLOG wouldn't actually be all that useful. You would still need it to guard against partial page writes, unless we have some guarantee that those can't happen. And once your transaction has scattered its transaction id into various xmin and xmax over many tables, you need an atomic, durable repository to decide if that id has or has not committed. Maybe clog fsynced on commit would serve this purpose? Jeff -- 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] View based upon function won't use index on joins
I don't think so. I actually dumped the tables involved into stripped down versions of themselves in a new database for testing, so the data involved should be completely fresh. I ran a vacuum analyze after the dump of course. Just for paranoia's sake though I did do the following: explain analyze select id_nbr, id_qfr, val_1_cd_1, val_1_cd_2, ... val_2_amt_12 from value_codes where main_table.create_dt = '20091001' and main_table.id_nbr = value_codes.id_nbr and main_table.id_qfr = value_codes.id_qfr with the following results Nested Loop (cost=0.00..1592.17 rows=132 width=150) (actual time=0.093..1.075 rows=4 loops=1) - Index Scan using main_table_create_dt_index on main_table (cost=0.00..21.47 rows=194 width=6) (actual time=0.035..0.249 rows=53 loops=1) Index Cond: (create_dt = '20091001'::bpchar) - Index Scan using value_codes_pkey on value_codes (cost=0.00..8.08 rows=1 width=150) (actual time=0.007..0.007 rows=0 loops=53) Index Cond: ((value_codes.id_nbr = main_table.id_nbr) AND (value_codes.id_qfr = main_table.id_qfr)) Total runtime: 1.279 ms I'm stumped. I'm starting to think that I'm trying to get postgres to do something that it just doesn't do. Shy of just throwing a trigger in the table to actually populate a second table with the same data solely for reporting purposes, which I hate to do for obvious reasons, I don't know what else to do. And this is only one example of this situation in the databases that I'm dealing with, I was hoping to come up with a more generic solution that I could apply in any number of locations. I do very much appreciate the responses...I've been gradually getting deeper and deeper into postgres, and am still very much learning as I go. All advice is very helpful. Thanks.. 2009/11/20 Віталій Тимчишин tiv...@gmail.com 20 листопада 2009 р. 17:01 Jonathan Foy the...@gmail.com написав: This seems to result in the same problem; should I attempt to pull for a specific id_nbr/id_qfr, postgres uses the index without a problem. If I try to join the two tables/views however, it insists on doing a sequential scan (actually two in this case) and will not use the index. Any other ideas/explanations? Have you tried to do same (join) when not using the viewes or converting columns into records? May be the problem is not in conversion, but in something simplier, like statistics or index bloat? Best regards, Vitalii Tymchyshyn
Re: [PERFORM] SSD + RAID
Axel Rau wrote:
Am 13.11.2009 um 14:57 schrieb Laszlo Nagy:
I was thinking about ARECA 1320 with 2GB memory + BBU. Unfortunately,
I cannot find information about using ARECA cards with SSD drives.
They told me: currently not supported, but they have positive customer
reports. No date yet for implementation of the TRIM command in firmware.
...
My other option is to buy two SLC SSD drives and use RAID1. It would
cost about the same, but has less redundancy and less capacity. Which
is the faster? 8-10 MLC disks in RAID 6 with a good caching
controller, or two SLC disks in RAID1?
Despite my other problems, I've found that the Intel X25-Es work
remarkably well. The key issue for short,fast transactions seems to be
how fast an fdatasync() call can run, forcing the commit to disk, and
allowing the transaction to return to userspace.
With all the caches off, the intel X25-E beat a standard disk by a
factor of about 10.
Attached is a short C program which may be of use.
For what it's worth, we have actually got a pretty decent (and
redundant) setup using a RAIS array of RAID1.
[primary server]
SSD }
} RAID1 ---} DRBD --- /var/lib/postgresql
SSD }}
}
}
}
}
[secondary server] }
}
SSD }}
} RAID1 gigE}
SSD }
The servers connect back-to-back with a dedicated Gigabit ethernet
cable, and DRBD is running in protocol B.
We can pull the power out of 1 server, and be using the next within 30
seconds, and with no dataloss.
Richard
#include string.h
#include stdio.h
#include stdlib.h
#include unistd.h
#include errno.h
#include sys/types.h
#include sys/stat.h
#include fcntl.h
#define NUM_ITER 1024
int main ( int argc, char **argv ) {
const char data[] = Liberate;
size_t data_len = strlen ( data );
const char *filename;
int fd;
unsigned int i;
if ( argc != 2 ) {
fprintf ( stderr, Syntax: %s output_file\n, argv[0] );
exit ( 1 );
}
filename = argv[1];
fd = open ( filename, ( O_WRONLY | O_CREAT | O_EXCL ), 0666 );
if ( fd 0 ) {
fprintf ( stderr, Could not create \%s\: %s\n,
filename, strerror ( errno ) );
exit ( 1 );
}
for ( i = 0 ; i NUM_ITER ; i++ ) {
if ( write ( fd, data, data_len ) != data_len ) {
fprintf ( stderr, Could not write: %s\n,
strerror ( errno ) );
exit ( 1 );
}
if ( fdatasync ( fd ) != 0 ) {
fprintf ( stderr, Could not fdatasync: %s\n,
strerror ( errno ) );
exit ( 1 );
}
}
return 0;
}
--
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] Postgres query completion status?
Kevin Grittner wrote: Richard Neill wrote: SELECT ( core.demand.qty - viwcs.wave_end_demand.qty_remaining ) FROM core.demand, viwcs.previous_wave LEFT OUTER JOIN viwcs.wave_end_demand USING ( wid ) WHERE core.demand.id = viwcs.wave_end_demand.demand_id; For comparison, how does this do?: SELECT (core.demand.qty - viwcs.wave_end_demand.qty_remaining) FROM core.demand JOIN viwcs.previous_wave ON (core.demand.id = viwcs.wave_end_demand.demand_id) LEFT OUTER JOIN viwcs.wave_end_demand USING (wid); Thanks for your help, Unfortunately, it just complains: ERROR: missing FROM-clause entry for table wave_end_demand LINE 4: ON (core.demand.id = viwcs.wave_end_demand.demand_id) Incidentally, I don't think that this particular re-ordering will make much difference: viwcs.previous_wave is a table with a single row, and 3 columns in it. Here are the bits of schema, if they're helpful. View viwcs.wave_end_demand Column | Type | Modifiers ---+---+--- wid | character varying(10) | storeorderid | character varying(30) | genreorderid | character varying(30) | target_id | bigint| sid | character varying(30) | material_id | bigint| demand_id | bigint| eqa | integer | aqu | bigint| qty_remaining | bigint| View definition: SELECT wave_gol.wid, wave_gol.storeorderid, wave_gol.genreorderid, wave_genreorders_map.target_id, wave_gol.sid, product_info_sku_map.material_id, demand.id AS demand_id, wave_gol.eqa, COALESCE(du_report_sku_sum.aqu, 0::bigint) AS aqu, wave_gol.eqa - COALESCE(du_report_sku_sum.aqu, 0::bigint) AS qty_remaining FROM viwcs.wave_gol LEFT JOIN viwcs.wave_genreorders_map USING (wid, storeorderid, genreorderid) LEFT JOIN viwcs.product_info_sku_map USING (sid) LEFT JOIN core.demand USING (target_id, material_id) LEFT JOIN ( SELECT du_report_sku.wid, du_report_sku.storeorderid, du_report_sku.genreorderid, du_report_sku.sid, sum(du_report_sku.aqu) AS aqu FROM viwcs.du_report_sku GROUP BY du_report_sku.wid, du_report_sku.storeorderid, du_report_sku.genreorderid, du_report_sku.sid) du_report_sku_sum USING (wid, storeorderid, genreorderid, sid); View viwcs.previous_wave Column | Type | Modifiers +---+--- wid| character varying(10) | View definition: SELECT wave_rxw.wid FROM viwcs.wave_rxw WHERE wave_rxw.is_previous; Table core.demand Column| Type | Modifiers -+-+ id | bigint | not null default core.new_id() target_id | bigint | not null material_id | bigint | not null qty | integer | not null benefit | integer | not null default 0 Indexes: demand_pkey PRIMARY KEY, btree (id) demand_target_id_key UNIQUE, btree (target_id, material_id) demand_material_id btree (material_id) demand_target_id btree (target_id) Foreign-key constraints: demand_material_id_fkey FOREIGN KEY (material_id) REFERENCES core.__material_id(id) demand_target_id_fkey FOREIGN KEY (target_id) REFERENCES core.waypoint(id) Referenced by: TABLE core.inventory CONSTRAINT inventory_demand_id_fkey FOREIGN KEY (demand_id) REFERENCES core.demand(id) Thanks, Richard -- 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] Postgres query completion status?
Thom Brown wrote: It looks like your statistics are way out of sync with the real data. Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual time=248577.879..253168.466 rows=347308 loops=1) This shows that it thinks there will be 8,686 rows, but actually traverses 347,308. Yes, I see what you mean. Have you manually run a VACUUM on these tables? Preferrably a full one if you can. Every night, it runs Vacuum verbose analyze on the entire database. We also have the autovacuum daemon enabled (in the default config). About 2 weeks ago, I ran cluster followed by vacuum full - which seemed to help more than I'd expect. [As I understand it, the statistics shouldn't change very much from day to day, as long as the database workload remains roughly constant. What we're actually doing is running a warehouse sorting books - so from one day to the next the particular book changes, but the overall statistics basically don't.] I notice that you appear ot have multiple sorts going on. Are all of those actually necessary for your output? I think so. I didn't actually write all of this, so I can't be certain. Also consider using partial or multicolumn indexes where useful. Already done that. The query was originally pretty quick, with a few weeks worth of data, but not now. (after a few months). The times don't rise gradually, but have a very sudden knee. And which version of PostgreSQL are you using? 8.4.1, including this patch: http://archives.postgresql.org/pgsql-bugs/2009-10/msg00118.php Richard -- 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] Postgres query completion status?
2009/11/20 Richard Neill rn...@cam.ac.uk Thom Brown wrote: It looks like your statistics are way out of sync with the real data. Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual time=248577.879..253168.466 rows=347308 loops=1) This shows that it thinks there will be 8,686 rows, but actually traverses 347,308. Yes, I see what you mean. Have you manually run a VACUUM on these tables? Preferrably a full one if you can. Every night, it runs Vacuum verbose analyze on the entire database. We also have the autovacuum daemon enabled (in the default config). About 2 weeks ago, I ran cluster followed by vacuum full - which seemed to help more than I'd expect. [As I understand it, the statistics shouldn't change very much from day to day, as long as the database workload remains roughly constant. What we're actually doing is running a warehouse sorting books - so from one day to the next the particular book changes, but the overall statistics basically don't.] I notice that you appear ot have multiple sorts going on. Are all of those actually necessary for your output? I think so. I didn't actually write all of this, so I can't be certain. Also consider using partial or multicolumn indexes where useful. Already done that. The query was originally pretty quick, with a few weeks worth of data, but not now. (after a few months). The times don't rise gradually, but have a very sudden knee. And which version of PostgreSQL are you using? 8.4.1, including this patch: http://archives.postgresql.org/pgsql-bugs/2009-10/msg00118.php Richard Okay, have you tried monitoring the connections to your database? Try: select * from pg_stat_activity; And this to see current backend connections: SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; It might also help if you posted your postgresql.conf too. Thom
Re: [PERFORM] Postgres query completion status?
Thom Brown wrote: Okay, have you tried monitoring the connections to your database? Try: select * from pg_stat_activity; Tried that - it's very useful as far as it goes. I can see that in most cases, the DB is running just the one query. What I really want to know is, how far through that query has it got? (For example, if the query is an update, then surely it knows how many rows have been updated, and how many are yet to go). And this to see current backend connections: SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; This looks identical to just some of the columns from pg_stat_activity. It might also help if you posted your postgresql.conf too. Below (have removed the really non-interesting bits). Thanks, Richard Thom #-- # CONNECTIONS AND AUTHENTICATION #-- max_connections = 500 # (change requires restart) #-- # RESOURCE USAGE (except WAL) #-- # - Memory - shared_buffers = 4500MB # min 128kB # (change requires restart) temp_buffers = 64MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared # memory per transaction slot, plus lock space (see # max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. work_mem = 256MB# min 64kB maintenance_work_mem = 256MB# min 1MB max_stack_depth = 4MB # min 100kB # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 # (change requires restart) #shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0ms# 0-100 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 1-1 credits # - Background Writer - #bgwriter_delay = 200ms # 10-1ms between rounds #bgwriter_lru_maxpages = 100# 0-1000 max buffers written/round #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round # - Asynchronous Behavior - #effective_io_concurrency = 1 # 1-1000. 0 disables prefetching #-- # WRITE AHEAD LOG #-- # - Settings - #fsync = on # turns forced synchronization on or off #synchronous_commit = on# immediate fsync at commit #wal_sync_method = fsync# the default is the first option # supported by the operating system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes wal_buffers = 2MB # min 32kB # (change requires restart) #wal_writer_delay = 200ms # 1-1 milliseconds commit_delay = 5# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 # - Checkpoints - checkpoint_segments = 64# in logfile segments, min 1, 16MB each (was safe value of 4) #checkpoint_timeout = 5min # range 30s-1h #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables # - Archiving - #archive_mode = off # allows archiving to be done # (change requires restart) #archive_command = '' # command to use to archive a logfile segment #archive_timeout = 0# force a logfile segment switch after this # number of seconds; 0 disables #-- # QUERY TUNING
Re: [PERFORM] [GENERAL] Strange performance degradation
Sam Jas wrote: Is there any idle connections exists ? I didn't see any, I'll look better next time. -- 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] [GENERAL] Strange performance degradation
Brian Modra wrote: I had a similar problem: I did a large delete, and then a selct which covered the previous rows. It took ages, because the index still had those deleted rows. Possibly the same happens with update. Try this: vacuum analyse reindex database (your database name instead of ...) or, rather do this table by table: vacuum analyse reindex table ... Autovacuum is a generally good thing. So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? there were probably some long transactions running. Stopping postgres effectively kills them off. I'll try that, thanks for your help Brian. -- 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] Postgres query completion status?
2009/11/20 Richard Neill rn...@cam.ac.uk It might also help if you posted your postgresql.conf too. Below (have removed the really non-interesting bits). Thanks, Richard I can't actually see anything in your config that would cause this problem. :/ As for seeing the progress of an update, I would have thought due to the atomic nature of updates, only the transaction in which the update is running would have visibility of the as-yet uncommitted updates. Thom
Re: [PERFORM] Postgres query completion status?
Thom Brown wrote: 2009/11/20 Richard Neill rn...@cam.ac.uk mailto:rn...@cam.ac.uk It might also help if you posted your postgresql.conf too. Below (have removed the really non-interesting bits). Thanks, Richard I can't actually see anything in your config that would cause this problem. :/ As for seeing the progress of an update, I would have thought due to the atomic nature of updates, only the transaction in which the update is running would have visibility of the as-yet uncommitted updates. Yes, but surely the postmaster itself (and any administrative user) should be able to find this out. What I need for slow queries is some kind of progress bar. Any estimate (no matter how poor, or non-linear) of the query progress, or time remaining would be invaluable. Richard -- 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] Postgres query completion status?
-Mensaje original- De: Richard Neill max_connections = 500 # (change requires restart) work_mem = 256MB# min 64kB Not that it has to do with your current problem but this combination could bog your server if enough clients run sorted queries simultaneously. You probably should back on work_mem at least an order of magnitude. -- 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] Postgres query completion status?
Fernando Hevia wrote: -Mensaje original- De: Richard Neill max_connections = 500 # (change requires restart) work_mem = 256MB# min 64kB Not that it has to do with your current problem but this combination could bog your server if enough clients run sorted queries simultaneously. You probably should back on work_mem at least an order of magnitude. What's the correct way to configure this? * We have one client which needs to run really big transactions (therefore needs the work memory). * We also have about 200 clients which run always very small, short queries. Richard -- 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] Postgres query completion status?
-Mensaje original- De: Richard Neill Fernando Hevia wrote: -Mensaje original- De: Richard Neill max_connections = 500 # (change requires restart) work_mem = 256MB# min 64kB Not that it has to do with your current problem but this combination could bog your server if enough clients run sorted queries simultaneously. You probably should back on work_mem at least an order of magnitude. What's the correct way to configure this? * We have one client which needs to run really big transactions (therefore needs the work memory). * We also have about 200 clients which run always very small, short queries. Richard Set the default value at postgresql.conf much lower, probably 4MB. And just before running any big transaction raise it for the current session only issuing a: set work_mem = '256MB'; Regards, Fernando. -- 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] Postgres query completion status?
max_connections = 500 # (change requires restart) work_mem = 256MB # min 64kB Not that it has to do with your current problem but this combination could bog your server if enough clients run sorted queries simultaneously. You probably should back on work_mem at least an order of magnitude. What's the correct way to configure this? * We have one client which needs to run really big transactions (therefore needs the work memory). You can set the work_mem for the specific user (like set work_mem to x) at the begginning of the session. Here are some things I noticed (it is more like shooting in the dark, but still...) the expensive part is this: - Sort (cost=280201.66..281923.16 rows=688602 width=300) (actual time=177511.806..183486.593 rows=41317448 loops=1) Sort Key: du_report_sku.wid, du_report_sku.storeorderid, du_report_sku.genreorderid Sort Method: external sort Disk: 380768kB - HashAggregate (cost=197936.75..206544.27 rows=688602 width=36) (actual time=7396.426..11224.839 rows=6282564 loops=1) - Seq Scan on du_report_sku (cost=0.00..111861.61 rows=6886011 width=36) (actual time=0.006..573.419 rows=6897682 loops=1) (it is pretty confusing that the HashAggregate reports ~6M rows, but the sort does 41M rows, but maybe I can not read this). Anyway, I think that if You up the work_mem for this query to 512M, the sort will be in memory, an thus plenty faster. Also, You say You are experiencing unstable query plans, and this may mean that geqo is kicking in (but Your query seems too simple for that, even considering the views involved). A quick way to check that would be to run explain the query a coule tens of times, and check if the plans change. If they do, try upping geqo_threshold. You have seq_page_cost 4 times larger than random_page_cost. You say You are on SSD, so there is no random access penalty. Try setting them equal. Your plan is full of merge-joins, some indices may be in order. Merge join is a kind of last-chance plan. the query is : SELECT ( core.demand.qty - viwcs.wave_end_demand.qty_remaining ) FROM core.demand, viwcs.previous_wave LEFT OUTER JOIN viwcs.wave_end_demand USING ( wid ) WHERE core.demand.id = viwcs.wave_end_demand.demand_id; Isn`t the left join equivalent to an inner join, since in where You are comparing values from the outer side of the join? If they come out nulls, they will get discarded anyway... I hope You find some of this useful. Greetings Marcin -- 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] Postgres query completion status?
Justin Pitts wrote: Set work_mem in postgresql.conf down to what the 200 clients need, which sounds to me like the default setting. In the session which needs more work_mem, execute: SET SESSION work_mem TO '256MB' Isn't that terribly ugly? It seems to me less hackish to rely on the many clients not to abuse work_mem (as we know exactly what query they will run, we can be sure it won't happen). It's a shame that the work_mem parameter is a per-client one, rather than a single big pool. Richard -- 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] Postgres query completion status?
Richard Neill wrote: Am I missing something though, or is this project dormant, without having released any files? My bad--gave you the wrong url. http://git.postgresql.org/gitweb?p=pg_top.git;a=summary has the project I meant to point you toward. What I really want to know is, how far through that query has it got? (For example, if the query is an update, then surely it knows how many rows have been updated, and how many are yet to go). I understand what you want. The reason you're not getting any suggestions is because that just isn't exposed in PostgreSQL yet. Clients ask for queries to be run, eventually they get rows of results back, but there's no notion of how many they're going to get in advance or how far along they are in executing the query's execution plan. There's a couple of academic projects that have started exposing more of the query internals, but I'm not aware of anyone who's even started moving in the direction of what you'd need to produce a progress bar. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] Postgres query completion status?
Richard Neill wrote: Likewise, is there any way to check whether, for example, postgres is running out of work memory? It doesn't work like that; it's not an allocation. What happens is that the optimizer estimates how much memory a sort is going to need, and then uses work_mem to decide whether that is something it can do in RAM or something that needs to be done via a more expensive disk-based sorting method. You can tell if it's not set high enough by toggling on log_temp_files and watching when those get created--those appear when sorts bigger than work_mem need to be done. commit_delay = 5# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 Random note: that is way too high of a value for commit_delay. It's unlikely to be helping you, and might be hurting sometimes. The whole commit_delay feature is quite difficult to tune correctly, and is really only useful for situations where there's really heavy writing going on and you want to carefully tweak write chunking size. The useful range for commit_delay is very small even in that situation, 50K is way too high. I'd recommend changing this back to the default, if you're not at the point where you're running your own benchmarks to prove the parameter is useful to you it's not something you should try to adjust. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] SSD + RAID
Richard Neill wrote: The key issue for short,fast transactions seems to be how fast an fdatasync() call can run, forcing the commit to disk, and allowing the transaction to return to userspace. Attached is a short C program which may be of use. Right. I call this the commit rate of the storage, and on traditional spinning disks it's slightly below the rotation speed of the media (i.e. 7200RPM = 120 commits/second).If you've got a battery-backed cache in front of standard disks, you can easily clear 10K commits/second. I normally test that out with sysbench, because I use that for some other tests anyway: sysbench --test=fileio --file-fsync-freq=1 --file-num=1 --file-total-size=16384 --file-test-mode=rndwr run | grep Requests/sec -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
