[PERFORM] Query plan changes after pg_dump / pg_restore
Greetings all, I am continously encountering an issue with query plans that changes after a pg_dump / pg_restore operation has been performed. On the production database, PostGre refuses to use the defined indexes in several queries however once the database has been dumped and restored either on another server or on the same database server it suddenly magically changes the query plan to utilize the indexes thereby cutting the query cost down to 10% of the original. Databases are running on the same PostGre v7.3.9 on RH Enterprise 3.1 server. A VACUUM FULL runs regularly once a day and VACUUM ANALYZE every other hour. The data in the tables affected by this query doesn't change very often Even doing a manual VACUUM FULL, VACUUM ANALYZE or REINDEX before the query is run on the production database changes nothing. Have tried to drop the indexes completely and re-create them as well, all to no avail. If the queries are run with SET ENABLE_SEQSCAN TO OFF, the live database uses the correct indexes as expected. Have placed an export of the query, query plan etc. online at: http://213.173.234.215:8080/plan.htm in order to ensure it's still readable. For the plans, the key tables are marked with bold. Any insight into why PostGre behaves this way as well as a possible solution (other than performing a pg_dump / pg_restore on the live database) would be very much appreciated? Cheers Jona ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
On Thu, 9 Jun 2005 [EMAIL PROTECTED] wrote: I am continously encountering an issue with query plans that changes after a pg_dump / pg_restore operation has been performed. Have placed an export of the query, query plan etc. online at: http://213.173.234.215:8080/plan.htm in order to ensure it's still readable. There is not a major difference in time, so pg is at least not way off (225ms vs. 280ms). The estimated cost is however not very related to the runtime (117 vs 1389). What you have not showed is if the database is properly tuned. The output of SHOW ALL; could help explain a lot together with info of how much memory your computer have. The first thing that comes to mind to me is that you probably have not tuned shared_buffers and effective_cache_size properly (SHOW ALL would tell). -- /Dennis Björklund ---(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] Query plan changes after pg_dump / pg_restore
Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size. shared_buffers: 13384 effective_cache_size: 4000 server memory: 2GB Please note, the databases are on the same server, it's merely 2 instances of the same database in order to figure out why there's a difference in the query plan before and after a dump / restore. What worries me is that the plan is different, in the bad plan it makes a seq scan of a table with 6.5k recods in (fairly silly) and another of a table with 50k records in (plan stupid). In the good plan it uses the indexes available as expected. The estimated cost is obviously way off in the live database, even though statistics etc should be up to date. Any insight into this? Appreciate the help here... Cheers Jona Dennis Bjorklund wrote: On Thu, 9 Jun 2005 [EMAIL PROTECTED] wrote: I am continously encountering an issue with query plans that changes after a pg_dump / pg_restore operation has been performed. Have placed an export of the query, query plan etc. online at: http://213.173.234.215:8080/plan.htm in order to ensure it's still readable. There is not a major difference in time, so pg is at least not way off (225ms vs. 280ms). The estimated cost is however not very related to the runtime (117 vs 1389). What you have not showed is if the database is properly tuned. The output of SHOW ALL; could help explain a lot together with info of how much memory your computer have. The first thing that comes to mind to me is that you probably have not tuned shared_buffers and effective_cache_size properly (SHOW ALL would tell).
Re: [PERFORM] Recommendations for configuring a 200 GB database
Kevin Grittner wrote: The manager of the DBA team is reluctant to change both the OS and the DBMS at the same time, so unless I can make a strong case for why it is important to run postgresql under Linux, we will be running this on Windows. Currently, there are two Java-based middle tier processes running on each central database server, one for the replication and one for the web. We expect to keep it that way, so the database needs to play well with these processes. Well, there's a lot more experience running PG on various *nix systems and a lot more help available. Also, I don't think performance on Windows is as good as on Linux/*BSD yet. Against switching OS is the fact that you presumably don't have the skills in-house for it, and the hardware was chosen for Windows compatibility/performance. Speaking of which, what sort of hardware are we talking about? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size. shared_buffers: 13384 effective_cache_size: 4000 server memory: 2GB effective_cache_size should be 10-100x larger perhaps... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
Thanks... have notified our sys admin of that so he can make the correct changes. It still doesn't explain the difference in query plans though? I mean, it's the same database server the two instances of the same database is running on. One instance (the live) just insists on doing the seq scan of the 50k records in Price_Tbl and the 6.5k records in SCT2SubCatType_Tbl. Seems weird Cheers Jona Christopher Kings-Lynne wrote: Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size. shared_buffers: 13384 effective_cache_size: 4000 server memory: 2GB effective_cache_size should be 10-100x larger perhaps... Chris ---(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] Query plan changes after pg_dump / pg_restore
Is effective_cache_size set the same on the test and live? Jona wrote: Thanks... have notified our sys admin of that so he can make the correct changes. It still doesn't explain the difference in query plans though? I mean, it's the same database server the two instances of the same database is running on. One instance (the live) just insists on doing the seq scan of the 50k records in Price_Tbl and the 6.5k records in SCT2SubCatType_Tbl. Seems weird Cheers Jona Christopher Kings-Lynne wrote: Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size. shared_buffers: 13384 effective_cache_size: 4000 server memory: 2GB effective_cache_size should be 10-100x larger perhaps... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] pg_autovacuum settings
Hi, My Secenario : P4 with 1G of memory on Fedora Core about 100 inserts/update per hour about 100 query per minute 20 concurrent connections 1. What is the best parameter setting in the pg_autovacuum for my scenario ? 2. what will be my sleep setting if i want to execute pg_autovacuum only after 10 hrs after the last execution. Sorry for asking those stupid question but is this my first time to use pg_autovacuum and i cant understand the README.pg_autovacuum file :) The default parameters in pg_autovacuum.h makes my box suffer some resources problem. Thanks
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
It's the same (physical) server as well as the same PostGreSQL daemon, so yes. The only difference is the actual database, the test database is made from a backup of the live database and restored onto the same PostGreSQL server. So if I run show databases in psql i get: - test - live Makes sense?? /Jona Christopher Kings-Lynne wrote: Is effective_cache_size set the same on the test and live? Jona wrote: Thanks... have notified our sys admin of that so he can make the correct changes. It still doesn't explain the difference in query plans though? I mean, it's the same database server the two instances of the same database is running on. One instance (the live) just insists on doing the seq scan of the 50k records in Price_Tbl and the 6.5k records in SCT2SubCatType_Tbl. Seems weird Cheers Jona Christopher Kings-Lynne wrote: Thank you for the swift reply, the following is the output of the SHOW ALL for shared_buffers and effective_cache_size. shared_buffers: 13384 effective_cache_size: 4000 server memory: 2GB effective_cache_size should be 10-100x larger perhaps... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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
[PERFORM] postgresql.conf runtime statistics default
Hi, after having migrated a 7.2 pg-database to 7.4 while upgrdaing from debian woody to debian sarge there are some more conf-Parameters to evaluate. We are running a small but continuously growing datawarehouse which has recently around 40 million fact entries. To my question: I found the parameter stats_reset_on_server_start which is set to true by default. Why did you choose this (and not false) and what are the impacts of changeing it to false? I mean, as long as I understood it, each query or statements generates some statistic data which is used by the optimizer (or anything equal) later on. So in my oppinion, wouldn't it be better so set this parameter to false and to enable a kind of a startup reset_stats option? Regards, Yann ---(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] Query plan changes after pg_dump / pg_restore
On Thu, 9 Jun 2005, Jona wrote: It's the same (physical) server as well as the same PostGreSQL daemon, so yes. The only thing that can differ then is the statistics collected and the amount of dead space in tables and indexes (but since you both reindex and run vacuum full that should not be it). So comparing the statistics in the system tables is the only thing I can think of that might bring some light on the issue. Maybe someone else have some ideas. And as KL said, the effective_cache_size looked like it was way to small. With that setting bigger then pg should select index scans more often. It doesn't explain why the databases behave like they do now, but it might make pg select the same plan nevertheless. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
Thank you for the insight, any suggestion as to what table / columns I should compare between the databases? Cheers Jona Dennis Bjorklund wrote: On Thu, 9 Jun 2005, Jona wrote: It's the same (physical) server as well as the same PostGreSQL daemon, so yes. The only thing that can differ then is the statistics collected and the amount of dead space in tables and indexes (but since you both reindex and run vacuum full that should not be it). So comparing the statistics in the system tables is the only thing I can think of that might bring some light on the issue. Maybe someone else have some ideas. And as KL said, the effective_cache_size looked like it was way to small. With that setting bigger then pg should select index scans more often. It doesn't explain why the databases behave like they do now, but it might make pg select the same plan nevertheless.
Re: [PERFORM] [Npgsql-general] index out of range
On 6/8/05, Francisco Figueiredo Jr. [EMAIL PROTECTED] wrote: --- Josh Close [EMAIL PROTECTED] escreveu: Well, that would make total sense. I was kinda curious how the data provider differentianted between :a and casting like now()::text. Hi Josh! Npgsql uses the info found in NpgsqlCommand.Parameters collection. We do check if a parameter in Parameters collection isn't found in query string. The other way around isn't done yet. So, you can safely put something like: :a::text and it will send the text 5::text for example. I hope it helps. Yes, that does help. Thanks. -Josh ---(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] Recommendations for configuring a 200 GB
Thanks for your reply. Besides your post regarding *nix vs. Windows I got a few which didn't go to the group. Words like bold move and disaster waiting to happen tended to feature prominently in these messages (regarding putting something this big on PostgreSQL under Windows), and management is considering deploying one under Windows and one under Linux, or possibly even both under Linux -- so please pass along advice for either environment. The four web servers are not all identical -- we have two large and two small. They are split between sites, and even one of the small ones is capable of keeping our apps running, although with significantly impaired performance. The initial PostgreSQL implementation will be on one large and one small, unless we decide to do one each of Windows and Linux; in that case we'd want identical hardware to better compare the OS issues, so it would probably be the two small servers. The small servers are IBM 8686-9RX servers with 4 xeon processors at 2 ghz, 6 gig of ram. The internal drives are set as a 67 gig raid 5 array with three drives. We have an external storage arry attached. This has a 490 gig raid 5 array on it. The drives are 15K drives. http://www-307.ibm.com/pc/support/site.wss/quickPath.do?quickPathEntry=86869rx for more info. The large servers are also IBM, although I don't have a model number handy. I know the xeons are 3 ghz and the bus is faster; otherwise they are similar. I know the large servers can go to 64 GB RAM, and management has said they are willing to add a lot more RAM if it will get used. (Our current, commercial database product can't use it under Windows.) There is also the possibility of adding additional CPUs. Like I said, with the current hardware and Sybase 12.5.1, one small machine can keep the applications limping along, although data replication falls behind during the day and catches up at night, and we get complaints from web users about slow response and some requests timing out. One large machine handles the load with little degradation, and using any two machines keeps everyone happy. We have four so that we can have two each at two different sites, and so we can take one out for maintenance and still tolerate a singe machine failure. We're hoping PostgreSQL can match or beat Sybase performance, and preliminary tests look good. We should be able to get some load testing going within a week, and we're shooting for slipping these machines into the mix around the end of this month. (We've gone to some lengths to keep our code portable.) -Kevin Richard Huxton dev@archonet.com 06/09/05 3:06 AM Kevin Grittner wrote: The manager of the DBA team is reluctant to change both the OS and the DBMS at the same time, so unless I can make a strong case for why it is important to run postgresql under Linux, we will be running this on Windows. Currently, there are two Java-based middle tier processes running on each central database server, one for the replication and one for the web. We expect to keep it that way, so the database needs to play well with these processes. Well, there's a lot more experience running PG on various *nix systems and a lot more help available. Also, I don't think performance on Windows is as good as on Linux/*BSD yet. Against switching OS is the fact that you presumably don't have the skills in-house for it, and the hardware was chosen for Windows compatibility/performance. Speaking of which, what sort of hardware are we talking about? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
Jona [EMAIL PROTECTED] writes: What worries me is that the plan is different, Given that the estimated costs are close to the same, this is probably just the result of small differences in the ANALYZE statistics leading to small differences in cost estimates and thus choice of different plans. I'll bet if you re-ANALYZE a few times on the source database you'll see it flipping between plan choices too. This is normal because ANALYZE takes a random sample of rows rather than being exhaustive. So the interesting question is not why are the plan choices different it is how do I get the cost estimates closer to reality. That's the only way in the long run to ensure the planner makes the right choice. Increasing the statistics targets or fooling with planner cost parameters are the basic tools you have available here. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Recommendations for configuring a 200 GB
We're hoping PostgreSQL can match or beat Sybase performance, and preliminary tests look good. We should be able to get some load testing going within a week, and we're shooting for slipping these machines into the mix around the end of this month. (We've gone to some lengths to keep our code portable.) Just make sure to set up and run the contrib/pg_autovacuum daemon, or make sure you fully read 'regular database maintenance' in the manual. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query plan changes after pg_dump / pg_restore
Hi Tom,
Thank you for the input, you're absolutely right.
Have just executed like 10 VACUUM ANALYZE on the Price_Tbl in both
databases and now both queries use the same plan the bad one, GREAT!
Who said ignorance is bliss?? ;-)
Have just messed around with ALTER TABLE ... ALTER SET STATISTICS
for both tables to no effect.
Have tried setting both high number (100 and 200) and a low number (1)
and run several VACUUM ANALYZE afterwards.
It still insists on the bad plan...
Furthermore I've played around with the RANDOM_PAGE_COST runtime
parameter.
Seems that when I set it to 2.2 it switch to using the aff_price_uq
index on Price_Tbl, however it needs to be set to 0.7 before it uses
the subcat_uq index on SCT2SubCatType_Tbl.
Has no effect wether the statistics is set to 1 or a 100 for this
behaviour.
The overall plan remains the same though, and even when it uses both
indexes the total cost is roughly 5.5 times higher than the good plan.
New plan:
Unique (cost=612.29..612.65 rows=3 width=75) (actual
time=255.88..255.89 rows=3 loops=1)
- Hash Join (cost=158.26..596.22 rows=288 width=75) (actual
time=60.91..99.69 rows=2477 loops=1)
Hash Cond: ("outer".sctid = "inner".sctid)
- Index Scan using aff_price_uq on price_tbl
(cost=0.00..409.24 rows=5025 width=4) (actual time=0.03..17.81
rows=5157 loops=1)
Index Cond: (affid = 8)
- Hash (cost=157.37..157.37 rows=355
width=71) (actual time=60.77..60.77 rows=0 loops=1)
- Merge Join (cost=10.26..157.37
rows=355 width=71) (actual time=14.42..53.79 rows=2493 loops=1)
Merge Cond: ("outer".subcattpid =
"inner".id)
- Index Scan using subcat_uq on
sct2subcattype_tbl (cost=0.00..126.28 rows=6536 width=8) (actual
time=0.03..23.25 rows=6527 loops=1)
- Sort (cost=10.26..10.28 rows=9
width=63) (actual time=2.46..5.66 rows=2507 loops=1)
"Total runtime: 257.49 msec"
Old "good" plan:
Unique (cost=117.18..117.20 rows=1 width=147)
(actual time=224.62..224.63 rows=3 loops=1)
- Index Scan using subcat_uq on
sct2subcattype_tbl (cost=0.00..100.47 rows=33
width=8) (actual time=0.01..0.20 rows=46 loops=54)
Index Cond: ("outer".id =
sct2subcattype_tbl.subcattpid)
- Index Scan
using aff_price_uq on price_tbl (cost=0.00..7.11
rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=2493)
Index Cond: ((price_tbl.affid = 8)
AND ("outer".sctid = price_tbl.sctid))
Total runtime: 225.14 msec
It seems that the more it knows about
Could you provide some input on how to make it realise that the plan it
selects is not the optimal?
Cheers
Jona
Tom Lane wrote:
Jona [EMAIL PROTECTED] writes:
What worries me is that the plan is different,
Given that the estimated costs are close to the same, this is probably
just the result of small differences in the ANALYZE statistics leading
to small differences in cost estimates and thus choice of different
plans. I'll bet if you re-ANALYZE a few times on the source database
you'll see it flipping between plan choices too. This is normal because
ANALYZE takes a random sample of rows rather than being exhaustive.
So the interesting question is not "why are the plan choices different"
it is "how do I get the cost estimates closer to reality". That's the
only way in the long run to ensure the planner makes the right choice.
Increasing the statistics targets or fooling with planner cost
parameters are the basic tools you have available here.
regards, tom lane
Re: [PERFORM] Help specifying new web server/database machine
Hi All. Thanks very much for Joshua, William, Bjoern and Matthew's replies. I've now looked at the famous Server for 7K thread. In my case we are looking for a server for around 3000 pounds (UK); the server is to be an all-purpose web and database server. Processor: First of all I noted that we were intending to use Opteron processors. I guess this isn't a straightforward choice because I believe Debian (our Linux of choice) doesn't have a stable AMD64 port. However some users on this list suggest that Opterons work very well even in a 32 bit environment. Some have suggested that a single dual core processor is the way to go. The RAM needs to fit the CPU arrangement too; William points out that one needs 2 DIMMS per CPU. Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. The following (without RAID card) breaks my budget by about 200 pounds: System: Armari Opteron AM-2138-A8 1U Base PCI-X (BEI) Case Accessories : IPMI 2.0 module for AM Series Opteron Servers CPU : AMD Opteron 265 - Dual Core 1.8GHz CPU (940pin) Memory: 2GB 400MHz DDR SDRAM (4 x 512MB (PC3200) ECC REG.s) Hard drive: Maxtor Atlas 10K V 147.1GB 10K U320/SCA - 8D147J0 Additional Drives : 3 x Maxtor Atlas 10K V 147.1GB 10K U320/SCA - 8D147J0 CD/DVD Drive : AM series Server 8x Slimline DVD-ROM Warranty : 3 Year Return to base Warranty (Opteron Server) Carriage : PC System Carriage (UK only) for 1U Server Thanks for any further comments, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] random_page_cost = 1?
Alex Stapleton [EMAIL PROTECTED] writes: Is this advisable? Only if your database is small enough that you expect it to remain fully cached in RAM. In that case random_page_cost = 1 does in fact describe the performance you expect Postgres to see. People occasionally use values for random_page_cost that are much smaller than physical reality would suggest, but I think this is mainly a workaround for deficiencies elsewhere in the planner cost models. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help specifying new web server/database machine
On 6/9/05, Rory Campbell-Lange [EMAIL PROTECTED] wrote: Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. Can you tell us about your application? How much data will you have, what is your ratio of reads to writes, how tollerant to data loss are you? (for example, some people load their data in batches and if they loose their data its no big deal, others would have heart failure if a few transactions were lost) If your application is 95% writes then people will suggest drastically different hardware than if your application is 95% selects. Here is an example of one of my servers: application is 95+% selects, has 15GB of data (counting indexes), low tollerance for data loss, runs on a 1 GHz P3 Compaq server with mirrored 35 GB IDE disks and 1.6GB of RAM. Application response time is aproximately .1 second to serve a request on a moderately loaded server. -- Matthew Nuzum www.bearfruit.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Help specifying new web server/database machine
On Thu, 2005-06-09 at 17:44 +0100, Rory Campbell-Lange wrote: Hi All. Thanks very much for Joshua, William, Bjoern and Matthew's replies. I've now looked at the famous Server for 7K thread. In my case we are looking for a server for around 3000 pounds (UK); the server is to be an all-purpose web and database server. Processor: First of all I noted that we were intending to use Opteron processors. I guess this isn't a straightforward choice because I believe Debian (our Linux of choice) doesn't have a stable AMD64 port. Yes it does. Now sarge has become the new stable release, the amd64 version has also become stable. It doesn't have as many packages as the i386 port, but those it has will be supported by the Debian security team. Look at the debian-amd64 mailing list for more information. It only has PostgreSQL 7.4. To run 8.0, download the source packages from unstable and build them yourself. You need postgresql-8.0 and postgresql-common; if you also have an existing database to upgrade you need postgresql and postgresql-7.4. However some users on this list suggest that Opterons work very well even in a 32 bit environment. You can treat the machine as a 32bit machine and install the i386 version of Debian; it will run rather slower than with 64 bit software. Oliver Elphick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] timestamp indexing
What does SET enable_seqscan = false; EXPLAIN ANALYZE SELECT * FROM ... get you? Is it faster? BTW, I suspect this behavior is because the estimates for the cost of an index scan don't give an appropriate weight to the correlation of the index. The 'sort and index' thread on this list from a few months ago has more info. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] timestamp indexing
[Jim C. Nasby - Thu at 01:04:53PM -0500] What does SET enable_seqscan = false; EXPLAIN ANALYZE SELECT * FROM ... get you? Is it faster? I was experimenting with this some weeks ago, by now our database server has quite low load numbers and I haven't gotten any complaints about anything that is too slow, so I have temporary stopped working with this issue - so I will not contribute with any more gory details at the moment. :-) I concluded with that our problem is that we (for performance reasons) store aggregated statistics in the wrong tables, and since updating a row in pg effectively means creating a new physical row in the database, the rows in the table are not in chronological order. If last months activity presents like 7% of the rows from the table is to be fetched, the planner will usually think that a seq scan is better. As time pass by and the table grows, it will jump to index scans. The old stuff in the database eventually grow historical, so the aggregated statistics will not be updated for most of those rows. Hence a forced index scan will often be a bit faster than a suggested table scan. I experimented, and doing an index scan for the 3rd time would usually be faster than doing a full table scan for the 3rd time, but with things not beeing in cache, the planner was right to suggest that seq scan was faster due to less disk seeks. The long term solution for this problem is to build a separate data warehouse system. The short time solution is to not care at all (eventually, buy more memory). As long as the queries is on the form give me everything since last monday, it is at least theoretically possible to serve this through partial indices, and have a cronjob dropping the old indices and creating new every week. Doing table clustering night time would probably also be a solution, but I haven't cared to test it out yet. I'm a bit concerned about performance/locking issues. -- Tobias Brox, +47-91700050 Tallinn, Europe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Help specifying new web server/database machine
Rory Campbell-Lange wrote: Processor: First of all I noted that we were intending to use Opteron processors. I guess this isn't a straightforward choice because I believe Debian (our Linux of choice) doesn't have a stable AMD64 port. However some users on this list suggest that Opterons work very well even in a 32 bit environment. Some have suggested that a single dual core processor is the way to go. The RAM needs to fit the CPU arrangement too; William points out that one needs 2 DIMMS per CPU. Your summary here just pointed out the obvious to me. Start with a 2P MB but only populate a single DC Opteron. That'll give you 2P system with room to expand to 4P in the future. Plus you only need to populate 1 memory bank so you can do 2x1GB. Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. With only 4 disks, a MegaRAID U320-1 is good enough. It's quite a premium to go to the 2x channel MegaRAID. With 4 drives, I'd still do 2 big drives mirrored for the DB partition and 2 small drives for OS+WAL. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help specifying new web server/database machine
On 09/06/05, William Yu ([EMAIL PROTECTED]) wrote: Rory Campbell-Lange wrote: ... Some have suggested that a single dual core processor is the way to go. The RAM needs to fit the CPU arrangement too; William points out that one needs 2 DIMMS per CPU. Your summary here just pointed out the obvious to me. Start with a 2P MB but only populate a single DC Opteron. That'll give you 2P system with room to expand to 4P in the future. Plus you only need to populate 1 memory bank so you can do 2x1GB. That makes sense. I should by a board with support for 2 Dual-core Opterons, but only use one Opteron for the moment. Then I should buy 2x1GB RAM sticks to service that processor. ... On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. With only 4 disks, a MegaRAID U320-1 is good enough. It's quite a premium to go to the 2x channel MegaRAID. With 4 drives, I'd still do 2 big drives mirrored for the DB partition and 2 small drives for OS+WAL. Should these all RAID1? I'm a bit worried about how to partition up my system if it is strictly divided between a system RAID1 disk entity and a DB disk entity, as the proportion of web server content (images, movies, sounds) to actual database data is an unknown quantity at the moment. I typically keep all the database stuff in a /var logical partition and for this project would expect to keep the web stuff under a /web logical partition. I was thinking of using LVM to be able to shift around space on a large (4 x 147GB RAID 1 or RAID10) raided volume. I appreciate that this may not be optimal for the WAL transaction log. Thanks for your comments; Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help specifying new web server/database machine
On 09/06/05, Matthew Nuzum ([EMAIL PROTECTED]) wrote: On 6/9/05, Rory Campbell-Lange [EMAIL PROTECTED] wrote: Disks: I'm somewhat confused here. I've followed the various notes about SATA vs SCSI and it seems that SCSI is the way to go. On a four-slot 1U server, would one do a single RAID10 over 4 disks 1rpm U320 disks? I would run the database in its own partition, separate from the rest of the OS, possible on LVM. An LSI-Megaraid-2 appears to be the card of choice. Can you tell us about your application? How much data will you have, what is your ratio of reads to writes, how tollerant to data loss are you? (for example, some people load their data in batches and if they loose their data its no big deal, others would have heart failure if a few transactions were lost) The application is a web-based prototype system for kids to make their own galleries based on content found in museums and galleries. They will link to content provided by curators, and be able to add in their own material, including movies, sounds and pictures. All the content, however, will be restricted in size. I also do not intend to store the movies, sounds or pictures in the database (although I have happily done the latter in the past). Up to the data will be uploaded from 3G handsets. The rest will be done on a per-user, per-pc basis through the web interface. The service is expected to be used by about 5 users over 18 months. Of these around half will be content creators, so will account for say half a million rows in the main content table and under 2 million rows in the commentary table. The most used table will probably be a 'history' function required by the contract, tracking use through the site. I imagine this will account for something like 20 million rows (with very little data in them). The main tables will have something like 80% read, 20% write (thumb suck). The history table will be read by an automated process at 3 in the morning, to pick up some stats on how people are using the system. It wouldn't be a problem to very occasionally (once a month) lose a tiny piece of data (i.e a record). Losing any significant amounts of data is entirely out of the question. If your application is 95% writes then people will suggest drastically different hardware than if your application is 95% selects. Here is an example of one of my servers: application is 95+% selects, has 15GB of data (counting indexes), low tollerance for data loss, runs on a 1 GHz P3 Compaq server with mirrored 35 GB IDE disks and 1.6GB of RAM. Application response time is aproximately .1 second to serve a request on a moderately loaded server. Yeah. Maybe the machine I'm speccing up is total overkill for this project? I'm just worried that if it is a big success, or if we have 400 kids pounding the server at once over high-speed school lines, the thing will grind to a halt. Thanks very much for your comments. Regards, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Help with rewriting query
Hi Bruno, I followed your suggestion. The query plan shows that it uses the index (id, person_id). However, the execution time is still slow. I have to do ctl-C to stop it. Maybe something is wrong with my postgresql config. It's running Solaris on dual Opteron, 4GB. I allocated around 128MB for sorting and more than 80% for effective_cache_size and shared_buffers = 32768. Any further ideas is much appreciated. On 6/8/05, Bruno Wolff III [EMAIL PROTECTED] wrote: On Wed, Jun 08, 2005 at 15:48:27 -0700, Junaili Lie [EMAIL PROTECTED] wrote: Hi, The suggested query below took forever when I tried it. In addition, as suggested by Tobias, I also tried to create index on food(p_id, id), but still no goal (same query plan). Here is the explain: TEST1=# explain select f.p_id, max(f.id) from Food f, Person p where (f.p_id = p.id) group by p.id; The above is going to require reading all the food table (assuming no orphaned records), so the plan below seems reasonable. QUERY PLAN GroupAggregate (cost=0.00..214585.51 rows=569 width=16) - Merge Join (cost=0.00..200163.50 rows=2884117 width=16) Merge Cond: (outer.id = inner.p_id) - Index Scan using person_pkey on person p (cost=0.00..25.17 rows=569 width=8) - Index Scan using person_id_food_index on food f (cost=0.00..164085.54 rows=2884117 width=16) (5 rows) TEST1=# explain select p.id, (Select f.id from food f where f.p_id=p.id order by f.id desc limit 1) from person p; Using a subselect seems to be the best hope of getting better performance. I think you almost got it right, but in order to use the index on (p_id, id) you need to order by f.p_id desc, f.id desc. Postgres won't deduce this index can be used because f.p_id is constant in the subselect, you need to give it some help. QUERY PLAN --- Seq Scan on Person p (cost=1.00..17015.24 rows=569 width=8) SubPlan - Limit (cost=0.00..12.31 rows=1 width=8) - Index Scan Backward using food_pkey on food f (cost=0.00..111261.90 rows=9042 width=8) Filter: (p_id = $0) (5 rows) any ideas or suggestions is appreciate. On 6/8/05, Tobias Brox [EMAIL PROTECTED] wrote: [Junaili Lie - Wed at 12:34:32PM -0700] select f.p_id, max(f.id) from person p, food f where p.id=f.p_id group by f.p_id will work. But I understand this is not the most efficient way. Is there another way to rewrite this query? (maybe one that involves order by desc limit 1) eventually, try something like select p.id,(select f.id from food f where f.p_id=p.id order by f.id desc limit 1) from person p not tested, no warranties. Since subqueries can be inefficient, use explain analyze to see which one is actually better. This issue will be solved in future versions of postgresql. -- Tobias Brox, +47-91700050 Tallinn ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Help with rewriting query
Hi Kevin, Thanks for the reply. I tried that query. It definately faster, but not fast enough (took around 50 second to complete). I have around 2.5 million on food and 1000 on person. Here is the query plan: QUERY PLAN Merge Join (cost=0.00..11662257.52 rows=1441579 width=16) Merge Cond: (outer.id = inner.p_id) - Index Scan using person_pkey on person p (cost=0.00..25.17 rows=569 width=8) - Index Scan using p_id_food_index on food f (cost=0.00..11644211.28 rows=1441579 width=16) Filter: (NOT (subplan)) SubPlan - Index Scan using p_id_food_index on food f2 (cost=0.00..11288.47 rows=2835 width=177) Index Cond: (p_id = $0) Filter: (id $1) (9 rows) I appreciate if you have further ideas to troubleshoot this issue. Thank you! On 6/8/05, Kevin Grittner [EMAIL PROTECTED] wrote: This is a pattern which I've seen many of times. I call it a best choice query -- you can easily match a row from one table against any of a number of rows in another, the trick is to pick the one that matters most. I've generally found that I want the query results to show more than the columns used for making the choice (and there can be many), which rules out the min/max technique. What works in a pretty straitforward way, and generally optimizes at least as well as the alternatives, is to join to the set of candidate rows and add a not exists test to eliminate all but the best choice. For your example, I've taken some liberties and added hypothetical columns from both tables to the result set, to demonstrate how that works. Feel free to drop them or substitute actual columns as you see fit. This will work best if there is an index for the food table on p_id and id. Please let me know whether this works for you. select p.id as p_id, p.fullname, f.id, f.foodtype, f.ts from food f join person p on f.p_id = p.id and not exists (select * from food f2 where f2.p_id = f.p_id and f2.id f.id) order by p_id Note that this construct works for inner or outer joins and works regardless of how complex the logic for picking the best choice is. I think one reason this tends to optimize well is that an EXISTS test can finish as soon as it finds one matching row. -Kevin Junaili Lie [EMAIL PROTECTED] 06/08/05 2:34 PM Hi, I have the following table: person - primary key id, and some attributes food - primary key id, foreign key p_id reference to table person. table food store all the food that a person is eating. The more recent food is indicated by the higher food.id. I need to find what is the most recent food a person ate for every person. The query: select f.p_id, max(f.id) from person p, food f where p.id=f.p_id group by f.p_id will work. But I understand this is not the most efficient way. Is there another way to rewrite this query? (maybe one that involves order by desc limit 1) Thank you in advance. ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgresql.conf runtime statistics default
Hi, On Thu, Jun 09, 2005 at 02:11:22PM +0100, Richard Huxton wrote: To my question: I found the parameter stats_reset_on_server_start which is set to true by default. Why did you choose this (and not false) and what are the impacts of changeing it to false? I mean, as long as I understood it, each query or statements generates some statistic data which is used by the optimizer (or anything equal) later on. So in my oppinion, wouldn't it be better so set this parameter to false and to enable a kind of a startup reset_stats option? This is administrator statistics (e.g. number of disk blocks read from this index) not planner statistics. You're right - it would be foolish to throw away planner stats. So what is best to set this parameter to and when? As I read this parameter is documented within the section 16.4.7.2. Query and Index Statistics Collector so I guess it is better to set it to false as described above. Or am I wrong? Regards, Yann ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
