[PERFORM] Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5
My best regards for all... Please. I need for an advice. I'm having a trouble, that puting others queries in wait state, becouse of ExclusiveLock granted by an Update that only update one row at each time. This update occurs into a function and this function are executed several times and concurrently. Below, query plan (explain): Nested Loop (cost=16.91..36.32 rows=1 width=75) - HashAggregate (cost=16.91..16.92 rows=1 width=4) - Index Scan using unq_customer_idx_msisdn on customer (cost=0.00..16.90 rows=1 width=4) Index Cond: ((msisdn)::text = '558796013980'::text) - Index Scan using pk_customer_rel_channel on customer_rel_channel (cost=0.00..19.39 rows=1 width=75) Index Cond: ((customer_rel_channel.id_customer = customer.id_customer) AND (customer_rel_channel.id_channel = 282)) But, the pg_locs shows: PIDRelationUserTransactionAccess ModeGranted Query StartQuery 22569customer_rel_channelpostgresExclusiveLock False 2013-01-10 15:54:09.308056-02UPDATE news.customer_rel_channel SET status = $1, source = $2 WHERE news.customer_rel_channel.id_channel = $3 AND news.customer_rel_channel.id_customer IN (SELECT id_customer FROM public.customer WHERE public.customer.msisdn = $4) I can't understand what happens here...This query can't be lock granted becouse another instance of this query already granted it. I can't understand why an update that modify one row only need an ExclusiveLock. Thanks a lot!! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Updates on one row causing ExclusiveLock on PostgreSQL 8.3.5
My best regards for all... Please. I need for an advice. I'm having a trouble, that puting others queries in wait state, becouse of ExclusiveLock granted by an Update that only update one row at each time. This update occurs into a function and this function are executed several times and concurrently. Below, query plan (explain): Nested Loop (cost=16.91..36.32 rows=1 width=75) - HashAggregate (cost=16.91..16.92 rows=1 width=4) - Index Scan using unq_customer_idx_msisdn on customer (cost=0.00..16.90 rows=1 width=4) Index Cond: ((msisdn)::text = '558796013980'::text) - Index Scan using pk_customer_rel_channel on customer_rel_channel (cost=0.00..19.39 rows=1 width=75) Index Cond: ((customer_rel_channel.id_customer = customer.id_customer) AND (customer_rel_channel.id_channel = 282)) But, the pg_locs shows: PIDRelationUserTransactionAccess ModeGranted Query StartQuery 22569customer_rel_channelpostgresExclusiveLock False 2013-01-10 15:54:09.308056-02UPDATE news.customer_rel_channel SET status = $1, source = $2 WHERE news.customer_rel_channel.id_channel = $3 AND news.customer_rel_channel.id_customer IN (SELECT id_customer FROM public.customer WHERE public.customer.msisdn = $4) -- 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] Slow query: bitmap scan troubles
Greg's book is awesome. It really gives a lot of informations/tips/whatever on performances. I mostly remember all the informations about hardware, OS, PostgreSQL configuration, and such. Not much on the EXPLAIN part. Arrived this morning :) http://www.pgcon.org/2010/audio/15%20The%20PostgreSQL%20Query% https://sites.google.com/site/robertmhaas/presentations http://momjian.us/main/presentations/internals.html http://www.dalibo.org/_media/understanding_explain.pdf Well that is my evenings occupied for the next week. Thank you kindly. - Phil -- 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] Slow query: bitmap scan troubles
That is very interesting indeed, these indexes are quite large! I will apply that patch and try it out this evening and let you know. Thank you very much everyone for your time, the support has been amazing. PS: Just looked at this thread on the archives page and realised I don't have my name in FROM: field, which is a misconfiguration of my email client, but figured I would leave it to prevent confusion, sorry about that. All the best, Philip Scott -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 05 December 2012 18:05 To: Jeff Janes Cc: postgre...@foo.me.uk; postgres performance list Subject: Re: [PERFORM] Slow query: bitmap scan troubles Jeff Janes jeff.ja...@gmail.com writes: I now see where the cost is coming from. In commit 21a39de5809 (first appearing in 9.2) the fudge factor cost estimate for large indexes was increased by about 10 fold, which really hits this index hard. This was fixed in commit bf01e34b556 Tweak genericcostestimate's fudge factor for index size, by changing it to use the log of the index size. But that commit probably won't be shipped until 9.3. Hm. To tell you the truth, in October I'd completely forgotten about the January patch, and was thinking that the 1/1 cost had a lot of history behind it. But if we never shipped it before 9.2 then of course that idea is false. Perhaps we should backpatch the log curve into 9.2 --- that would reduce the amount of differential between what 9.2 does and what previous branches do for large indexes. It would definitely be interesting to know if applying bf01e34b556 helps the OP's example. regards, tom lane -- 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] Slow query: bitmap scan troubles
I also wonder if increasing (say x10) of default_statistics_target or just doing ALTER TABLE SET STATISTICS for particular tables will help. It will make planned to produce more precise estimations. Do not forget ANALYZE afer changing it. Thanks Sergey, I will try this too. I think the bother here is that this statistics are pretty good (we do analyse regularly and default_statistics_target is already 1000), but once I start filtering the two tables the correlations alter quite a bit. I don't think there is that much that can be done about that :) - Phil -- 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] Slow query: bitmap scan troubles
Hi Jeff It kind of does. The expected speed is predicated on the number of rows being 200 fold higher. If the number of rows actually was that much higher, the two speeds might be closer together. That is why it would be interesting to see a more typical case where the actual number of rows is closer to the 2000 estimate. Ah, I see of course. Makes a lot of sense when you think about it. This has been quite an enlightening adventure into the guts of postgres for me :) But I am curious about how the cost estimate for the primary key look up is arrived at: ( Delt with in your next reply, thanks for figuring that out! I will certainly try the patch) I've heard good things about Greg Smith's book, but I don't know if it covers this particular thing. A copy is on its way, thank you. We are in the rather pleasant situation here in that we are willing to spend money on the box (up to a point, but quite a large point) to get it up to the spec so that it should hardly ever need to touch the disk, the trick is figuring out how to let our favourite database server know that. Well, that part is fairly easy. Make random_page_cost and seq_page_cost much smaller than their defaults. Like, 0.04 and 0.03, for example. Yes, I have been playing a lot with that it makes a lot of difference. When I tweak them down I end up getting a lot of nested loops instead of hash or merge joins and they are much faster (presumably we might have gotten a nested loop out of the planner if it could correctly estimate the low number of rows returned). I've got plenty of ammunition now to dig deeper, you guys have been invaluable. Cheers, Phil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow query: bitmap scan troubles
Hi guys (and girls) I've been banging my head over this for a few days now so if any of you kind souls could take a minute to take a look at this I would be eternally grateful. I have a pretty straightforward query that is very slow by default, and about 70 times faster when I set enable_bitmapscan=off. I would like to convince the planner to use my lovely indexes. The scenario is this; I have two tables, trade and position_effect. A trade is a deal we do with somebody to exchange something for something else. It has a time it was done, and is associated with a particular book for accounting purposes. A position effect records changes to our position (e.g. how much we have) of an particular asset. One trade can many position effects (usually only 1,2 or 3) For example, I do a trade of USD/GBP and I get two position effects, +1000 GBP and -1200USD SCHEMA: --- The actual schema is a bit more complicated but I will put the important parts here (if you think it important, the full schema for the two tables is here: http://pastebin.com/6Y52aDFL): CREATE TABLE trade ( id bigserial NOT NULL, time_executed timestamp with time zone NOT NULL, id_book integer NOT NULL, CONSTRAINT cons_trade_primary_key PRIMARY KEY (id), ) CREATE INDEX idx_trade_id_book ON trade USING btree (id_book, time_executed, id); CREATE TABLE position_effect ( id bigserial NOT NULL, id_trade bigint NOT NULL, id_asset integer NOT NULL, quantity double precision NOT NULL, CONSTRAINT cons_pe_primary_key PRIMARY KEY (id_trade, id_asset), ) SETUP: -- These tables are relatively large (~100 million rows in position effect). The box is a pretty beastly affair with 512Mb of ram and 4x10 2Ghz cores. The postgres configuration is here: http://pastebin.com/48uyiak7 I am using a 64bit postgresql 9.2.1, hand compiled on a RedHat 6.2 box. QUERY: -- What I want to do is sum all of the position effects, for a particular asset while joined to the trade table to filter for the time it was executed and the book it was traded into: SELECT sum(position_effect.quantity) FROM trade, position_effect WHERE trade.id = position_effect.id_trade AND position_effect.id_asset = 1837 AND trade.time_executed = '2012-10-28 00:00:00' AND trade.id_book = 41 In this case there are only 11 rows that need to be summed. If I just let postgres do its thing, that query takes 5000ms (Which when multiplied over many books and assets gets very slow). I think this is because it is bitmapping the whole position_effect table which is very large. If I disable bitmap scans: set enable_bitmapscan = off; The query takes 43ms, and properly uses the indexes I have set up. Slow version with bitmapscan enabled: http://explain.depesz.com/s/6I7 Fast version with bitmapscan disabled: http://explain.depesz.com/s/4MWG -- 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] Slow query: bitmap scan troubles
Bad form to reply to yourself I know but just check-reading that for the third time I noticed two mistakes - The box has 128Gb of ram, not 512Mb - There is an additional constraint on the position_effect table (though I don't think it matters for this discussion): CONSTRAINT cons_pe_trade FOREIGN KEY (id_trade) REFERENCES trade (id) Sorry to clog your inboxes further! Regards, Philip -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of postgre...@foo.me.uk Sent: 04 December 2012 15:07 To: pgsql-performance@postgresql.org Subject: [PERFORM] Slow query: bitmap scan troubles Hi guys (and girls) I've been banging my head over this for a few days now so if any of you kind souls could take a minute to take a look at this I would be eternally grateful. I have a pretty straightforward query that is very slow by default, and about 70 times faster when I set enable_bitmapscan=off. I would like to convince the planner to use my lovely indexes. The scenario is this; I have two tables, trade and position_effect. A trade is a deal we do with somebody to exchange something for something else. It has a time it was done, and is associated with a particular book for accounting purposes. A position effect records changes to our position (e.g. how much we have) of an particular asset. One trade can many position effects (usually only 1,2 or 3) For example, I do a trade of USD/GBP and I get two position effects, +1000 GBP and -1200USD SCHEMA: --- The actual schema is a bit more complicated but I will put the important parts here (if you think it important, the full schema for the two tables is here: http://pastebin.com/6Y52aDFL): CREATE TABLE trade ( id bigserial NOT NULL, time_executed timestamp with time zone NOT NULL, id_book integer NOT NULL, CONSTRAINT cons_trade_primary_key PRIMARY KEY (id), ) CREATE INDEX idx_trade_id_book ON trade USING btree (id_book, time_executed, id); CREATE TABLE position_effect ( id bigserial NOT NULL, id_trade bigint NOT NULL, id_asset integer NOT NULL, quantity double precision NOT NULL, CONSTRAINT cons_pe_primary_key PRIMARY KEY (id_trade, id_asset), ) SETUP: -- These tables are relatively large (~100 million rows in position effect). The box is a pretty beastly affair with 512Mb of ram and 4x10 2Ghz cores. The postgres configuration is here: http://pastebin.com/48uyiak7 I am using a 64bit postgresql 9.2.1, hand compiled on a RedHat 6.2 box. QUERY: -- What I want to do is sum all of the position effects, for a particular asset while joined to the trade table to filter for the time it was executed and the book it was traded into: SELECT sum(position_effect.quantity) FROM trade, position_effect WHERE trade.id = position_effect.id_trade AND position_effect.id_asset = 1837 AND trade.time_executed = '2012-10-28 00:00:00' AND trade.id_book = 41 In this case there are only 11 rows that need to be summed. If I just let postgres do its thing, that query takes 5000ms (Which when multiplied over many books and assets gets very slow). I think this is because it is bitmapping the whole position_effect table which is very large. If I disable bitmap scans: set enable_bitmapscan = off; The query takes 43ms, and properly uses the indexes I have set up. Slow version with bitmapscan enabled: http://explain.depesz.com/s/6I7 Fast version with bitmapscan disabled: http://explain.depesz.com/s/4MWG -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Slow query: bitmap scan troubles
But the row estimates are not precise at the top of the join/filter. It thinks there will 2120 rows, but there are only 11. Ah... I didn't spot that one... Yes, you are right there - this is probably a slightly atypical query of this sort actually, 2012 is a pretty good guess. On Claudio's suggestion I have found lots more things to read up on and am eagerly awaiting 6pm when I can bring the DB down and start tweaking. The effective_work_mem setting is going from 6Gb-88Gb which I think will make quite a difference. I still can't quite wrap around my head why accessing an index is expected to use more disk access than doing a bitmap scan of the table itself, but I guess it does make a bit of sense if postgres assumes the table is more likely to be cached. It's all quite, quite fascinating :) I'll let you know how it goes. - Phil -- 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] Slow query: bitmap scan troubles
But the row estimates are not precise at the top of the join/filter. It thinks there will 2120 rows, but there are only 11. So it seems like there is a negative correlation between the two tables which is not recognized. Yes, you are right there. I am only just beginning to understand how to parse these explain reports.. As I mentioned above, I probably picked a bad example to run that query on 11 is an unusually low number of results to get back, a few thousand would be more normal. Though that doesn't account for the 70x difference between the speed of the two queries in actuality given a pretty similar expected speed (does it?). It does go some way to explaining why a bad choice of plan was made. Is there some nice bit of literature somewhere that explains what sort of costs are associated with the different types of lookup? I have found bits and bobs online but I still don't have a really clear idea in my head what the difference is between a bitmap index scan and index only scan is, though I can sort of guess I don't see why one would be considered more likely to use the disk than the other. On the 'slow' query (with the better predicted score) First, make sure caching isn't interfering with your results. Run each query several times. If that is not how the production system works (running the same query over and over) then you want to model the cold cache, not the hot one. But in any case, the posted explains indicates that all buffers were cached. We are in the rather pleasant situation here in that we are willing to spend money on the box (up to a point, but quite a large point) to get it up to the spec so that it should hardly ever need to touch the disk, the trick is figuring out how to let our favourite database server know that. I've just discovered pgtune and am having some fun with that too. Cheers, Phil -- 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] Slow query: bitmap scan troubles
Ah, okay - my reasoning was there's a big fancy-pants raid array behind it that makes disk operations faster relative to CPU ones. I'll test it and see if it actually makes any difference. -Original Message- From: Claudio Freire [mailto:klaussfre...@gmail.com] Sent: 04 December 2012 18:33 To: Philip Scott Cc: postgre...@foo.me.uk; postgres performance list Subject: Re: [PERFORM] Slow query: bitmap scan troubles On Tue, Dec 4, 2012 at 3:31 PM, Philip Scott psc...@foo.me.uk wrote: r_p_c 2- 1 (s_p_c 1-0.5): Is this really necessary? (looks like a no-op, unless your CPU is slow) -- 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] Slow query: bitmap scan troubles
Ah okay, thanks. I knew I could set various things but not effective_work_mem (I tried reloading the edited config file but it didn't seem to pick it up) From: Vitalii Tymchyshyn [mailto:tiv...@gmail.com] Sent: 04 December 2012 18:51 To: postgre...@foo.me.uk Cc: postgres performance list Subject: Re: [PERFORM] Slow query: bitmap scan troubles Well, you don't need to put anything down. Most settings that change planner decisions can be tuned on per-quey basis by issuing set commands in given session. This should not affect other queries more than it is needed to run query in the way planner chooses. Best regards, Vitalii Tymchyshyn 2012/12/4 postgre...@foo.me.uk But the row estimates are not precise at the top of the join/filter. It thinks there will 2120 rows, but there are only 11. Ah... I didn't spot that one... Yes, you are right there - this is probably a slightly atypical query of this sort actually, 2012 is a pretty good guess. On Claudio's suggestion I have found lots more things to read up on and am eagerly awaiting 6pm when I can bring the DB down and start tweaking. The effective_work_mem setting is going from 6Gb-88Gb which I think will make quite a difference. I still can't quite wrap around my head why accessing an index is expected to use more disk access than doing a bitmap scan of the table itself, but I guess it does make a bit of sense if postgres assumes the table is more likely to be cached. It's all quite, quite fascinating :) I'll let you know how it goes. - Phil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Loading the entire DB into RAM
If memory serves me correctly I have seen several posts about this in the past. I'll try to recall highlights. 1. Create a md in linux sufficiently large enough to handle the data set you are wanting to store. 2. Create a HD based copy somewhere as your permanent storage mechanism. 3. Start up your PostgreSQL instance with the MD as the data store 4. Load your data to the MD instance. 5. Figure out how you will change indexes _and_ ensure that your disk storage is consistent with your MD instance. I haven't done so, but it would be interesting to have a secondary database somewhere that is your primary storage. It needn't be especially powerful, or even available. It serves as the place to generate your indexing data. You could then use SLONY to propogate the data to the MD production system. Of course, if you are updating your system that resides in ram, you should be thinking the other way. Have SLONY replicate changes to the other, permanent storage, system. Either way you do it, I can't think of an out of the box method to doing it. Somehow one has to transfer data from permanent storage to the md instance, and, likewise, back to permanent storage. Out of curiosity, what are you using as the search engine? Charles A. Landemaine wrote: I have a web server with PostgreSQL and RHEL. It hosts a search engine, and each time some one makes a query, it uses the HDD Raid array. The DB is not very big, it is less than a GB. I plan to add more RAM anyway. What I'd like to do is find out how to keep the whole DB in RAM so that each time some one does a query, it doesn't use the HDD. Is it possible, if so, how? Thanks, Charles. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Storing Digital Video
Rodrigo Madera wrote: I am concerned with performance issues involving the storage of DV on a database. I though of some options, which would be the most advised for speed? 1) Pack N frames inside a container and store the container to the db. 2) Store each frame in a separate record in the table frames. 3) (type something here) Thanks for the help, My experience has been that this is a very bad idea. Many people want to store all sorts of data in a database such as email messages, pictures, etc... The idea of a relational database is to perform queries against data. If you are needing to just store data then store it on a disk and use the database as the indexer of the data. Keep in mind the larger the database the slower some operations become. Unless you are operating on the frame data (which you either store as blobs or hex-encoded data) I'd recommend you store the data on a hard drive and let the database store meta data about the video such as path information, run time, author, etc... We do this on an application storing close to a million images and the performance is impressive. 1. we don't have to do any sort of data manipulation storing the data in or retrieving the data out of the database. 2. our database is compact and extremely fast - it is using the database for what it was designed for - relational queries. My $0.02 Rodrigo ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] ALTER TABLE SET TABLESPACE and pg_toast
We're storing tif images in a table as bytea. We were running low on our primary space and moved several tables, including the one with the images, to a second tablespace using ALTER TABLE SET TABLESPACE. This moved quite cleaned out quite a bit of space on the original tablespace, but not as much as it should have. It does not appear that the corresponding pg_toast tables were moved. So, my questions are: 1) Is there a way to move pg_toast tables to new tablespaces (or at least assure that new ones are created there)? 2) Also, is there a good way to determine which pg_toast tables are associated with any particular table and column? Thank you for your help, Martin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 8.1 iss
My most humble apologies to the pg development team (pg_lets?). I took Greg Stark's advice and set: shared_buffers = 1 # was 5 work_mem = 1048576# 1Gb - was 16384 Also, I noticed that the EXPLAIN ANALYZE consistently thought reads would take longer than they actually did, so I decreased random_page_cost down to 1 (the server has a SATA Raid at level 10). Queries that previously seemed to stall out are still a little slow but nothing like before. And I'm seeing a more normal balance of CPU and disk i/o while a query is running instead of the high-cpu-low-disk-read situation I was seeing before. Concurrency is way up. I tried a couple of interim sizes for work_mem and so far, the larger the better (the server has 16Gb). I'll test a little larger size this evening and see what it does. Yes, I've read the warning that this is per process. Kudos to you Greg, thanks Luke for your comment (though it seems to disagree with my experience). Also to Dennis, there were not drastic changes in the plan between 8.0 and 8.1, it was just the actual execution times. Martin PostgreSQL [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING count(*) 1; This is a pretty good example of the place where 8.1 seems to be quite broken. ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 8.1 iss
SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING count(*) 1; This is a pretty good example of the place where 8.1 seems to be quite broken. I understand that this query will want to do a full table scan (even through v_barcode is indexed). And the table is largish, at 34 million rows. In the 8.0 world, this took around 4 minutes. With 8.1beta3, this has run for 30 minutes (as I began to write this) and is still going strong. And it behaves differently than I'd expect. Top shows the postmaster process running the query as using up 99.9 percent of one CPU, while the i/o wait time never gets above 3%. vmstat shows the block out (bo) number quite high, 15 to 20 thousand, which also surprises me. block in is from 0 to about 2500. iostat shows 15,000 to 20,000 blocks written every 5 seconds, while it shows 0 blocks read. There is no other significant process running on the box. (Apache is running but is not being used here a 3:00a.m. on Sunday). This is a dual Opteron box with 16 Gb memory and a 3ware SATA raid runing 64bit SUSE. Something seems badly wrong. As I post this, the query is approaching an hour of run time. I've listed an explain of the query and my non-default conf parameters below. Please advise on anything I should change or try, or on any information I can provide that could help diagnose this. GroupAggregate (cost=9899282.83..10285434.26 rows=223858 width=15) Filter: (count(*) 1) - Sort (cost=9899282.83..9994841.31 rows=38223392 width=15) Sort Key: v_barcode - Seq Scan on lead (cost=0.00..1950947.92 rows=38223392 width=15) shared_buffers = 5 work_mem = 16384 maintenance_work_mem = 16384 max_fsm_pages = 10 max_fsm_relations = 5000 wal_buffers = 32 checkpoint_segments = 32 effective_cache_size = 5 default_statistics_target = 50 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 8.1beta3 performance
I'm seeing some other little oddities in the beta as well. I'm watching an ALTER TABLE ADD COLUMN right now that has been running almost two hours. I stopped it the first time at 1 hour; I suppose I'll let it go this time and see if it ever completes. The table is about 150K rows. Top, vmstat, and iostat show almost no cpu or disk activity (1 to 3%) - it's as if it just went to sleep. Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Jon Brisbin [EMAIL PROTECTED] writes: I have a stock FreeBSD 5.4 box that I put 8.1 on last night. I ran pgbench against it and my tps dropped from ~300tps in 8.0.3 to 20tps in 8.1. That's right. 20. No changes in any system configuration. You sure about that last? These numbers are kind of consistent with the idea that fsync is off in the 8.0 database and on in the 8.1 database. Using the same test case you mention (pgbench -s 10, -c 25 -t 1000), I find that 8.1 is a bit faster than 8.0, eg 8.1 fsync off: tps = 89.831186 (including connections establishing) tps = 89.865065 (excluding connections establishing) 8.1 fsync on: tps = 74.865078 (including connections establishing) tps = 74.889066 (excluding connections establishing) 8.0 fsync off: tps = 80.271338 (including connections establishing) tps = 80.302054 (excluding connections establishing) 8.0 fsync on: tps = 67.405708 (including connections establishing) tps = 67.426546 (excluding connections establishing) (All database parameters are defaults except fsync.) These numbers are with assert-enabled builds, on a cheap PC whose drive lies about write-complete, so they're not very representative of the real world I suppose. But I'm sure not seeing any 10x degradation. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
[PERFORM] 8.1beta3 performance
We're running 8.1beta3 on one server and are having ridiculous performance issues. This is a 2 cpu Opteron box and both processors are staying at 98 or 99% utilization processing not-that-complex queries. Prior to the upgrade, our I/O wait time was about 60% and cpu utilization rarely got very high, now I/O wait time is at or near zero. I'm planning to go back to 8.0 tonight or tomorrow night but thought I'd check the pqsql-performance prophets before I gave it up. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Simple query: how to optimize
Postgres is somewhat speed-challenged on aggregate functions. The most-repeated work-around would be something like: SELECT u.user_id, (SELECT activity_date FROM user_activity WHERE user_activity.user_id = pp_users.user_id AND user_activity_type_id = 7 ORDER BY activity_date DESC LIMIT 1) FROM pp_users u WHERE u.userstatus_id 4 AND age(u.joined_date) interval '30 days' (code above is untested) I've read that aggregate functions are improved in the 8.1 code. I'm running 8.1beta3 on one machine but haven't experimented to verify the claimed improvements. Martin Nickel Collin Peters [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have two tables, one is called 'users' the other is 'user_activity'. The 'users' table simply contains the users in the system there is about 30,000 rows. The 'user_activity' table stores the activities the user has taken. This table has about 430,000 rows and also (notably) has a column which tracks the type of activity. 90% of the table is type 7 which indicates the user logged into the system. I am trying to write a simple query that returns the last time each user logged into the system. This is how the query looks at the moment: SELECT u.user_id, MAX(ua.activity_date) FROM pp_users u LEFT OUTER JOIN user_activity ua ON (u.user_id = ua.user_id AND ua.user_activity_type_id = 7) WHERE u.userstatus_id 4 AND age(u.joined_date) interval '30 days' GROUP BY u.user_id The above query takes about 5 seconds but I'm wondering how it can be optimized. When the query is formatted as above it does use an index on the user_id column of the user_activity table... but the cost is huge (cost=0.00..1396700.80). I have tried formatting it another way with a sub-query but it takes about the same amount to completed: SELECT u.user_id, ua.last FROM pp_users u LEFT OUTER JOIN (SELECT max(activity_date) as last, user_id FROM user_activity WHERE user_activity_type_id = 7 GROUP BY user_id) as ua ON (u.user_id = ua.user_id) WHERE u.userstatus_id 4 AND age(u.joined_date) interval '30 days' Can anybody offer any pointers on this scenario? Regards, Collin ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] How much memory?
Is there a rule-of-thumb for determining the amount of system memory a database requres (other than all you can afford)? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] What gets cached?
Thank each of you for your replies. I'm just beginning to understand the scope of my opportunities. Someone (I apologize, I forgot who) recently posted this query: SELECT oid::regclass, reltuples, relpages FROM pg_class ORDER BY 3 DESC Though the application is a relatively low-volume TP system, it is structured a lot like a data warehouse with one primary table that everything else hangs off. What the query above shows is that my largest table, at 34 million rows, takes almost 1.4 million pages or 10+ Gb if my math is good. The same table has 14 indexes, totaling another 12Gb. All this is running on a box with 4Gb of memory. So what I believe I see happening is that almost every query is clearing out memory to load the particular index it needs. Hence my first queries are the fastest observation at the beginning of this thread. There are certainly design improvements to be done, but I've already started the process of getting the memory increased on our production db server. We are btw running 8.1 beta 3. Steinar H. Gunderson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Mon, Oct 24, 2005 at 11:09:55AM -0400, Alex Turner wrote: Just to play devils advocate here for as second, but if we have an algorithm that is substational better than just plain old LRU, which is what I believe the kernel is going to use to cache pages (I'm no kernel hacker), then why don't we apply that and have a significantly larger page cache a la Oracle? There have (AFAIK) been reports of setting huge amounts of shared_buffers (close to the total amount of RAM) performing much better in 8.1 than in earlier versions, so this might actually be okay these days. I haven't heard of anybody reporting increase setting such values, though. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
