Thread: Shared buffers vs large files
Do shared_buffers help when at all when using large tables which do not fit on the buffer? Does it help to brake tables so their most heavily used columns are by themselves? While we were testing Postgresql we were using subsets of data. Now that we are in limited production the tables have grown significantly. My settings are: shared_buffers at 4096 (32MB if my calculations are correct), sort_mem = 65536 # min 32 vacuum_mem = 16384 # min 1024 The machine has 1GB of ram. I don't expect to have more than a handfull of connections at a time (from 1 to 10). Should I increate the shared buffers to 64MB? 128MB? My sys V shared memory parameters are: kern.ipc.shmall=65535 kern.ipc.shmmax=100663296 kern.ipc.shm_use_phys=1 (From /etc/sysctl.conf on a FreeBSD machine) My most commonly used tables are from 30MB to 200MB One of those was 300MB+, but we split it and kept the most commonly used columns in one table and the rest in another hoping this would help. This, however, was a lot of work and we are wondering whether it would be benefitial to do it with any other tables. After we split this one table in two the most heavily used part is about 30MB and the other part about 270MB. NOTE: For all reported sizes I am using the file size of the ASCII file we import. I am just starting to look at how size of the files reported by oid2name relates to this. My assumption is that the ASCII files may be representative of the sizes they may use as tables.
On Fri, 1 Mar 2002, Andrew Sullivan wrote: > On Fri, Mar 01, 2002 at 04:48:16PM -0500, Francisco Reyes wrote: > > > sort_mem = 65536 # min 32 > > I think this is way too high. That's the amount of memory _each sort > operation_ can take. A single query can spawn several sort > operations. > > I have 16 Gig on my Sun boxes, and I only have that set to 2048. Thanks for the advice. I thought I had seen examples of people using 32K. Will lower it to 4096 then.
> shared_buffers at 4096 (32MB if my calculations are correct), > sort_mem = 65536 # min 32 > vacuum_mem = 16384 # min 1024 > > The machine has 1GB of ram. > > I don't expect to have more than a handfull of connections at a time (from > 1 to 10). Should I increate the shared buffers to 64MB? 128MB? On a 1GB machine (still PG 7.1.3) I'm currently running: shared_buffers: 48000 (about 400MB) sort_mem: 8192 I haven't done much testing with sort_mem values, but... This is very very VERY unscientific, but I haven't seen a shared_buffers value that is so big that it seems to hurt performance (unless it causes swapping obviously), and my installation is dedicated to postgres so I don't need the memory for much of anything else. It appears (and it makes sense) that the performance improvement is roughly an inverse J-curve; bigger is never really a bad thing, it just starts to make very little difference. Any time you can save a system call and a memory copy, you're ahead. I'd say that 4096 is VERY low for shared_mem, especially with so much available ram - I'd bet the farm you'd see a *significant* improvement by bumping it to 16384 at least. Just my $.02 :-) Glen
On Fri, 2002-03-01 at 18:57, Glen Parker wrote: > > shared_buffers at 4096 (32MB if my calculations are correct), > > sort_mem = 65536 # min 32 > > vacuum_mem = 16384 # min 1024 > > > > The machine has 1GB of ram. > > > > I don't expect to have more than a handfull of connections at a time (from > > 1 to 10). Should I increate the shared buffers to 64MB? 128MB? > > On a 1GB machine (still PG 7.1.3) I'm currently running: > > shared_buffers: 48000 (about 400MB) > sort_mem: 8192 > > I haven't done much testing with sort_mem values, but... > > This is very very VERY unscientific, but I haven't seen a shared_buffers > value that is so big that it seems to hurt performance (unless it causes > swapping obviously), and my installation is dedicated to postgres so I don't > need the memory for much of anything else. Keep in mind that this memory is allocated by Postgres on postmaster startup. Thus, the kernel can't use it for I/O buffers. Depending on what UNIX variant you're running and the kind of load the box is under, setting shared_buffers that high may or may not be a performance win. However, I agree with you in principle: for a production PostgreSQL server, the default shared_buffers settings are ridiculously small. Another parameter to consider increasing is wal_buffers; in my experience that can improve performance as well. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
On 1 Mar 2002, Neil Conway wrote: > Another parameter to consider increasing is wal_buffers; in my > experience that can improve performance as well. But only for data changing operations, not for simple selects. -- PGP/GPG Key-ID: http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1
On Fri, 1 Mar 2002, Glen Parker wrote: > > shared_buffers at 4096 (32MB if my calculations are correct), > > sort_mem = 65536 # min 32 > > vacuum_mem = 16384 # min 1024 > > > > The machine has 1GB of ram. > > > > I don't expect to have more than a handfull of connections at a time (from > > 1 to 10). Should I increate the shared buffers to 64MB? 128MB? > > On a 1GB machine (still PG 7.1.3) I'm currently running: > > shared_buffers: 48000 (about 400MB) > sort_mem: 8192 What OS are you running this on? What were your sysv memory settings?
> > shared_buffers: 48000 (about 400MB) > > sort_mem: 8192 > > What OS are you running this on? > What were your sysv memory settings? RedHat 7.2 echo 1000000000 >> /proc/sys/kernel/shmmax Glen