Re: [HACKERS] Postres dilemma
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Why are you running a vacuum every 45 seconds? Increase your fsm_pages and run it every hour. If I understood his description correctly, he's turning over 10% of a 500-row table every minute. So waiting an hour would mean 3000 dead rows in a 500-live-row table, which seems excessive. I'd agree with running a vacuum on this specific table every five minutes or so. Given that he is doing more than enough vacuums, I think that the problem is probably not table bloat, but index bloat (ie, from a constantly shifting range of live index keys, which pre-7.4 btrees didn't handle well at all). This is just speculation though, without proof as yet. Another information to know is if there are connection in the unfamous state: Idle in transaction. Is usefull if the OP show us the vacuum verbose output. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Postres dilemma
Hi I am using Postgres 7.3.4 over linux Redhat 7.3 on i686 machine. My app has one parent table and five child tables. I mean the parent table has a primary key and child tables have foreign key relationship with parent. My App is doing 500 inserts initially in each table. After all this done, we inserting 50 in each table and deleting previous 50 records every seconds. System performs well for awhile (30 Hrs). After 30 hrs I seen that dir size of $PGDATA/base dir is keep on growing and to goes up to 2G in 48 hrs. App is also doing vacuum every 45 seconds. Every time vacuum is triggered, the system goes extreamly slugginsh, and results in various errors like deadlock detected(confirmed in the $PGDATA/../LOG/logfile). vmstat is also showing that blocks sents to the block device (disk) is going crazy. I do not know what is the remedy for this problem. If someone has come across to the issue, please help me as soon as possible. ++ NOTE: I can not use Postgres 7.4 and higher releases beacuse postmaster crashes gauranteed in (20hrs). I have already reported this bug many times (Bug # 1104 is one of them). All crashes show the same behavior and error messages. (specified item offset is too large) ++ I appreciate if some one have the solution for my problem. otherwise it looks like all our app development done on top of Postgres is going in vain. Thanking you in advance. Neeraj K Sharma email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postres dilemma
Hello, Perhaps you could provide some more detailed information? Example of queries? Type of hardware? Operating system? Why are you running a vacuum every 45 seconds? Increase your fsm_pages and run it every hour. Are you sure the vacuums are trampling eachother and thus getting more than one vacuum running at a time? J Neeraj Sharma wrote: Hi I am using Postgres 7.3.4 over linux Redhat 7.3 on i686 machine. My app has one parent table and five child tables. I mean the parent table has a primary key and child tables have foreign key relationship with parent. My App is doing 500 inserts initially in each table. After all this done, we inserting 50 in each table and deleting previous 50 records every seconds. System performs well for awhile (30 Hrs). After 30 hrs I seen that dir size of $PGDATA/base dir is keep on growing and to goes up to 2G in 48 hrs. App is also doing vacuum every 45 seconds. Every time vacuum is triggered, the system goes extreamly slugginsh, and results in various errors like deadlock detected(confirmed in the $PGDATA/../LOG/logfile). vmstat is also showing that blocks sents to the block device (disk) is going crazy. I do not know what is the remedy for this problem. If someone has come across to the issue, please help me as soon as possible. ++ NOTE: I can not use Postgres 7.4 and higher releases beacuse postmaster crashes gauranteed in (20hrs). I have already reported this bug many times (Bug # 1104 is one of them). All crashes show the same behavior and error messages. (specified item offset is too large) ++ I appreciate if some one have the solution for my problem. otherwise it looks like all our app development done on top of Postgres is going in vain. Thanking you in advance. Neeraj K Sharma email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postres dilemma
Joshua D. Drake [EMAIL PROTECTED] writes: Why are you running a vacuum every 45 seconds? Increase your fsm_pages and run it every hour. If I understood his description correctly, he's turning over 10% of a 500-row table every minute. So waiting an hour would mean 3000 dead rows in a 500-live-row table, which seems excessive. I'd agree with running a vacuum on this specific table every five minutes or so. Given that he is doing more than enough vacuums, I think that the problem is probably not table bloat, but index bloat (ie, from a constantly shifting range of live index keys, which pre-7.4 btrees didn't handle well at all). This is just speculation though, without proof as yet. regards, tom lane ---(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
