Thread: Upgraded to 8.2.3 --- still having performance issues
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 --
Carlos Moreno <moreno_pg@mochima.com> 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
Tom Lane wrote: >Carlos Moreno <moreno_pg@mochima.com> 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 --
Carlos Moreno wrote: > Tom Lane wrote: > >> Carlos Moreno <moreno_pg@mochima.com> 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...
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 --
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 --
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
Tom Lane wrote: >Carlos Moreno <moreno_pg@mochima.com> 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 --