On 06/07/2010 09:24 AM, Florian Heigl wrote:
> Hi all,
>
>> Within psql/mysql:
>> 1) select count(*) from File;                    1sec    30min
>
> Disclaimer: I don't know a dime's worth of databases per se. But I
> spend a lot of time hunting other peoples performance issues. :)
>
> I think you should start identifying the cause for this bit at the
> very first, as it shows the absolutely worst perfomance and probably
> what slows this also slows the rest.
> My nose says this is really smelly and should'nt even take as long
> without any indexes.

I totally agree.  This was my first cause for concern when after the 
data import, I wanted to check that I still had the same number of rows.

>
> Can you verify whether the system swaps during your test commands or doesn't?
>

No, it does not swap, in this case or any other one I've tested.

> sar -dp 100 1 | grep -v nodev will do nicely with the external storage.
>

sdc1 is the database partition (including log and temp files)...

11:05:21 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz 
     await     svctm     %util
11:07:01 AM       sdd    449.95 111886.88      4.32    248.67      1.54 
      3.41      2.11     94.85
11:07:01 AM      sdd1    449.95 111886.88      4.32    248.67      1.54 
      3.41      2.11     94.85

Average:          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz 
     await     svctm     %util
Average:          sdd    449.95 111886.88      4.32    248.67      1.54 
      3.41      2.11     94.85
Average:         sdd1    449.95 111886.88      4.32    248.67      1.54 
      3.41      2.11     94.85


> also can you please let us know the iowait times (also from sar) and
> promise it's not a Raid5 array you have in use?
> (but, admittedly it doesn't explain for a big difference between mysql
> and postgresql)
>

Yes, it is RAID5, it was the only place I could go to get external 
space, with internal being inadequate.  However, you point out that this 
isn't a fundamental problem as mysql performance was mostly ok.
The different must be in the postgres config (or postgres itself).


> Also, I don't know if I would value RedHat supporting postgre 8.1
> higher than running 8.4.1  :)
>
>
> Florian
>

thanks!
Stephen
-- 
Stephen Thompson               Berkeley Seismological Laboratory
stephen AT seismo.berkeley DOT edu    215 McCone Hall # 4760
404.538.7077 (phone)           University of California, Berkeley
510.643.5811 (fax)             Berkeley, CA 94720-4760

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users