Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
And here is that latest benchmark we did, using a 8 dual core opteron Sun Fire x4600. Unfortunately PostgreSQL seems to have some difficulties scaling over 8 cores, but not as bad as MySQL. http://tweakers.net/reviews/674 Best regards, Arjen Arjen van der Meijden wrote: Alvaro Herrera wrote: Interesting -- the MySQL/Linux graph is very similar to the graphs from the .nl magazine posted last year. I think this suggests that the "MySQL deficiency" was rather a performance bug in Linux, not in MySQL itself ... The latest benchmark we did was both with Solaris and Linux on the same box, both showed such a drop. So I doubt its "not in MySQL", although it might be possible to fix the load MySQL's usage pattern poses on a system, via the OS. And since MySQL 5.0.32 is less bad than 4.1.22 on that system. We didn't have time to test 5.0.25 again, but .32 scaled better, so at least some of the scaling issues where actually fixed in MySQL itself. Best regards, Arjen ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Arjen van der Meijden wrote: And here is that latest benchmark we did, using a 8 dual core opteron Sun Fire x4600. Unfortunately PostgreSQL seems to have some difficulties scaling over 8 cores, but not as bad as MySQL. http://tweakers.net/reviews/674 ouch - do I read that right that even after tom's fixes for the "regressions" in 8.2.0 we are still 30% slower then the -HEAD checkout from the middle of the 8.2 development cycle ? Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Stefan Kaltenbrunner wrote: ouch - do I read that right that even after tom's fixes for the "regressions" in 8.2.0 we are still 30% slower then the -HEAD checkout from the middle of the 8.2 development cycle ? Yes, and although I tested about 17 different cvs-checkouts, Tom and I weren't really able to figure out where "it" happened. So its a bit of a mystery why the performance is so much worse. Best regards, Arjen ---(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
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Arjen van der Meijden wrote: And here is that latest benchmark we did, using a 8 dual core opteron Sun Fire x4600. Unfortunately PostgreSQL seems to have some difficulties scaling over 8 cores, but not as bad as MySQL. http://tweakers.net/reviews/674 Hmm - interesting reading as always Arjen. Thanks for the notice on this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
Hi All, I tried posting this last week but it has not come through yet, so please excuse me if there is a double post. We're having some issue's with the vacuum times within our database environment, and would like some input from the guru's out there that could potentially suggest a better approach or mechanism. Problem Background First off, I'm no PostgreSQL guru, so, please be nice :) Over time we have noticed increased response times from the database which has an adverse affect on our registration times. After doing some research it appears that this may have been related to our maintenance regime, and has thus been amended as follows: [1] AutoVacuum runs during the day over the entire PostgreSQL cluster, [2] A Vacuum Full Verbose is run during our least busy period (generally 03:30) against the Database, [3] A Re-Index on the table is performed, [4] A Cluster on the table is performed against the most used index, [5] A Vacuum Analyze Verbose is run against the database. These maintenance steps have been setup to run every 24 hours. The database in essence, once loaded up and re-index is generally around 17MB for data and 4.7MB for indexes in size. Over a period of 24 hours the database can grow up to around 250MB and the indexes around 33MB (Worst case thus far). When the maintenance kicks in, the vacuum full verbose step can take up to 15 minutes to complete (worst case). The re-index, cluster and vacuum analyze verbose steps complete in under 1 second each. The problem here is the vacuum full verbose, which renders the environment unusable during the vacuum phase. The idea here is to try and get the vacuum full verbose step to complete in less than a minute. Ideally, if we could get it to complete quicker then that would be GREAT, but our minimal requirement is for it to complete at the very most 1 minute. Looking at the specifications of our environment below, do you think that this is at all possible? Environment Background: We are running a VoIP service whereby the IP phones perform a registration request every 113 seconds. These registration requests are verified against the database and the details are updated accordingly. Currently we average around 100 - 150 read/write requests per second to this particular database. The requirement here is that the database response is sub 15 milliseconds for both types of requests, which it currently is. The database _must_ also be available 24x7. Hardware Environment: SunFire X4200 2 x Dual Core Opteron 280's 8GB RAM 2 x Q-Logic Fibre Channel HBA's Sun StorEdge 3511 FC SATA Array 1 x 1GB RAID Module 12 x 250GB 7200 RPM SATA disks RAID Environment: 5 Logical drives, each LD is made up of 2 x 250GB SATA HDD in a RAID 1 mirror. 2 x 250GB SATA HDD allocated as hot spares The logical drives are partitioned and presented to the OS as follows: LD0 (2 x 250GB SATA HDD's RAID 1) Partition 0 (120GB) Partition 1 (120GB) LD1 (2 x 250GB SATA HDD's RAID 1) Partition 0 (120GB) Partition 1 (120GB) LD2 (2 x 250GB SATA HDD's RAID 1) Partition 0 (80GB) Partition 1 (80GB) Partition 2 (80GB) LD3 (2 x 250GB SATA HDD's RAID 1) Partition 0 (80GB) Partition 1 (80GB) Partition 2 (80GB) LD4 (2 x 250GB SATA HDD's RAID 1) Partition 0 (120GB) Partition 1 (120GB) - OS Environment Solaris 10 Update 3 (11/06) Boot disks are 76GB 15000 RPM configure in a RAID 1 mirror. - Filesystem Layout PostgreSQL Data 250GB ZFS file-system made up of: LD0 Partition 0 Mirrored to LD1 Partition 0 (120GB) LD0 Partition 1 Mirrored to LD1 Partition 1 (120GB) The above 2 vdevs are then striped across each other PostgreSQL WAL 80GB ZFS filesystem made up of: LD2 Partition 0 Mirrored to LD3 Partition 0 (80GB) LD2 partition 1 Mirrored to LD3 Partition 1 (80GB) The above 2 vdevs are then striped across each other - PostgreSQL Configuration PostgreSQL version 8.2.3 #--- # RESOURCE USAGE (except WAL) #--- #
Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
Bruce McAlister wrote: Over time we have noticed increased response times from the database which has an adverse affect on our registration times. After doing some research it appears that this may have been related to our maintenance regime, and has thus been amended as follows: [1] AutoVacuum runs during the day over the entire PostgreSQL cluster, [2] A Vacuum Full Verbose is run during our least busy period (generally 03:30) against the Database, [3] A Re-Index on the table is performed, [4] A Cluster on the table is performed against the most used index, [5] A Vacuum Analyze Verbose is run against the database. These maintenance steps have been setup to run every 24 hours. The database in essence, once loaded up and re-index is generally around 17MB for data and 4.7MB for indexes in size. Over a period of 24 hours the database can grow up to around 250MB and the indexes around 33MB (Worst case thus far). When the maintenance kicks in, the vacuum full verbose step can take up to 15 minutes to complete (worst case). The re-index, cluster and vacuum analyze verbose steps complete in under 1 second each. The problem here is the vacuum full verbose, which renders the environment unusable during the vacuum phase. The idea here is to try and get the vacuum full verbose step to complete in less than a minute. Ideally, if we could get it to complete quicker then that would be GREAT, but our minimal requirement is for it to complete at the very most 1 minute. Looking at the specifications of our environment below, do you think that this is at all possible? 250MB+33MB isn't very much. It should easily fit in memory, I don't see why you need the 12 disk RAID array. Are you sure you got the numbers right? Vacuum full is most likely a waste of time. Especially on the tables that you cluster later, cluster will rewrite the whole table and indexes anyway. A regular normal vacuum should be enough to keep the table in shape. A reindex is also not usually necessary, and for the tables that you cluster, it's a waste of time like vacuum full. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] which Xeon processors don't have the context switching problem
Joshua D. Drake wrote: Geoffrey wrote: Guillaume Smet wrote: On 2/23/07, Geoffrey <[EMAIL PROTECTED]> wrote: As I've heard. We're headed for 8 as soon as possible, but until we get our code ready, we're on 7.4.16. You should move to at least 8.1 and possibly 8.2. It's not a good idea to upgrade only to 8 IMHO. When I said 8, I meant whatever the latest greatest 8 is. Right now, that looks like 8.2.3. No. The latest version of 8.2 is 8.2.3, there is also 8.1 which is at 8.1.8 and 8.0 which is at 8.0.12. They are all different *major* releases. Yes I am aware of the various releases. My bad in that my reference to '8' was lazy and did not indicate the full release. Our intention is to move to the latest 8.2.* when we are able. IMO, nobody should be running anything less than 8.1.8. Same old thing, time and money. Too busy bailing the boat to patch it right now... -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
Hi Heikki, Thanks for the reply. The RAID array was implemented due to a projected growth pattern which incorporate all 18 of our databases. The sizings I mentioned only refer to 1 of those databases, which, is also the most heavily used database :) If I understand you correctly, we could in essence change our maintenance routine to the follwing: [1] Cluster on most used index [2] Perform a vacuum analyze on the table If I read your post correctly, this will regenerate the index that the cluster is performed on (1 of 3) and also re-generate the table in the sequence of that index? If that is the case, why would anyone use the vacuum full approach if they could use the cluster command on a table/database that will regen these files for you. It almost seems like the vacuum full approach would, or could, be obsoleted by the cluster command, especially if the timings in their respective runs are that different (in our case the vacuum full took 15 minutes in our worst case, and the cluster command took under 1 second for the same table and scenario). The output of our script for that specific run is as follows (just in-case i'm missing something): Checking disk usage before maintenance on service (sipaccounts) at 02-Mar-2007 03:30:00 258M /database/pgsql/bf_service/data 33M /database/pgsql/bf_service/index Completed checking disk usage before maintenance on service (sipaccounts) at 02-Mar-2007 03:30:00 Starting VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007 03:30:00 INFO: vacuuming "public.sipaccounts" INFO: "sipaccounts": found 71759 removable, 9314 nonremovable row versions in 30324 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 318 to 540 bytes long. There were 439069 unused item pointers. Total free space (including removable row versions) is 241845076 bytes. 28731 pages are or will become empty, including 41 at the end of the table. 30274 pages containing 241510688 free bytes are potential move destinations. CPU 0.00s/0.05u sec elapsed 31.70 sec. INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages DETAIL: 7265 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.01u sec elapsed 1.52 sec. INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages DETAIL: 7161 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.01u sec elapsed 3.07 sec. INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages DETAIL: 71759 index row versions were removed. 1151 index pages have been deleted, 1151 are currently reusable. CPU 0.02s/0.08u sec elapsed 56.31 sec. INFO: "sipaccounts": moved 3395 row versions, truncated 30324 to 492 pages DETAIL: CPU 0.03s/0.56u sec elapsed 751.99 sec. INFO: index "sippeers_name_key" now contains 9314 row versions in 69 pages DETAIL: 3395 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.21 sec. INFO: index "sippeers_pkey" now contains 9314 row versions in 135 pages DETAIL: 3395 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "accountcode_index" now contains 9314 row versions in 3347 pages DETAIL: 3395 index row versions were removed. 1159 index pages have been deleted, 1159 are currently reusable. CPU 0.01s/0.01u sec elapsed 30.03 sec. INFO: vacuuming "pg_toast.pg_toast_2384131" INFO: "pg_toast_2384131": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_2384131_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Completed VACUUM FULL VERBOSE on service (sipaccounts) at 02-Mar-2007 03:44:35 Starting REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35 REINDEX Completed REINDEX on service (sipaccounts) at 02-Mar-2007 03:44:35 Starting CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:35 CLUSTER sipaccounts; CLUSTER Completed CLUSTER on service (sipaccounts) at 02-Mar-2007 03:44:36 Starting VACUUM ANALYZE VERBOSE on service (sipaccounts) at 02-Mar-2007 03:44:36 INFO: vacuuming "public.sipaccounts" INFO: scanned index "sippeers_name_key" to remove 9 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: scanned index "sippeers_pkey" to remove 9 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.20 sec. INFO: scanned index "accountcode_index" to remove 9 row version
Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
Bruce McAlister wrote: Hi Heikki, Thanks for the reply. The RAID array was implemented due to a projected growth pattern which incorporate all 18 of our databases. The sizings I mentioned only refer to 1 of those databases, which, is also the most heavily used database :) If I understand you correctly, we could in essence change our maintenance routine to the follwing: [1] Cluster on most used index [2] Perform a vacuum analyze on the table If I read your post correctly, this will regenerate the index that the cluster is performed on (1 of 3) and also re-generate the table in the sequence of that index? That's right. In fact, even cluster probably doesn't make much difference in your case. Since the table fits in memory anyway, the physical order of it doesn't matter much. I believe you would be fine just turning autovacuum on, and not doing any manual maintenance. If that is the case, why would anyone use the vacuum full approach if they could use the cluster command on a table/database that will regen these files for you. It almost seems like the vacuum full approach would, or could, be obsoleted by the cluster command, especially if the timings in their respective runs are that different (in our case the vacuum full took 15 minutes in our worst case, and the cluster command took under 1 second for the same table and scenario). In fact, getting rid of vacuum full, or changing it to work like cluster, has been proposed in the past. The use case really is pretty narrow; cluster is a lot faster if there's a lot of unused space in the table, and if there's not, vacuum full isn't going to do much so there's not much point running it in the first place. The reason it exists is largely historical, there hasn't been a pressing reason to remove it either. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Estimate the size of the SQL file generated by pg_dump utility
Dear All, I have to take backup of a database as a SQL file using the pg_dump utility and I have to check the disk space before taking the backup. Hence I need to estimate the size of the pg_dump output. The size given by pg_database_size procedure and the size of file generated by pg_dump vary to a large extent. Kindly let me know how to estimate the size of the file that pg_dump generates. Note: Please bear with us for the disclaimer because it is automated in the exchange server. Regards, Ravi DISCLAIMER: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
Re: [PERFORM] Estimate the size of the SQL file generated by pg_dump utility
am Mon, dem 05.03.2007, um 20:25:21 +0530 mailte Ravindran G-TLS,Chennai. folgendes: > Dear All, > > I have to take backup of a database as a SQL file using the pg_dump utility > and > I have to check the disk space > > before taking the backup. Hence I need to estimate the size of the pg_dump > output. You can take a empty backup by pipe the output to wc -c. Is this a solution for you? You can see the result size in bytes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] Turning off Autovacuum
Not quite a performance question, but I can't seem to find a simple answer to this. We're using 8.1.4 and the autovacuum daemon is running every 40 seconds cycling between 3 databases. What is the easiest way to disable the autovacuumer for a minute or two, do some other work, then re-enable it? Do I have to modify postgresql.conf and send a HUP signal to pick up the changes? I figured this would work but I can't find a reason why not: # show autovacuum; autovacuum on (1 row) # set autovacuum to off; ERROR: parameter "autovacuum" cannot be changed now In postgresql.conf: autovacuum = on Thanks, Steve
Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
"Bruce McAlister" <[EMAIL PROTECTED]> writes: > [1] AutoVacuum runs during the day over the entire PostgreSQL cluster, Good, but evidently you need to make it more aggressive. > [2] A Vacuum Full Verbose is run during our least busy period (generally > 03:30) against the Database, > [3] A Re-Index on the table is performed, > [4] A Cluster on the table is performed against the most used index, > [5] A Vacuum Analyze Verbose is run against the database. That is enormous overkill. Steps 2 and 3 are a 100% waste of time if you are going to cluster in step 4. Just do the CLUSTER and then ANALYZE (or VACUUM ANALYZE if you really must, but the value is marginal). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance
Aidan Van Dyk wrote: * Heikki Linnakangas <[EMAIL PROTECTED]> [070305 09:46]: In fact, getting rid of vacuum full, or changing it to work like cluster, has been proposed in the past. The use case really is pretty narrow; cluster is a lot faster if there's a lot of unused space in the table, and if there's not, vacuum full isn't going to do much so there's not much point running it in the first place. The reason it exists is largely historical, there hasn't been a pressing reason to remove it either. I've never used CLUSTER, because I've always heard murmerings of it not being completely MVCC safe. From the TODO: * CLUSTER o Make CLUSTER preserve recently-dead tuples per MVCC requirements Good point, I didn't remember that. Using cluster in an environment like the OP has, cluster might actually break the consistency of concurrent transactions. But the documents don't mention anything about cluster being unsafe. Really? . Looks like you're right. Should definitely be mentioned in the docs. AFAIK, Vacuum full doesn't suffer the same MVCC issues that cluster does. Is this correct? That's right. Vacuum full goes to great lengths to be MVCC-safe. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Arjen van der Meijden wrote: > Stefan Kaltenbrunner wrote: >> ouch - do I read that right that even after tom's fixes for the >> "regressions" in 8.2.0 we are still 30% slower then the -HEAD checkout >> from the middle of the 8.2 development cycle ? > > Yes, and although I tested about 17 different cvs-checkouts, Tom and I > weren't really able to figure out where "it" happened. So its a bit of a > mystery why the performance is so much worse. double ouch - losing that much in performance without an idea WHY it happened is really unfortunate :-( Stefan ---(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] Turning off Autovacuum
If you want to disable it only for some tables, you can put special values into pg_autovacuum. This won't disable the autovacuum daemon, but some of the tables won't be vacuumed. Tomas Not quite a performance question, but I can't seem to find a simple answer to this. We're using 8.1.4 and the autovacuum daemon is running every 40 seconds cycling between 3 databases. What is the easiest way to disable the autovacuumer for a minute or two, do some other work, then re-enable it? Do I have to modify postgresql.conf and send a HUP signal to pick up the changes? I figured this would work but I can't find a reason why not: # show autovacuum; autovacuum on (1 row) # set autovacuum to off; ERROR: parameter "autovacuum" cannot be changed now In postgresql.conf: autovacuum = on Thanks, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > Arjen van der Meijden wrote: >> Stefan Kaltenbrunner wrote: >>> ouch - do I read that right that even after tom's fixes for the >>> "regressions" in 8.2.0 we are still 30% slower then the -HEAD checkout >>> from the middle of the 8.2 development cycle ? >> >> Yes, and although I tested about 17 different cvs-checkouts, Tom and I >> weren't really able to figure out where "it" happened. So its a bit of a >> mystery why the performance is so much worse. > double ouch - losing that much in performance without an idea WHY it > happened is really unfortunate :-( Keep in mind that Arjen's test exercises some rather narrow scenarios; IIRC its performance is mostly determined by some complicated bitmap-indexscan cases. So that "30% slower" bit certainly doesn't represent an across-the-board figure. As best I can tell, the decisions the planner happened to be making in late June were peculiarly nicely suited to his test, but not so much for other cases. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
On 5-3-2007 21:38 Tom Lane wrote: Keep in mind that Arjen's test exercises some rather narrow scenarios; IIRC its performance is mostly determined by some complicated bitmap-indexscan cases. So that "30% slower" bit certainly doesn't represent an across-the-board figure. As best I can tell, the decisions the planner happened to be making in late June were peculiarly nicely suited to his test, but not so much for other cases. True, its not written as a database-comparison-test, but as a platform-comparison test. As I showed you back then, there where indeed querytypes faster on the final version (I still have that database of executed queries on dev and 8.2 rc1), especially after your three patches. Still, its a pitty that both the general performance and scalability seem to be worse on these platforms. Best regards, Arjen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Turning off Autovacuum
Yeah, I'm hoping there's an easier way. I'd have to put several thousand entries in the pg_autovacuum table only to remove them a few minutes later. What I really want is to disable the daemon. Any idea why I can't just simply set autovacuum to off? Steve On 3/5/07, Tomas Vondra <[EMAIL PROTECTED]> wrote: If you want to disable it only for some tables, you can put special values into pg_autovacuum. This won't disable the autovacuum daemon, but some of the tables won't be vacuumed. Tomas > Not quite a performance question, but I can't seem to find a simple > answer to this. We're using 8.1.4 and the autovacuum daemon is > running every 40 seconds cycling between 3 databases. What is the > easiest way to disable the autovacuumer for a minute or two, do some > other work, then re-enable it? Do I have to modify postgresql.conf > and send a HUP signal to pick up the changes? > > I figured this would work but I can't find a reason why not: > > # show autovacuum; > autovacuum > > on > (1 row) > > # set autovacuum to off; > ERROR: parameter "autovacuum" cannot be changed now > > In postgresql.conf: > > autovacuum = on > > Thanks, > > Steve >
Re: [PERFORM] Estimate the size of the SQL file generated by pg_dump utility
Ravindran G-TLS,Chennai. wrote: > Note: Please bear with us for the disclaimer because it is automated in > the exchange server. > Regards, > Ravi FYI, we are getting closer to rejecting any email with such a disclaimer, or emailing you back every time saying we are ignoring the disclaimer. --- > > > DISCLAIMER: > --- > > The contents of this e-mail and any attachment(s) are confidential and > intended for the named recipient(s) only. > It shall not attach any liability on the originator or HCL or its affiliates. > Any views or opinions presented in > this email are solely those of the author and may not necessarily reflect the > opinions of HCL or its affiliates. > Any form of reproduction, dissemination, copying, disclosure, modification, > distribution and / or publication of > this message without the prior written consent of the author of this e-mail > is strictly prohibited. If you have > received this email in error please delete it and notify the sender > immediately. Before opening any mail and > attachments please check them for viruses and defect. > > --- -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Insert performance
Dear all, After many tests and doc reading, i finally try to get help from you... Here is my problem. With some heavy insert into a simple BD (one table, no indexes) i can't get better perf than 8000 inserts/sec. I'm testing it using a simple C software which use libpq and which use: - Insert prepared statement (to avoid too many request parsing on the server) - transaction of 10 inserts My server which has the following config: - 3G RAM - Pentium D - 64 bits, 3Ghz - database data on hardware raid 0 disks - x_log (WAL logs) on an other single hard drive The server only use 30% of the CPU, 10% of disk access and not much RAM... So i'm wondering where could be the bottle neck and why i can't get better performance ? I really need to use inserts and i can't change it to use COPY... Any advice is welcome. Sorry in advance for my bad understanding of database ! Thanks in advance. Regards, Joël.W ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] performances with Pentium D
Hi all, Do someone already had some problem with performances using a pentium D (64 bits) and postgres 8.2.3 on a redhat enterprise update 2 ? I did the install from sources and nothing change... I have a RAID 0 for data and 3Gb of RAM and my inserts rate is quite low, 8333 inserts/ sec (lower than on my laptop which is 10526 inserts/sec). I suspect a problem with the CPU because using gkrellm, the use of 1 CPU is always quite low... Is it normal ? Many thanks for your help, Joël ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Opinions on Raid
Really appreciate all of the valuable input. The current server has the Perc4ei controller. The impression I am taking from the responses is that we may be okay with software raid, especially if raid 1 and 10 are what we intend to use. I think we can collect enough information from the archives of this list to help make decisions for the new machine(s), was just very interested in hearing feedback on software vs. hardware raid. We will likely be using the 2.6.18 kernel. Thanks for everyone's input, Joe -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 27, 2007 12:56 PM To: Joe Uhl Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Opinions on Raid On Tue, 2007-02-27 at 07:12, Joe Uhl wrote: > We have been running Postgres on a 2U server with 2 disks configured in > raid 1 for the os and logs and 4 disks configured in raid 10 for the > data. I have since been told raid 5 would have been a better option > given our usage of Dell equipment and the way they handle raid 10. Some controllers do no layer RAID effectively. Generally speaking, the cheaper the controller, the worse it's gonna perform. Also, some controllers are optimized more for RAID 5 than RAID 1 or 0. Which controller does your Dell have, btw? > I > have just a few general questions about raid with respect to Postgres: > > [1] What is the performance penalty of software raid over hardware raid? > Is it truly significant? We will be working with 100s of GB to 1-2 TB > of data eventually. For a mostly read system, the performance is generally pretty good. Older linux kernels ran layered RAID pretty slowly. I.e. RAID 1+0 was no faster than RAID 1. The best performance software RAID I found in older linux kernels (2.2, 2.4) was plain old RAID-1. RAID-5 was good at reading, but slow at writing. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Upgraded to 8.2.3 --- still having performance issues
Tom Lane wrote: Carlos Moreno <[EMAIL PROTECTED]> writes: I would have expected a mind-blowing increase in responsiveness and overall performance. However, that's not the case --- if I didn't know better, I'd probably tend to say that it is indeed the opposite (performance seems to have deteriorated) Did you remember to re-ANALYZE everything after loading up the new database? That's a frequent gotcha ... I did. I didn't think it would be necessary, but being paranoid as I am, I figured let's do it just in case. After a few hours of operation, I did a vacuumdb -z also. But it seems to continue downhill :-( Thanks, Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] strange performance regression between 7.4 and 8.1
Hello, I have noticed a strange performance regression and I'm at a loss as to what's happening. We have a fairly large database (~16 GB). The original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB of ram running Solaris on local scsi discs. The new server is a sun Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux (AMD64) on a 4 Gbps FC SAN volume. When we created the new database it was created from scratch rather than copying over the old one, however the table structure is almost identical (UTF8 on the new one vs. C on the old). The problem is queries are ~10x slower on the new hardware. I read several places that the SAN might be to blame, but testing with bonnie and dd indicates that the SAN is actually almost twice as fast as the scsi discs in the old sun server. I've tried adjusting just about every option in the postgres config file, but performance remains the same. Any ideas? Thanks, Alex ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Hibernate left join
Hi , I want to know about hibernate left join, Is their any way to do left join in hibernate ?. Please give me an example of hibernate left join with its maaping Thanks & Regards Bholu. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] performances with Pentium D
Hi Ben, Thanks for you answer. I was thinking about CPU speed bottleneck because one of the CPU load was always quite low on the server (Pentium D, dual core) and on the other hand, the CPU load on my laptop was always very high. After some more testing (using a threaded client software which does the same inserts using 10 parallel connections), i was able to have the other CPU arround 90% too :-) Then the INSERT speed reached 18'181 inserts/sec !! So now i'm wondering if i reached the disk limit or again the CPU limit... Anyway, thanks a lot for your advice, i didn't know this difference between fsync implementation on SCSI and IDE ! Joël On Mar 1, 12:00 pm, "Ben Trewern" <[EMAIL PROTECTED]> wrote: > I suspect the difference is your disk subsystem. IDE disks (in your laptop > I assume) quite often (almost always!!) ignore fsync calls and return as > soon as the data gets to the disk cache, not the physical disk. SCSI disks > are almost always more correct, and wait until the data gets to the physical > disk before they return from an fsync call. > > I hope this helps. > > Regards, > > Ben"hatman" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > Hi all, > > Do someone already had some problem with performances using a pentium > D (64 bits) and postgres 8.2.3 on a redhat enterprise update 2 ? > I did the install from sources and nothing change... I have a RAID 0 > for data and 3Gb of RAM and my inserts rate is quite low, 8333 inserts/ > sec (lower than on my laptop which is 10526 inserts/sec). > I suspect a problem with the CPU because using gkrellm, the use of 1 > CPU is always quite low... Is it normal ? > > Many thanks for your help, > > Joël ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] performances with Pentium D
I suspect the difference is your disk subsystem. IDE disks (in your laptop I assume) quite often (almost always!!) ignore fsync calls and return as soon as the data gets to the disk cache, not the physical disk. SCSI disks are almost always more correct, and wait until the data gets to the physical disk before they return from an fsync call. I hope this helps. Regards, Ben "hatman" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hi all, Do someone already had some problem with performances using a pentium D (64 bits) and postgres 8.2.3 on a redhat enterprise update 2 ? I did the install from sources and nothing change... I have a RAID 0 for data and 3Gb of RAM and my inserts rate is quite low, 8333 inserts/ sec (lower than on my laptop which is 10526 inserts/sec). I suspect a problem with the CPU because using gkrellm, the use of 1 CPU is always quite low... Is it normal ? Many thanks for your help, Joël ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM]
Hi, I'm new to tuning PostgreSQL and I have a query that gets slower
after I run a vacuum analyze. I believe it uses a Hash Join before
the analyze and a Nested Loop IN Join after. It seems the Nested
Loop IN Join estimates the correct number of rows, but underestimates
the amount of time required. I am curious why the vacuum analyze
makes it slower and if that gives any clues as too which parameter I
should be tuning.
BTW, I know the query could be re-structured more cleanly to remove
the sub-selects, but that doesn't impact the performance.
thanks,
Jeff
Welcome to psql 8.1.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms
WHERE ( 1=1 and symptoms.id in (select symptom_id from
symptom_reports sr where 1=1 and sr.user_id in (select id from users
where disease_id=1))) ;
QUERY PLAN
---
Aggregate (cost=366.47..366.48 rows=1 width=0) (actual
time=125.093..125.095 rows=1 loops=1)
-> Hash Join (cost=362.41..366.38 rows=36 width=0) (actual
time=124.162..124.859 rows=106 loops=1)
Hash Cond: ("outer".id = "inner".symptom_id)
-> Seq Scan on symptoms (cost=0.00..3.07 rows=107
width=4) (actual time=0.032..0.295 rows=108 loops=1)
-> Hash (cost=362.25..362.25 rows=67 width=4) (actual
time=124.101..124.101 rows=106 loops=1)
-> HashAggregate (cost=361.58..362.25 rows=67
width=4) (actual time=123.628..123.854 rows=106 loops=1)
-> Hash IN Join (cost=35.26..361.41 rows=67
width=4) (actual time=9.767..96.372 rows=13074 loops=1)
Hash Cond: ("outer".user_id = "inner".id)
-> Seq Scan on symptom_reports sr
(cost=0.00..259.65 rows=13165 width=8) (actual time=0.029..33.359
rows=13074 loops=1)
-> Hash (cost=35.24..35.24 rows=11
width=4) (actual time=9.696..9.696 rows=1470 loops=1)
-> Bitmap Heap Scan on users
(cost=2.04..35.24 rows=11 width=4) (actual time=0.711..6.347
rows=1470 loops=1)
Recheck Cond: (disease_id = 1)
-> Bitmap Index Scan on
users_disease_id_index (cost=0.00..2.04 rows=11 width=0) (actual
time=0.644..0.644 rows=2378 loops=1)
Index Cond: (disease_id
= 1)
Total runtime: 134.045 ms
(15 rows)
plm_demo=# vacuum analyze;
VACUUM
plm_demo=# analyze;
ANALYZE
plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms
WHERE ( 1=1 and symptoms.id in (select symptom_id from
symptom_reports sr where 1=1 and sr.user_id in (select id from users
where disease_id=1))) ;
QUERY
PLAN
-
Aggregate (cost=586.47..586.48 rows=1 width=0) (actual
time=3441.385..3441.386 rows=1 loops=1)
-> Nested Loop IN Join (cost=149.05..586.26 rows=85 width=0)
(actual time=54.517..3441.115 rows=106 loops=1)
Join Filter: ("outer".id = "inner".symptom_id)
-> Seq Scan on symptoms (cost=0.00..3.08 rows=108
width=4) (actual time=0.007..0.273 rows=108 loops=1)
-> Hash IN Join (cost=149.05..603.90 rows=13074 width=4)
(actual time=0.078..24.503 rows=3773 loops=108)
Hash Cond: ("outer".user_id = "inner".id)
-> Seq Scan on symptom_reports sr
(cost=0.00..258.74 rows=13074 width=8) (actual time=0.003..9.044
rows=3773 loops=108)
-> Hash (cost=145.38..145.38 rows=1470 width=4)
(actual time=7.608..7.608 rows=1470 loops=1)
-> Seq Scan on users (cost=0.00..145.38
rows=1470 width=4) (actual time=0.006..4.353 rows=1470 loops=1)
Filter: (disease_id = 1)
Total runtime: 3441.452 ms
(11 rows)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Having performance problems with TSearch2
>
> I have problems with queries over tsearch index.I have a table of books, with
> 120 registers. I have created an GIST index over the title and subtitle,
> CREATE INDEX "idxts2_titsub_idx" ON "public"."libros" USING gist
("idxts2_titsub");
Your query didn't use index that you are created..
After CREATE INDEX you mast ran VACUUM (FULL recomended) and REINDEX
The query is: select userid,msg,idxfti from _my_msg0 where idxfti @@
to_tsquery('utf8_russian','хочу & трахаться');
---(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] PostgreSQL 8.2.3 VACUUM Timings/Performance
* Heikki Linnakangas <[EMAIL PROTECTED]> [070305 09:46]: > >If that is the case, why would anyone use the vacuum full approach if they > >could use the cluster command on a table/database that will regen these > >files for you. It almost seems like the vacuum full approach would, or > >could, be obsoleted by the cluster command, especially if the timings in > >their respective runs are that different (in our case the vacuum full took > >15 minutes in our worst case, and the cluster command took under 1 second > >for the same table and scenario). > > In fact, getting rid of vacuum full, or changing it to work like > cluster, has been proposed in the past. The use case really is pretty > narrow; cluster is a lot faster if there's a lot of unused space in the > table, and if there's not, vacuum full isn't going to do much so there's > not much point running it in the first place. The reason it exists is > largely historical, there hasn't been a pressing reason to remove it either. I've never used CLUSTER, because I've always heard murmerings of it not being completely MVCC safe. From the TODO: * CLUSTER o Make CLUSTER preserve recently-dead tuples per MVCC requirements But the documents don't mention anything about cluster being unsafe. AFAIK, Vacuum full doesn't suffer the same MVCC issues that cluster does. Is this correct? a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[PERFORM] query slows down after vacuum analyze
Hi, I'm new to tuning PostgreSQL and I have a query that gets slower
after I run a vacuum analyze. I believe it uses a Hash Join before
the analyze and a Nested Loop IN Join after. It seems the Nested
Loop IN Join estimates the correct number of rows, but underestimates
the amount of time required. I am curious why the vacuum analyze
makes it slower and if that gives any clues as too which parameter I
should be tuning.
BTW, I know the query could be re-structured more cleanly to remove
the sub-selects, but that doesn't seem to impact the performance.
thanks,
Jeff
Welcome to psql 8.1.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms
WHERE ( 1=1 and symptoms.id in (select symptom_id from
symptom_reports sr where 1=1 and sr.user_id in (select id from users
where disease_id=1))) ;
QUERY PLAN
---
Aggregate (cost=366.47..366.48 rows=1 width=0) (actual
time=125.093..125.095 rows=1 loops=1)
-> Hash Join (cost=362.41..366.38 rows=36 width=0) (actual
time=124.162..124.859 rows=106 loops=1)
Hash Cond: ("outer".id = "inner".symptom_id)
-> Seq Scan on symptoms (cost=0.00..3.07 rows=107
width=4) (actual time=0.032..0.295 rows=108 loops=1)
-> Hash (cost=362.25..362.25 rows=67 width=4) (actual
time=124.101..124.101 rows=106 loops=1)
-> HashAggregate (cost=361.58..362.25 rows=67
width=4) (actual time=123.628..123.854 rows=106 loops=1)
-> Hash IN Join (cost=35.26..361.41 rows=67
width=4) (actual time=9.767..96.372 rows=13074 loops=1)
Hash Cond: ("outer".user_id = "inner".id)
-> Seq Scan on symptom_reports sr
(cost=0.00..259.65 rows=13165 width=8) (actual time=0.029..33.359
rows=13074 loops=1)
-> Hash (cost=35.24..35.24 rows=11
width=4) (actual time=9.696..9.696 rows=1470 loops=1)
-> Bitmap Heap Scan on users
(cost=2.04..35.24 rows=11 width=4) (actual time=0.711..6.347
rows=1470 loops=1)
Recheck Cond: (disease_id = 1)
-> Bitmap Index Scan on
users_disease_id_index (cost=0.00..2.04 rows=11 width=0) (actual
time=0.644..0.644 rows=2378 loops=1)
Index Cond: (disease_id
= 1)
Total runtime: 134.045 ms
(15 rows)
plm_demo=# vacuum analyze;
VACUUM
plm_demo=# analyze;
ANALYZE
plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms
WHERE ( 1=1 and symptoms.id in (select symptom_id from
symptom_reports sr where 1=1 and sr.user_id in (select id from users
where disease_id=1))) ;
QUERY
PLAN
-
Aggregate (cost=586.47..586.48 rows=1 width=0) (actual
time=3441.385..3441.386 rows=1 loops=1)
-> Nested Loop IN Join (cost=149.05..586.26 rows=85 width=0)
(actual time=54.517..3441.115 rows=106 loops=1)
Join Filter: ("outer".id = "inner".symptom_id)
-> Seq Scan on symptoms (cost=0.00..3.08 rows=108
width=4) (actual time=0.007..0.273 rows=108 loops=1)
-> Hash IN Join (cost=149.05..603.90 rows=13074 width=4)
(actual time=0.078..24.503 rows=3773 loops=108)
Hash Cond: ("outer".user_id = "inner".id)
-> Seq Scan on symptom_reports sr
(cost=0.00..258.74 rows=13074 width=8) (actual time=0.003..9.044
rows=3773 loops=108)
-> Hash (cost=145.38..145.38 rows=1470 width=4)
(actual time=7.608..7.608 rows=1470 loops=1)
-> Seq Scan on users (cost=0.00..145.38
rows=1470 width=4) (actual time=0.006..4.353 rows=1470 loops=1)
Filter: (disease_id = 1)
Total runtime: 3441.452 ms
(11 rows)
---(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
Re: [PERFORM] Opinions on Raid
On Sat, Mar 03, 2007 at 12:30:16PM +0100, Arjen van der Meijden wrote: > If you have a MegaCLI-version, I'd like to see it, if possible? That > would definitely save us some reinventing the wheel :-) A friend of mine just wrote MegaCli -AdpAllInfo -a0|egrep ' (Degraded|Offline|Critical Disks|Failed Disks)' | grep -v ': 0 $' which will output errors if there are any, and none otherwise. Or just add -q to the grep and check the return status. (Yes, simplistic, but often all you want to know is if all's OK or not...) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM]
Jeff Cole <[EMAIL PROTECTED]> writes: > Hi, I'm new to tuning PostgreSQL and I have a query that gets slower > after I run a vacuum analyze. I believe it uses a Hash Join before > the analyze and a Nested Loop IN Join after. It seems the Nested > Loop IN Join estimates the correct number of rows, but underestimates > the amount of time required. I am curious why the vacuum analyze > makes it slower and if that gives any clues as too which parameter I > should be tuning. Hm, the cost for the upper nestloop is way less than you would expect given that the HASH IN join is going to have to be repeated 100+ times. I think this must be due to a very low "join_in_selectivity" estimate but I'm not sure why you are getting that, especially seeing that the rowcount estimates aren't far off. Can you show us the pg_stats rows for symptoms.id and symptom_reports.symptom_id? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] strange performance regression between 7.4 and 8.1
On 01.03.2007, at 13:40, Alex Deucher wrote: I read several places that the SAN might be to blame, but testing with bonnie and dd indicates that the SAN is actually almost twice as fast as the scsi discs in the old sun server. I've tried adjusting just about every option in the postgres config file, but performance remains the same. Any ideas? As mentioned last week: Did you actually try to use the local drives for speed testing? It might be that the SAN introduces latency especially for random access you don't see on local drives. cug ---(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
Re: [PERFORM] Estimate the size of the SQL file generated by pg_dump utility
Bruce Momjian wrote: Ravindran G-TLS,Chennai. wrote: Note: Please bear with us for the disclaimer because it is automated in the exchange server. Regards, Ravi FYI, we are getting closer to rejecting any email with such a disclaimer, or emailing you back every time saying we are ignoring the disclaimer. I think this issue cropped up a year or two ago, and one of the suggestions was for the offender to simply put a link back to their disclaimer at the foot of their email, rather than that uber-verbose message. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Estimate the size of the SQL file generated by pg_dump utility
Bricklen Anderson wrote: > Bruce Momjian wrote: > > Ravindran G-TLS,Chennai. wrote: > >> Note: Please bear with us for the disclaimer because it is automated in > >> the exchange server. > >> Regards, > >> Ravi > > > > FYI, we are getting closer to rejecting any email with such a > > disclaimer, or emailing you back every time saying we are ignoring the > > disclaimer. > > I think this issue cropped up a year or two ago, and one of the > suggestions was for the offender to simply put a link back to their > disclaimer at the foot of their email, rather than that uber-verbose > message. Right. The problem is that most of the posters have no control over their footers --- it is added by their email software. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] strange performance regression between 7.4 and 8.1
On 3/5/07, Guido Neitzer <[EMAIL PROTECTED]> wrote: On 01.03.2007, at 13:40, Alex Deucher wrote: > I read several places that the SAN might be to blame, but > testing with bonnie and dd indicates that the SAN is actually almost > twice as fast as the scsi discs in the old sun server. I've tried > adjusting just about every option in the postgres config file, but > performance remains the same. Any ideas? As mentioned last week: Did you actually try to use the local drives for speed testing? It might be that the SAN introduces latency especially for random access you don't see on local drives. Yes, I started setting that up this afternoon. I'm going to test that tomorrow and post the results. Alex cug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] strange performance regression between 7.4 and 8.1
On 05.03.2007, at 19:56, Alex Deucher wrote: Yes, I started setting that up this afternoon. I'm going to test that tomorrow and post the results. Good - that may or may not give some insight in the actual bottleneck. You never know but it seems to be one of the easiest to find out ... cug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] query slows down after vacuum analyze
Are you sure that:
SELECT count(distinct s.id) AS count_all
FROM symptoms s ,symptom_reports sr,users u
WHERE s.id=sr.symptom_id and sr.user_id=u.id and u.disease_id=1;
is as slow as
SELECT count(*) AS count_all
FROM symptoms
WHERE (1=1 and symptoms.id in (
select symptom_id from symptom_reports sr
where 1=1 and sr.user_id in (
select id from users where disease_id=1
)
)
);
I think that it's best to have database to deside how to find rows, so I
like to write all as "clean" as possible.
only when queries are slow I analyze them and try to write those different
way.
that have worked great in oracle, where it seems that "cleanest" query is
always fastest. in postgres it's not always true, sometimes you must write
subqueries to make it faster.
Ismo
On Mon, 5 Mar 2007, Jeff Cole wrote:
> Hi, I'm new to tuning PostgreSQL and I have a query that gets slower after I
> run a vacuum analyze. I believe it uses a Hash Join before the analyze and a
> Nested Loop IN Join after. It seems the Nested Loop IN Join estimates the
> correct number of rows, but underestimates the amount of time required. I am
> curious why the vacuum analyze makes it slower and if that gives any clues as
> too which parameter I should be tuning.
>
> BTW, I know the query could be re-structured more cleanly to remove the
> sub-selects, but that doesn't seem to impact the performance.
>
> thanks,
> Jeff
>
> Welcome to psql 8.1.5, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help with psql commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms WHERE (
> 1=1 and symptoms.id in (select symptom_id from symptom_reports sr where 1=1
> and sr.user_id in (select id from users where disease_id=1))) ;
>
> QUERY
> PLAN
> ---
> Aggregate (cost=366.47..366.48 rows=1 width=0) (actual time=125.093..125.095
> rows=1 loops=1)
> -> Hash Join (cost=362.41..366.38 rows=36 width=0) (actual
> -> time=124.162..124.859 rows=106 loops=1)
> Hash Cond: ("outer".id = "inner".symptom_id)
> -> Seq Scan on symptoms (cost=0.00..3.07 rows=107 width=4) (actual
> -> time=0.032..0.295 rows=108 loops=1)
> -> Hash (cost=362.25..362.25 rows=67 width=4) (actual
> -> time=124.101..124.101 rows=106 loops=1)
> -> HashAggregate (cost=361.58..362.25 rows=67 width=4) (actual
> -> time=123.628..123.854 rows=106 loops=1)
> -> Hash IN Join (cost=35.26..361.41 rows=67 width=4)
> -> (actual time=9.767..96.372 rows=13074 loops=1)
> Hash Cond: ("outer".user_id = "inner".id)
> -> Seq Scan on symptom_reports sr
> -> (cost=0.00..259.65 rows=13165 width=8) (actual
> -> time=0.029..33.359 rows=13074 loops=1)
> -> Hash (cost=35.24..35.24 rows=11 width=4)
> -> (actual time=9.696..9.696 rows=1470 loops=1)
> -> Bitmap Heap Scan on users
> -> (cost=2.04..35.24 rows=11 width=4) (actual
> -> time=0.711..6.347 rows=1470 loops=1)
> Recheck Cond: (disease_id = 1)
> -> Bitmap Index Scan on
> users_disease_id_index (cost=0.00..2.04
> rows=11 width=0) (actual
> time=0.644..0.644 rows=2378 loops=1)
> Index Cond: (disease_id = 1)
> Total runtime: 134.045 ms
> (15 rows)
>
>
> plm_demo=# vacuum analyze;
> VACUUM
> plm_demo=# analyze;
> ANALYZE
>
> plm_demo=# explain analyze SELECT count(*) AS count_all FROM symptoms WHERE (
> 1=1 and symptoms.id in (select symptom_id from symptom_reports sr where 1=1
> and sr.user_id in (select id from users where disease_id=1))) ;
> QUERY PLAN
> -
> Aggregate (cost=586.47..586.48 rows=1 width=0) (actual
> time=3441.385..3441.386 rows=1 loops=1)
> -> Nested Loop IN Join (cost=149.05..586.26 rows=85 width=0) (actual
> -> time=54.517..3441.115 rows=106 loops=1)
> Join Filter: ("outer".id = "inner".symptom_id)
> -> Seq Scan on symptoms (cost=0.00..3.08 rows=108 width=4) (actual
> -> t
Re: [PERFORM] Insert performance
hatman wrote: Dear all, After many tests and doc reading, i finally try to get help from you... Here is my problem. With some heavy insert into a simple BD (one table, no indexes) i can't get better perf than 8000 inserts/sec. I'm testing it using a simple C software which use libpq and which use: - Insert prepared statement (to avoid too many request parsing on the server) - transaction of 10 inserts Are each of the INSERTs in their own transaction? If so, you'll be limited by the speed of the disk the WAL is running on. That means you have two main options: 1. Have multiple connections inserting simultaneously. 2. Batch your inserts together, from 10 to 10,000 per transaction. Are either of those possible? -- Richard Huxton Archonet Ltd ---(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] Hibernate left join
[EMAIL PROTECTED] wrote: Hi , I want to know about hibernate left join, Is their any way to do left join in hibernate ?. Please give me an example of hibernate left join with its maaping This isn't really a performance question, or even a PostgreSQL question. You'll do better asking this on the pgsql-general list and even better asking on a hibernate-related list. Another tip - for these sorts of questions many projects have details in their documentation. Google can help you here - try searching for "hibernate manual left join" and see if the results help. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
