[PERFORM] Slow query postgres 8.3
Hi, I am trying to tune a query that is taking too much time on a large dataset (postgres 8.3). SELECT DISTINCT role_user.project_id AS projectId, sfuser.username AS adminUsername, sfuser.full_name AS adminFullName FROM role_operation role_operation, role role, sfuser sfuser, role_user role_user WHERE role_operation.role_id=role.id AND role.id=role_user.role_id AND role_user.user_id=sfuser.id AND role_operation.object_type_id='SfMain.Project' AND role_operation.operation_category='admin' AND role_operation.operation_name='admin' ORDER BY adminFullName ASC It has the following query plan: QUERY PLAN --- Unique (cost=1218.57..1221.26 rows=269 width=35) (actual time=16700.332..17212.849 rows=30136 loops=1) -> Sort (cost=1218.57..1219.24 rows=269 width=35) (actual time=16700.306..16885.972 rows=41737 loops=1) Sort Key: sfuser.full_name, role_user.project_id, sfuser.username Sort Method: quicksort Memory: 4812kB -> Nested Loop (cost=0.00..1207.71 rows=269 width=35) (actual time=71.173..15788.798 rows=41737 loops=1) -> Nested Loop (cost=0.00..1118.22 rows=269 width=18) (actual time=65.550..12440.383 rows=41737 loops=1) -> Nested Loop (cost=0.00..256.91 rows=41 width=18) (actual time=19.312..7150.925 rows=6108 loops=1) -> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..85.15 rows=41 width=9) (actual time=19.196..2561.765 rows=6108 loops=1) Index Cond: (((object_type_id)::text = 'SfMain.Project'::text) AND ((operation_category)::text = 'admin'::text) AND ((operation_name)::text = 'admin'::text)) -> Index Scan using role_pk on role (cost=0.00..4.18 rows=1 width=9) (actual time=0.727..0.732 rows=1 loops=6108) Index Cond: ((role.id)::text = (role_operation.role_id)::text) -> Index Scan using role_user_proj_idx on role_user (cost=0.00..20.84 rows=13 width=27) (actual time=0.301..0.795 rows=7 loops=6108) Index Cond: ((role_user.role_id)::text = (role_operation.role_id)::text) -> Index Scan using sfuser_pk on sfuser (cost=0.00..0.32 rows=1 width=35) (actual time=0.056..0.062 rows=1 loops=41737) Index Cond: ((sfuser.id)::text = (role_user.user_id)::text) Total runtime: 17343.185 ms (16 rows) I have tried adding an index on role_operation.role_id but it didn't seem to help or changing the query to: SELECT role_user.project_id AS projectId, sfuser.username AS adminUsername, sfuser.full_name AS adminFullName FROM sfuser sfuser, role_user role_user WHERE role_user.role_id in (select role_operation.role_id from role_operation where role_operation.object_type_id= 'SfMain.Project' AND role_operation.operation_category='admin' AND role_operation.operation_name='admin') AND role_user.user_id=sfuser.id ORDER BY adminFullName ASC None of this seemed to improve the performance. Does anyone have a suggestion? Thanks a lot, Anne
[PERFORM]
http%3A%2F%2Fwww%2Eproductionsoundmixer%2Eorg%2Fimages%2Famw%2Ephp -- 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] Background fsck
Achilleas Mantzios wrote: How are you gonna show up in the FreeBSD-* mailing list when you are still on 6.2? Psst! - I came just here. Don't tell them. -- 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] Why it is using/not using index scan?
On Thu, Mar 31, 2011 at 12:26 PM, Laszlo Nagy wrote:
> For this query:
>
> select pp.id,pp.product_id,pp.selling_site_id,pp.asin
> from product_price pp
> where
> (pp.asin is not null and pp.asin<>'')
> and (pp.upload_status_id<>1)
> and pp.selling_site_id in (8,7,35,6,9)
> and (pp.last_od < 'now'::timestamp - '1 week'::interval )
> limit 5000
>
> Query plan is:
>
> "Limit (cost=9182.41..77384.80 rows=3290 width=35)"
> " -> Bitmap Heap Scan on product_price pp (cost=9182.41..77384.80
> rows=3290 width=35)"
> " Recheck Cond: ((last_od < '2011-03-24 13:05:09.540025'::timestamp
> without time zone) AND (selling_site_id = ANY ('{8,7,35,6,9}'::bigint[])))"
> " Filter: ((asin IS NOT NULL) AND (asin <> ''::text) AND
> (upload_status_id <> 1))"
> " -> Bitmap Index Scan on idx_product_price_last_od_ss
> (cost=0.00..9181.59 rows=24666 width=0)"
> " Index Cond: ((last_od < '2011-03-24
> 13:05:09.540025'::timestamp without time zone) AND (selling_site_id = ANY
> ('{8,7,35,6,9}'::bigint[])))"
>
> For this query:
>
> select pp.id,pp.product_id,pp.selling_site_id,pp.asin
> from product_price pp
> where
> (pp.asin is not null and pp.asin<>'')
> and (pp.upload_status_id<>1)
> and pp.selling_site_id in (8,7,35,6,9)
> and (pp.last_od + '1 week'::interval < 'now'::timestamp )
> limit 5000
>
> Query plan is:
>
> "Limit (cost=0.00..13890.67 rows=5000 width=35)"
> " -> Seq Scan on product_price pp (cost=0.00..485889.97 rows=174898
> width=35)"
> " Filter: ((asin IS NOT NULL) AND (asin <> ''::text) AND
> (upload_status_id <> 1) AND ((last_od + '7 days'::interval) < '2011-03-31
> 13:06:17.460013'::timestamp without time zone) AND (selling_site_id = ANY
> ('{8,7,35,6,9}'::bigint[])))"
>
>
> The only difference is this: instead of (pp.last_od < 'now'::timestamp - '1
> week'::interval ) I have used (pp.last_od + '1 week'::interval <
> 'now'::timestamp )
>
> First query with index scan opens in 440msec. The second query with seq scan
> opens in about 22 seconds. So the first one is about 50x faster.
>
> My concern is that we are working on a huge set of applications that use
> thousands of different queries on a database. There are programs that we
> wrote years ago. The database structure continuously changing. We are adding
> new indexes and columns, and of course we are upgrading PostgreSQL when a
> new stable version comes out. There are cases when a change in a table
> affects 500+ queries in 50+ programs. I really did not think that I have to
> be THAT CAREFUL with writing conditions in SQL. Do I really have to manually
> analyze all those queries and "correct" conditions like this?
>
> If so, then at least I would like to know if there is a documentation or
> wiki page where I can learn about "how not to write conditions". I just
> figured out that I need to put constant expressions on one side of any
> comparison, if possible. But probably there are other rules I wouldn't think
> of.
>
> Might it be possible to change the optimizer so that it tries to rally
> constant expressions in the first place? That cannot be bad, right?
It's pretty well understood by database developers that indexable
expressions are such that the expression being compared is in the same
form being used in 'create index'. Even if you did not understand
that, simple trial and error gave the answer immediately using the
standard tools (explain,timing etc) provided by the database. If you
are concerned, just start logging slow queries
(log_min_duration_statement) and fix them if the sql is bad.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] postgresql benchmark
Database Test Suite H o w are you all ?? I am new in this group. I have windows 7 as OS and i installed postgresql 9.0 i want a to do some tests so i need bench mark to test some workloads? i need the same as Database Test Suite http://sourceforge.net/projects/osdldbt/ but for windows can any one help me?? Rad..
Re: [PERFORM] Background fsck
Friday, April 8, 2011, 1:52:03 PM you wrote: > Scott Marlowe wrote: >> If I may ask, how often does it crash? And have you tried updating >> the firmware of the controller and / or the driver in the OS? >> > It happens once per two or three months, or so, taking the average. The > firmware is beta as of > January this year, advised to use by their technical support. Do you run any software to periodically check the array status? Or are there any other regular tasks involving 'tw_cli'-calls? I had this effect while trying to examine the SMART-status of the attached drives on a 9690-8E, leading to spurious controller resets due to timeouts, also under high load. Disabling the task solved the problem, and no further resets occured. -- Jochen Erwied | home: joc...@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: j...@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erw...@vodafone.de +49-173-5404164 -- 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] Background fsck
Στις Friday 08 April 2011 14:53:58 ο/η Ireneusz Pluta έγραψε: > > My general plan for now is to migrate all services from this machine to the > new ones and refresh it > completely for use with less critical services. But it is not a task for just > a few days so the That's a pain. Migrating from 7.1 to 8.2 was a pain for me as well. But OTOH, you should upgrade to FreeBSD 8.2 since it is a production system. Imagine your 3ware card was ok, but the driver has the problem. How are you gonna show up in the FreeBSD-* mailing list when you are still on 6.2? BTW, when you make the final transition to 8.2, DO NOT upgrade in place, make a new system and migrate the data. Or just upgrade system in place but pkg_deinstall all your ports before the upgrade. portupgrade will not make it through. > failures have their chances to happen. While bearing this, I wanted to check > if I could ease my life > a little with background checks. > > > -- Achilleas Mantzios -- 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] Background fsck
Scott Marlowe wrote: If I may ask, how often does it crash? And have you tried updating the firmware of the controller and / or the driver in the OS? It happens once per two or three months, or so, taking the average. The firmware is beta as of January this year, advised to use by their technical support. Kinda off topic here, but as asked, take a look at the details: /c0 Driver Version = 3.60.04.006 /c0 Model = 9650SE-16ML /c0 Available Memory = 224MB /c0 Firmware Version = FE9X 4.10.00.016 /c0 Bios Version = BE9X 4.08.00.002 /c0 Boot Loader Version = BL9X 3.08.00.001 There is a newer beta firmware available from LSI support page, but changelog does not indicate anything which might be related to this problem. OS is 6.2-RELEASE FreeBSD The driver also should be the newest for this platform. What's more, this is already a new controller. It replaced the previous one because of exactly the same persisting problem. I think tech support people not knowing a solution just buy some time for them and say "flash this beta firmware maybe it helps" or "replace your hardware". The controller always hangs with the following: Send AEN (code, time): 0031h, 04/06/2011 21:56:45 Synchronize host/controller time (EC:0x31, SK=0x00, ASC=0x00, ASCQ=0x00, SEV=04, Type=0x71) Assert:0 from Command Task File:cacheSegMgr.cpp Line:290 and this is usually at the time of IO peaks, when dumps get transferred to another system. My general plan for now is to migrate all services from this machine to the new ones and refresh it completely for use with less critical services. But it is not a task for just a few days so the failures have their chances to happen. While bearing this, I wanted to check if I could ease my life a little with background checks. -- 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] Background fsck
Scott Marlowe wrote: If I may ask, how often does it crash? And have you tried updating the firmware of the controller and / or the driver in the OS? It happens once per two or three months, or so, taking the average. The firmware is beta as of January this year, advised to use by their technical support. Kinda off topic here, but as asked, take a look at the details: /c0 Driver Version = 3.60.04.006 /c0 Model = 9650SE-16ML /c0 Available Memory = 224MB /c0 Firmware Version = FE9X 4.10.00.016 /c0 Bios Version = BE9X 4.08.00.002 /c0 Boot Loader Version = BL9X 3.08.00.001 There is a newer beta firmware available from LSI support page, but changelog does not indicate anything which might be related to this problem. OS is 6.2-RELEASE FreeBSD The driver also should be the newest for this platform. What's more, this is already a new controller. It replaced the previous one because of exactly the same persisting problem. I think tech support people not knowing a solution just buy some time for them and say "flash this beta firmware maybe it helps" or "replace your hardware". The controller always hangs with the following: Send AEN (code, time): 0031h, 04/06/2011 21:56:45 Synchronize host/controller time (EC:0x31, SK=0x00, ASC=0x00, ASCQ=0x00, SEV=04, Type=0x71) Assert:0 from Command Task File:cacheSegMgr.cpp Line:290 and this is usually at the time of IO peaks, when dumps get transferred to another system. My general plan for now is to migrate all services from this machine to the new ones and refresh it completely for less critical services. But it is not a task for just a few days so the failures have their chances to happen. While bearing this, I wanted to check if I could ease my life a little with background checks. -- 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] Background fsck
On 08/04/2011 07:55, Ireneusz Pluta wrote: Achilleas Mantzios wrote: In anyway, having FreeBSD to fsck, (background or not) should not happen. And the problem becomes bigger when cheap SATA drives will cheat about their write cache being flushed to the disk. So in the common case with cheap hardware, it is wise to have a UPS connected and being monitored by the system. It's not lack of UPS. Power issues are taken care of here. It's a buggy 3ware controller which hangs the machine ocassionally and the only way to have it come back is to power cycle, hard reset is not enough. So just to summarize your position: you think it is ok to run a database on a buggy disk controller but are afraid of problems with normal OS utilities? :) You are of course free to do whatever you want but it seems odd :) -- 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] Background fsck
Greg Smith wrote: The soft update code used in FreeBSD makes sure that there's no damage to the filesystem that PostgreSQL can't recover from. Once the WAL is replayed after a crash, the database is consistent. The main purpose of the background fsck is to find "orphaned" space, things that the filesystem incorrectly remembers the state of in regards to whether it was allocated and used. In theory, there's no reason that can't happen in the background, concurrent with normal database activity. In practice, background fsck is such an infrequently used piece of code that it's developed a bit of a reputation for being buggier than average. It's really hard to test it, filesystem code is complicated, and the sort of inconsistent data you get after a hard crash is often really surprising. I wouldn't be too concerned about the database integrity, but there is a small risk that background fsck will run into something unexpected and panic. And that's a problem you're much less likely to hit using the more stable regular fsck code; thus the recommendations by some to avoid it. Thank you all for your responses. Greg, given your opinion, and these few raised issues found on the net, I think I better stay with background fsck disabled. What I was primarily concerned about, was long time waiting in front of console, looking at lazy fsck messages and nervously confirming that disk LEDs are still blinking. It's even harder with remote KVM, where LED's view is not available. But my personal comfort is not a priority, anyway, so I let foreground fsck doing its job for as much time as it needs. As I said in my another response, the problem initially comes from the machine hanging and having to be manually power cycled. There is already a significant downtinme before the recycle has a chance to happen. So yet another fourty minutes of fsck does not matter too much from the point of view of service availability. fsck runtime duration could be shortened if I used smaller inode density for the filesystem. I think that makes much sense for a filesystem fully decicated to a postgres data cluster, specifically if I have not so many but large tables, which I rather do. The system in question has: df -hi | grep -E 'base|ifree' Filesystem SizeUsed Avail Capacity iused ifree %iused Mounted on /dev/da1p3 3.0T1.7T1.0T63%485k 392M0% /pg/base (will I ever have even tens of millions of tables?) I reserved less inodes in a newer, bigger system: FilesystemSizeUsed Avail Capacity iused ifree %iused Mounted on /dev/mfid0p8 12T4.8T6.0T45%217k 49M0% /pg/base or even less in yet newer one: FilesystemSizeUsed Avail Capacity iused ifree %iused Mounted on /dev/mfid0p1 12T3.6T7.4T33%202k 3.4M6% /pg/base (ups, maybe too aggressive here?) When I forced a power drop on these two other systems, to check how they survive, fsck duration on them was substantially less. In the inode density context, let me ask you yet another question. Does tuning it in this way have any other, good or bad, significant impact on system performance? Irek. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
