Thread: Re: Very slow joins
Btw. It looks like this issue: http://archives.postgresql.org/pgsql-performance/2007-09/msg00374.php In my case the CPU usage is low too (3%) but IO wait is high (95%). I'm using Postgresql 8.3.
MS wrote: > Btw. It looks like this issue: > http://archives.postgresql.org/pgsql-performance/2007-09/msg00374.php > > In my case the CPU usage is low too (3%) but IO wait is high (95%). > > I'm using Postgresql 8.3. > > for more info on disk iowaits, use `iostat -x 5` (5 means sample every 5 seconds), and ignore the first sample as its the average system system boot. this will give you drive by drive and flie system by file system details of disk IO. The exact details shown vary by operating system. note, on many linux distributions, iostat is part of the sysstat package, which often isn't installed by default especailly on a 'minimum' install... on RH/Fedora/Centos type systems, try `yum install sysstat` to install it. I never cease to be amazed at how many times people have these monster CPUs, like dual quad core 3Ghz processors, with 16GB or whatever of ram, and then try and run a database off a single 7200 rpm desktop SATA drive. at work our production databases often run on dozens of 10000 or 15000 rpm drives, organized as raid1+0's.
> I never cease to be amazed at how many times people have these monster > CPUs, like dual quad core 3Ghz processors, with 16GB or whatever of ram, > and then try and run a database off a single 7200 rpm desktop SATA > drive. at work our production databases often run on dozens of 10000 > or 15000 rpm drives, organized as raid1+0's. Yeah. I just took the effort and copied all data from those tables to mysql and run an equivalent query - all took around 1 minute to execute. So either me or postgres is seriously broken. ;) I'm going back to mysql. :(
On Fri, Jul 24, 2009 at 4:40 PM, MS<fretka1990@gmail.com> wrote: > >> I never cease to be amazed at how many times people have these monster >> CPUs, like dual quad core 3Ghz processors, with 16GB or whatever of ram, >> and then try and run a database off a single 7200 rpm desktop SATA >> drive. at work our production databases often run on dozens of 10000 >> or 15000 rpm drives, organized as raid1+0's. > > > Yeah. I just took the effort and copied all data from those tables to > mysql and run an equivalent query - all took around 1 minute to > execute. > So either me or postgres is seriously broken. ;) I'm going back to > mysql. :( can we see an explain analyze at least? merlin
> can we see an explain analyze at least? > Hi, Well, it won't be necessary - I mean it looks just like the explain I sent in my first post. BUT I found the real cause of my problem - the "fk2" field from my example had not only an index, but it was also a foreign key to another table. I believe the update took so long because pgsql was checking if the changes don't break the referential integrity. When I dropped the FK constraint (and index too - just in case) the update took around 3 minutes which is acceptable. So - problem solved, postgres good. ;) But isn't there a way to make some bulk operations without having to drop indexes/FKs? Something that would work like: begin transaction + forget about RI make some lenghty operation (update/delete...) if RI is OK then commit; else rollback Thanks, MS
On 25 Jul 2009, at 11:36, MS wrote: >> can we see an explain analyze at least? >> > > Hi, > Well, it won't be necessary - I mean it looks just like the explain I > sent in my first post. What first post? The only thing I can find is a reference in a message by you from yesterday, to a two-year old post that you claim is about the same problem. Though it's possible that it is the same problem, you don't provide any data to back that up. The message you referred to was about a one-of-a-kind problem with communications to the client and had nothing to do with performance on the server; is that indeed what you're seeing? In that case you should check your network infrastructure for problems. Usually server performance problems are due to problems with tuning parameters or outdated statistics. Those issues can usually be solved easily. Without posting an EXPLAIN ANALYSE people here can only guess what your problem is. > BUT I found the real cause of my problem - the "fk2" field from my > example had not only an index, but it was also a foreign key to > another table. > I believe the update took so long because pgsql was checking if the > changes don't break the referential integrity. > When I dropped the FK constraint (and index too - just in case) the > update took around 3 minutes which is acceptable. > So - problem solved, postgres good. ;) But isn't there a way to make > some bulk operations without having to drop indexes/FKs? > Something that would work like: > > begin transaction + forget about RI > make some lenghty operation (update/delete...) > if RI is OK then commit; else rollback That seems unlikely to be the cause. From the above it seems much more likely that you're suffering from a bad query plan instead, but you don't provide any details. Disabling referential integrity is a bad thing to do, and very rarely necessary. But we don't know what you're trying to do, except that you're updating some records that apparently have a foreign key reference. It would also help to know what version of PostgreSQL this is and on what hardware and setup you're running into this issue. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a6af5d410132049512701!
On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote: > I believe the update took so long because pgsql was checking if the > changes don't break the referential integrity. > So - problem solved, postgres good. ;) But isn't there a way to make > some bulk operations without having to drop indexes/FKs? I've never had the need to use this, but I believe this works using the "SET CONSTRAINTS" command[1]; e.g. I can do: CREATE TABLE foo ( id INTEGER PRIMARY KEY ); CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE ); INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (1); the following will now fail: BEGIN; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; but the following is OK: BEGIN; SET CONSTRAINTS bar_id_fkey DEFERRED; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; Unfortunatly only foreign key constraints are affected by this setting, but I believe there are plans to extend this further. -- Sam http://samason.me.uk/ http://www.postgresql.org/docs/current/static/sql-set-constraints.html
On Sat, Jul 25, 2009 at 8:45 AM, Sam Mason<sam@samason.me.uk> wrote: > On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote: >> I believe the update took so long because pgsql was checking if the >> changes don't break the referential integrity. >> So - problem solved, postgres good. ;) But isn't there a way to make >> some bulk operations without having to drop indexes/FKs? > > I've never had the need to use this, but I believe this works using the > "SET CONSTRAINTS" command[1]; e.g. I can do: > > CREATE TABLE foo ( id INTEGER PRIMARY KEY ); > CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE ); > > INSERT INTO foo VALUES (1); > INSERT INTO bar VALUES (1); > > the following will now fail: > > BEGIN; > INSERT INTO bar VALUES (2); > INSERT INTO foo VALUES (2); > COMMIT; > > but the following is OK: > > BEGIN; > SET CONSTRAINTS bar_id_fkey DEFERRED; > INSERT INTO bar VALUES (2); > INSERT INTO foo VALUES (2); > COMMIT; > > Unfortunatly only foreign key constraints are affected by this setting, > but I believe there are plans to extend this further. You can also disable triggers completely: begin; alter table foo disable trigger all; <do stuff> alter table foo enable trigger all; commit; of course, if you do this the data is never checked at all, so you have to be super careful with it.... merlin
Alban Hertroys wrote: > On 25 Jul 2009, at 11:36, MS wrote: > >>> can we see an explain analyze at least? >>> >> >> Hi, >> Well, it won't be necessary - I mean it looks just like the explain I >> sent in my first post. > > What first post? The only thing I can find is a reference in a message > by you from yesterday, to a two-year old post that you claim is about > the same problem. Though it's possible that it is the same problem, > you don't provide any data to back that up. Yeah I'm confused too. The first post in this thread that I recieved was the same one you mention and began with a "Re:" in the subject line as if it wasn't the first message, but I can find no sign of a message prior to it. Was this a cross-post where the thread started in another group? Or did something go awry with the listserv and the first post or three get lost? Eric
> What first post? The only thing I can find is a reference in a message > by you from yesterday, to a two-year old post that you claim is about > the same problem. Though it's possible that it is the same problem, > you don't provide any data to back that up. Strange - you can see the full thread here: http://groups.google.pl/group/pgsql.general/browse_thread/thread/6c4ea8356327276c I post via Google, maybe that's the cause? > The message you referred to was about a one-of-a-kind problem with > communications to the client and had nothing to do with performance on > the server; is that indeed what you're seeing? In that case you should > check your network infrastructure for problems. No, I actually meant that the CPU usage was low during my query. On the other hand IO wait was very high so the low CPU usage was kind of normal. > Usually server performance problems are due to problems with tuning > parameters or outdated statistics. Those issues can usually be solved > easily. Well, maybe - I started to use postgres only recently, so maybe I'm doing some obvious mistakes. My database was filled incrementally by a shell script - I don't know if that alone lets postgres collect all necessary stats. Maybe an implicit analyze is necessary? I also tried to vacuum/vacuum full my tables before running my queries but it took too long so I had to break it. > Without posting an EXPLAIN ANALYSE people here can only guess what > your problem is. I'm posting another "explain analyze" below. I've run my query with "explain analyze", but forgot to save it :( I think I won't be able to run my queries again, because they took around 30-60 minutes and almost killed my server (was almost completely unresponsive during the query, because of 90% IO wait). > > BUT I found the real cause of my problem - the "fk2" field from my > > example had not only an index, but it was also a foreign key to > > another table. > That seems unlikely to be the cause. From the above it seems much more > likely that you're suffering from a bad query plan instead, but you > don't provide any details. I just tried to create a test with similar data - here is what it looks like: (it should work if you just paste it in some Test db) -- ------------- generate test tables + data drop table if exists article, keyword, article_keyword, tochange, sums cascade; CREATE TABLE "article" ( "id" serial NOT NULL PRIMARY KEY, "content" varchar(255) NULL, "ip" inet NULL, "has_comments" bool not null ) ; CREATE TABLE "keyword" ( "id" serial NOT NULL PRIMARY KEY, "keyword" varchar(40) NOT NULL UNIQUE, "articles" integer NOT NULL ) ; CREATE TABLE "article_keyword" ( "id" serial NOT NULL PRIMARY KEY, "article_id" integer NOT NULL REFERENCES "article" ("id") DEFERRABLE INITIALLY DEFERRED, "keyword_id" integer NOT NULL REFERENCES "keyword" ("id") DEFERRABLE INITIALLY DEFERRED, "votes_yes" integer NOT NULL, "votes_no" integer NOT NULL ) ; CREATE INDEX "article_keyword_keyword_id" ON "article_keyword" ("keyword_id"); insert into article(content, ip, has_comments) values ('some article', '123.121.121.223', true); insert into keyword select nextval('keyword_id_seq'), md5(to_char(i, '9999999999999')), 0 from generate_series(1,2000000) as i; insert into article_keyword select nextval('article_keyword_id_seq'), 1, k.id, 0, 0 from generate_series(1,2000000) as i join keyword k on k.keyword=md5(to_char(i, '9999999999999')) join generate_series(1,5) as times on true ; create table tochange ( fromid int not null primary key, toid int not null ); insert into tochange select k1.id, k2.id from generate_series(1,200000) as i join keyword k1 on k1.keyword=md5(to_char(i, '9999999999999')) join keyword k2 on k2.keyword=md5(to_char(i+200000, '9999999999999')) ; create table sums ( id int not null primary key, sum int ); -- ----------------- now my queries: -- replace fromid's with toid's update article_keyword set keyword_id=tc.toid from tochange tc where keyword_id=tc.fromid ; -- delete unused keywords delete from article_keyword where id in ( select k.id from keyword k left join article_keyword ak on k.id=ak.keyword_id where ak.keyword_id is null ) ; -- recalculate sums - in how many articles is a keyword used? insert into sums select keyword_id, count(*) from article_keyword group by keyword_id; update keyword k set articles=s.sum from sums s where k.id=s.id; ---------------------- The problem is that I can't reproduce this slow behaviour with this test case. :( The tables are almost identical - only the article table is bigger in reality (it has around million rows) When I run "explain update" (first update from the test case) it prints this now: Merge Join (cost=5.14..53436.13 rows=3636710 width=26) Merge Cond: (tc.fromid = article_keyword.keyword_id) -> Index Scan using tochange_pkey on tochange tc (cost=0.00..2830.26 rows=100000 width=8) -> Index Scan using article_keyword_keyword_id on article_keyword (cost=0.00..148216.29 rows=5000040 width=26) (4 rows) When I disable enable_mergejoin I have this plan: Hash Join (cost=6160.91..274121.21 rows=5500010 width=26) Hash Cond: (article_keyword.keyword_id = tc.fromid) -> Seq Scan on article_keyword (cost=0.00..87353.10 rows=5500010 width=26) -> Hash (cost=2882.74..2882.74 rows=199774 width=8) -> Seq Scan on tochange tc (cost=0.00..2882.74 rows=199774 width=8) (5 rows) This is the plan I was getting with my original query which took so long. Also the second Update was very slow. Deletes, and inserts were quite fast. > It would also help to know what version of PostgreSQL this is and on > what hardware and setup you're running into this issue. I tried both postgress 8.3, and 8.4. Now I use 8.4. I have a standard config + pgtune which added the following entries: default_statistics_target = 50 maintenance_work_mem = 28MB constraint_exclusion = on checkpoint_completion_target = 0.9 effective_cache_size = 352MB work_mem = 2816kB wal_buffers = 8MB checkpoint_segments = 16 shared_buffers = 112MB max_connections = 80 The server is Intel(R) Core(TM)2 CPU E8400 @3.00GHz, 4GB ram, 2x SATA disks in Raid1 Thanks, MS
> postgres collect all necessary stats. Maybe an implicit analyze is > necessary? Should be: "explicit analyze". > > > BUT I found the real cause of my problem - the "fk2" field from my > > > example had not only an index, but it was also a foreign key to > > > another table. > > That seems unlikely to be the cause. It's just what I saw. First I tried with all the FKs but had to break the queries because they took too long. Then I dropped the FK and the query run in a couple of minutes. Thanks, MS