Обсуждение: low performance
I am running the precomplied binary of Postgreql 7.1.2 on a Redhat 7.1 (on a Dual Celeron System with 256MB, kernel 2.4.4and 2.4.5) System. (The installation of the new 7.1.3 doesn't seem to solve the problem) I am connecting to the DB with a Perl Program (using Perl 5.6.0 with DBD-Pg-1.01 and DBI-1.19). The program inserts some million rows into a db with about 30 tables. The processing takes (if everyting works fine) about10 hours to complete. Usually the my Perl-Script and the database share the available CPU time 50:50. But sometimes the database is very slow eating up most (>98%) of the available CPU time. (Of cause I know VACUUM and VACUUM ANALYZE, this is not the problem). The only thing that seems to help then, is killing the perl script, stopping postgresql, running "ipcclean", and start againfrom the beginning. If it works from the beginning, the database is ususally very fast until all data are processed. But if someone else connects (using psql), sometimes the database gets very slow until it is using all the CPU time. There are no error messages at postgres-startup. I already increased the number of buffers to 2048 (doesn't help) I cannot reproduce these problems, sometimes the db is fast, sometimes very slow. The perl script doesn't seem to be theproblem, because I wrote all SQL Commands to a file and processed them later ("psql dbname postgres < SQL-File"). Same thing: sometimes slow sometimes fast. Andreas
Is this running as one transaction, or is it not a transaction? Have you tried committing every 10,000 or so if it is in one transaction? It could be a logging problem with the transaction being too big. Does the file system as a whole get slow, or just Postgres? Is it one connection, or does it disconnect and reconnect a lot? Is it the main postmaster sucking up all the CPU, or the one spawned by the PERL, or the one spawned by psql? How much do the file system cache and io buffers grow? ______________________________________________________________________________ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);
It is running on many transactions. At least after 5 inserts a transaction is commited. The filesystems doesn't get slow (reading a (big) file works still at >20 MBytes/s). 14839 postgres 20 0 19948 19M 18980 R 98.5 7.7 477:24 postmaster 14819 postgres 8 0 1856 1856 1700 S 0.0 0.7 0:00 postmaster 14838 andreas 9 0 15228 14M 1796 S 0.7 5.9 11:58 parse.pl The main postmaster is job 14819 (0.0% CPU). The postmaster spawned by perl is sucking up 98.5% CPU. cat /proc/meminfo writes: total: used: free: shared: buffers: cached: Mem: 261959680 260149248 1810432 0 6115328 129863680 Swap: 133885952 204800 133681152 MemTotal: 255820 kB MemFree: 1768 kB MemShared: 0 kB Buffers: 5972 kB Cached: 126820 kB Active: 38432 kB Inact_dirty: 83408 kB Inact_clean: 10952 kB Inact_target: 520 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 255820 kB LowFree: 1768 kB SwapTotal: 130748 kB SwapFree: 130548 kB On Mon, 20 Aug 2001 10:28:04 -0700 (MST) grant <grant@amadensor.com> wrote: > Is this running as one transaction, or is it not a transaction? Have you > tried committing every 10,000 or so if it is in one transaction? It could > be a logging problem with the transaction being too big. > > Does the file system as a whole get slow, or just Postgres? Is it one > connection, or does it disconnect and reconnect a lot? > > Is it the main postmaster sucking up all the CPU, or the one spawned by > the PERL, or the one spawned by psql? > > How much do the file system cache and io buffers grow? > ______________________________________________________________________________ > > Your mouse has moved. > You must restart Windows for your changes to take effect. > > #!/usr/bin/perl > print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); > > >