Re: [PERFORM] The black art of postgresql.conf tweaking
hi, Paul Serby wrote: Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to setup our db server that serves our web applications. http://www.phpbuilder.com/columns/smith20010821.php3?page=2 We have a Dell Poweredge with the following spec. CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 sort_mem = 12000 But Apache is still maxing out the non-super user connection limit. The machine is under no load and I would like to up the max_connections but I would like to know more about what you need to consider before doing so. One more: In php.ini, set the pgsql.max_persistent lower then 300 ; Maximum number of persistent links. -1 means no limit. pgsql.max_persistent = -1 - change this C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] The black art of postgresql.conf tweaking
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks to everyone for there help. I've changed my postgres settings to the following max_connections = 500 shared_buffers = 1 sort_mem = 2000 effective_cache_size = 5000 The 'effective_cache_size' is just a guess, but some references suggest it so I added it. Dropping the Apache Keep-alive down to 3 seconds seems to have was a great tip I now have far less idle connections hanging about. I've not maxed out the connections since making the changes, but I'm still not convinced everything is running as well as it could be. I've got some big result sets that need sorting and I'm sure I could spare a bit more sort memory. Where does everyone get there information about the settings? I still can't find anything that helps explain each of the settings and how you determine there optimal settings. If anyone wants interested here is a table schema form one of the most used tables. CREATE TABLE tblForumMessages ( ~pk_iForumMessagesID serial, ~fk_iParentMessageID integer DEFAULT 0 NOT NULL, ~fk_iAuthorID integer NOT NULL, ~sSubject character varying(255) NOT NULL, ~sBody text, ~fk_iImageID oid, ~dtCreatedOn timestamp with time zone DEFAULT now(), ~iType integer DEFAULT 0, ~bAnonymous boolean DEFAULT false, ~bLocked boolean DEFAULT false, ~dtHidden timestamp with time zone, ~fk_iReplyToID integer, ~iCreateLevel integer DEFAULT 7 ); This is the query that is most called on the server explained EXPLAIN ANALYZE SELECT tblForumMessages.* FROM tblForumMessages WHERE fk_iParentMessageID = 90 ORDER BY dtCreatedOn DESC Which gives the following: Sort (cost=8156.34..8161.71 rows=2150 width=223) (actual time=0.264..0.264 rows=0 loops=1) ~ Sort Key: dtCreatedOn ~ - Index Scan using fk_iParentMessageID_key on tblForumMessages ~ (cost=0.00..8037.33 rows=2150 width=223) (actual time=0.153..0.153 rows=0 loops=1) ~ Index Cond: (fk_iParentMessageID = 90) ~ Total runtime: 0.323 ms SELECT COUNT(*) FROM tblForumMessages WHERE fk_iParentMessageID = 90 Returns: 22920 SELECT COUNT(*) FROM tblForumMessages Returns: 429913 Paul Serby wrote: | Can anyone give a good reference site/book for getting the most out of | your postgres server. | | All I can find is contradicting theories on how to work out your settings. | | This is what I followed to setup our db server that serves our web | applications. | | http://www.phpbuilder.com/columns/smith20010821.php3?page=2 | | We have a Dell Poweredge with the following spec. | | CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) | CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) | CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) | CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) | Physical Memory: 2077264 kB | Swap Memory: 2048244 kB | | Apache on the Web server can take up to 300 connections and PHP is using | pg_pconnect | | Postgres is set with the following. | | max_connections = 300 | shared_buffers = 38400 | sort_mem = 12000 | | But Apache is still maxing out the non-super user connection limit. | | The machine is under no load and I would like to up the max_connections | but I would like to know more about what you need to consider before | doing so. | | The only other source I've found is this: | | http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html | | But following its method my postgres server locks up straight away as it | recommends setting max_connections to 16 for Web sites? | | Is there a scientific method for optimizing postgres or is it all | 'finger in the air' and trial and error. | | ---(end of broadcast)--- | TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBF4nxp51pUZR6gxsRAi8cAJ9HBfpNMGQR7vurk0wYW+p6KfqZzACfc9NX k72iabZxK+gku06Pf7NmHfQ= =Ftv6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] The black art of postgresql.conf tweaking
On Monday 09 Aug 2004 7:58 pm, Paul Serby wrote: I've not maxed out the connections since making the changes, but I'm still not convinced everything is running as well as it could be. I've got some big result sets that need sorting and I'm sure I could spare a bit more sort memory. You could set the sort mem for that connection before issuing the query. i.e. # set sort_mem=2; # select * ; And reset it back. Setting it globally is not that good. If you do it selectively, that would tune it as per your needs.. Where does everyone get there information about the settings? I still can't find anything that helps explain each of the settings and how you determine there optimal settings. http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html HTH Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] The black art of postgresql.conf tweaking
Paul Serby wrote: Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to setup our db server that serves our web applications. http://www.phpbuilder.com/columns/smith20010821.php3?page=2 We have a Dell Poweredge with the following spec. CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 sort_mem = 12000 But Apache is still maxing out the non-super user connection limit. Tell us the value MaxClients in your apache configuration Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] The black art of postgresql.conf tweaking
Paul, Physical Memory: 2077264 kB sort_mem = 12000 Hmmm. Someone may already have mentioned this, but that looks problematic. You're allowing up to 12MB per sort, and up to 300 connections. Even if each concurrent connection averages only one sort (and they can use more) that's 3600MB ... roughly 1.5 times your *total* RAM, leaving out RAM for Apache, postmaster, shared buffers, etc. I strongly suggest that you either decrease your total connections or your sort_mem, or both. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] The black art of postgresql.conf tweaking
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: | Paul, | | |Physical Memory: 2077264 kB | | |sort_mem = 12000 | | | Hmmm. Someone may already have mentioned this, but that looks problematic. | You're allowing up to 12MB per sort, and up to 300 connections. Even if each | concurrent connection averages only one sort (and they can use more) that's | 3600MB ... roughly 1.5 times your *total* RAM, leaving out RAM for Apache, | postmaster, shared buffers, etc. | | I strongly suggest that you either decrease your total connections or your | sort_mem, or both. Of course your are speaking about the worst case, I aplly in scenarios like this on the rule 80/20: 80% of connection will perform a sort and 20% will allocate memory for the sort operation in the same window time: 300 -- 80% -- 240 -- 20% -- 48 48 * 12MB = 576 MB that seems resonable with the total ammount of memory available. Am I too optimistic? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBE81z7UpzwH2SGd4RAuzzAJ98Ze0HQedKaZ/laT7P1OS44FG0CwCfaWkY MAR1TEY1+x61PoXjK/K8Q4Y= =8UmF -END PGP SIGNATURE- ---(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
Re: [PERFORM] The black art of postgresql.conf tweaking
Gaetano, Of course your are speaking about the worst case, I aplly in scenarios like this on the rule 80/20: 80% of connection will perform a sort and 20% will allocate memory for the sort operation in the same window time: Well, I suppose it depends on how aggresive your connection pooling is. If you minimize idle connections, then 300 connections can mean 200 concurrent queries. And since Paul *is* having problems, this is worth looking into. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] The black art of postgresql.conf tweaking
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: | Gaetano, | | |Of course your are speaking about the worst case, I aplly in scenarios | | like | |this on the rule 80/20: 80% of connection will perform a sort and 20% will | | allocate | |memory for the sort operation in the same window time: | | | Well, I suppose it depends on how aggresive your connection pooling is. If | you minimize idle connections, then 300 connections can mean 200 concurrent | queries. And since Paul *is* having problems, this is worth looking into. With 4 CPU ( like Paul have ) there is a lot of space in order to have 200 concurrent connection running but I don't believe that all 200 togheter are allocating space for sort, I have not seen the code but I'm quite confident that the memory for sort is released as soon the sort operation is over, not at the end of connection. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBFBcn7UpzwH2SGd4RAuNhAJ0f+NVUlRUszX+gUE6EfYiFYQy5JQCgnaRj HcguR1U3CgvQiZ4a56PBtVU= =6Jzo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] The black art of postgresql.conf tweaking
Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to setup our db server that serves our web applications. http://www.phpbuilder.com/columns/smith20010821.php3?page=2 We have a Dell Poweredge with the following spec. CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 sort_mem = 12000 But Apache is still maxing out the non-super user connection limit. The machine is under no load and I would like to up the max_connections but I would like to know more about what you need to consider before doing so. The only other source I've found is this: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html But following its method my postgres server locks up straight away as it recommends setting max_connections to 16 for Web sites? Is there a scientific method for optimizing postgres or is it all 'finger in the air' and trial and error. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] The black art of postgresql.conf tweaking
On Aug 4, 2004, at 8:45 AM, Paul Serby wrote: Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 sort_mem = 12000 But Apache is still maxing out the non-super user connection limit. Did you restart PG after making that change? (you need to restart, reload won't change max_connections) Also, you're sort_mem is likely too high (That is the amount of memory that can be used PER SORT) and you s hould back down on shared_buffers. (General consensus is don't go over 10k shared buffers) Another thing you may want to try is using pgpool and regular pg_connect - this way you only have a pool of say, 32 connections to the DB that are shared among all apache instances. This gets rid of the need to have hundreds of idle postgres' sitting around. Connecting to pgpool is very fast. We use it in production here and it works wonderfully. And it is 100% transparent to your application. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] The black art of postgresql.conf tweaking
Paul Serby wrote: Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect max_connections = 300 But Apache is still maxing out the non-super user connection limit. Don't forget also that some connections are reserved for superusers (usually 2), so if you want 300 users, you need to set max_connections to 300 + superuser_reserved_connections. -- Michal Taborsky http://www.taborsky.cz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] The black art of postgresql.conf tweaking
Am Mittwoch, 4. August 2004 14:45 schrieb Paul Serby: Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 sort_mem = 12000 But Apache is still maxing out the non-super user connection limit. for most websites 300 connections is far too much (imagine even 10 request per second for 10 hours a day ends up to 10.8 Mio pages a month) but anyway: you should first focus on closing your http connection to the user as fast as possible. then you dont need so much concurrent connections which keep db connections open and uses memory. I did the following: - apache: keepalive off - apache patch: lingerd (google for it) - apache mod_gzip - pg_pconnect this keeps your http connection as short as possible, so the apache child is ready to serve the next client. Imagine 5 seconds of keepalive 1 second on lingering half-closed tcp connections and 4 more seconds for transport of uncompressed content. in this scenario your apache child uses memory an your pooled db connection for 10 seconds while doing nothing! in my experience apache in standard configuration can be the main bottleneck. and teh combination of keepalive off, lingerd and mod_gzip is GREAT and i didn't found much sites propagating a configuration like this. kind regards, janning p.s: sorry for being slightly off topic and talking about apache but when it comes to performance it is always important to look at the complete system. ---(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] The black art of postgresql.conf tweaking
On 04/08/2004 13:45 Paul Serby wrote: Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to setup our db server that serves our web applications. http://www.phpbuilder.com/columns/smith20010821.php3?page=2 We have a Dell Poweredge with the following spec. CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 Might be higher that neccessary. Some people reckon that there's no measurable performance going above ~10,000 buffers sort_mem = 12000 Do you really need 12MB of sort memory? Remember that this is per connection so you could end up with 300x that being allocated in a worst case scenario. But Apache is still maxing out the non-super user connection limit. The machine is under no load and I would like to up the max_connections but I would like to know more about what you need to consider before doing so. I can't think why you should be maxing out when under no load. Maybe you need to investigate this further. The only other source I've found is this: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html But following its method my postgres server locks up straight away as it recommends setting max_connections to 16 for Web sites? I think you've mis-interpreted that. She's talking about using persistent connections - i.e., connection pooling. Is there a scientific method for optimizing postgres or is it all 'finger in the air' and trial and error. Posting more details of the queries which are giving the performance problems will enable people to help you. You're vacuum/analyzing regularly of course ;) People will want to know: - PostgreSQL version - hardware configuration (SCSI or IDE? RAID level?) - table schemas - queries together with EXPLAIN ANALYZE output also output from utils like vmstat, top etc may be of use. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
