Re: Slow in morning hours

Hello,

Has anyone designed/implemented postgresql server on storage networks?

Are there any design considerations?

Are there any benchmarks for storage products (HBAs, Switches, Storage
Arrays)?

Any recommendation on the design, resources, references, keeping PG in
mind?


Thanks,
Anjan

************************************************************************
**

This e-mail and any files transmitted with it are intended for the use
of the addressee(s) only and may be confidential and covered by the
attorney/client and other privileges. If you received this e-mail in
error, please notify the sender; do not disclose, copy, distribute, or
take any action in reliance on the contents of this information; and
delete it from your system. Any other use of this e-mail is prohibited.

Anjan,

> Has anyone designed/implemented postgresql server on storage networks?

Yes, Zapatec.com runs their stuff this way. Probably others as well.

> Are there any design considerations?

I don't know. Probably.

> Are there any benchmarks for storage products (HBAs, Switches, Storage
> Arrays)?

Not specific to PostgreSQL. I'm sure there are generic benchmarks. Keep
in mind that PostgreSQL needs lots of 2-way I/O, batch writes, and random
reads.

> Any recommendation on the design, resources, references, keeping PG in
> mind?

See above. Also keep in mind that PostgreSQL's use of I/O should improve
100% in version 7.5.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

>Anjan,
>
>
>
>>Has anyone designed/implemented postgresql server on storage networks?
>>
>>
>
>Yes, Zapatec.com runs their stuff this way. Probably others as well.
>
>
>
>>Are there any design considerations?
>>
>>
>
>I don't know. Probably.
>
>
>
>>Are there any benchmarks for storage products (HBAs, Switches, Storage
>>Arrays)?
>>
>>
>
>Not specific to PostgreSQL. I'm sure there are generic benchmarks. Keep
>in mind that PostgreSQL needs lots of 2-way I/O, batch writes, and random
>reads.
>
>
>
>>Any recommendation on the design, resources, references, keeping PG in
>>mind?
>>
>>
>
>See above. Also keep in mind that PostgreSQL's use of I/O should improve
>100% in version 7.5.
>
>
>
We run PG on a SAN array. We currently have it setup so a single PG
instance runs off of a single LUN, this includes the WAL logs. Apart
from that we have made no other special considerations; we just treat it
as a fast RAID array. We haven't got to the stage where the speed of the
SAN is a problem as load hasn't increased as expected. This will change,
when it does I am sure the performance list will be hearing from us ;-).
Out current limitations, as I see it, are amount of memory and then
processing power. The only problem we have had was a dodgy set of kernel
modules (drivers) for the fibre cards, this was because they were beta
drivers and obviously still had a few bugs. This was solved by reverting
to an older version. Everything has run smoothly since then (uptime is
153 days :-)).

Nick

Hi All,

I am using Linux 7.2 and postgresql 7.2.

Our Office hours are over at 6pm but we use to keep our server
running 24 hours a day. On the second day morning, Our PGSQL
Server becomes very slow.

After continuous usage of one hour, It gradually starts responding
faster ! This has become every day routine !

do u have any idea related to this !!!! Is there any other reason that I
need to check up?

Please any any idea to get relief daily morning problem !!

Thanxs,
Vishal

On Fri, Feb 20, 2004 at 02:46:15PM +0530, vathakar(at)banas(dot)guj(dot)nic(dot)in wrote:
>
> After continuous usage of one hour, It gradually starts responding
> faster ! This has become every day routine !
>
> do u have any idea related to this !!!! Is there any other reason that I
> need to check up?

What's running on the machine during those hours? Maybe VACUUM is
sucking up all your bandwidth. Or your backups. Or some other
cron job.

Note that 7.2 is pretty old. There are several performance
improvements in subsequent versions.

A

--
Andrew Sullivan

