Re: [PERFORM] Swapping in 7.4.3
This is normal. My personal workstation has been up for 16 days, and it shows 65 megs used for swap. The linux kernel looks for things that haven't been accessed in quite a while and tosses them into swap to free up the memory for other uses. This isn't PostgreSQL's fault, or anything elses. It's how a typical Unix kernel works. I.e. you're seeing a problem that simply isn't there. Actually it (and other OSes) does slightly better than that. It _copies_ the least recently used pages into swap, but leaves them in memory. Then when there really is a need to swap stuff out there is no need to actually write to swap because it's already been done, and conversely if those pages are wanted then they don't have to be read from disk because they were never removed from memory. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Insert are going slower ...
Josh, Le jeudi 15 Juillet 2004 20:09, Josh Berkus a écrit : I suggest you check this first. Check the performance tuning guide.. http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php That is a starters. As Josh suggested, increase checkpoint segments if you have disk space. Correspondingly WAL disk space requirements go up as well. Well, not if he has fsync=off. But having fsync off is a very bad idea. You do realize, Herve', that if you lose power on that machine you'll most likely have to restore from backup? Hum ... it's only for speed aspect ... I was using postgresql with this option since 7.01 ... and for me fsync=on was so slow ... Is it really no time consuming for the system to bring it ON now with v7.4.3 ?? Tell me ... -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Wierd issues
I lost the email that had the fix for this and now I need it again can someone or tom let me know what the fix was, I cant find it in any of my emails or archived on the internet This is what I got Two servers, one debian, one fedora Debain dual 3ghz, 1 gig ram, ide, PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Fedora: Dual 3ghz, 1 gig ram, scsi, PostgreSQL 7.3.4-RH on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1) Both have same databases, Both have had vacume full ran on them. Both doing the same query Select * from vpopmail; The vpopmail is a view, this is the view View vpopmail Column | Type | Modifiers ---++--- pw_name | character varying(32) | pw_domain | character varying(64) | pw_passwd | character varying | pw_uid | integer | pw_gid | integer | pw_gecos | character varying | pw_dir | character varying(160) | pw_shell | character varying(20) | View definition: SELECT ea.email_name AS pw_name, ea.domain AS pw_domain, get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) AS pw_passwd, 0 AS pw_uid, 0 AS pw_gid, ''::varchar AS pw_gecos, ei.directory AS pw_dir, ei.quota AS pw_shell FROM email_addresses ea, email_info ei, users u, user_resources ur WHERE (ea.user_resource_id = ei.user_resource_id) AND (get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) IS NOT NULL)) AND (ur.id = ei.user_resource_id)) AND (u.id = ur.user_id)) AND (NOT (EXISTS (SELECT forwarding.email_id FROM forwarding WHERE (forwarding.email_id = ea.id); Both are set to the same buffers and everything this is the execution time: Debian: Total runtime: 35594.81 msec Fedora: Total runtime: 2279869.08 msec Huge difference as you can see here are the pastes of the stuff Debain: user_acl=# explain analyze SELECT count(*) from vpopmail; NOTICE: QUERY PLAN: Aggregate (cost=438231.94..438231.94 rows=1 width=20) (actual time=35594.67..35594.67 rows=1 loops=1) - Hash Join (cost=434592.51..438142.51 rows=35774 width=20) (actual time=34319.24..35537.11 rows=70613 loops=1) - Seq Scan on email_info ei (cost=0.00..1721.40 rows=71640 width=4) (actual time=0.04..95.13 rows=71689 loops=1) - Hash (cost=434328.07..434328.07 rows=35776 width=16) (actual time=34319.00..34319.00 rows=0 loops=1) - Hash Join (cost=430582.53..434328.07 rows=35776 width=16) (actual time=2372.45..34207.21 rows=70613 loops=1) - Seq Scan on users u (cost=0.00..1938.51 rows=71283 width=4) (actual time=0.81..30119.58 rows=70809 loops=1) - Hash (cost=430333.64..430333.64 rows=35956 width=12) (actual time=2371.51..2371.51 rows=0 loops=1) - Hash Join (cost=2425.62..430333.64 rows=35956 width=12) (actual time=176.73..2271.14 rows=71470 loops=1) - Seq Scan on email_addresses ea (cost=0.00..426393.25 rows=35956 width=4) (actual time=0.06..627.49 rows=71473 loops=1) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.88 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=71960) - Hash (cost=1148.37..1148.37 rows=71637 width=8) (actual time=176.38..176.38 rows=0 loops=1) - Seq Scan on user_resources ur (cost=0.00..1148.37 rows=71637 width=8) (actual time=0.03..82.21 rows=71686 loops=1) Total runtime: 35594.81 msec EXPLAIN And for fedora its Aggregate (cost=416775.52..416775.52 rows=1 width=20) (actual time=2279868.57..2279868.58 rows=1 loops=1) - Hash Join (cost=413853.79..416686.09 rows=35772 width=20) (actual time=2279271.26..2279803.91 rows=70841 loops=1) Hash Cond: (outer.user_resource_id = inner.id) - Seq Scan on email_info ei (cost=0.00..1666.07 rows=71907 width=4) (actual time=8.12..171.10 rows=71907 loops=1) - Hash (cost=413764.36..413764.36 rows=35772 width=16) (actual time=2279263.03..2279263.03 rows=0 loops=1) - Hash Join (cost=410712.87..413764.36 rows=35772 width=16) (actual time=993.90..2279008.72 rows=70841 loops=1) Hash Cond: (outer.id = inner.user_id) - Seq Scan on users u (cost=0.00..1888.85 rows=71548 width=4) (actual time=18.38..2277152.51 rows=71028 loops=1) Filter: (get_pwd(username, '127.0.0.1'::character varying, '101'::character varying, 'MD5'::character varying) IS NOT NULL) - Hash (cost=410622.99..410622.99 rows=35952 width=12) (actual time=975.40..975.40 rows=0 loops=1) - Hash Join (cost=408346.51..410622.99 rows=35952 width=12) (actual time=507.52..905.91 rows=71697 loops=1) Hash Cond: (outer.id = inner.user_resource_id) - Seq Scan on user_resources ur (cost=0.00..1108.04 rows=71904 width=8) (actual time=0.05..95.65 rows=71904 loops=1) - Hash (cost=408256.29..408256.29 rows=36091 width=4) (actual time=507.33..507.33 rows=0 loops=1) - Seq Scan on email_addresses ea (cost=0.00..408256.29 rows=36091 width=4) (actual time=0.15..432.83 rows=71700 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.63 rows=1 width=4)
[PERFORM] same plan, different time
Hi, I have a query, which runs fast for one id (query 1) and slow for other id (query 2) though both plans and cost are same except these two qeries return different number of rows. explain analyze SELECT * FROM user U LEFT JOIN user_timestamps T USING (user_id), user_alias A WHERE U.user_id = A.user_id AND A.domain_id=7551070; \g QUERY PLAN --- Merge Join (cost=234.22..61015.98 rows=12 width=238) (actual time=7.73..7.73 rows=0 loops=1) Merge Cond: (outer.user_id = inner.user_id) - Merge Join (cost=0.00..58585.67 rows=909864 width=180) (actual time=0.07..0.07 rows=1 loops=1) Merge Cond: (outer.user_id = inner.user_id) - Index Scan using user_pkey on user u (cost=0.00..29714.99 rows=909864 width=156) (actual time=0.04..0.04 rows=1 loops=1) - Index Scan using user_timestamps_uid_idx on user_timestamps t (cost=0.00..16006.05 rows=706896 width=24) (actual time=0.02..0.02 rows=1 loops=1) - Sort (cost=234.22..234.25 rows=12 width=58) (actual time=7.65..7.65 rows=0 loops=1) Sort Key: a.user_id - Seq Scan on user_alias a (cost=0.00..234.00 rows=12 width=58) (actual time=7.61..7.61 rows=0 loops=1) Filter: (domain_id = 7551070) Total runtime: 7.96 msec (11 rows) explain analyze SELECT * FROM user U LEFT JOIN user_timestamps T USING (user_id), user_alias A WHERE U.user_id = A.user_id AND A.domain_id=2005921193; \g QUERY PLAN Merge Join (cost=247.92..61035.28 rows=332 width=238) (actual time=94511.70..95127.94 rows=493 loops=1) Merge Cond: (outer.user_id = inner.user_id) - Merge Join (cost=0.00..58585.67 rows=909864 width=180) (actual time=6.43..93591.06 rows=897655 loops=1) Merge Cond: (outer.user_id = inner.user_id) - Index Scan using user_pkey on user u (cost=0.00..29714.99 rows=909864 width=156) (actual time=6.29..55634.85 rows=897655 loops=1) - Index Scan using user_timestamps_uid_idx on user_timestamps t (cost=0.00..16006.05 rows=706896 width=24) (actual time=0.10..20331.13 rows=700466 loops=1) - Sort (cost=247.92..248.75 rows=332 width=58) (actual time=10.76..11.17 rows=493 loops=1) Sort Key: a.user_id - Seq Scan on user_alias a (cost=0.00..234.00 rows=332 width=58) (actual time=7.43..9.86 rows=493 loops=1) Filter: (domain_id = 2005921193) Total runtime: 95128.74 msec (11 rows) I also know if I change the order of 2nd query, it will run much faster: explain analyze SELECT * FROM (user_alias A JOIN user U USING (user_id) ) LEFT JOIN user_timestamps T USING (user_id) WHERE A.domain_id=2005921193; \g QUERY PLAN -- Nested Loop (cost=0.00..2302.31 rows=332 width=238) (actual time=15.32..256.54 rows=493 loops=1) - Nested Loop (cost=0.00..1263.43 rows=332 width=214) (actual time=15.17..130.58 rows=493 loops=1) - Seq Scan on user_alias a (cost=0.00..234.00 rows=332 width=58) (actual time=15.04..21.01 rows=493 loops=1) Filter: (domain_id = 2005921193) - Index Scan using user_pkey on user u (cost=0.00..3.08 rows=1 width=156) (actual time=0.17..0.17 rows=1 loops=493) Index Cond: (outer.user_id = u.user_id) - Index Scan using user_timestamps_uid_idx on user_timestamps t (cost=0.00..3.11 rows=1 width=24) (actual time=0.16..0.23 rows=1 loops=493) Index Cond: (outer.user_id = t.user_id) Total runtime: 257.79 msec (9 rows) user with 911932 rows user_id - PK user_timestamps with 708851 rows user_id - FK with index user_alias with 9689 rows user_id - FK with index domain_id - no index on this column My questions are: 1. Why 1st Merge Join in 2nd query gets actual rows=897655 while 1st Merge Join in 1st query is actual rows=1? If I know the answer, I will understand: Why 1st Merge Join in 2nd query took so longer time than 1st Merge Join in 1st query? 2. Why PG optimzer is not smart enough to use 3rd (nested Loop) plan? Thanks, __ Do you Yahoo!? Vote for the stars of Yahoo!'s next ad campaign! http://advision.webevents.yahoo.com/yahoo/votelifeengine/ ---(end of
Re: [PERFORM] extrem bad performance
The database grows very slowly. The main load comes from SELECT's and not from INSERT's or UPDATE's, but the performance gets slower day by day... I have no idea where to search for the speed break! Lets start with an example. Please send us an EXPLAIN ANALYZE of a couple of the poorly performing queries. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] hardware raid suggestions
We're looking into getting an Adaptec 2200S or the Megaraid 320 2x which have better processors, and hopefully better performance. We feel that the use of the AIC7930 as the CPU on the ZCR just doesn't cut it and a faster raid controller would work better. Does anyone out there have any experience with these cards with postgresql and linux? If so, would you be willing to share your experiences and possibly give a recommendation? I have worked with at least four major name brands of scsi and ide raid controllers and so far the one I have found to be generally the most featured and fastest is the ICP Vortex controllers (http://www.icp-vortex.com/). It is also more expensive than the others but has been worth the cost IMHO. It has a command line utility to measure disk performance and I believe the source code for it is available. I have measured over 200 MB/s reads off these controllers on 3u disk array units. I'm sure I could have gotten more with additional tuning. Fred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Scaling with lazy index updates
Pg Performers, This might be a out of the ordinary question, or perhaps I have been out of the loop for a while but does PostgreSQL (or any other database) have support for lazy index updates. What I mean by lazy index updates is index updating which occur at a regular interval rather than per transaction. I have found that inserts and updates tend to slow down when the database gets really big. I think it is likely an effect of updating indexes when the insert or update occurs. Looking forward to feedback and possibly direction on my lazy index update question. TIA, Fred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] same plan, different time
Litao Wu [EMAIL PROTECTED] writes: SELECT * FROM user U LEFT JOIN user_timestamps T USING (user_id), user_alias A WHERE U.user_id = A.user_id AND A.domain_id=7551070; Ick. Try changing the join order, perhaps SELECT * FROM (user U JOIN user_alias A ON (U.user_id = A.user_id)) LEFT JOIN user_timestamps T USING (user_id) WHERE A.domain_id=7551070; As you have it, the entire LEFT JOIN has to be formed first, and the useful restriction clause only gets applied later. The fact that the case with 7551070 finishes quickly is just blind luck --- the slow case is much more representative. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
