Обсуждение: Upgraded to 8.2.3 --- still having performance issues

От:
Carlos Moreno
Дата:

As the subject says.  A quite puzzling situation:  we not only upgraded the
software, but also the hardware:

Old system:

PG 7.4.x  on Red Hat 9  (yes, it's not a mistake!!!)
P4 HT 3GHz with 1GB of RAM and IDE hard disk (120GB, I believe)

New system:
PG 8.2.3 on Fedora Core 4
Athlon64 X2  4200+  with 2GB of RAM and SATA hard disk (250GB)

I would have expected a mind-blowing increase in responsiveness and
overall performance.  However, that's not the case --- if I didn't know
better, I'd probably tend to say that it is indeed the opposite
(performance
seems to have deteriorated)

I wonder if some configuration parameters have somewhat different
meaning, or the considerations around them are different?   Here's what
I have in postgresql.conf  (the ones I believe are relevant) :

max_connections = 100
shared_buffers = 1024MB
#temp_buffers = 8MB
#max_prepared_transactions = 5
#work_mem = 1MB
#maintenance_work_mem = 16MB
#max_stack_depth = 2MB
max_fsm_pages = 204800
checkpoint_segments = 10

Here's my eternal confusion --- the kernel settings for shmmax and shmall:
I did the following in /ec/rc.local, before starting postgres:

echo -n "1342177280" > /proc/sys/kernel/shmmax
echo -n "83886080" > /proc/sys/kernel/shmall

I still haevn't found any docs that clarify this issue  I know it's not
PG-specific,
but Linux kernel specific, or maybe even distro-specific??)

For shmall, I read "if in bytes, then ...., if in pages, then ....", and
I see
a reference to PAGE_SIZE  (if memory serves --- no pun intended!);
How would I know if the spec has to be given in bytes or in pages?
And if in pages, how can I know the page size??   I put it like this to
maintain the ratio between the numbers that were by default.  But I'm
still puzzled by this.

PostgreSQL does start  (which it wouldn't if I put shmmax too low),
which suggests to me that the setting is ok ...  Somehow, I'm extremely
uncomfortable with having to settle for a "seems like it's fine".

The system does very frequent insertions and updates --- the longest
table has, perhaps, some 20 million rows, and it's indexed (the primary
key is the combination of two integer fields).  This longest table only
has inserts (and much less frequent selects), at a peak rate of maybe
one or a few insertions per second.

The commands  top  and  ps  seem to indicate that postgres is quite
comfortable in terms of CPU  (CPU idle time rarely goes below 95%).
vmstat indicates activity, but it all looks quite smooth  (si and so are
always 0 --- without exception).

However, I'm seeing the logs of my application, and right now the
app. is inserting records from last night around midnight (that's a
12 hours delay).

Any help/tips/guidance in troubleshooting this issue?  It will be
much appreciated!

Thanks,

Carlos
--


От:
Tom Lane
Дата:

Carlos Moreno <> writes:
> I would have expected a mind-blowing increase in responsiveness and
> overall performance.  However, that's not the case --- if I didn't know
> better, I'd probably tend to say that it is indeed the opposite
> (performance seems to have deteriorated)

Did you remember to re-ANALYZE everything after loading up the new
database?  That's a frequent gotcha ...

            regards, tom lane

От:
Carlos Moreno
Дата:

Tom Lane wrote:

>Carlos Moreno <> writes:
>
>
>>I would have expected a mind-blowing increase in responsiveness and
>>overall performance.  However, that's not the case --- if I didn't know
>>better, I'd probably tend to say that it is indeed the opposite
>>(performance seems to have deteriorated)
>>
>>
>
>Did you remember to re-ANALYZE everything after loading up the new
>database?  That's a frequent gotcha ...
>
>

I had done it, even though I was under the impression that it wouldn't be
necessary with 8.2.x  (I still chose to do it just in case).

I've since discovered a problem that *may* be related to the deterioration
of the performance *now* --- but that still does not explain the machine
choking since last night, so any comments or tips are still most welcome.

Thanks,

Carlos
--


От:
Rodrigo Gonzalez
Дата:

