Re: [PERFORM] Slow update with simple query
Tom Lane a écrit :
Arnaud Lesauvage [EMAIL PROTECTED] writes:
Indeed, the new query does not perform that well :
Hash Join (cost=112.75..307504.97 rows=2024869 width=355) (actual
time=53.995..246443.811 rows=2020061 loops=1)
...
Total runtime: 2777844.892 ms
I removed all unnecessary indexes on t1 before running the query (I left the
index on uid and the multicolumn index containind the updated field).
I believe the multicolumn-functional-index computation is taking some time
here, isn't it ?
Given that the plan itself only takes 246 sec, there's *something*
associated with row insertion that's eating the other 2500+ seconds.
Either index entry computation or constraint checking ...
There is an insert trigger (but here I am only updating the data), and a
multicolumn functional index. That's all I can think of.
I must be missing something, so here is the full table description.
The field I am updating is incluredansstats.
The field I am join on is userinternalid.
CREATE TABLE statistiques.log
(
gid serial NOT NULL,
userinternalid character(32),
ip character varying(255),
browser character varying(255),
fichier character varying(255),
querystring text,
page character varying(255),
useridentity character varying(100),
incluredansstats boolean NOT NULL DEFAULT true,
date character varying,
heure character varying,
dateformatee timestamp without time zone,
sessionid character(32),
sortindex integer,
CONSTRAINT log_pkey PRIMARY KEY (gid)
)
WITHOUT OIDS;
ALTER TABLE statistiques.log OWNER TO postgres;ncluredansstats;
CREATE INDEX idx_page_datemonth_incluredansstats
ON statistiques.log
USING btree
(page, date_trunc('month'::text, dateformatee), incluredansstats);
CREATE INDEX idx_userinternalid
ON statistiques.log
USING btree
(userinternalid);
CREATE INDEX idx_userinternalid_page_datemonth
ON statistiques.log
USING btree
(userinternalid, page, date_trunc('month'::text, dateformatee));
ALTER TABLE statistiques.log
ADD CONSTRAINT log_pkey PRIMARY KEY(gid);
CREATE TRIGGER parse_log_trigger
BEFORE INSERT
ON statistiques.log
FOR EACH ROW
EXECUTE PROCEDURE statistiques.parse_log_trigger();
This was a one-shot query, so I don't really mind it being slow, but if you
want I can still troubleshoot it !
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] strange query behavior
18,273,008 rows in observationresults
pg_stats:
select * from pg_stats where tablename='observationresults' and
attname='batteryidentifier';
schemaname | tablename | attname | null_frac |
avg_width | n_distinct | most_common_vals
|most_common_freqs
| histogram_bounds
| correlation
++---+---+--
-++-
-+--
---+
-+-
public | observationresults | batteryidentifier | 0 |
4 | 12942 |
{437255,1588952,120420,293685,356599,504069,589910,693683,834990,854693}
|
{0.0013,0.0013,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001}
|
{3561,271263,556929,839038,1125682,1406538,1697589,1970463,2226781,25392
41,2810844} | 0.31779
thanks
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 13, 2006 6:25 PM
To: Tim Jones
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] strange query behavior
The large rowcount estimate makes it back off to a non-nestloop plan for
the outer joins, and in this situation that's a loser.
I'm actually not sure why they're not both too high --- with the
rowcount estimate of 1362 for the inner scan in the first example, you'd
expect about twice that for the join result. But the immediate problem
is that in the case where it knows exactly what batteryidentifier is
being probed for, it's still off by more than a factor of 100 on the
rowcount estimate for observationresults. How many rows in
observationresults, and may we see the pg_stats entry for
observationresults.batteryidentifier?
It's likely that the answer for you will be raise the statistics target
for observationresults and re-ANALYZE, but I'd like to gather more info
about what's going wrong first.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[PERFORM] EXPLAIN ANALYZE on 8.2
Hi, everybody! Running the same query on pg 8.2 through EXPLAIN ANALYZE takes 4x-10x time as running it without it. Is it ok? Example: testing= select count(*) from auth_user; count - 2575675 (1 row) Time: 1450,829 ms testing= explain analyze select count(*) from auth_user; QUERY PLAN Aggregate (cost=89814.87..89814.88 rows=1 width=0) (actual time=18460.436..18460.439 rows=1 loops=1) - Seq Scan on auth_user (cost=0.00..83373.89 rows=2576389 width=0) (actual time=0.424..9871.520 rows=2575675 loops=1) Total runtime: 18460.535 ms (3 rows) Time: 18461,194 ms ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] New to PostgreSQL, performance considerations
On Wed, 13 Dec 2006, Ron wrote: The slowest results, Michael's, are on the system with what appears to be the slowest CPU of the bunch; and the ranking of the rest of the results seem to similarly depend on relative CPU performance. This is not what one would naively expect when benching a IO intensive app like a DBMS. pgbench with 3000 total transactions and fsync off is barely doing I/O to disk; it's writing a bunch of data to the filesystem cache and ending the benchmark before the data even makes it to the hard drive. This is why his results become completely different as soon as the number of transactions increases. With little or no actual disk writes, you should expect results to be ranked by CPU speed. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow update with simple query
Arnaud Lesauvage [EMAIL PROTECTED] writes: I must be missing something, so here is the full table description. It looks pretty harmless, except for CREATE TRIGGER parse_log_trigger BEFORE INSERT ON statistiques.log FOR EACH ROW EXECUTE PROCEDURE statistiques.parse_log_trigger(); It seems the time must be going into this trigger function. What does it do? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Slow update with simple query
Tom Lane a écrit : Arnaud Lesauvage [EMAIL PROTECTED] writes: I must be missing something, so here is the full table description. It looks pretty harmless, except for CREATE TRIGGER parse_log_trigger BEFORE INSERT ON statistiques.log FOR EACH ROW EXECUTE PROCEDURE statistiques.parse_log_trigger(); It seems the time must be going into this trigger function. What does it do? A lot of things ! Indeed, if it runs it will very badly hurt performances (table lookups, string manipulation, etc...) ! But it should only be tringered on INSERTs, and I am doing an UPDATE ! I can post the function's body if you want. Regards -- Arnaud ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] New to PostgreSQL, performance considerations
On Thu, 14 Dec 2006, Tom Lane wrote: The pgbench app itself becomes the bottleneck at high transaction rates. Awhile back I rewrote it to improve its ability to issue commands concurrently, but then desisted from submitting the changes --- if we change the app like that, future numbers would be incomparable to past ones, which sort of defeats the purpose of a benchmark no? Not at all. Here's an example from the PC hardware benchmarking landscape. Futuremark Corporation has a very popular benchmark for 3D hardware called 3DMark. Every year, they release a new version, and numbers from it are completely different from those produced by the previous year's version. That lets them rev the entire approach taken by the benchmark to reflect current practice. So when the standard for high-end hardware includes, say, acceleration of lighting effects, the new version will include a lighting test. In order to break 1000 points (say) on that test, you absolutely have to have lighting acceleration, even though on the previous year's test you could score that high without it. That is not an isolated example; every useful PC benchmark gets updated regularly, completely breaking backward compatibility, to reflect the capabilities of current hardware and software. Otherwise we'd still be testing how well DOS runs on new processors. Right now everyone is (or has already) upgraded their PC benchmarking code such that you need a dual-core processor to do well on some of the tests. If you have a pgbench version with better concurrency features, I for one would love to see it. I'm in the middle of patching that thing up right now anyway but hadn't gotten that far (yet--I just spent some of yesterday staring at how it submits into libpq trying to figure out how to improve that). I would be happy to take your changes, my changes, changes to the base code since you forked it, and reconcile everything together into a pgbench2007--whose results can't be directly compared to the earlier version, but are more useful on current gen multi-processor/core systems. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
On Wed, Dec 13, 2006 at 01:03:04PM -0500, Ron wrote: What I find interesting is that so far Guido's C2D Mac laptop has gotten the highest values by far in this set of experiments, and no one else is even close. The slowest results, Michael's, are on the system with what appears to be the slowest CPU of the bunch; and the ranking of the rest of the results seem to similarly depend on relative CPU performance. This is not what one would naively expect when benching a IO intensive app like a DBMS. Note that I ran with fsync off, that the data set is 300M, and that all of the systems (IIRC) have at least 1G RAM. This is exactly the distribution I would expect since we're configuring the benchmark to determine whether cpu-specific optimizations affect the results. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
At 10:00 AM 12/14/2006, Greg Smith wrote: On Wed, 13 Dec 2006, Ron wrote: The slowest results, Michael's, are on the system with what appears to be the slowest CPU of the bunch; and the ranking of the rest of the results seem to similarly depend on relative CPU performance. This is not what one would naively expect when benching a IO intensive app like a DBMS. pgbench with 3000 total transactions and fsync off is barely doing I/O to disk; it's writing a bunch of data to the filesystem cache and ending the benchmark before the data even makes it to the hard drive. This is why his results become completely different as soon as the number of transactions increases. With little or no actual disk writes, you should expect results to be ranked by CPU speed. I of course agree with you in the general sense. OTOH, I'm fairly sure the exact point where this cross-over occurs is dependent on the components and configuration of the system involved. (Nor do I want to dismiss this scenario as irrelevant or unimportant. There are plenty of RW situations where this takes place or where the primary goal of a tuning effort is to make it take place. Multi-GB BB RAID caches anyone?) In addition, let's keep in mind that we all know that overall system performance is limited by whatever component hits its limits first. Local pg performance has been known to be limited by any of : CPUs, memory subsystems, or physical IO subsystems. Intuitively, one tends to expect only the later to be a limiting factor in the vast majority of DBMS tasks. pg has a history of regularly surprising such intuition in many cases. IMO, this makes good bench marking tools and procedures more important to have. (If nothing else, knowing what component is likely to be the bottleneck in system X made of components x1, x2, x3, for task Y is valuable lore for the pg community to have as preexisting data when first asked any given question on this list! ) One plausible positive effect of tuning like that Daniel advocates is to move the level of system activity where the physical IO subsystem becomes the limiting factor on overall system performance. We are not going to know definitively if such an effect exists, or to what degree, or how to achieve it, if we don't have appropriately rigorous and reproducible experiments (and documentation of them) in place to test for it. So it seem to make sense that the community should have a discussion about the proper bench marking of pg and to get some results based on said. Ron Peacetree ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
Evgeny Gridasov [EMAIL PROTECTED] writes: Running the same query on pg 8.2 through EXPLAIN ANALYZE takes 4x-10x time as running it without it. If your machine has slow gettimeofday() this is not surprising. 8.2 is no worse (or better) than any prior version. Some quick arithmetic from your results suggests that gettimeofday() is taking about 3.3 microseconds, which is indeed pretty awful. What sort of machine is this exactly? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Wed, 2006-12-13 at 18:36 -0800, Josh Berkus wrote: Mostly, though, pgbench just gives the I/O system a workout. It's not a really good general workload. It also will not utilize all cpus on a many cpu machine. We recently found that the only way to *really* test with pgbench was to actually run 4+ copies of pgbench at the same time. The pgbench app itself becomes the bottleneck at high transaction rates. Awhile back I rewrote it to improve its ability to issue commands concurrently, but then desisted from submitting the changes --- if we change the app like that, future numbers would be incomparable to past ones, which sort of defeats the purpose of a benchmark no? What is to stop us from running the new pgbench against older versions of PGSQL? Any stats taken from a run of pgbench a long time ago probably aren't relevant against a modern test anyway as the underlying hardware and OS are likely to have changed or been updated. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Slow update with simple query
Arnaud Lesauvage [EMAIL PROTECTED] writes: Tom Lane a écrit : It seems the time must be going into this trigger function. What does it do? A lot of things ! Indeed, if it runs it will very badly hurt performances (table lookups, string manipulation, etc...) ! But it should only be tringered on INSERTs, and I am doing an UPDATE ! Doh, right, I obviously still need to ingest more caffeine this morning. I think the conclusion must be that there was just too much I/O to be done to update all the rows. Have you done any tuning of shared_buffers and so forth? I recall having seen cases where update performance went bad as soon as the upper levels of a large index no longer fit into shared_buffers ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Slow update with simple query
Tom Lane a écrit : I think the conclusion must be that there was just too much I/O to be done to update all the rows. Have you done any tuning of shared_buffers and so forth? I recall having seen cases where update performance went bad as soon as the upper levels of a large index no longer fit into shared_buffers ... Yes, that's probably it. I think my raid1 array's performances are very bad. I am switching to a brand new hardware next week, I am quite confident that this will solve many problems. Thanks for helping ! Regards -- Arnaud ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
Tom, Hello. This is a Linux Debian 3.1 ontop of 2x XEON 3.4 Ghz. PostgreSQL is 8.2 checked out from CVS REL8_2_STABLE yesterday. I'm running the same Postgres on another machine, with Debian Etch and have the same results. On Thu, 14 Dec 2006 11:11:42 -0500 Tom Lane [EMAIL PROTECTED] wrote: Evgeny Gridasov [EMAIL PROTECTED] writes: Running the same query on pg 8.2 through EXPLAIN ANALYZE takes 4x-10x time as running it without it. If your machine has slow gettimeofday() this is not surprising. 8.2 is no worse (or better) than any prior version. Some quick arithmetic from your results suggests that gettimeofday() is taking about 3.3 microseconds, which is indeed pretty awful. What sort of machine is this exactly? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow update with simple query
Out of curiosity, how hard would it be to modify the output of EXPLAIN ANALYZE when doing an insert/update to include the index update times and/or non-FK constraint checking times and/or the table row update times? Or any other numbers that might be useful in circumstances like this. I'm wondering if it's possible to shed some light on the remaining dark shadows of PG performance troubleshooting. -- Mark Lewis On Thu, 2006-12-14 at 11:19 -0500, Tom Lane wrote: Arnaud Lesauvage [EMAIL PROTECTED] writes: Tom Lane a crit : It seems the time must be going into this trigger function. What does it do? A lot of things ! Indeed, if it runs it will very badly hurt performances (table lookups, string manipulation, etc...) ! But it should only be tringered on INSERTs, and I am doing an UPDATE ! Doh, right, I obviously still need to ingest more caffeine this morning. I think the conclusion must be that there was just too much I/O to be done to update all the rows. Have you done any tuning of shared_buffers and so forth? I recall having seen cases where update performance went bad as soon as the upper levels of a large index no longer fit into shared_buffers ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
Evgeny Gridasov [EMAIL PROTECTED] writes: This is a Linux Debian 3.1 ontop of 2x XEON 3.4 Ghz. PostgreSQL is 8.2 checked out from CVS REL8_2_STABLE yesterday. I'm running the same Postgres on another machine, with Debian Etch and have the same results. Hmph. With 8.2 on Fedora 5 on a 2.8Ghz dual Xeon, I get this: regression=# create table foo as select x from generate_series(1,250) x; SELECT regression=# vacuum foo; VACUUM regression=# checkpoint; CHECKPOINT regression=# \timing Timing is on. regression=# select count(*) from foo; count - 250 (1 row) Time: 666.639 ms regression=# select count(*) from foo; count - 250 (1 row) Time: 609.514 ms regression=# explain analyze select count(*) from foo; QUERY PLAN - Aggregate (cost=44764.00..44764.01 rows=1 width=0) (actual time=1344.812..1344.813 rows=1 loops=1) - Seq Scan on foo (cost=0.00..38514.00 rows=250 width=0) (actual time=0.031..748.571 rows=250 loops=1) Total runtime: 1344.891 ms (3 rows) Time: 1345.755 ms regression=# explain analyze select count(*) from foo; QUERY PLAN - Aggregate (cost=44764.00..44764.01 rows=1 width=0) (actual time=1324.846..1324.847 rows=1 loops=1) - Seq Scan on foo (cost=0.00..38514.00 rows=250 width=0) (actual time=0.046..748.582 rows=250 loops=1) Total runtime: 1324.902 ms (3 rows) Time: 1325.591 ms regression=# which works out to about 0.14 microsec per gettimeofday call, on a machine that ought to be slower than yours. So I think you've got either a crummy motherboard, or a kernel that doesn't know the best way to read the clock on that hardware. There is some discussion of this in the archives (probably in pgsql-hackers); look back around May or so when we were unsuccessfully trying to hack EXPLAIN to use fewer gettimeofday calls. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] unsubscribe
unsubscribe Have you checked out the new-look www.indiatimes.com yet? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] File Systems Compared
On Thu, Dec 14, 2006 at 01:39:00 -0500, Jim Nasby [EMAIL PROTECTED] wrote: On Dec 11, 2006, at 12:54 PM, Bruno Wolff III wrote: This appears to be changing under Linux. Recent kernels have write barriers implemented using cache flush commands (which some drives ignore, so you need to be careful). In very recent kernels, software raid using raid 1 will also handle write barriers. To get this feature, you are supposed to mount ext3 file systems with the barrier=1 option. For other file systems, the parameter may need to be different. But would that actually provide a meaningful benefit? When you COMMIT, the WAL data must hit non-volatile storage of some kind, which without a BBU or something similar, means hitting the platter. So I don't see how enabling the disk cache will help, unless of course it's ignoring fsync. When you do an fsync, the OS sends a cache flush command to the drive, which on most drives (but supposedly there are ones that ignore this command) doesn't return until all of the cached pages have been written to the platter, and doesn't return from the fsync until the flush is complete. While this writes more sectors than you really need, it is safe. And it allows for caching to speed up some things (though not as much as having queued commands would). I have done some tests on my systems and the speeds I am getting make it clear that write barriers slow things down to about the same range as having caches disabled. So I believe that it is likely working as advertised. Note the use case for this is more for hobbiests or development boxes. You can only use it on software raid (md) 1, which rules out most real systems. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] strange query behavior
Tim Jones [EMAIL PROTECTED] writes: 18,273,008 rows in observationresults [ and n_distinct = 12942 ] OK, so the estimated rowcounts are coming from those two numbers. It's notoriously hard to get a decent n_distinct estimate from a small sample :-(, and I would imagine the number of batteryidentifiers is really a lot more than 12942? What you need to do is increase the statistics target for observationresults.batteryidentifier (see ALTER TABLE) and re-ANALYZE and see if you get a saner n_distinct in pg_stats. I'd try 100 and then 1000 as target. Or you could just increase the global default target (see postgresql.conf) but that might be overkill. It's still a bit odd that the case with two batteryidentifiers was estimated fairly accurately when the other wasn't; I'll go look into that. But in any case you need better stats if you want good plans. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] strange query behavior
I wrote: It's still a bit odd that the case with two batteryidentifiers was estimated fairly accurately when the other wasn't; I'll go look into that. For the sake of the archives: I looked into this, and it seems there's not anything going wrong other than the bogusly small n_distinct for observationresults. I'm assuming that battery.batteryidentifier is unique (stop me here, Tim, if not). That means that (a) there won't be any most-common-values statistics list for it, and (b) the n_distinct estimate should be pretty accurate. What happens in the multiple-batteryidentifier case is that eqjoinsel() doesn't have two MCV lists to work with, and so it bases its selectivity estimate on the larger n_distinct, which in this case is the accurate value from the battery table. So we come out with a decent estimate even though the other n_distinct is all wrong. What happens in the single-batteryidentifier case is that transitive equality deduction removes the battery.batteryidentifier = observationresults.batteryidentifier join condition altogether, replacing it with two restriction conditions batteryidentifier = 1177470. So eqjoinsel() is never called, and the join size estimate is just the product of the indexscan size estimates, and the scan estimate for observationresults is too high because its n_distinct is too small. So the bottom line is that eqjoinsel() is actually a bit more robust than one might have thought ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 14, 2006, at 16:00 , Greg Smith wrote: On Wed, 13 Dec 2006, Ron wrote: The slowest results, Michael's, are on the system with what appears to be the slowest CPU of the bunch; and the ranking of the rest of the results seem to similarly depend on relative CPU performance. This is not what one would naively expect when benching a IO intensive app like a DBMS. pgbench with 3000 total transactions and fsync off is barely doing I/O to disk; it's writing a bunch of data to the filesystem cache and ending the benchmark before the data even makes it to the hard drive. This is why his results become completely different as soon as the number of transactions increases. With little or no actual disk writes, you should expect results to be ranked by CPU speed. I also second your suggestion that pgbench should be run with -S to disable updates. As far as I can see, nobody has reported numbers for this setting, so here goes. I also increased the buffer size, which I found was needed to avoid hitting the disk for block reads, and increased the memory settings. My PostgreSQL config overrides, then, are: shared_buffers = 1024MB work_mem = 1MB maintenance_work_mem = 16MB fsync = off Environment: Linux 2.6.15-23-amd64-generic on Ubuntu. Dual-core AMD Opteron 280 with 4GB of RAM. LSI PCI-X Fusion-MPT SAS. Running with: pgbench -S -v -n -t 5000 -c 5. Results as a graph: http://purefiction.net/paste/pgbench.pdf Stats for CFLAGS=-O0: 18440.181894 19207.882300 19894.432185 19635.625622 19876.858884 20032.597042 19683.597973 20370.19 19989.157881 20207.343510 19993.745956 20081.353580 20356.416424 20047.810017 20319.834190 19417.807528 19906.788454 20536.039929 19491.308046 20002.144230 Stats for CFLAGS=-O3 -msse2 -mfpmath=sse -funroll-loops -m64 - march=opteron -pipe: 23830.358351 26162.203569 25569.091264 26762.755665 26590.822550 26864.908197 26608.029665 26796.116921 26323.742015 26692.576261 26878.859132 26106.770425 26328.371664 26755.595130 25488.304946 26635.527959 26377.485023 24817.590708 26480.245737 26223.427801 Alexander. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] strange query behavior
ok thanks Tom I will alter the statistics and re-analyze the table. Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, December 14, 2006 12:49 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] strange query behavior Tim Jones [EMAIL PROTECTED] writes: 18,273,008 rows in observationresults [ and n_distinct = 12942 ] OK, so the estimated rowcounts are coming from those two numbers. It's notoriously hard to get a decent n_distinct estimate from a small sample :-(, and I would imagine the number of batteryidentifiers is really a lot more than 12942? What you need to do is increase the statistics target for observationresults.batteryidentifier (see ALTER TABLE) and re-ANALYZE and see if you get a saner n_distinct in pg_stats. I'd try 100 and then 1000 as target. Or you could just increase the global default target (see postgresql.conf) but that might be overkill. It's still a bit odd that the case with two batteryidentifiers was estimated fairly accurately when the other wasn't; I'll go look into that. But in any case you need better stats if you want good plans. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] New to PostgreSQL, performance considerations
Alexander, Good stuff. Can you do runs with just CFLAGS=-O3 and just CFLAGS=-msse2 -mfpmath=sse -funroll-loops -m64 - march=opteron -pipe as well ? As it is, you've given a good lower and upper bound on your performance obtainable using compiler options, but you've given no data to show what effect arch specific compiler options have by themselves. Also, what HDs are you using? How many in what config? Thanks in Advance, Ron Peacetree At 02:14 PM 12/14/2006, Alexander Staubo wrote: My PostgreSQL config overrides, then, are: shared_buffers = 1024MB work_mem = 1MB maintenance_work_mem = 16MB fsync = off Environment: Linux 2.6.15-23-amd64-generic on Ubuntu. Dual-core AMD Opteron 280 with 4GB of RAM. LSI PCI-X Fusion-MPT SAS. Running with: pgbench -S -v -n -t 5000 -c 5. Results as a graph: http://purefiction.net/paste/pgbench.pdf Stats for CFLAGS=-O0: 18440.181894 19207.882300 19894.432185 19635.625622 19876.858884 20032.597042 19683.597973 20370.19 19989.157881 20207.343510 19993.745956 20081.353580 20356.416424 20047.810017 20319.834190 19417.807528 19906.788454 20536.039929 19491.308046 20002.144230 Stats for CFLAGS=-O3 -msse2 -mfpmath=sse -funroll-loops -m64 - march=opteron -pipe: 23830.358351 26162.203569 25569.091264 26762.755665 26590.822550 26864.908197 26608.029665 26796.116921 26323.742015 26692.576261 26878.859132 26106.770425 26328.371664 26755.595130 25488.304946 26635.527959 26377.485023 24817.590708 26480.245737 26223.427801 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Insertion to temp table deteriorating over time
Thanks for your replies. Starting a fresh session (not restarting the postmaster) seems to be sufficient to reset performance (and is an easy enough workaround). Still, it would be nice to know the root cause of the problem. The backend process does not seem to be bloating memory-wise (I'm using vmstat to monitor memory usage on the machine). It also does not appear to be bloating in terms of open file handles (using fstat, I can see the backend process has 160-180 open file handles, not growing). Regarding your other email -- interesting -- but we are vacuuming pg_class every hour. So I don't think the answer lies there... Steve On 12/13/06, Tom Lane [EMAIL PROTECTED] wrote: Steven Flatt [EMAIL PROTECTED] writes: Having said that, what kinds of things should I be looking for that could deteriorate/bloat over time? Ordinarily the culprit might be infrequent vacuuming or analyzing, but that wouldn't be corrected by a restart of Postgres. In our case, restarting Postgres gives us a huge performance improvement (for a short while, anyways). Do you actually need to restart the postmaster, or is just starting a fresh session (fresh backend) sufficient? And again, have you monitored the backend process to see if it's bloating memory-wise or open-file-wise? regards, tom lane
Re: [PERFORM] File Systems Compared
Bruno Wolff III wrote: On Thu, Dec 14, 2006 at 01:39:00 -0500, Jim Nasby [EMAIL PROTECTED] wrote: On Dec 11, 2006, at 12:54 PM, Bruno Wolff III wrote: This appears to be changing under Linux. Recent kernels have write barriers implemented using cache flush commands (which some drives ignore, so you need to be careful). Is it true that some drives ignore this; or is it mostly an urban legend that was started by testers that didn't have kernels with write barrier support. I'd be especially interested in knowing if there are any currently available drives which ignore those commands. In very recent kernels, software raid using raid 1 will also handle write barriers. To get this feature, you are supposed to mount ext3 file systems with the barrier=1 option. For other file systems, the parameter may need to be different. With XFS the default is apparently to enable write barrier support unless you explicitly disable it with the nobarrier mount option. It also will warn you in the system log if the underlying device doesn't have write barrier support. SGI recommends that you use the nobarrier mount option if you do have a persistent (battery backed) write cache on your raid device. http://oss.sgi.com/projects/xfs/faq.html#wcache But would that actually provide a meaningful benefit? When you COMMIT, the WAL data must hit non-volatile storage of some kind, which without a BBU or something similar, means hitting the platter. So I don't see how enabling the disk cache will help, unless of course it's ignoring fsync. With write barriers, fsync() waits for the physical disk; but I believe the background writes from write() done by pdflush don't have to; so it's kinda like only disabling the cache for WAL files and the filesystem's journal, but having it enabled for the rest of your write activity (the tables except at checkpoints? the log file?). Note the use case for this is more for hobbiests or development boxes. You can only use it on software raid (md) 1, which rules out most real systems. Ugh. Looking for where that's documented; and hoping it is or will soon work on software 1+0 as well. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Insertion to temp table deteriorating over time
Steven Flatt [EMAIL PROTECTED] writes: Regarding your other email -- interesting -- but we are vacuuming pg_class every hour. So I don't think the answer lies there... That's good, but is the vacuum actually accomplishing anything? I'm wondering if there's also a long-running transaction in the mix. Try a manual VACUUM VERBOSE pg_class; after the thing has slowed down, and see what it says about removable and nonremovable rows. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 14, 2006, at 20:28 , Ron wrote: Can you do runs with just CFLAGS=-O3 and just CFLAGS=-msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe as well ? All right. From my perspective, the effect of -O3 is significant, whereas architecture-related optimizations have no statistically significant effect. As far as I'm aware, though, there's no other arch targets on the Opteron that will make sense, there being no predecessor CPU instruction set to choose from; -march=pentium4 doesn't exist. Also, what HDs are you using? How many in what config? I believe the volume is a two-drive RAID 1 configuration, but I'm not managing these servers, so I'll ask the company's support people. Interestingly enough I see that PostgreSQL seems to be writing around 1MB/s during the pgbench run, even though I'm running pgbench in the - S mode. I haven't had the chance to look at the source yet; is it really only doing selects? Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
On 12/14/06, Tom Lane [EMAIL PROTECTED] wrote: Evgeny Gridasov [EMAIL PROTECTED] writes: This is a Linux Debian 3.1 ontop of 2x XEON 3.4 Ghz. PostgreSQL is 8.2 checked out from CVS REL8_2_STABLE yesterday. I'm running the same Postgres on another machine, with Debian Etch and have the same results. Hmph. With 8.2 on Fedora 5 on a 2.8Ghz dual Xeon, I get this: snip regression=# explain analyze select count(*) from foo; QUERY PLAN - Aggregate (cost=44764.00..44764.01 rows=1 width=0) (actual time=1324.846..1324.847 rows=1 loops=1) - Seq Scan on foo (cost=0.00..38514.00 rows=250 width=0) (actual time=0.046..748.582 rows=250 loops=1) Total runtime: 1324.902 ms (3 rows) Time: 1325.591 ms regression=# which works out to about 0.14 microsec per gettimeofday call, on a machine that ought to be slower than yours. So I think you've got either a crummy motherboard, or a kernel that doesn't know the best way to read the clock on that hardware. There is some discussion of this in the archives (probably in pgsql-hackers); look back around May or so when we were unsuccessfully trying to hack EXPLAIN to use fewer gettimeofday calls. Yow! I notice the same thing on our HP BL25p blades w/2*opteron 270 (four total cores, AMD 8111 or 8131 chipset). 1.25 microsec/call vs my new desktop (Intel Core2 6300) 0.16 microsec/call. I hope this isn't a crummy mainboard but I can't seem to affect things by changing clock source (kernel 2.6.16 SLES10). I tried kernel command option clock=XXX where XXX in (cyclone,hpet,pmtmr,tsc,pit), no option was significantly better than the default. Anyone know how this might be improved (short of replacing hardware)? -K ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
Kelly Burkhart wrote: I hope this isn't a crummy mainboard but I can't seem to affect things by changing clock source (kernel 2.6.16 SLES10). I tried kernel command option clock=XXX where XXX in (cyclone,hpet,pmtmr,tsc,pit), no option was significantly better than the default. Anyone know how this might be improved (short of replacing hardware)? Updating the BIOS might be worth investigating, and then bugging your Linux distro mailing list/support etc for more help. (What sort of motherboard is it?) Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] EXPLAIN ANALYZE on 8.2
Kelly Burkhart [EMAIL PROTECTED] writes: I hope this isn't a crummy mainboard but I can't seem to affect things by changing clock source (kernel 2.6.16 SLES10). I tried kernel command option clock=XXX where XXX in (cyclone,hpet,pmtmr,tsc,pit), no option was significantly better than the default. I believe that on machines where gettimeofday is really nice and fast, it doesn't require entry to the kernel at all; there's some hack that makes the clock readable from userspace. (Obviously a switch to kernel mode would set you back a lot of the cycles involved here.) So it's not so much the kernel that you need to teach as glibc. How you do that is beyond my expertise, but maybe that will help you google for the right thing ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] New to PostgreSQL, performance considerations
At 05:39 PM 12/14/2006, Alexander Staubo wrote: On Dec 14, 2006, at 20:28 , Ron wrote: Can you do runs with just CFLAGS=-O3 and just CFLAGS=-msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe as well ? All right. From my perspective, the effect of -O3 is significant, whereas architecture-related optimizations have no statistically significant effect. Is this opinion? Or have you rerun the tests using the flags I suggested? If so, can you post the results? If -O3 -msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe results in a 30-40% speed up over -O0, and -msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe results in a 5-10% speedup, then ~ 1/8 - 1/3 of the total possible speedup is due to arch specific optimizations. (testing -O3 in isolation in addition tests for independence of factors as well as showing what plain -O3 can accomplish.) Some might argue that a 5-10% speedup which represents 1/8 - 1/3 of the total speedup is significant... But enough speculating. I look forward to seeing your data. Ron Peacetree ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
On Dec 15, 2006, at 01:16 , Ron wrote: At 05:39 PM 12/14/2006, Alexander Staubo wrote: On Dec 14, 2006, at 20:28 , Ron wrote: Can you do runs with just CFLAGS=-O3 and just CFLAGS=-msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe as well ? All right. From my perspective, the effect of -O3 is significant, whereas architecture-related optimizations have no statistically significant effect. Is this opinion? Or have you rerun the tests using the flags I suggested? If so, can you post the results? Sorry, I neglected to include the pertinent graph: http://purefiction.net/paste/pgbench2.pdf The raw data: CFLAGS=-msse2 -mfpmath=sse -funroll-loops -m64 -march=opteron -pipe: 18480.899621 19977.162108 19640.562003 19823.585944 19500.293284 19964.383540 20228.664827 20515.766366 19956.431120 19740.795459 20184.551390 19984.907398 20457.260691 19771.395220 20159.225628 19907.248149 20197.580815 19947.498185 20209.450748 20088.501904 CFLAGS=-O3 23814.672315 26846.761905 27137.807960 26957.898233 27109.057570 26997.227925 27291.056939 27565.553643 27422.624323 27392.397185 27757.144967 27402.365372 27563.365421 27349.544685 27544.658154 26957.200592 27523.824623 27457.380654 27052.910082 24452.819263 CFLAGS=-O0 18440.181894 19207.882300 19894.432185 19635.625622 19876.858884 20032.597042 19683.597973 20370.19 19989.157881 20207.343510 19993.745956 20081.353580 20356.416424 20047.810017 20319.834190 19417.807528 19906.788454 20536.039929 19491.308046 20002.144230 CFLAGS=-O3 -msse2 -mfpmath=sse -funroll-loops -m64 -march=opteron - pipe 23830.358351 26162.203569 25569.091264 26762.755665 26590.822550 26864.908197 26608.029665 26796.116921 26323.742015 26692.576261 26878.859132 26106.770425 26328.371664 26755.595130 25488.304946 26635.527959 26377.485023 24817.590708 26480.245737 26223.427801 If -O3 -msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron - pipe results in a 30-40% speed up over -O0, and -msse2 - mfpmath=sse -funroll-loops -m64 - march=opteron -pipe results in a 5-10% speedup, then ~ 1/8 - 1/3 of the total possible speedup is due to arch specific optimizations. Unfortunately, I don't see a 5-10% speedup; -O0 and -msse2 ... are statistically identical. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] New to PostgreSQL, performance considerations
At 07:27 PM 12/14/2006, Alexander Staubo wrote: Sorry, I neglected to include the pertinent graph: http://purefiction.net/paste/pgbench2.pdf In fact, your graph suggests that using arch specific options in addition to -O3 actually =hurts= performance. ...that seems unexpected... Ron Peacetree ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] New to PostgreSQL, performance considerations
On Thu, 14 Dec 2006, Alexander Staubo wrote: Interestingly enough I see that PostgreSQL seems to be writing around 1MB/s during the pgbench run, even though I'm running pgbench in the -S mode. I haven't had the chance to look at the source yet; is it really only doing selects? I've noticed the same thing and have been meaning to figure out what the cause is. It's just doing a select in there; it's not even in a begin/end block. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