vathakar(at)banas(dot)guj(dot)nic(dot)in wrote:
> Hi All,
>
> I am using Linux 7.2 and postgresql 7.2.
>
> Our Office hours are over at 6pm but we use to keep our server
> running 24 hours a day. On the second day morning, Our PGSQL
> Server becomes very slow.
>
> After continuous usage of one hour, It gradually starts responding
> faster ! This has become every day routine !
>
> do u have any idea related to this !!!! Is there any other reason that I
> need to check up?
>
> Please any any idea to get relief daily morning problem !!

I've seen this happen, and not just with PostgreSQL. The reasons are many
an varied, but here's my experience on the most common.

1) As someone else suggested, there may be some daily maintenance process
(i.e. backup) that's still running when you come in. Check this, and
reschedule if necessary.

2) Even if these nightly maintenance processes are finished when you first
come in, they've probably completely rearranged the contents of RAM.
Meaning, data that Linux had cached that made Postgres fast now needs
to be fetched from disk again. There are some things you can do, such
as adding RAM or getting faster disks, but this is a difficult problem
to solve. Some of the nightly processes could be safely disabled,
possibly, such as rebuilding the located database (if you don't use
locate) Possibly (I'm guessing here) if you scheduled pg_dump to be
the last process to run at night, it might put the cache back in a
better state?

3) First thing AM load. It's quite common for load to be higher at certain
times of the day, and first thing in the morning is a common time for
load to be higher than usual (especially for email servers). Check the
load on the machine with tools like top and see if it isn't just busier
in the morning than other times during the day. There might even be one
or two particular queries that people only run first thing that bog the
machine down. Depending on what you find, you may be able to optomise
some queries. Possibly some fine-tuning could correct the problem. Or
you might be forced to upgrade hardware if you want the machine to handle
the higher morning load faster. First thing to determine, though, is
whether or not the load is higher or the same.

Without more detail on the load, setting, etc of your system, these are all
guesses. Hopefully the information is helpful, though.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Have you tried VACUUM ANALYZE at least one a day?

Regards
On Fri, 20 Feb 2004 vathakar(at)banas(dot)guj(dot)nic(dot)in wrote:

> Date: Fri, 20 Feb 2004 14:46:15 +0530
> From: vathakar(at)banas(dot)guj(dot)nic(dot)in
> To: pgsql-performance(at)postgresql(dot)org
> Cc: vathakar(at)banas(dot)guj(dot)nic(dot)in
> Subject: [PERFORM] Slow in morning hours
>
> Hi All,
>
> I am using Linux 7.2 and postgresql 7.2.
>
> Our Office hours are over at 6pm but we use to keep our server
> running 24 hours a day. On the second day morning, Our PGSQL
> Server becomes very slow.
>
> After continuous usage of one hour, It gradually starts responding
> faster ! This has become every day routine !
>
> do u have any idea related to this !!!! Is there any other reason that I
> need to check up?
>
> Please any any idea to get relief daily morning problem !!
>
> Thanxs,
> Vishal
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

Josh Berkus wrote:
>
>
> See above. Also keep in mind that PostgreSQL's use of I/O should improve
> 100% in version 7.5.
>

Really? What happened?

In article <40361DBE(dot)3636(dot)10FEBF(at)localhost>,
<vathakar(at)banas(dot)guj(dot)nic(dot)in> writes:

> Hi All,
> I am using Linux 7.2 and postgresql 7.2.

> Our Office hours are over at 6pm but we use to keep our server
> running 24 hours a day. On the second day morning, Our PGSQL
> Server becomes very slow.

> After continuous usage of one hour, It gradually starts responding
> faster ! This has become every day routine !

> do u have any idea related to this !!!! Is there any other reason that I
> need to check up?

> Please any any idea to get relief daily morning problem !!

I guess you're doing a VACUUM at night which invalidates the buffer
cache. If that's what happens, it's easy to fix: run some dummy
queries after the VACUUM which cause the buffer cache to get filled.