Обсуждение: 7.0.2 -> 7.1 performance drop
When I upgraded from 7.0.2 to 7.1, one of my batch scripts went from 10 to 20 minutes? Some time ago I wrote a script that imports a 1.5M text file into 3 tables (about 7K, 9K, 9.5K rows). I had set the script to run nightly when the input file was changing regularly. However, the input hasn't changed now for several weeks. Last week I upgraded from 7.0.2 to 7.1 and now notice that the script takes 20min instead of 10min. I'm running on an OpenBSD box and pg releases were installed from 2.8 and 2.9 packages respectively. The OpenBSD upgrade is not a factor, because I upgraded that first and was running 7.0.2 under 2.9 (script was still 10 min) for about three weeks before the pg upgrade. The script just deletes all rows in tables, imports data as seperate inserts for each row, then does vaccum analyse on the tables. I've read that delete/rebuild of indexes will likely improve performance of this script, but the runtime itself is not really a concern here, just the change in runtime between releases. I'm curious what changed between releases to cause the slower processing? Frank
--- Frank Bax <fbax@sympatico.ca> wrote: > The script just deletes all rows in > tables, imports > data as seperate inserts for each row, then does vaccum > analyse on the > tables. I've read that delete/rebuild of indexes will likely > improve > performance of this script, but the runtime itself is not > really a concern > here, just the change in runtime between releases. I'm > curious what > changed between releases to cause the slower processing? Your script performs 3 separate actions. Do you know which one (delete, import, vacuum analyze) is slower when using the new version of PG? That will help pinpoint the problem so that we can more easily solve the problem. Brent Brent __________________________________________________ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com
The script spent 20.91 of 21.19 minutes on the import process. I guess we can rule out delete and vacuum as the source of problem. Expecting more questions like yours, I did a little more research. The import is all INSERT's except for two selects. I realised I could rewrite a portion of the code to remove one of those SELECT's. Guess What no effect on run-time. Isn't that curious? So I pondered that for a while and thought that if removeing one SELECT didn't make any difference, then making changes to the second SELECT (which was on same table) wsn't likely to change anything either. If that's true then the problem was only INSERT's. A little more pondering and I remember that I looked a little closer at startup parms during the PG upgrade. I had removed -F option. Put it back in and runtime dropped to 10 minutes again - problem solved! Frank Brent R. Matzelle <bmatzelle@yahoo.com> wrote: >--- Frank Bax <fbax@sympatico.ca> wrote: >> The script just deletes all rows in tables, imports >> data as seperate inserts for each row, then does vacuum >> analyse on the tables. I've read that delete/rebuild >> of indexes will likely improve performance of this >> script, but the runtime itself is not really a concern >> here, just the change in runtime between releases. I'm >> curious what changed between releases to cause the >> slower processing? > >Your script performs 3 separate actions. Do you know which one >(delete, import, vacuum analyze) is slower when using the new >version of PG? That will help pinpoint the problem so that we >can more easily solve the problem. > >Brent
On Mon, 12 Nov 2001, Frank Bax wrote: > Expecting more questions like yours, I did a little more research. The > import is all INSERT's except for two selects. I realised I could rewrite > a portion of the code to remove one of those SELECT's. Guess What no > effect on run-time. Isn't that curious? So I pondered that for a while > and thought that if removeing one SELECT didn't make any difference, then > making changes to the second SELECT (which was on same table) wsn't likely > to change anything either. If that's true then the problem was only > INSERT's. A little more pondering and I remember that I looked a little > closer at startup parms during the PG upgrade. I had removed -F option. > Put it back in and runtime dropped to 10 minutes again - problem solved! Maybe you are paying too much transaction overhead. Is your script one single transaction? If you explicitly make it so by means of enclosing it all in a BEGIN-COMMIT block (or several, depending on how much INSERT clauses you have) it will perhaps run much faster. HTH... -- Alvaro Herrera (<alvherre[@]atentus.com>) "Crear es tan dificil como ser libre" (Elsa Triolet)
Frank Bax <fbax@sympatico.ca> writes: > A little more pondering and I remember that I looked a little > closer at startup parms during the PG upgrade. I had removed -F option. > Put it back in and runtime dropped to 10 minutes again - problem solved! You do understand the implications of -F for your data integrity, right? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Doug McNaught <doug@wireboard.com> writes: > Frank Bax <fbax@sympatico.ca> writes: > > > A little more pondering and I remember that I looked a little > > closer at startup parms during the PG upgrade. I had removed -F option. > > Put it back in and runtime dropped to 10 minutes again - problem solved! > > You do understand the implications of -F for your data integrity, > right? Of course, he is rebuilding these tables from scratch every night. My guess is that integrity is not much of an issue. On the other hand Frank, If you could work your inserts into one large transaction you would probably get quite a bit of the benefits of removing the -F option without risking the integrity of your database. Jason
At 05:13 PM 11/12/01 -0700, Jason Earl wrote: >Doug McNaught <doug@wireboard.com> writes: > >> Frank Bax <fbax@sympatico.ca> writes: >> >> > A little more pondering and I remember that I looked a little >> > closer at startup parms during the PG upgrade. I had removed -F option. >> > Put it back in and runtime dropped to 10 minutes again - problem solved! >> >> You do understand the implications of -F for your data integrity, >> right? > >Of course, he is rebuilding these tables from scratch every night. My >guess is that integrity is not much of an issue. > >On the other hand Frank, If you could work your inserts into one large >transaction you would probably get quite a bit of the benefits of >removing the -F option without risking the integrity of your database. On the other hand, I do have other tables to worry about on my system... I read up on -F during upgrade; that's why I decided to take it out; default install from OpenBSD packages has this option included in suggested startup script. I was just confirming that this option accounted for all the time difference in processing of my script. I plan to investigate the impact of using transactions without -F in the near future. Thanks for the help guys. I've been unable to access email for a couple of days. Sorry about delay in replying. Frank