Re: [PERFORM] Postgres Performance Tuning
On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote: Now I have to start more queries on Database Server and issue new connections after some time. Why the cached memory is not freed. It's freed on-demand. Flushing the cache memory is needed how it could use so much if I set Why would forced flushing be needed? And why would it be useful? It is not. effective_cache_size = 4096 MB. Watch the cached field of free's output and set effective_cache_size to that amount (given that your server is running postgres only, has no major other tasks) I think if i issue some new select queries on large set of data, it will use Swap Memory degrades Performance. Have you ever tried that? Will not. Please correct if I'm wrong. You seem to know very little about Unix/Linux memory usage and how to interpret the tools' output. Please read some (very basic) documentation for sysadmins regarding these subjects. It will help you a lot to understand how things work. -- Akos Gabriel General Manager Liferay Hungary Ltd. Liferay Hungary Symposium, May 26, 2011 | Register today: http://www.liferay.com/hungary2011 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: [root@s8-mysd-2 ~]# free -m total used free shared buffers cached Mem: 15917 15826 90 0 101 15013 -/+ buffers/cache: 711 15205 Swap: 16394 143 16250 It means 15 GB memory is cached. Note that the kernel takes all otherwise unused memory and uses it for cache. If, at any time a process needs more memory, the kernel just dumps some cached data and frees up the memory and hands it over, it's all automatic. As long as cache is large, things are OK. You need to be looking to see if you're IO bound or CPU bound first. so, vmstat (install the sysstat package) is the first thing to use. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Scott Marlowe wrote: On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: [root@s8-mysd-2 ~]# free -m total used free sharedbuffers cached Mem: 15917 15826 90 0101 15013 -/+ buffers/cache:711 15205 Swap:16394143 16250 It means 15 GB memory is cached. Note that the kernel takes all otherwise unused memory and uses it for cache. If, at any time a process needs more memory, the kernel just dumps some cached data and frees up the memory and hands it over, it's all automatic. As long as cache is large, things are OK. You need to be looking to see if you're IO bound or CPU bound first. so, vmstat (install the sysstat package) is the first thing to use. Thanks a lot , Scott. :-) Best Regards , Adarsh
Re: [PERFORM] Postgres Performance Tuning
On Tue, Apr 5, 2011 at 7:20 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Scott Marlowe wrote: On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: [root@s8-mysd-2 ~]# free -m total used free shared buffers cached Mem: 15917 15826 90 0 101 15013 -/+ buffers/cache: 711 15205 Swap: 16394 143 16250 It means 15 GB memory is cached. Note that the kernel takes all otherwise unused memory and uses it for cache. If, at any time a process needs more memory, the kernel just dumps some cached data and frees up the memory and hands it over, it's all automatic. As long as cache is large, things are OK. You need to be looking to see if you're IO bound or CPU bound first. so, vmstat (install the sysstat package) is the first thing to use. BTW, just remembered that vmstat is it's own package, it's iostat and sar that are in sysstat. If you install sysstat, enable stats collecting by editing the /etc/default/sysstat file and changing the ENABLED=false to ENABLED=true and restarting the service with sudo /etc/init.d/sysstat restart -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Postgres Performance Tuning
Dear all, I have a Postgres database server with 16GB RAM. Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. Below is the no. of connections output :- postgres=# select datname,numbackends from pg_stat_database; datname | numbackends ---+- template1 | 0 template0 | 0 postgres | 3 template_postgis | 0 pdc_uima_dummy| 107 pdc_uima_version3 | 1 pdc_uima_olap | 0 pdc_uima_s9 | 3 pdc_uima | 1 (9 rows) I am totally confused for setting configuration parameters in Postgres Parameters :- First of all, I research on some tuning parameters and set mu postgresql.conf as:- max_connections = 1000 shared_buffers = 4096MB temp_buffers = 16MB work_mem = 64MB maintenance_work_mem = 128MB wal_buffers = 32MB checkpoint_segments = 3 random_page_cost = 2.0 effective_cache_size = 8192MB Then I got some problems from Application Users that the Postgres Slows down and free commands output is :- [root@s8-mysd-2 ~]# free -g total used free sharedbuffers cached Mem:15 15 0 0 0 14 -/+ buffers/cache: 0 14 Swap: 16 0 15 [root@s8-mysd-2 ~]# free total used free sharedbuffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 I think there may be some problem in my Configuration parameters and change it as : max_connections = 700 shared_buffers = 4096MB temp_buffers = 16MB work_mem = 64MB maintenance_work_mem = 128MB wal_buffers = 32MB checkpoint_segments = 32 random_page_cost = 2.0 effective_cache_size = 4096MB but Still Postgres Server uses Swap Memory While SELECT INSERT into database tables. Please check the attached postgresql.conf . And also have some views on how to tune this server. DO I need to Increase my RAM s.t I hit H/W limitation. Thanks best Regards, Adarsh Sharma # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The = is optional.) Whitespace may be used. Comments are introduced with # # anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use pg_ctl reload. Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # postgres -c log_connections=on. Some parameters can be changed at run time # with the SET SQL command. # # Memory units: kB = kilobytesTime units: ms = milliseconds #MB = megabytes s = seconds #GB = gigabytes min = minutes # h = hours # d = days #-- # FILE LOCATIONS #-- # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '(none)' # write an extra PID file # (change requires restart) #-- # CONNECTIONS AND AUTHENTICATION #-- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Re: [PERFORM] Postgres Performance Tuning
max_connections = 700 shared_buffers = 4096MB temp_buffers = 16MB work_mem = 64MB maintenance_work_mem = 128MB wal_buffers = 32MB checkpoint_segments = 32 random_page_cost = 2.0 effective_cache_size = 4096MB First of all, there's no reason to increase wal_buffers above 32MB. AFAIK the largest sensible value is 16MB - I doubt increasing it further will improve performance. Second - effective_cache_size is just a hint how much memory is used by the operating system for filesystem cache. So this does not influence amount of allocated memory in any way. but Still Postgres Server uses Swap Memory While SELECT INSERT into database tables. Are you sure it's PostgreSQL. What else is running on the box? Have you analyzed why the SQL queries are slow (using EXPLAIN)? regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I have a Postgres database server with 16GB RAM. Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. Below is the no. of connections output :- postgres=# select datname,numbackends from pg_stat_database; datname | numbackends ---+- template1 | 0 template0 | 0 postgres | 3 template_postgis | 0 pdc_uima_dummy | 107 pdc_uima_version3 | 1 pdc_uima_olap | 0 pdc_uima_s9 | 3 pdc_uima | 1 (9 rows) I am totally confused for setting configuration parameters in Postgres Parameters :- First of all, I research on some tuning parameters and set mu postgresql.conf as:- max_connections = 1000 That's a little high. shared_buffers = 4096MB work_mem = 64MB That's way high. Work mem is PER SORT as well as PER CONNECTION. 1000 connections with 2 sorts each = 128,000MB. [root@s8-mysd-2 ~]# free total used free shared buffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap. I think there may be some problem in my Configuration parameters and change it as : Don't just guess and hope for the best. Examine your system to determine where it's having issues. Use vmstat 10 iostat -xd 10 top htop and so on to see where your bottleneck is. CPU? Kernel wait? IO wait? etc. log long running queries. Use pgfouine to examine your queries. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Also you can try to take the help of pgtune before hand. pgfoundry.org/projects/*pgtune*/ On Mon, Apr 4, 2011 at 12:43 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I have a Postgres database server with 16GB RAM. Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. Below is the no. of connections output :- postgres=# select datname,numbackends from pg_stat_database; datname | numbackends ---+- template1 | 0 template0 | 0 postgres | 3 template_postgis | 0 pdc_uima_dummy| 107 pdc_uima_version3 | 1 pdc_uima_olap | 0 pdc_uima_s9 | 3 pdc_uima | 1 (9 rows) I am totally confused for setting configuration parameters in Postgres Parameters :- First of all, I research on some tuning parameters and set mu postgresql.conf as:- max_connections = 1000 That's a little high. shared_buffers = 4096MB work_mem = 64MB That's way high. Work mem is PER SORT as well as PER CONNECTION. 1000 connections with 2 sorts each = 128,000MB. [root@s8-mysd-2 ~]# free total used free shared buffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap. I think there may be some problem in my Configuration parameters and change it as : Don't just guess and hope for the best. Examine your system to determine where it's having issues. Use vmstat 10 iostat -xd 10 top htop and so on to see where your bottleneck is. CPU? Kernel wait? IO wait? etc. log long running queries. Use pgfouine to examine your queries. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe scott.marl...@gmail.com wrote: [root@s8-mysd-2 ~]# free total used free shared buffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap. Sorry that's 15.xG is system cache, not shared buffers. Anyway, still not a problem. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Adarsh, What is the Size of Database? Best Regards, Raghavendra EnterpriseDB Corporation On Mon, Apr 4, 2011 at 4:24 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe scott.marl...@gmail.com wrote: [root@s8-mysd-2 ~]# free total used free shared buffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap. Sorry that's 15.xG is system cache, not shared buffers. Anyway, still not a problem. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Mem: 16299476k total, 16198784k used, 100692k free, 73776k buffers Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3401 postgres 20 0 4288m 3.3g 3.3g S 0 21.1 0:24.73 postgres 3397 postgres 20 0 4286m 119m 119m S 0 0.8 0:00.36 postgres PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly. OK, first, see the 15585396k cached? That's how much memory your OS is using to cache file systems etc. Basically that's memory not being used by anything else right now, so the OS borrows it and uses it for caching. Next, VIRT is how much memory your process would need to load every lib it might need but may not be using now, plus all the shared memory it might need, plus it's own space etc. It's not memory in use, it's memory that might under the worst circumstances, be used by that one process. RES is the amount of memory the process IS actually touching, including shared memory that other processes may be sharing. Finally, SHR is the amount of shared memory the process is touching. so, taking your biggest process, it is linked to enough libraries and shared memory and it's own private memory to add up to 4288Meg. It is currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is shared with other processes. So, the difference between RES and SHR is 0, so the delta, or extra memory it's using besides shared memory is ZERO (or very close to it, probably dozens or fewer of megabytes). So, you're NOT running out of memory. Remember when I mentioned iostat, vmstat, etc up above? Have you run any of those? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Thanks Scott : My iostat package is not installed but have a look on below output: [root@s8-mysd-2 8.4SS]# vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo incs us sy id wa st 1 0 147664 93920 72332 1558074801 113 170 47 177 6 1 92 1 0 0 0 147664 94020 72348 1558074800 0 4 993 565 0 0 100 0 0 0 0 147664 93896 72364 1558074800 0 5 993 571 0 0 100 0 0 0 0 147664 93524 72416 1558086000 0 160 1015 591 0 0 100 0 0 0 0 147664 93524 72448 1558086000 0 8 1019 553 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 0 1019 555 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 3 1023 560 0 0 100 0 0 [root@s8-mysd-2 8.4SS]# iostat -bash: iostat: command not found [root@s8-mysd-2 8.4SS]# Best regards, Adarsh Scott Marlowe wrote: On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Mem: 16299476k total, 16198784k used, 100692k free,73776k buffers Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 3401 postgres 20 0 4288m 3.3g 3.3g S0 21.1 0:24.73 postgres 3397 postgres 20 0 4286m 119m 119m S0 0.8 0:00.36 postgres PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly. OK, first, see the 15585396k cached? That's how much memory your OS is using to cache file systems etc. Basically that's memory not being used by anything else right now, so the OS borrows it and uses it for caching. Next, VIRT is how much memory your process would need to load every lib it might need but may not be using now, plus all the shared memory it might need, plus it's own space etc. It's not memory in use, it's memory that might under the worst circumstances, be used by that one process. RES is the amount of memory the process IS actually touching, including shared memory that other processes may be sharing. Finally, SHR is the amount of shared memory the process is touching. so, taking your biggest process, it is linked to enough libraries and shared memory and it's own private memory to add up to 4288Meg. It is currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is shared with other processes. So, the difference between RES and SHR is 0, so the delta, or extra memory it's using besides shared memory is ZERO (or very close to it, probably dozens or fewer of megabytes). So, you're NOT running out of memory. Remember when I mentioned iostat, vmstat, etc up above? Have you run any of those?
Re: [PERFORM] Postgres Performance Tuning
You got to have something to compare against. I would say, try to run some benchmarks (pgbench from contrib) and compare them against a known good instance of postgresql, if you have access in such a machine. That said, and forgive me if i sound a little explicit but if you dont know how to install iostat then there are few chances that you understand unix/linux/bsd concepts properly and therefore any efforts to just speed up postgresql in such an environment , at this point, will not have the desired effect, because even if you manage to solve smth now, tommorow you will still be in confusion about smth else that might arise. So, i suggest: 1) try to get an understanding on how your favorite distribution works (read any relevant info, net, books, etc..) 2) Go and get the book PostgreSQL 9.0 High Performance by Greg Smith. It is a very good book not only about postgresql but about the current state of systems performance as well. Στις Monday 04 April 2011 14:51:13 ο/η Adarsh Sharma έγραψε: Thanks Scott : My iostat package is not installed but have a look on below output: [root@s8-mysd-2 8.4SS]# vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo incs us sy id wa st 1 0 147664 93920 72332 1558074801 113 170 47 177 6 1 92 1 0 0 0 147664 94020 72348 1558074800 0 4 993 565 0 0 100 0 0 0 0 147664 93896 72364 1558074800 0 5 993 571 0 0 100 0 0 0 0 147664 93524 72416 1558086000 0 160 1015 591 0 0 100 0 0 0 0 147664 93524 72448 1558086000 0 8 1019 553 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 0 1019 555 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 3 1023 560 0 0 100 0 0 [root@s8-mysd-2 8.4SS]# iostat -bash: iostat: command not found [root@s8-mysd-2 8.4SS]# Best regards, Adarsh Scott Marlowe wrote: On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Mem: 16299476k total, 16198784k used, 100692k free,73776k buffers Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 3401 postgres 20 0 4288m 3.3g 3.3g S0 21.1 0:24.73 postgres 3397 postgres 20 0 4286m 119m 119m S0 0.8 0:00.36 postgres PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly. OK, first, see the 15585396k cached? That's how much memory your OS is using to cache file systems etc. Basically that's memory not being used by anything else right now, so the OS borrows it and uses it for caching. Next, VIRT is how much memory your process would need to load every lib it might need but may not be using now, plus all the shared memory it might need, plus it's own space etc. It's not memory in use, it's memory that might under the worst circumstances, be used by that one process. RES is the amount of memory the process IS actually touching, including shared memory that other processes may be sharing. Finally, SHR is the amount of shared memory the process is touching. so, taking your biggest process, it is linked to enough libraries and shared memory and it's own private memory to add up to 4288Meg. It is currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is shared with other processes. So, the difference between RES and SHR is 0, so the delta, or extra memory it's using besides shared memory is ZERO (or very close to it, probably dozens or fewer of megabytes). So, you're NOT running out of memory. Remember when I mentioned iostat, vmstat, etc up above? Have you run any of those? -- Achilleas Mantzios -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
On Mon, Apr 4, 2011 at 5:51 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Thanks Scott : My iostat package is not installed but have a look on below output: [root@s8-mysd-2 8.4SS]# vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 147664 93920 72332 15580748 0 1 113 170 47 177 6 1 92 1 0 0 0 147664 94020 72348 15580748 0 0 0 4 993 565 0 0 100 0 0 0 0 147664 93896 72364 15580748 0 0 0 5 993 571 0 0 100 0 0 0 0 147664 93524 72416 15580860 0 0 0 160 1015 591 0 0 100 0 0 0 0 147664 93524 72448 15580860 0 0 0 8 1019 553 0 0 100 0 0 0 0 147664 93648 72448 15580860 0 0 0 0 1019 555 0 0 100 0 0 0 0 147664 93648 72448 15580860 0 0 0 3 1023 560 0 0 100 0 0 OK, right now your machine is at idle. Run vmstat / iostat when it's under load. If the wa column stays low, then you're not IO bound but more than likely CPU bound. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Thanks Scott : My iostat package is not installed but have a look on below output: [root@s8-mysd-2 8.4SS]# vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo incs us sy id wa st 1 0 147664 93920 72332 1558074801 113 170 47 177 6 1 92 1 0 0 0 147664 94020 72348 1558074800 0 4 993 565 0 0 100 0 0 0 0 147664 93896 72364 1558074800 0 5 993 571 0 0 100 0 0 0 0 147664 93524 72416 1558086000 0 160 1015 591 0 0 100 0 0 0 0 147664 93524 72448 1558086000 0 8 1019 553 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 0 1019 555 0 0 100 0 0 0 0 147664 93648 72448 1558086000 0 3 1023 560 0 0 100 0 0 Is this from a busy or idle period? I guess it's from an idle one, because the CPU is 100% idle and there's very little I/O activity. That's useless - we need to see vmstat output from period when there's something wrong. [root@s8-mysd-2 8.4SS]# iostat -bash: iostat: command not found [root@s8-mysd-2 8.4SS]# Then install it. Not sure what distro you use, but it's usually packed in sysstat package. Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres Performance Tuning
Thank U all, I know some things to work on after some work study on them , I will continue this discussion tomorrow . Best Regards, Adarsh Raghavendra wrote: Adarsh, [root@s8-mysd-2 8.4SS]# iostat -bash: iostat: command not found /usr/bin/iostat Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. When you are doing bulk inserts you need to tune AUTOVACUUM parameters or Change the autovacuum settings for those tables doing bulk INSERTs. Insert's need analyze. #autovacuum = on# Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, 0 logs only # actions running at least this number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit These are all default AUTOVACUUM settings. If you are using PG 8.4 or above, try AUTOVACUUM settings on bulk insert tables for better performance. Also need to tune the 'autovacuum_naptime' Eg:- ALTER table table name SET (autovacuum_vacuum_threshold=x, autovacuum_analyze_threshold=); wal_buffers //max is 16MB checkpoint_segment/// Its very less in your setting checkpoint_timeout temp_buffer // If application is using temp tables These parameter will also boost the performance. Best Regards Raghavendra EnterpriseDB Corporation. Scott Marlowe wrote: On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma adarsh.sha...@orkash.com mailto:adarsh.sha...@orkash.com wrote: Mem: 16299476k total, 16198784k used, 100692k free,73776k buffers Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 3401 postgres 20 0 4288m 3.3g 3.3g S0 21.1 0:24.73 postgres 3397 postgres 20 0 4286m 119m 119m S0 0.8 0:00.36 postgres PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly. OK, first, see the 15585396k cached? That's how much memory your OS is using to cache file systems etc. Basically that's memory not being used by anything else right now, so the OS borrows it and uses it for caching. Next, VIRT is how much memory your process would need to load every lib it might need but may not be using now, plus all the shared memory it might need, plus it's own space etc. It's not memory in use, it's memory that might under the worst circumstances, be used by that one process. RES is the amount of memory the process IS actually touching, including shared memory that other processes may be sharing. Finally, SHR is the amount of shared memory the process is touching. so, taking your biggest process, it is linked to enough libraries and shared memory and it's own private memory to add up to 4288Meg. It is currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is shared with other processes. So, the difference between RES and SHR is 0, so the delta, or extra memory it's using besides shared memory is ZERO (or very close to it, probably dozens or fewer of megabytes). So, you're NOT running out of memory. Remember when I mentioned iostat, vmstat, etc up above? Have you run any of those?
Re: [PERFORM] Postgres Performance Tuning
Adarsh, [root@s8-mysd-2 8.4SS]# iostat -bash: iostat: command not found /usr/bin/iostat Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. When you are doing bulk inserts you need to tune AUTOVACUUM parameters or Change the autovacuum settings for those tables doing bulk INSERTs. Insert's need analyze. #autovacuum = on# Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, 0 logs only # actions running at least this number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit These are all default AUTOVACUUM settings. If you are using PG 8.4 or above, try AUTOVACUUM settings on bulk insert tables for better performance. Also need to tune the 'autovacuum_naptime' Eg:- ALTER table table name SET (autovacuum_vacuum_threshold=x, autovacuum_analyze_threshold=); wal_buffers //max is 16MB checkpoint_segment/// Its very less in your setting checkpoint_timeout temp_buffer // If application is using temp tables These parameter will also boost the performance. Best Regards Raghavendra EnterpriseDB Corporation. Scott Marlowe wrote: On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma adarsh.sha...@orkash.com adarsh.sha...@orkash.com wrote: Mem: 16299476k total, 16198784k used, 100692k free,73776k buffers Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 3401 postgres 20 0 4288m 3.3g 3.3g S0 21.1 0:24.73 postgres 3397 postgres 20 0 4286m 119m 119m S0 0.8 0:00.36 postgres PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly. OK, first, see the 15585396k cached? That's how much memory your OS is using to cache file systems etc. Basically that's memory not being used by anything else right now, so the OS borrows it and uses it for caching. Next, VIRT is how much memory your process would need to load every lib it might need but may not be using now, plus all the shared memory it might need, plus it's own space etc. It's not memory in use, it's memory that might under the worst circumstances, be used by that one process. RES is the amount of memory the process IS actually touching, including shared memory that other processes may be sharing. Finally, SHR is the amount of shared memory the process is touching. so, taking your biggest process, it is linked to enough libraries and shared memory and it's own private memory to add up to 4288Meg. It is currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is shared with other processes. So, the difference between RES and SHR is 0, so the delta, or extra memory it's using besides shared memory is ZERO (or very close to it, probably dozens or fewer of megabytes). So, you're NOT running out of memory. Remember when I mentioned iostat, vmstat, etc up above? Have you run any of those?
