Re: [PERFORM] Howto Increased performace ?
Iain wrote: sort_mem 4096 (=400MB RAM for 100 connections) If I understand correctly, memory usage related to `sort_mem' is per connection *and* per sort. If every client runs a query with 3 sorts in its plan, you are going to need (in theory) 100 connections * 4Mb * 3 sorts, which is 1.2 Gb. Please correct me if I'm wrong... -- Cosimo ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Howto Increased performace ?
Ho Cosimo, I had read that before, so you are right. The amount of memory being used could run much higher than I wrote. In my case, I know that not all the connections are not busy all the time (this isn't a web application with thousands of users connecting to a pool) so not all active connections will be doing sorts all the time. As far as I can tell, sort memory is allocated as needed, so my estimate of 400MB should still be reasonable, and I have plenty of unaccounted for memory outside the effective cache so it shouldn't be a problem. Presumably, that memory isn't needed after the result set is built. If I understand correctly, there isn't any way to limit the amount of memory allocated for sorting, which means that you can't specifiy generous sort_mem values to help out when there is spare capacity (few connections) because in the worst case it could cause swapping when the system is busy. In the the not so bad case, the effective cache size estimate will just be completely wrong. Maybe a global sort memory limit would be a good idea, I don't know. regards Iain Iain wrote: sort_mem 4096 (=400MB RAM for 100 connections) If I understand correctly, memory usage related to `sort_mem' is per connection *and* per sort. If every client runs a query with 3 sorts in its plan, you are going to need (in theory) 100 connections * 4Mb * 3 sorts, which is 1.2 Gb. Please correct me if I'm wrong... -- Cosimo ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Howto Increased performace ?
Hi Amrit, I'm sorry to hear about the disaster in Thailand. I live in a tsunami prone area myself :-( I think that you have enough information to solve your problem now, but it will just take some time and testing. When you have eliminated the excessive swapping and tuned your system as best you can, then you can decide if that is fast enough for you. More memory might help, but I can't say for sure. There are many other things to consider. I suggest that you spend some time reading through the performance and maybe the admin lists. regards Iain - Original Message - From: "Amrit Angsusingh" <[EMAIL PROTECTED]> To: "Iain" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, December 28, 2004 1:48 AM Subject: Re: [PERFORM] Howto Increased performace ? Hi, These are some settings that I am planning to start with for a 4GB RAM dual opteron system with a maximum of 100 connections: shared_buffers 8192 (=67MB RAM) sort_mem 4096 (=400MB RAM for 100 connections) effective_cache_size 38(@8KB =3.04GB RAM) vacuum_mem 32768 KB wal_buffers 64 checkpoint_segments 8 In theory, effective cache size is the amount of memory left over for the OS to cache the filesystem after running all programs and having 100 users connected, plus a little slack. regards Iain I'm not sure if I put more RAM on my mechine ie: 6 GB . The performance would increase for more than 20 % ? Any comment please, Amrit Angsusingh Comcenter Sawanpracharuck Hospital Thailand ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Howto Increased performace ?
Hi, These are the /etc/sysctl.conf settings that I am planning to use. Coincidentally, these are the settings recommended by Oracle. If anything they would be generous, I think. file-max 65536 (for 2.2 and 2.4 kernels) kernel.shmall 134217728 (=128MB) kernel.shmmax 268435456 fs.file-max 65536 By the way, when you tested your changes, was that with a busy system? I think that a configuration that gives the best performance (at the client end) on a machine with just a few connections might not be the configuration that will give you the best throughput when the system is stressed. I'm certainly no expert on tuning Linux systems, or even Postgres but I'd suggest that you become knowlegable in the use of the various system monitoring tools that Linux has and keep a record of their output so you can compare as you change your configuration. In the end though, I think your aim is to reduce swapping by tuning your memory usage for busy times. Also, I heard that (most?what versions?) 32 bit linux kernals are slow at handling more than 2GB memory so a kernal upgrade might be worth considering. regards Iain ---(end of broadcast)--- TIP 3: 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] Howto Increased performace ?
On Mon, 2004-12-27 at 22:31 +0700, Amrit Angsusingh wrote: > [ [EMAIL PROTECTED] ] > > > > These are some settings that I am planning to start with for a 4GB RAM > > dual > > opteron system with a maximum of 100 connections: > > > > > > shared_buffers 8192 (=67MB RAM) > > sort_mem 4096 (=400MB RAM for 100 connections) > > effective_cache_size 38(@8KB =3.04GB RAM) > > vacuum_mem 32768 KB > > wal_buffers 64 > > checkpoint_segments 8 > > > > In theory, effective cache size is the amount of memory left over for the > > OS > > to cache the filesystem after running all programs and having 100 users > > connected, plus a little slack. > I reduced the connection to 160 and configured as below there is some > improvement in speed . > shared_buffers = 27853 [Should I reduce it to nearly as you do and what > will happen?] at some point, more shared buffers will do less good than leaving the memory to the OS to use as disk buffers. you might want to experiment a bit with different values to find what suits your real-life conditions > sort_mem = 8192 > vacuum_mem = 16384 > effective_cache_size = 81920 [Should I increase it to more than 20 ?] as Iain wrote, this value is an indication of how much memory will be available to the OS for disk cache. when all other settings have been made, try to see how much memory your OS has left under normal conditions, and adjust your setting accordingly, if it differs significantly. I have seen cases where an incorrect value (too low) influenced the planner to use sequential scans instead of better indexscans, presumably because of a higher ratio of estimated cache hits. > Thanks for any comment again. > > NB. There is a huge diaster in my country "Tsunamies" and all the people > over the country include me felt into deep sorrow. my condolescences. > Amrit Angsusingh > Thailand gnari ---(end of broadcast)--- TIP 3: 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] Howto Increased performace ?
Hi, These are some settings that I am planning to start with for a 4GB RAM dual opteron system with a maximum of 100 connections: shared_buffers 8192 (=67MB RAM) sort_mem 4096 (=400MB RAM for 100 connections) effective_cache_size 38(@8KB =3.04GB RAM) vacuum_mem 32768 KB wal_buffers 64 checkpoint_segments 8 In theory, effective cache size is the amount of memory left over for the OS to cache the filesystem after running all programs and having 100 users connected, plus a little slack. regards Iain - Original Message - From: "Amrit Angsusingh" <[EMAIL PROTECTED]> To: "Iain" <[EMAIL PROTECTED]> Cc: Sent: Monday, December 27, 2004 6:21 PM Subject: Re: [PERFORM] Howto Increased performace ? #sort_mem = 1024 # min 64, size in KB sort_mem = 6 I think this might be too much. You are using 6KB _per connection_ here = 10GB for your maximum of 180 connections. By comparison, I am specifiying 4096 (subject to adjustment) for a machine with a similar spec to yours. regards Iain I reduced it to sort_mem = 8192 If I increase it higher , what will be result I could expect. and I also reduce the max connection to 160 and shared buffer to shared_buffers = 27853 effective_cache_size = 81920 [what does it for?] do you think is it still too much especialy effective cache ? Thanks Amrit Amrit Angsusingh Nakornsawan,Thailand ---(end of broadcast)--- TIP 3: 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] Howto Increased performace ?
Hi, #sort_mem = 1024 # min 64, size in KB sort_mem = 6 I think this might be too much. You are using 6KB _per connection_ here = 10GB for your maximum of 180 connections. By comparison, I am specifiying 4096 (subject to adjustment) for a machine with a similar spec to yours. regards Iain ---(end of broadcast)--- TIP 3: 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] Howto Increased performace ?
On Tue, 2004-12-21 at 16:31 +0700, Amrit Angsusingh wrote: > I used postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram > of 4 Gb. Since 1 1/2 yr. when I started to use the database server after > optimizing the postgresql.conf everything went fine until a couple of > weeks ago , my database grew up to 3.5 Gb and there were more than 140 > concurent connections. ... > shared_buffers = 25 this is much higher than usually adviced on this list. try to reduce this to 25000 > effective_cache_size = 5000 and increase this instead, to say, 5 gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Howto Increased performace ?
I used postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram of 4 Gb. Since 1 1/2 yr. when I started to use the database server after optimizing the postgresql.conf everything went fine until a couple of weeks ago , my database grew up to 3.5 Gb and there were more than 140 concurent connections. The server seemed to be slower in the rush hour peroid than before . There is some swap process too. My top and meminfo are shown here below: 14:52:13 up 13 days, 2:50, 2 users, load average: 5.58, 5.97, 6.11 218 processes: 210 sleeping, 1 running, 0 zombie, 7 stopped CPU0 states: 7.2% user 55.2% system0.0% nice 0.0% iowait 36.4% idle CPU1 states: 8.3% user 56.1% system0.0% nice 0.0% iowait 34.4% idle CPU2 states: 10.0% user 57.0% system0.0% nice 0.0% iowait 32.4% idle CPU3 states: 6.2% user 55.3% system0.0% nice 0.0% iowait 37.3% idle Mem: 4124720k av, 4105916k used, 18804k free, 0k shrd, 10152k buff 2900720k actv, 219908k in_d, 167468k in_c Swap: 20370412k av, 390372k used, 19980040k free 2781256k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 14 root 18 0 00 0 SW 54.5 0.0 766:10 1 kscand/HighMem 13304 postgres 17 0 280M 280M 276M D52.5 6.9 0:10 2 postmaster 12035 postgres 16 0 175M 174M 169M D33.0 4.3 0:26 3 postmaster 13193 postgres 16 0 128M 127M 124M S28.4 3.1 0:05 3 postmaster 12137 postgres 16 0 498M 497M 431M D27.2 12.3 0:34 1 postmaster 11 root 15 0 00 0 SW 13.9 0.0 363:00 2 kswapd 13241 postgres 16 0 318M 318M 314M D 7.3 7.9 0:09 2 postmaster 13 root 16 0 00 0 SW6.9 0.0 82:17 0 kscand/Normal 13367 postgres 15 0 196M 196M 193M D 6.5 4.8 0:02 2 postmaster 11984 postgres 15 0 305M 305M 301M S 4.9 7.5 2:55 1 postmaster 13331 postgres 16 0 970M 970M 966M S 4.9 24.0 0:22 1 postmaster 12388 postgres 15 0 293M 292M 289M S 3.9 7.2 2:42 3 postmaster 13328 postgres 15 0 276M 276M 272M S 2.7 6.8 0:22 0 postmaster 26 root 16 0 00 0 SW2.3 0.0 10:12 1 kjournald 11831 postgres 15 0 634M 634M 630M S 1.5 15.7 1:33 3 postmaster 12127 postgres 15 0 117M 116M 114M S 1.1 2.8 0:20 1 postmaster 12002 postgres 15 0 429M 429M 426M S 0.9 10.6 0:24 1 postmaster 12991 postgres 15 0 143M 143M 139M S 0.7 3.5 0:29 1 postmaster 13234 postgres 15 0 288M 288M 284M S 0.7 7.1 0:17 0 postmaster 13337 postgres 15 0 172M 171M 168M S 0.3 4.2 0:06 0 postmaster 13413 root 15 0 1276 1276 856 R 0.3 0.0 0:00 0 top 11937 postgres 15 0 379M 379M 375M S 0.1 9.4 2:59 2 postmaster Shared kernel mem: [EMAIL PROTECTED] root]# cat < /proc/sys/kernel/shmmax 40 [EMAIL PROTECTED] root]# cat < /proc/sys/kernel/shmall 3 meminfo : total:used:free: shared: buffers: cached: Mem: 4223713280 4200480768 232325120 11497472 3555827712 Swap: 20859301888 303460352 20555841536 MemTotal: 4124720 kB MemFree: 22688 kB MemShared: 0 kB Buffers: 11228 kB Cached:3367688 kB SwapCached: 104800 kB Active:3141224 kB ActiveAnon: 684960 kB ActiveCache: 2456264 kB Inact_dirty:220504 kB Inact_laundry: 166844 kB Inact_clean: 94252 kB Inact_target: 724564 kB HighTotal: 3276736 kB HighFree: 3832 kB LowTotal: 847984 kB LowFree: 18856 kB SwapTotal:20370412 kB SwapFree: 20074064 kB Postgresql.conf : # Connection Parameters # tcpip_socket = true #ssl = false #max_connections = 32 max_connections = 180 #superuser_reserved_connections = 2 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # #shared_buffers = 64# min max_connections*2 or 16, 8KB each shared_buffers = 25 #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8# min 4, typically 8KB each # # Non-shared Memory Sizes # #sort_mem = 1024 # min 64, size in KB sort_mem = 6 #vacuum_mem = 8192 # min 1024, size in KB vacuum_mem = 20072 # Write-ahead log (WAL) # #checkpoint_segments = 3# in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-10, in microseconds commit_delay = 10 #commit_siblings = 5# range 1-1000 # #fsync = true fsync = false #wal_sync_method = fsync# the default var