Carlos Moreno wrote:
> Tom Lane wrote:
>
>> Carlos Moreno <> writes:
>>
>>
>>> I would have expected a mind-blowing increase in responsiveness and
>>> overall performance.  However, that's not the case --- if I didn't know
>>> better, I'd probably tend to say that it is indeed the opposite
>>> (performance seems to have deteriorated)
>>>
>>
>> Did you remember to re-ANALYZE everything after loading up the new
>> database?  That's a frequent gotcha ...
>>
>>
>
> I had done it, even though I was under the impression that it wouldn't be
> necessary with 8.2.x  (I still chose to do it just in case).
>
> I've since discovered a problem that *may* be related to the deterioration
> of the performance *now* --- but that still does not explain the machine
> choking since last night, so any comments or tips are still most welcome.
>
> Thanks,
>
> Carlos
> --
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

And the problem that *may* be related is....?

All the information is required so someone can give you good information...

От:
Carlos Moreno
Дата:

Rodrigo Gonzalez wrote:

>
>> I've since discovered a problem that *may* be related to the
>> deterioration
>> of the performance *now* --- but that still does not explain the machine
>> choking since last night, so any comments or tips are still most
>> welcome.
>> [...]
>>
> And the problem that *may* be related is....?
>
> All the information is required so someone can give you good
> information...


You are absolutely right, of course --- it was an instance of "making a
long
story short" for everyone's benefit  :-)

To make the story as short as possible:  I was running a program that does
clean up on the database (move records older than 60 days).  That program
creates log files, and it exhausted the available space on the /home
partition
(don't ask!  :-)).

The thing is, all of postgres's data is below the /var partition (which
has a
total of 200GB, and still around 150GB available) --- in particular, the
postgres' home directory is /var/users/postgres, and the database cluster's
data directory is /var/users/postgres/data --- that tells me that this
issue with
the /home partition should not make postgres itself choke;  the clean up
program was totally choking, of course.

And yes, after realizing that, I moved the cleanup program to some place
below the /var directory, and /home now has 3.5GB available.

Thanks,

Carlos
--


От:
Carlos Moreno
Дата:

Are there any issues with client libraries version mismatching
backend version?

I'm just realizing that the client software is still running on the
same machine (not the same machine where PG is running) that
has PG 7.4 installed on it, and so it is using the client libraries 7.4

Any chance that this may be causing trouble on the performance
side?   (I had been monitoring the logs to watch for SQLs now
failing when they worked before...  But I was thinking rather
incompatibilities on the backend side ... )

Thanks,

Carlos
--


От:
Dennis Bjorklund
Дата:

Carlos Moreno skrev:
> The system does very frequent insertions and updates --- the longest
> table has, perhaps, some 20 million rows, and it's indexed (the primary
> key is the combination of two integer fields).  This longest table only
> has inserts (and much less frequent selects), at a peak rate of maybe
> one or a few insertions per second.

One or a few inserts per second doesn't sound like that much. I would
have expected it to work. If you can you might want to group several
inserts into a single transaction.

A standard hint is also to move the WAL onto its own disk. Or get a disk
controller with battery backed up ram.

But it's hard to say from your description what the bottleneck is and
thus hard to give any advice.

> Any help/tips/guidance in troubleshooting this issue?  It will be
> much appreciated!

You could try to find specific queries that are slow. Pg can for example
log queries for you that run for longer than X seconds.

/Dennis

От:
Carlos Moreno
Дата:

Tom Lane wrote:

>Carlos Moreno <> writes:
>
>
>>I would have expected a mind-blowing increase in responsiveness and
>>overall performance.  However, that's not the case --- if I didn't know
>>better, I'd probably tend to say that it is indeed the opposite
>>(performance seems to have deteriorated)
>>
>>
>
>Did you remember to re-ANALYZE everything after loading up the new
>database?  That's a frequent gotcha ...
>
>

I did.

I didn't think it would be necessary, but being paranoid as I am, I figured
let's do it just in case.

After a few hours of operation, I did a vacuumdb -z  also.  But it seems to
continue downhill   :-(

Thanks,

Carlos
--