Re: [PERFORM] select max() much slower than select min()
On Fri, Jun 19, 2009 at 2:05 PM, Brian Cox wrote: > David Rees [dree...@gmail.com] wrote: >> >> Along those lines, couldn't you just have the DB do the work? >> >> select max(ts_id), min(ts_id) from ... where ts_interval_start_time >= >> ... and ... >> >> Then you don't have to transfer 500k ids across the network... > > I guess you didn't read the entire thread: I started it because the query > you suggest took 15 mins to complete. I read the whole thing and just scanned through it again - I didn't see any queries where you put both the min and max into the same query, but perhaps I missed it. Then again - I don't quite see why your brute force method is any faster than using a min or max, either. It would be interesting to see the analyze output as apparently scanning on the ts_interval_start_time is a lot faster than scanning the pkey (even though Tom thought that it would not be much difference since either way you have to hit the heap a lot). My thought was that putting both the min and max into the query would encourage Pg to use the same index as the brute force method. If not, you could still put the ts_ids into a temporary table using your brute force query and use that to avoid the overhead transferring 500k ids over the network. -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] select max() much slower than select min()
David Rees [dree...@gmail.com] wrote: Along those lines, couldn't you just have the DB do the work? select max(ts_id), min(ts_id) from ... where ts_interval_start_time >= ... and ... Then you don't have to transfer 500k ids across the network... I guess you didn't read the entire thread: I started it because the query you suggest took 15 mins to complete. 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] select max() much slower than select min()
On Fri, Jun 19, 2009 at 1:05 PM, Brian Cox wrote: > Thanks to all for the analysis and suggestions. Since the number of rows in > an hour < ~500,000, brute force looks to be a fast solution: > > select ts_id from ... where ts_interval_start_time >= ... and ... > > This query runs very fast as does a single pass through the ids to find the > min and max. Along those lines, couldn't you just have the DB do the work? select max(ts_id), min(ts_id) from ... where ts_interval_start_time >= ... and ... Then you don't have to transfer 500k ids across the network... -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] select max() much slower than select min()
Tom Lane [...@sss.pgh.pa.us] wrote: Some experimentation suggests that it might help to provide a 2-column index on (ts_id, ts_interval_start_time). This is still going to be scanned in order by ts_id, but it will be possible to check the ts_interval_start_time condition in the index, eliminating a large number of useless trips to the heap. Whether this type of query is important enough to justify maintaining an extra index for is something you'll have to decide for yourself... Thanks to all for the analysis and suggestions. Since the number of rows in an hour < ~500,000, brute force looks to be a fast solution: select ts_id from ... where ts_interval_start_time >= ... and ... This query runs very fast as does a single pass through the ids to find the min and max. 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] processor running queue - general rule of thumb?
> Like the other poster said, we likely don't have enough to tell you > what's going on, but from what you've said here it sounds like you're > mostly just CPU bound. Assuming you're reading the output of vmstat > and top and other tools like that. Thanks. I used 'sadc' from the sysstat RPM (part of the sar suite) to collect data, and it does collect Vm and other data like that from top and vmstat. I did not see any irregular activity in those areas. I realise I did not give you all enough details, which is why I worded my question they way I did : "is there a general rule of thumb for running queue" -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] processor running queue - general rule of thumb?
On Fri, Jun 19, 2009 at 9:59 AM, Alan McKay wrote: > Hey folks, > > I'm new to all this stuff, and am sitting here with kSar looking at > some graphed results of some load tests we did, trying to figure > things out :-) > > We got some unsatisfactory results in stressing our system, and now I > have to divine where the bottleneck is. > > We did 4 tests, upping the load each time. The 3rd and 4th ones have > all 8 cores pegged at about 95%. Yikes! > > In the first test the processor running queue spikes at 7 and maybe > averages 4 or 5 > > In the last test it spikes at 33 with an average maybe 25. > > Looks to me like it could be a CPU bottleneck. But I'm new at this :-) > > Is there a general rule of thumb "if queue is longer than X, it is > likely a bottleneck?" > > In reading an IBM Redbook on Linux performance, I also see this : > "High numbers of context switches in connection with a large number of > interrupts can signal driver or application issues." > > On my first test where the CPU is not pegged, context switching goes > from about 3700 to about 4900, maybe averaging 4100 That's not too bad. If you see them in the 30k to 150k range, then worry about it. > On the pegged test, the values are maybe 10% higher than that, maybe 15%. That's especially good news. Normally when you've got a problem, it will increase in a geometric (or worse) way. > It is an IBM 3550 with 8 cores, 2660.134 MHz (from dmesg), 32Gigs RAM Like the other poster said, we likely don't have enough to tell you what's going on, but from what you've said here it sounds like you're mostly just CPU bound. Assuming you're reading the output of vmstat and top and other tools 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] processor running queue - general rule of thumb?
Alan McKay wrote: Hey folks, We did 4 tests, upping the load each time. The 3rd and 4th ones have all 8 cores pegged at about 95%. Yikes! In the first test the processor running queue spikes at 7 and maybe averages 4 or 5 In the last test it spikes at 33 with an average maybe 25. Looks to me like it could be a CPU bottleneck. But I'm new at this :-) Is there a general rule of thumb "if queue is longer than X, it is likely a bottleneck?" In reading an IBM Redbook on Linux performance, I also see this : "High numbers of context switches in connection with a large number of interrupts can signal driver or application issues." On my first test where the CPU is not pegged, context switching goes from about 3700 to about 4900, maybe averaging 4100 Well the people here will need allot more information to figure out what is going on. What kind of Stress did you do is it a specific query causing the problem in the test What kind of load? How many simulated clients How big is the database? Need to see the postgresql.config What kind of IO Subsystem do you have ??? what does vmstat show have you look at wiki yet http://wiki.postgresql.org/wiki/Performance_Optimization -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] processor running queue - general rule of thumb?
Hey folks, I'm new to all this stuff, and am sitting here with kSar looking at some graphed results of some load tests we did, trying to figure things out :-) We got some unsatisfactory results in stressing our system, and now I have to divine where the bottleneck is. We did 4 tests, upping the load each time. The 3rd and 4th ones have all 8 cores pegged at about 95%. Yikes! In the first test the processor running queue spikes at 7 and maybe averages 4 or 5 In the last test it spikes at 33 with an average maybe 25. Looks to me like it could be a CPU bottleneck. But I'm new at this :-) Is there a general rule of thumb "if queue is longer than X, it is likely a bottleneck?" In reading an IBM Redbook on Linux performance, I also see this : "High numbers of context switches in connection with a large number of interrupts can signal driver or application issues." On my first test where the CPU is not pegged, context switching goes from about 3700 to about 4900, maybe averaging 4100 On the pegged test, the values are maybe 10% higher than that, maybe 15%. It is an IBM 3550 with 8 cores, 2660.134 MHz (from dmesg), 32Gigs RAM thanks, -Alan -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] select max() much slower than select min()
On Fri, Jun 19, 2009 at 3:26 PM, Tom Lane wrote: > > That's the problem then. Notice what the query plan is doing: it's > scanning the table in order by ts_id, looking for the first row that > falls within the ts_interval_start_time range. Evidently this > particular range is associated with smaller ts_ids, so you reach it a > lot sooner in a ts_id ascending scan than a ts_id descending one. > > Given the estimated size of the range, scanning with the > ts_interval_start_time index wouldn't be much fun either, since it would > have to examine all rows in the range to determine the min or max ts_id. > You could possibly twiddle the cost constants to make the planner choose > that plan instead, but it's still not going to be exactly speedy. If your range of ts_interval_start_time is relatively static -- it doesn't look like it in this case given that's only an hour, but... -- then one option is to create a partial index on "ts_id" with the condition "WHERE ts_interval_start_time >= 'foo' AND ts_interval_start_time < 'bar' ". But if your range of times is always going to vary then you're going to have a problem there. There ought to be a way to use GIST to do this but I don't think we have any way to combine two different columns of different types in a single GIST index except as a multicolumn index which doesn't do what you want. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] 8.4 COPY performance regression on Solaris
Kenneth Marshall writes: > Looking at the XLogInsert() from 8.3 and 8.4, the 8.4 > version includes a call to RecoveryInProgress() at > the top as well as a call to TRACE_POSTGRESQL_XLOG_INSERT(). > Could either of those have caused a context switch or > cache flush resulting in worse performance. Hmm. TRACE_POSTGRESQL_XLOG_INSERT() should be a no-op (or at least, none of the complainants have admitted to building with --enable-dtrace). RecoveryInProgress() should be just a quick test of a local boolean, so it's hard to believe that it costs anything noticeable; but if anyone who is able to reproduce the problem wants to test this theory, try taking out these lines /* cross-check on whether we should be here or not */ if (RecoveryInProgress()) elog(FATAL, "cannot make new WAL entries during recovery"); which are only a sanity check anyway. 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] select max() much slower than select min()
Brian Cox writes: > Kevin Grittner [kevin.gritt...@wicourts.gov] wrote: >> Is there any correlation between ts_id and ts_interval_start_time? > only vaguely: increasing ts_interval_start_time implies increasing ts_id > but there may be many rows (100,000's) with the same ts_interval_start_time That's the problem then. Notice what the query plan is doing: it's scanning the table in order by ts_id, looking for the first row that falls within the ts_interval_start_time range. Evidently this particular range is associated with smaller ts_ids, so you reach it a lot sooner in a ts_id ascending scan than a ts_id descending one. Given the estimated size of the range, scanning with the ts_interval_start_time index wouldn't be much fun either, since it would have to examine all rows in the range to determine the min or max ts_id. You could possibly twiddle the cost constants to make the planner choose that plan instead, but it's still not going to be exactly speedy. Some experimentation suggests that it might help to provide a 2-column index on (ts_id, ts_interval_start_time). This is still going to be scanned in order by ts_id, but it will be possible to check the ts_interval_start_time condition in the index, eliminating a large number of useless trips to the heap. Whether this type of query is important enough to justify maintaining an extra index for is something you'll have to decide for yourself... 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] select max() much slower than select min()
> -Original Message- > From: Brian Cox > Subject: [PERFORM] select max() much slower than select min() > > seems like max() shouldn't take any longer than min() and > certainly not 10 times as long. Any ideas on how to determine > the max more quickly? That is odd. It seems like max should actually have to scan fewer rows than min should. It might still be bloat in the table, because unless you did VACUUM FULL there could still be dead rows. A vacuum verbose would show if there is bloat or not. Also maybe you could try a two column index like this: create index test_index on ts_stats_transet_user_interval (ts_interval_start_time, ts_id); 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] 8.4 COPY performance regression on Solaris
Hi, Looking at the XLogInsert() from 8.3 and 8.4, the 8.4 version includes a call to RecoveryInProgress() at the top as well as a call to TRACE_POSTGRESQL_XLOG_INSERT(). Could either of those have caused a context switch or cache flush resulting in worse performance. Cheers, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
