RE: [Bacula-users] Re: abysmal database write performance

[Magnus H. <mh...@so...>]

> >> [...] I tried to "tune" postgreSQL by modifying some=20
> settings in the=20
> >> postgresql.conf: shared_buffers =3D 2048 wal_buffers =3D 64=20
> >> max_connections =3D 40 Nevertheless, it doesn't help. :-(
> >=20
> > What kind of hardware are you on? 2048 shared buffers is=20
> very low for=20
> > a lot of systems. I'd bump it up a bit (but not too far!=20
> FOr example,=20
> > I use about 25,000 for a system with 2Gb RAM)
>=20
> We are using a Dual-Xeon 2.8 GHz with still only 1 GB RAM.
> The system uses two SCSI disks (10k rpm) with software RAID1.=20
> It contains the OS, swap and the database. (It seems that was=20
> no good idea.) There are also two disks with RAID0 for=20
> spooling data and six disks with a RAID5 for some backups=20
> (without spooling ;-)).
>=20
> I tried to increase the shared_buffers to 8192 but postgres=20
> told me, that the shared memory segment exceeded the kernel's=20
> SHMMAX parameter.
> So I temporarily decreased the value again.

Ok. you'll want to change the max value in your kernel so you can get it
up a bit. See
http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYS
VIPC.


> > Is your effective_cache_size properly set?

This one is important for the planner to pick the correct plan. Don't
set it too high, though. For my 2Gb system, I hav eit on 131072. So set
it to maybe half ot that.


> > Setting of full_page_writes? [that may be a 8.1 setting, can't=20
> > remember, but it makes a huge difference if you have an I/O=20
> system yuo=20
> > acn trust]
> >=20
> > work_mem, maintenance_work_mem? checkpoint_segments?
>=20
> No changes were made at these settings until now. So postgres=20
> uses the defaults. Any suggestions for these values?

I run my 2Gb system with:
work_mem =3D 256000
maintenance_work_mem =3D 256000

The default is 1024 and 16384, so yeah, those should go up high. Try
128000 for work_mem and 256000 for maintenance_work_mem.

Make sure your checkpoint_segments is set to at least 8. It'll cost a
bit of disk space, but it'll give better performance for burst
insert/updates.


> > Most improtantly, what's your I/O system? Writeback cache? WAL on=20
> > separate spindles? Type of disks (IDE/SATA/SCSI?)
>=20
> I fear that I have underestimated the requirements.
> Until a few minutes ago the disks of the softRAID were=20
> running with write cache disabled.
> WAL is still on the same spindles.

That's the safe mode, of course. So that's good. Writeback cache should
only be enabled if you have battery backup for the controller.

If you trust your disks, you can disable full_page_writes. This is still
a lot safer than disabling fsync, and cuts down WAL traffic
considerably.


> > The indexes will generally *hurt* your insert performance, which is=20
> > what Bacula mainly needs during writes. I'll speed up restore=20
> > operations, but there's the cost.
>=20
> I mentioned the indexes because I'm also with the read=20
> performance not really happy.

That's most likely because your work_mem was far too low. Every large
sort operation will spill to disk, and that'll kill your performance.


//Magnus