Re: Questions about server. - Mailing list pgsql-general

On Wed, Dec 21, 2005 at 09:46:59AM +0000, Richard Huxton wrote:
> max chin wrote:
> >1.)  What I knew is when too many users access a database at the same
> >time, it will slow down database server process. My question is how
> >to make database server process more faster even if a lot of users
> >access information in database at the same time? What kind of
> >technology is needed to help database server process more faster ?
>
> The first step is to find out *why* it is too slow - not enough memory?
> Not enough disk bandwidth? Not enough CPU? Badly planned queries? Badly
> written application?
>
> So - the first thing to do is to start monitoring your server closely.

Also, an experienced PostgreSQL consultant would probably be able to
look at both the database configuration as well as the overall system
architecture and make recommendations based on just his experience.

> >4.) Sometimes IIS web server is unavailable and the web pages can' t
> >display for clients. Can you tell me the reasons and the methods to
> >overcome the problems?
>
> Although there are many people who use IIS in big installations, I'm not
> one of them. For maximum reliability, I'd guess Apache on top of one of
> the BSD's would be a winner, but probably not an option for you.

You should also consider having more than one web server connecting to
the database, with some kind of connection pooling.

> >THANKS YOU. CAN YOU GIVE ME YOUR ANSWER AS FAST AS POSSIBLE BECAUSE I
> >NEED THESE IMPORTANT ANSWER URGENTLY.
>
> If you're a student on work placement, I'm not sure you should have to
> deal with these sort of issues, certainly not urgently.

I tend to agree with Richard. I'm guessing that you're dealing with some
kind of e-commerce site that costs your employer money any time it's
unavailable. No offense to you, but if the website is that important to
them they should have access to someone who has experience with high
availability. They should find a PostgreSQL consultant who can at least
advise them on their needs, and provide guidance and training to you and
other people on staff. Most people I know who do consulting like that
have many years of database experience (maybe decades) and at least a
few years of PostgreSQL experience to guide them. While the PostgreSQL
community is an outstanding resource, it's not the same as having a
person to talk to on the phone and get out to your office. In some ways
it's better, but in others it's worse.

Of course, being 'the new guy' you may not be in a position to recommend
this to your boss, but it is something to think about.

http://www.postgresql.org/support/professional_support_asia is a listing
of companies offering commercial support in Asia.

Disclosure: I work for a company that provides commercial support,
although we don't have much presence in Asia.

Having said all that, here's some things you should look at:

If the data's important, having 1 hour old backups might well not be
good enough. PITR with default settings will get you 5-minute old
backups, but that's probably still more delay than is desirable. Slony
should normally be only a few seconds behind a master (if even that
much), so that's probably the best way to go. It is more difficult to
setup, though.

Make certain you're vacuuming enough! This is a huge gotcha for people
new to PostgreSQL.

Make sure your FSM (Free Space Map) settings are high enough. If you
mail the list with the last 10 lines from a vacuumdb -av we can advise
you. See also
http://pervasivepostgres.com/instantkb13/article.aspx?id=10087.

The default shared_buffers setting of 1000 is very small and is
typically only suited for a desktop machine. Generally recommended
settings are 10-25% of server memory, but keep in mind that
shared_buffers is in 8K pages.

You should also take a look at
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
for what different postgresql.conf parameters do. And
http://www.powerpostgresql.com/PerfList is a good general guide to
database performance.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461