Обсуждение: 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: neeraj.sharma@arroyo.tv neerajsharma@hotmail.com
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: neeraj.sharma@arroyo.tv > neerajsharma@hotmail.com > >---------------------------(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 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
"Joshua D. Drake" <jd@commandprompt.com> 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
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> 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