Re: [PERFORM] plain inserts and deletes very slow
Hmm, you said you don't experience this when executing the query manually. What adapter are you using to access postgres from your application? libpq, npgsql or something else? And what is your method for running the query 'manually'. Are you running it locally or from a remote machine or what? Regards David Enrico Weigelt wrote: * David Mitchell <[EMAIL PROTECTED]> wrote: Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). hmm. I've just done vacuum full at the moment on these tables, but it doesnt seem to change anything :( cu -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] plain inserts and deletes very slow
On Mon, Jul 04, 2005 at 02:17:47AM +0200, Enrico Weigelt wrote: > * David Mitchell <[EMAIL PROTECTED]> wrote: > > Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 > > minutes would be closer to your mark. Try vacuuming every 15 minutes for > > a start and see how that affects things (you will have to do a vacuum > > full to get the tables back into shape after them slowing down as they > > have). > > hmm. I've just done vacuum full at the moment on these tables, but it > doesnt seem to change anything :( Maybe you need a REINDEX, if you have indexes on that table. Try that, coupled with the frequent VACUUM suggestion. -- Alvaro Herrera () "World domination is proceeding according to plan"(Andrew Morton) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] plain inserts and deletes very slow
* David Mitchell <[EMAIL PROTECTED]> wrote: > Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 > minutes would be closer to your mark. Try vacuuming every 15 minutes for > a start and see how that affects things (you will have to do a vacuum > full to get the tables back into shape after them slowing down as they > have). hmm. I've just done vacuum full at the moment on these tables, but it doesnt seem to change anything :( cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.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] plain inserts and deletes very slow
Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). David Enrico Weigelt wrote: * David Mitchell <[EMAIL PROTECTED]> wrote: Did you vacuum full? When you do lots of inserts and deletes, dead tuples get left behind. When you vacuum, postgres will reuse those dead tuples, but if you don't vacuum for a long time these tuples will build up lots. Even when you vacuum in this case, the dead tuples are still there, although they are marked for reuse. Vacuuming full actually removes the dead tuples. I'm doing a VACUUM ANALYZE every 6 hours. vacuum'ing manually doesnt seem to have any effect on that. cu -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
* David Mitchell <[EMAIL PROTECTED]> wrote: > Did you vacuum full? > > When you do lots of inserts and deletes, dead tuples get left behind. > When you vacuum, postgres will reuse those dead tuples, but if you don't > vacuum for a long time these tuples will build up lots. Even when you > vacuum in this case, the dead tuples are still there, although they are > marked for reuse. Vacuuming full actually removes the dead tuples. I'm doing a VACUUM ANALYZE every 6 hours. vacuum'ing manually doesnt seem to have any effect on that. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: 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] plain inserts and deletes very slow
Did you vacuum full? When you do lots of inserts and deletes, dead tuples get left behind. When you vacuum, postgres will reuse those dead tuples, but if you don't vacuum for a long time these tuples will build up lots. Even when you vacuum in this case, the dead tuples are still there, although they are marked for reuse. Vacuuming full actually removes the dead tuples. If you vacuum (normal) regularly, then the number of dead tuples will stay down, as they are regularly marked for reuse. David Enrico Weigelt wrote: * Enrico Weigelt <[EMAIL PROTECTED]> wrote: forgot to mention: + linux-2.6.9 + postgres-7.4.6 + intel celeron 2ghz + intel ultra ata controller + 768mb ram cu -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
* Enrico Weigelt <[EMAIL PROTECTED]> wrote: forgot to mention: + linux-2.6.9 + postgres-7.4.6 + intel celeron 2ghz + intel ultra ata controller + 768mb ram cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: 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] plain inserts and deletes very slow
On Mon, Jul 04, 2005 at 12:45:37AM +0200, Enrico Weigelt wrote: > my application reads and writes some table quite often > (multiple times per second). these tables are quite small > (not more than 20 tuples), but the operations take quite a > long time (>300 ms!). Are you VACUUMing often enough? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] plain inserts and deletes very slow
* Steinar H. Gunderson <[EMAIL PROTECTED]> wrote: > On Mon, Jul 04, 2005 at 12:45:37AM +0200, Enrico Weigelt wrote: > > my application reads and writes some table quite often > > (multiple times per second). these tables are quite small > > (not more than 20 tuples), but the operations take quite a > > long time (>300 ms!). > > Are you VACUUMing often enough? I've just VACUUM'ed multiple times, so it's perhaps not the problem. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] plain inserts and deletes very slow
Hi folks, my application reads and writes some table quite often (multiple times per second). these tables are quite small (not more than 20 tuples), but the operations take quite a long time (>300 ms!). The query operations are just include text matching (=) and date comparison (<,>). I wasn't yet able to track down, if all these queries take sucha long time or just sometimes. When running them manually or trying explain, evrything's fast. Probably there could be some side effects with other concurrent quries. Could anyone give me advice ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.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] investigating slow queries through pg_stat_activity
* Dan Harris <[EMAIL PROTECTED]> wrote: Hi, > I've got some queries generated by my application that will, for some > reason, run forever until I kill the pid. Yet, when I run the > queries manually to check them out, they usually work fine. If you can change your application, you could try to encapsulate the queries into views - this makes logging and tracking down problems much easier. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
