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]

    • Post #19

    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:

                        http://groups.google.com/groups?q=kernel.sem&hl=en&group=comp.os.linux.*&rnum=2&selm=3AAE76B6.E5779D16%40opentext.com

                        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.

                          Sorry for taking so long to reply.....

                          Ok, after doing what you've outlined here I have successfully gotten Postgres to start with 4000 backends, however I decided to drop it to 2000 and put MaxClients to 500 on the 4 webservers.

                          But the problem still remains that the DB server starts to hang around 160-200 connections(processes), let alone if i ever had to go upto 2000! I see no other way of getting around this than apachectl graceful'ing every 2 minutes (which is obviously not a viable option) to clear off idle processes.

                          Keith

                            Do the postgresql debug logs have anything in them that can give you a hint on what's going wrong? How big can you set the number of clients with pgbench (it's in the postgresql-7.1.3/contrib/pgbench directory)?

                              as far as I know , pgbench doesn't have a limit on how many users you can ask it to simulate. I've tried viewing the debug logs, but can't find anything wrong in there either..........

                              • Keith
                                4 months later
                                Write a Reply...