Postgres: use with MASSIVE load/traffic

[Anon]

Greetings,

I am having a problem with Postgres' speed when I receive HUGE amounts of traffic / queries (approx. 200 simultaneous users performing on average 300-400 queries per second).

I was using pg_connect then after switching to pg_pconnect I noticed the site FLEW!!!

The only problem is the number of httpd processes also flies, and in a few minutes it exceeds the max number of connections allowed when running postgres and thus crashes the server.

I've set shared mem, buffers to very high amounts, I'm running on a FreeBSD machine with PostgreSQL 7.1.3, 1Gig RAM and dual PIII-1GHz processors. I've built my kernal to donate 75% of the RAM to SHMHAX (or to postgres)

in my situation, should I be using pg_connect or pg_pconnect?? pg_connect takes a lot longer to reach max connections, but ti still does and is generally slower than pg_pconnect. however if I use either of these methods I still need ot find a way to kill off processes so they don't surpass the max connections.

any help on this problem ASAP would be greatly appreciated.

Thankx, Keith

[e-mail]
[phone] ext.225
Senior Programmer
http://www.wisol.com

[amcgrath]

Keith,

I'm curious if you've tried setting the database max connections (-N) to the same number of httpd connections. Or, increase the number of db connections to an amount exceeding the http connections. (I had a similiar problem a few years ago, with a different database and this helped solve it).

[[deleted]]

This sounds like a logical solution to me too.

[Anon]

Thanks for the suggestion, however there are still problems with it.

My MaxClients is 1000, on 4 webservers for a total of 4000......starting Postgres allowing 4001 connections works, however it is super slow. And the amount of pconnect processes keeps growing, as they never close themselves.

Keith Bussey
[e-mail]
[phone] ext.225
Senior Programmer - wisol.com

[amcgrath]

OK, hopefully my questions "might" help:

You can't close with pg_close() so that's outta the question for pg_pconnect, but would doing this in a pg_connect() scenario work for you? (I'm referring to your first post here).

4000 sounds kinda high, though I'm not as familiar with your situation as I would be if you hired me (hint hint... just kidding). Depending on the true speed of "the site FLEW" (from first post), then would lowering this amount to say 100 across the 4 web servers and incrementally increasing it (or decreasing, if the case arises) until you reach an acceptable peformance level be doable for you?

Do all the PHP scripts access the database? if not, is moving those that don't to a different machine (God, that would be a pain) work for you?

400 queries from one user per second or all combined for 400? I guess I'm hinting at optimization of the queries more than anything, though thats old advice.

let me know how it goes, I'm really interested in the final solution.

[smarlowe]

Play around with having fewer max clients under your apache farm. 100 or so per machine should be enough if you are handling 200 or so simo users.

[Anon]

To answer your questions:

1 - pg_connect isn't an option really, it just seems to be sooooooo slow when we get more than a few users coming on (im guessing the overhead of opening/closing connections).

2- we need the MaxClients 1000 on 4 webservers because we just get that much traffic, with members performing many many queries.

3- Practically every script does access the DB, so moving the 1% that don't to another server wouldn't really be useful I think.

4- I meant combined for 400, not 400 each user per second =)....If each user had to do 400 queries per second, I don't have a clue what kind of site we'd be running hehe....and I've optimized the queries and limited the number of them as best I can.

5- Any lasts thoughts/opinions ?? as for hiring you hehe we are located in Montreal =) no idea where you are.

Keith Bussey
[e-mail]
[phone] ext.225
Senior Programmer - wisol.com

[Anon]

Thing is, we can have much more than that number, upto 2000-3000 simultaneous users.

Keith Bussey
[e-mail]
[phone] ext.225
Senior Programmer - wisol.com

[amcgrath]

Keith,

Lowering the number, I think, will give you a lot of bang for your buck. More isn't necessarily better, trust me. I'm trying to find an idea of how many sim connections is too much for Postgres, but I did see the man page for postmaster uses 1024 as reference. Not sure, though.

Either way, 400 sim connects to postgres would be where I'd start (100 across the farm) and move it up or down from there.

(I've been in a sim situation (15 million pageviews using Cold Fusion with Oracle, and lowering the sim connects actually helped.

[[deleted]]

Just a little question:

what kind of cpu-load are you getting on the database server? and what kind of memory usage?

[Anon]

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

[Anon]

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?

[amcgrath]

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?

[Anon]

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

[Anon]

I'm not exactly sure what you are asking....
do you want to know basicaly what 'top' command gives me ?

Keith

[smarlowe]

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?

[Anon]

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%. 🙂

[smarlowe]

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.