Обсуждение: 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);
>
>
>