[PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/ reiserfs)
I have a postgresql 7.4.8-server with 4 GB ram. #effective_cache_size = 1000# typically 8KB each This is computed by sysctl -n vfs.hibufspace / 8192 (on FreeBSD). So I changed it to: effective_cache_size = 27462# typically 8KB each Apparently this formula is no longer relevant on the FreeBSD systems as it can cache up to almost all the available RAM. With 4GB of RAM, one could specify most of the RAM as being available for caching, assuming that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM would be a reasonable value to tell the planner. (This was verified by using dd: dd if=/dev/zero of=/usr/local/pgsql/iotest bs=128k count=16384 to create a 2G file then dd if=/usr/local/pgsql/iotest of=/dev/null If you run systat -vmstat 2 you will see 0% diskaccess during the read of the 2G file indicating that it has, in fact, been cached) Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? regards Claus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/
On Tue, 2005-10-11 at 16:54 +0200, Claus Guttesen wrote: I have a postgresql 7.4.8-server with 4 GB ram. #effective_cache_size = 1000# typically 8KB each This is computed by sysctl -n vfs.hibufspace / 8192 (on FreeBSD). So I changed it to: effective_cache_size = 27462# typically 8KB each Apparently this formula is no longer relevant on the FreeBSD systems as it can cache up to almost all the available RAM. With 4GB of RAM, one could specify most of the RAM as being available for caching, assuming that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM would be a reasonable value to tell the planner. (This was verified by using dd: dd if=/dev/zero of=/usr/local/pgsql/iotest bs=128k count=16384 to create a 2G file then dd if=/usr/local/pgsql/iotest of=/dev/null If you run systat -vmstat 2 you will see 0% diskaccess during the read of the 2G file indicating that it has, in fact, been cached) Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? Not sure about 6.0 (but I don't know why it would change) but definitely on 5.4 amd64 (and I would imagine i386 as well). Sven ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/
Apparently this formula is no longer relevant on the FreeBSD systems as it can cache up to almost all the available RAM. With 4GB of RAM, one could specify most of the RAM as being available for caching, assuming that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM would be a reasonable value to tell the planner. (This was verified by using dd: dd if=/dev/zero of=/usr/local/pgsql/iotest bs=128k count=16384 to create a 2G file then dd if=/usr/local/pgsql/iotest of=/dev/null If you run systat -vmstat 2 you will see 0% diskaccess during the read of the 2G file indicating that it has, in fact, been cached) Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? Not sure about 6.0 (but I don't know why it would change) but definitely on 5.4 amd64 (and I would imagine i386 as well). Works on FreeBSD 6.0 RC1 as well. Tried using count=4096 on a 1 GB ram box. Same behaviour as you describe above. regards Claus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/ reiserfs)
On Oct 11, 2005, at 10:54 AM, Claus Guttesen wrote: Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? It applies to FreeBSD = 5.0. However, I have not been able to get a real answer from the FreeBSD hacker community on what the max buffer space usage will be to properly set this. The `sysctl -n vfs.hibufspace` / 8192 estimation works very well for me, still, and I continue to use it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Effective Cache Size
Hi, I have been following a thread on this list Inconsistent performance and had a few questions especially the bits about effective_cache_size. I have read some of the docs, and some other threads on this setting, and it seems to used by the planner to either choose a sequential or index scan. So it will not necessarily increase performance I suppose but instead choose the most optimal plan. Is this correct? We are not that we are suffering massive performance issues at the moment but it is expected that our database is going to grow considerably in the next couple of years, both in terms of load and size. Also what would an appropriate setting be? From what I read of Scott Marlowes email, and from the information below I reckon it should be somewhere in the region of 240,000. Danger maths ahead. Beware maths 141816K buff + 1781764K cached - 1923580K total effective_cache_size = 1923580 / 8 = 240447.5 /maths Here is some information on the server in question. If any more information is required then please say. It is a dedicated PG machine with no other services being hosted off it. As you can see from the uptime, its load average is 0.00, and is currently so chilled its almost frozen! That will change though :-( Hardware Dual PIII 1.4GHz 2Gb RAM 1Tb SAN with hardware RAID 5 using 1Gbps Fibre channel. OS == Linux webbasedth5 2.4.18-18.7.xsmp #1 SMP Wed Nov 13 19:01:42 EST 2002 i686 Red Hat Linux release 7.3 (Valhalla) PG == PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Database This includes all indexes and tables. I can provide more information on how this is chopped up if needed. Size : 1,141.305 Mb Tuples : 13,416,397 Uptime == 11:15am up 197 days, 16:50, 1 user, load average: 0.00, 0.00, 0.00 Top === Mem: 2064836K av, 2018648K used, 46188K free, 0K shrd, 141816K buff Swap: 2096472K av,4656K used, 2091816K free 1781764K cached Postgresql.conf (all defaults except) = max_connections = 1000 shared_buffers = 16000 (128 Mb) max_fsm_relations = 5000 max_fsm_pages = 50 vacuum_mem = 65535 Kind Regards, Nick Barr This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Effective Cache Size
On 17 Sep 2003 at 11:48, Nick Barr wrote: Hi, I have been following a thread on this list Inconsistent performance and had a few questions especially the bits about effective_cache_size. I have read some of the docs, and some other threads on this setting, and it seems to used by the planner to either choose a sequential or index scan. So it will not necessarily increase performance I suppose but instead choose the most optimal plan. Is this correct? That is correct. Danger maths ahead. Beware maths 141816K buff + 1781764K cached - 1923580K total effective_cache_size = 1923580 / 8 = 240447.5 /maths That would be bit too aggressive. I would say set it around 200K to leave room for odd stuff. Rest seems fine with your configuration. Of course a latest version of postgresql is always good though.. Bye Shridhar -- Power is danger.-- The Centurion, Balance of Terror, stardate 1709.2 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Effective Cache Size
Thanks. Some theoretical questions. The documentation says that Effective Cache Size sets the optimizer's assumption about the effective size of the disk cache ( that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files ). What then will be the effect of setting this too high? And too low? How does it impact on other applications eg Java ? -Original Message- From: scott.marlowe [SMTP:[EMAIL PROTECTED] Sent: 01 July 2003 03:20 To: Howard Oblowitz Cc: [EMAIL PROTECTED] Subject: Re: FW: [PERFORM] Version 7 question The best way to set it is to let the machine run under normal load for a while, then look at the cache / buffer usage using top (or some other program, top works fine for this). My server with 1.5 gig ram shows 862328K cache right now. So, just divide by page size (usually 8192) and I get ~ 100,000 blocks. On Tue, 1 Jul 2003, Howard Oblowitz wrote: What would be the best value range for effective_cache_size on Postgres 7.3.2, assuming say 1.5 GB of RAM and shared_buffers set to 8192, and shmmax set to 750mb? And what are the most important factors one should take into account in determining the value? -Original Message- From: scott.marlowe [SMTP:[EMAIL PROTECTED] Sent: 01 July 2003 02:56 To: Michael Mattox Cc: Hilary Forbes; [EMAIL PROTECTED] Subject: Re: [PERFORM] Version 7 question 8192 is only 64 megs of RAM, not much, but a good number. Keep in mind that the kernel tends to be better at buffering huge amounts of disk, while postgresql is better left to use buffers that are large enough for the current working set (i.e. not your whole database, just the largest amount of data you're slinging about on a regular basis in one query.) On a machine with 1.5 gig of RAM, I've found settings as high as 32768 (256 megs of ram) to run well, but anything over that doesn't help. Of course, we don't toss around more than a hundred meg or so at a time. If our result sets were in the gigabyte range, I'd A: want more memory and B: Give more of it to postgresql. The original poster was, I believe running 7.0.x, which is way old, so no, I don't think there was an equivalent of effective_cache_size in that version. Upgrading would be far easier than performance tuning 7.0. since the query planner was much simpler (i.e. more prone to make bad decisions) in 7.0. On Tue, 1 Jul 2003, Michael Mattox wrote: I have my shared buffers at 8192 and my effective cache at 64000 (which is 500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and I've been asking my boss for another 512megs for over a month now. I have no idea if my buffers are too high/low. Michael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Hilary Forbes Sent: Tuesday, July 01, 2003 2:10 PM To: [EMAIL PROTECTED] Subject: [PERFORM] Version 7 question I'm just trying to improve performance on version 7 before doing some tests and hopefully upgrading to 7.3. At the moment we have B=64 (no of shared buffers) N=32 (no of connections) in postmaster.opt which I take it is the equivalent of the new postgresql.conf file. From all that is being written about later versions I suspect that this is far too low. Would I be fairly safe in making the no of shared buffers larger? Also is there an equivalent of effective_cache_size that I can set for version 7? Many thanks in advance Hilary Hilary Forbes - DMR Computer Limited: http://www.dmr.co.uk/ Direct line: 01689 889950 Switchboard: (44) 1689 86 Fax: (44) 1689 860330 E-mail: [EMAIL PROTECTED] ** ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is
