Re: [PERFORM] 7.3 vs 7.4 performance
Oops! [EMAIL PROTECTED] (Orion Henry) was seen spray-painting on a wall: > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% > slower than 7.3.4. Is this common knowledge or am I just unlucky with > my query/data selection? That seems unusual; the opposite seems more typical in view of there being some substantial improvements to the query optimizer. Have you tried doing EXPLAIN ANALYZE on the queries on both sides? There would doubtless be interest in figuring out what is breaking down... > Things of note that might matter: the machine is a dual Opteron > 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from > the Fedora distro and the 7.4.1 was the PGDG package. The database > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram. > > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel > has more than DOUBLED the speed of all my Postgres queries over the > 2.4. =) I did some heavy-transaction-oriented tests recently on somewhat heftier quad-Xeon hardware, and found little difference between 2.4 and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9. Now, I'm quite sure my load was rather different from yours, but I find the claim of doubling of speed rather surprising. -- (format nil "[EMAIL PROTECTED]" "aa454" "freenet.carleton.ca") http://www.ntlug.org/~cbbrowne/spiritual.html Failure is not an option. It comes bundled with your Microsoft product. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] 7.3 vs 7.4 performance
Orion, > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% > slower than 7.3.4. Is this common knowledge or am I just unlucky with > my query/data selection? No, it's not common knowledge. It should be the other way around. Perhaps it's the queries you picked? Even so . feel free to post individual EXPLAIN ANALYZEs to the list. > Things of note that might matter: the machine is a dual Opteron 1.4GHz > running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora > distro and the 7.4.1 was the PGDG package. The database is 3.5 Gigs > with 10 millions rows and the machine had 1 Gig or ram. I'm wondering if we need specific compile-time switches for Opteron. I know we got Opteron code tweaks in the last version, but am not sure if a --with is required to activate them. > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has > more than DOUBLED the speed of all my Postgres queries over the 2.4. =) Keen. Waiting for upgrades -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 7.3 vs 7.4 performance
I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30% slower than 7.3.4. Is this common knowledge or am I just unlucky with my query/data selection? Things of note that might matter: the machine is a dual Opteron 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora distro and the 7.4.1 was the PGDG package. The database is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram. Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has more than DOUBLED the speed of all my Postgres queries over the 2.4. =) -- Orion Henry <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [PERFORM] select is not using index?
On Wed, 2004-02-04 at 14:55, Mark Harrison wrote: > testdb=# \d bigtable > Table "public.bigtable" > Column | Type | Modifiers > -+-+--- > id | bigint | not null > typeid | integer | not null > reposid | integer | not null > Indexes: bigtable_id_key unique btree (id) > testdb=# explain select * from bigtable where id = 123; Your column is a bigint but 123 defaults to type int. Indexes aren't used when there's a type mismatch. Use an explicit cast or quote it: select * from bigtable where id = 123::bigint; Or select * from bigtable where id = '123'; Corey ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] select is not using index?
We are suddenly getting slow queries on a particular table. Explain shows a sequential scan. We have "vacuum analyze" ed the table. Any hints? Many TIA! Mark testdb=# \d bigtable Table "public.bigtable" Column | Type | Modifiers -+-+--- id | bigint | not null typeid | integer | not null reposid | integer | not null Indexes: bigtable_id_key unique btree (id) Foreign Key constraints: type FOREIGN KEY (typeid) REFERENCES types(typeid) ON UPDATE NO ACTION ON DELETE NO ACTION, repository FOREIGN KEY (reposid) REFERENCES repositories(reposid) ON UPDATE NO ACTION ON DELETE NO ACTION testdb=# select count(1) from bigtable; count - 3056831 (1 row) testdb=# explain select * from bigtable where id = 123; QUERY PLAN --- Seq Scan on bigtable (cost=0.00..6.00 rows=1 width=16) Filter: (id = 123) (2 rows) testdb=# vacuum verbose analyze bigtable; INFO: --Relation public.bigtable-- INFO: Pages 19200: Changed 0, Empty 0; Tup 3056831: Vac 0, Keep 0, UnUsed 207009. Total CPU 1.03s/0.24u sec elapsed 9.32 sec. INFO: Analyzing public.bigtable VACUUM testdb=# explain select * from bigtable where id = 123; QUERY PLAN --- Seq Scan on bigtable (cost=0.00..57410.39 rows=1 width=16) Filter: (id = 123) (2 rows) -- Mark Harrison Pixar Animation Studios ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] cache whole data in RAM
David Teran wrote: Hi, we are trying to speed up a database which has about 3 GB of data. The server has 8 GB RAM and we wonder how we can ensure that the whole DB is read into RAM. We hope that this will speed up some queries. regards David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Upon bootup, automatically run "SELECT * FROM xyz" on every table in your database. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Compile Vs RPMs
On Tue, 3 Feb 2004, Christopher Browne wrote:
> [EMAIL PROTECTED] ("Anjan Dave") writes:
> > I would like to know whether there are any significant performance
> > advantages of compiling (say, 7.4) on your platform (being RH7.3, 8,
> > and 9.0, and Fedora especially) versus getting the relevant binaries
> > (rpm) from the postgresql site? Hardware is Intel XEON (various
> > speeds, upto 2.8GHz, single/dual/quad configuration).
>
> Some Linux distribution makers make grand claims of such advantages,
> but it is not evident that this is much better than superstition.
>
> You are certainly NOT going to see GCC generating MMX code
> automagically that would lead to PostgreSQL becoming 8 times faster.
>
> Indeed, in database work, it is quite likely that you will find things
> to be largely I/O bound, with CPU usage being a very much secondary
> factor.
>
> I did some relative benchmarking between compiling PostgreSQL on GCC
> versus IBM's PPC compilers a while back; did not see differences that
> could be _clearly_ discerned as separate from "observational noise."
>
> You should expect find that adding RAM, or adding a better disk
> controller would provide discernable differences in performance. It
> is much less clear that custom compiling will have any substantial
> effect on I/O-bound processing.
I would add that the primary reason for compiling versus using RPMs is to
take advantage of some compile time option having to do with block size,
or using a patch to try and test a system that has found a new corner case
where postgresql is having issues performing well, like the vacuum page
delay patch for fixing the issue with disk bandwidth saturation. If
you've got a machine grinding to its knees under certain loads, and have a
test box to test it on, and the test box shows better performance, it
might be better to patch the live server on the off hours if it will keep
the thing up and running during the day.
In that way, performance differences are very real, but because you are
doing something you can't do with factory rpms. Of course, building
custom rpms isn't that hard to do, so if you had a lot of boxes that
needed a patched flavor of postgresql, you could still run from rpms and
have the custom patch.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [pgsql-advocacy] MySQL+InnoDB vs. PostgreSQL test?
> Seriously, I am tired of this kind of question. You gotta get bold > enough to stand up in a "meeting" like that, say "guy's, you can ask me > how this compares to Oracle ... but if you're seriously asking me how > this compares to MySQL, call me again when you've done your homework". Can they call you at the unemployment office? -- Mike Nolan ---(end of broadcast)--- TIP 3: 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
