Thread: 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.
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 <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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
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.
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 | +------------------------------+---------------------------------------------+
Janning Vygen wrote: >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 >> The number of connections in apache and in postgresql are the keys. From what you've described, apache can have up to 300 child processes. If this application uses a different identity or db for different connects then you may have more than one connection open per process easily exhausting your available connections. Also, your application may open multiple connections to postgresql per process. See if setting max_connections in postgres to a larger works, but you may want to reduce your sort_mem proportionately to keep from overbooking your system. >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 > > KeepAlive for 2 or 3 seconds is quite sufficient. This keeps the current number of connections down for those browsers that support it, and keeps the server from leaving too many open. We found KeepAlive off caused too many http connections to be opened and closed for our applications and hardware to keep up. The benefit is facilitating a rapid succession of requests: page loads, graphics, embedded objects, frames, etc. >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. > > Good advice.
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
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
-----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-----
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
-----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-----
-----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 = 10000
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 majordomo@postgresql.org
-----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-----
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=20000; # 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
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.