[deleted]
Just a little question:
what kind of cpu-load are you getting on the database server? and what kind of memory usage?
Postgres: use with MASSIVE load/traffic
There is no way to lower them anywhere below about 500, which I tried and did not help.
I have also just tried setting
pgsql.max_persistent = 40 ; maximum number of persistent links. -1 means no limit
in /usr/local/etc/php.ini but it doesn't seem to matter as
ps aux | grep postgres | wc -l
gives me many more than 160 processes (40*4 servers)......
Keith
Hrm......each process was taking up about 1-2% of the CPU.....and about half our memory.
keith
[deleted]
I meant what's the total load of your db-server?
I'm kinda grasping here, I am very interested in seeing what fixes it:
What did u set as the max connections when you started the database (-N option on postmaster). ? Does it match your php.ini file?
Are any queries running slower than others, maybe causing a bottleneck?
Well yes, more complex queries run slower than others.....but in testing (with only about 1-2 users) everything went smooth.
I've tried setting max connections to 64, 128, 165 (5 more than 40*4 servers php.ini value),200,300,even 500. upto 300 its cool, beyond that people get "max number of clients reafched" error
I'm also very interested to finding a oslution to this problem =))
Keith
I'm not exactly sure what you are asking....
do you want to know basicaly what 'top' command gives me ?
Keith
A couple more ideas you may or may not have tried...
After setting the pgsql.maxpersistant var, did you restart both apache and postgresql to be sure the change "took". I don't know if you have to do that or not, just guessing on that one.
What kernel are you running on your db server, I've found the 2.4 series is much faster under heavy parallel load than 2.2 was.
have you edited the /etc/sysctl.conf file to include higher settings for shmmax and shmall? The defaults are woefully small, but I thought I saw somewhere here you had changed that.
If so, what does ipcs say about your shared memory usage by postgresql?
Yes I restarted both apache and postgres....although I found that was not my problem with setting pgsql.maxpersistant var. I read that this var works not as I originally thought, and setting ti to 40 was definately wrong! it is the number of allowed p connections per httpd process, not in all. so if i had 40, and Max Clients 1000, thats 40,000 * 4 servers for 160,000 allowed!!
the solution ot this is setting it to only 1 instead of it's default infinite (-1)....but that is still 1 1000 4 which is way too many.
im using a 2.4 kernal, and all memory settings are cool....
Keith
[deleted]
I want to know how close you are getting to a CPU load of 100%. 
So, what are your shmmax and shmall settings? I've got a box with 512 Meg ram and can run about 500 to 700 backends for postgresql with no problem. my smmax is 128000000 and my shmall is 8000000.
Ok here are the details I can find on the box:
FreeBSD 4.3-RELEASE #0
kern.ipc.shmmax: 786432000
kern.ipc.shmall: 192000
to start postgres i use:
/usr/local/pgsql/bin/postmaster -Si -o -F -N128 -B90000 -D /usr/local/pgsql/data
when im not running postgres, here are my loads:
CPU states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 5460K Active, 362M Inact, 49M Wired, 7940K Cache, 112M Buf, 579M Free
Swap: 2052M Total, 1664K Used, 2050M Free
when i startup postgres:
CPU states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 9056K Active, 369M Inact, 50M Wired, 7724K Cache, 112M Buf, 568M Free
Swap: 2052M Total, 1664K Used, 2050M Free
on the webservers, I have these settings:
PHP.INI:
pgsql.allow_persistent = On
pgsql.max_persistent = 1
pgsql.max_links = 1
HTTPD.CONF:
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 20
MaxSpareServers 30
StartServers 10
MaxClients 1000
MaxRequestsPerChild 0
and for opening my connections i've tried both pg_pconnect & pg_connect/pg_close.
Thanks, Keith
to add ot that, when i start getting requests this is how they look to 'top' command:
39215 postgres 2 0 728M 21272K sbwait 0:00 5.00% 0.24% postgres
39214 postgres 2 0 728M 21272K sbwait 0:00 2.05% 0.20% postgres
39036 postgres 2 0 720M 2380K select 0:00 0.00% 0.00% postgres
39179 postgres 2 0 728M 21276K sbwait 0:00 0.00% 0.00% postgres
39164 postgres 2 0 728M 21276K sbwait 0:00 0.00% 0.00% postgres
39153 postgres 2 0 728M 21144K sbwait 0:00 0.00% 0.00% postgres
39187 postgres 2 0 728M 21276K sbwait 0:00 0.00% 0.00% postgres
39154 postgres 2 0 728M 21272K sbwait 0:00 0.00% 0.00% postgres
39196 postgres 2 0 728M 21276K sbwait 0:00 0.00% 0.00% postgres
39207 postgres 2 0 728M 21276K sbwait 0:00 0.00% 0.00% postgres
- Keith
Ok, a few things.
I use pg_ctl to start and stop postgresql, and the $PGDATA/postgresql.conf file to configure it. Much easier, and it works (unlike apachectl, which seems to work only when it's convenient :-)
-N128 is prett small. You need to have the number of backends equal or exceed the total number of apache front ends you're gonna have usually. I've found that with 10,000 buffer blocks or more, you can easily handle 500 to 1000 backend servers usually. In this case, with max clients of 1000 on four boxes, you probably need to be able to handle 4000 backends, and while I have started up postgresql with that many in the past, performance really can be pretty dodgy by the time the postmaster is handling that many backends. I'll assume you've got plenty of inodes allocated (if BSD needs that setting, I'm more familiar with Linux) so you aren't running out of handles for all those processes.
Try to increase the -N param while decreasing the maxclients in httpd.conf and see if you can get it to behave a little better. But I'm not sure what you can do at this point without more memory on your db server. How much memory can your server hold, by the way?
On a system this busy, set your start servers to 100, minspare to 100, and max spare to something like 200. That way apache won't be creating and destroying children as load rises and falls. Also crank up max keepalive if you've got enough memory for it, and extend it to 60 seconds or so if you can.
Well, I hope that helped...
OK, I've tried using pg_ctl to start it up instead but no differenc, just less to type =)
another thing is, I cannot start Postgres with more than 1024 max connections. Thus, No way I can get near 4000. I am able to lower MaxClients to 500 per server.....but that would be 2000, how can I get past this 1024 max?
FATAL 1: option 'max_connections' value 4000 is outside of permissible range [1 .. 1024]
I've tried changing the other apache variables you've suggested, but not noticed any difference with Postgres. And our server can hold another gig of ram, which if we put in would bring us up to 2Gigs of RAM
Thanks, Keith
You need to recompile postgresql with a --with-maxbackends=xxxx switch.
Try setting it to 4096 or so. Then you should be able to get enough backends going to handle the load.
Hope that helps.
Cool, I've recompiled with that parameter........I'm going to try testing now using pgbench and pounding the database to see what hapens........hopefully that might give me some clues as to what the problem is , it may be Apache just isn't recognizing open persistent connections and thus always opens another new one (someone suggested that), does that make sense ?
Just fyi, I also had to edit postgresql-7.1.3/src/include/config.h to increase the max backends to something higher. Forgot that.
OK, I can get up to about 1350 or so backends on my box before I get this message from postmaster on startup:
IpcSemaphoreCreate: semget(key=5432094, num=17, 03600) failed: No space left on device
This error does not mean that you have run out of disk space.
It occurs either because system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to aise the respective kernel parameter. Look into the PostgreSQL documentation for details.
So now I'm off to find out how to tweak the kernel to handle more semaphores...
OK, I now have my postgresql box at work running with 4000 backends max. Here's what I had to do:
edit postgresql-7.1.3/src/include/config.h.in
Find the line that needs to be changed, it should look like this:
#define MAXBACKENDS (DEF_MAXBACKENDS > 4096 ? DEF_MAXBACKENDS : 4096)
The max of 1024 is still hardcoded into the silly thing, so change it to 4096 or high like I did.
Then rebuild postgresql from source.
Next, we need to increase the shmmax and shmall, on my linux box I did it this way:
edit /etc/sysctl.conf and add the following lines:
kernel.shmall=16000000
kernel.shmmax=128000000
kernel.sem=250 32000 32 512
Note that my shmmax is so small because the test box I'm only only has 256 Megs. On a real server you can crank it up to a much higher number.
The kernel.sem parameter lets you set the semaphore parameters. It is referenced in the kernel source, but this usenet message was my source of reference:
The layout is:
kernel.sem=SEMMSL SEMMNS SEMOPM SEMMNI
and I cranked up SEMMNI to 512 from the default 256. Seems to have done the trick.