Select hangs and there are lots of files in table and index directories.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Have a problem where a stored procedure is taking a week to run.  The stored procedure should take less than a second to run.   In researching a select hanging problem, three things are suggested; an autovacuum problem, a resource is locked, or there is something wrong with the stored procedure.

·         Autovacuum is running.  A ‘ps –elf | grep postgres’ shows:

00:00:43 postgres: logger process

00:5:50 postgres: writer process

00:3:04 postgres: wal writer process

00:00:48 postgres: autovacuum launcher process

00:00:50 postgres: stats collector process

00:01:28 postgres: operstions OPDB [local] idle

154.11.29 postgres: operstions OPDB [local] select

·         The select is from running a select of a stored procedure from a ‘c’ program using the PqsendQuery function.

·         Postgres.conf has both autovacuum and track_counts set to ‘on’.  All other autovacuum values are left as delivered (commented out).

·         A ‘select * from pg_stats_activity;’ shows no query is blocked.

·         We have recently changed from using Oracle 10g (running on Red Hat AS 4.5) to PostgreSQL 9.1.2 (running on CentOS 6.3).  The only differences between the two versions are:

o   Syntax changes between Oracle and Postgres.

o   In Oracle a commit was executed after each ‘chuck’ of work was done.  A commit is no longer used in Postgres because the Postgres documentation indicates that a commit has no affect until the end of the transaction (i.e., the end of the stored procedure).

o   The same stored procedure is running just fine on all of our test systems and at one of our two customer sites.  All of the systems are configured the same (same operating system and software).

Lastly, in the directories used to store the tables and indexes, there are 918896 files in the tables directory and 921291 files in the indexes directory.  All of the file names are just numbers (no extensions).  About 60 files are added to each directory every second.  On our test systems and at our other customer site, there are only about 50 files in each directory.

Why are there so many files?
Thank you everyone for your time.
Peter Blair

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux