Re: Performance advice

On 24 Jun 2003 at 9:39, Michael Mattox wrote:

> I'd like to get some feedback on my setup to see if I can optimize my
> database performance. My application has two separate applications:
>
> The first application connects to websites and records the statistics in the
> database. Websites are monitored every 5 or 10 minutes (depends on client),
> there are 900 monitors which comes out to 7,800 monitorings per hour. The
> monitor table has columns "nextdate" and "status" which are updated with
> every monitoring, and also a row is inserted into the status table and the
> status item table. For my performance testing (we're just about to go live)
> I've loaded the database with a month of data (we don't plan to keep data
> longer than 1 month). So my status table has 6 million records and my
> status item table has 6 million records as well. One key is that the system
> is multithreaded so up to 32 processes are accessing the database at the
> same time, updating the "nextdate" before the monitoring and inserting the
> status and status item records after. There is a serious performance
> constraint here because unlike a webserver, this application cannot slow
> down. If it slows down, we won't be able to monitor our sites at 5 minute
> intervals which will make our customers unhappy.
>
> The second application is a web app (tomcat) which lets customers check
> their status. Both of these applications are deployed on the same server, a
> 4 CPU (Xeon) with 1.5 gigs of RAM. The OS (RedHat Linux 7.3) and servers
> are running on 18gig 10,000 RPM SCSI disk that is mirrored to a 2nd disk.
> The database data directory is on a separate 36 gig 10,000 RPM SCSI disk
> (we're trying to buy a 2nd disk to mirror it). I'm using Postgres 7.3.2.

I recommend that you use a latest kernel with, pre-empt+low latency + O(1)
patches. First two are said to affect desktop only, but I believe a loaded
server need it as well.

I suggest you get latest kernel from kernel.org and apply con kolivas's patches
from http://members.optusnet.com.au/ckolivas/kernel/. That is the easiest way
around.

Furthermore if I/O throghput is an issue and you aer ready to experiment at
this stage, try freeBSD. Many out here believe that it has superior IO
scheduling and of course VM. If you move off your database server to another
machine, you might get a chance to play with it.

> Issue #1 - Vacuum => Overall the system runs pretty well and seems stable.
> Last night I did a "vacuum full analyze" and then ran my app overnight and
> first thing in the morning I did a "vacuum analyze", which took 35 minutes.
> I'm not sure if this is normal for a database this size (there are 15,000
> updates per hour). During the vacuum my application does slow down quite a
> bit and afterwards is slow speeds back up. I've attached the vacuum output
> to this mail. I'm using Java Data Objects (JDO) so if table/column names
> look weird it's because the schema is automatically generated.

That is expected given how much data you have inserted overnight. The changes
in status and status item table would need some time to come back.

Vacuum is IO intensive process. In case of freeBSD, if you lower the nice
priority, IO priority is also lowered. That mean a vacuum process with lower
priority will not hog disk bandwidth on freeBSD. Unfortunately not so on linux.
So the slowdown you are seeing is probably due to disk bandwidth congestion.

Clearly with a load like this, you can not rely upon scheduled vacuums. I
recommend you use pgavd in contrib directory in postgresql CVS tree. That would
vacuum the database whenever needed. It's much better than scheduled vacuum.

If you can not use it immediately, do a hourly vacuum analyze, may be even more
frequent. Nightly vacuum would simply not do.

>
> Issue #2 - postgres.conf => I'd love to get some feedback on these settings.
> I've read the archives and no one seems to agree I know, but with the above
> description of my app I hope someone can at least point me in the right
> direction:
>
> max_connections = 200
>
> #
> # Shared Memory Size
> #
> shared_buffers = 3072 # min max_connections*2 or 16, 8KB each

I would say of the order of 10K would be good. You need to experiment a bit to
find out what works best for you.

> #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
> #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes

You may bump these two as well. See past discussions for reference. Doubling
them would be a good start.

> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8 # min 4, typically 8KB each
>
> #
> # Non-shared Memory Sizes
> #
> sort_mem = 8192 # min 64, size in KB
> vacuum_mem = 24576 # min 1024, size in KB
>
> The rest are left uncommented (using the defaults).

Not good. You need to tune effective_cache_size so that postgresql accounts for
1.5GB RAM your machine has. I would say set it up around 800MB.

Secondly with SCSI in place, lower random_tuple_cost. Default is 4. 1 might be
too agrressive. 2 might be OK. Experiment and decide.

>
> Issue #3 - server hardware =>
>
> - Is there anything I can do with the hardware to increase performance?
>
> - Should I increase the ram to 2 gigs? top shows that it is using the swap
> a bit (about 100k only).

Means it does not need swap almost at all. Linux has habit to touch swap just
for no reason. So memory is not the bottleneck.

> - I have at my disposal one other server which has 2 Xeons, 10,000 RPM SCSI
> drive. Would it make sense to put Postgres on it and leave my apps running
> on the more powerful 4 CPU server?
>
> - Would a RAID setup make the disk faster? Because top rarely shows the
> CPUs above 50%, I suspect maybe the disk is the bottleneck.

Yes it is. You need to move WAL to a different disk. Even if it is IDE. (OK
that was over exaggeration but you got the point). If your data directories and
WAL logs are on physically different disks, that should bump up performance
plenty.

HTH

Bye
Shridhar

--
Ambidextrous, adj.: Able to pick with equal skill a right-hand pocket or a
left. -- Ambrose Bierce, "The Devil's Dictionary"