Re: Slow count(*) again...

From: Mladen Gogala <mgogala(at)vmsinfo(dot)com>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, "david(at)lang(dot)hm" <david(at)lang(dot)hm>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow count(*) again...
Date: 2010-10-12 13:55:39
Message-ID: 4CB468DB.8040902@vmsinfo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Jon Nelson wrote:
>
> Well, I didn't quite mean that - having no familiarity with Oracle I
> don't know what the alter system statement does, but I was talking
> specifically about the linux buffer and page cache.
>

Those are not utilized by Oracle. This is a RAC instance, running on
top of ASM, which is an Oracle volume manager, using raw devices. There
is no file system on those disks:

SQL> select file_name from dba_data_files
2 where tablespace_name='ADBASE_DATA';

FILE_NAME
--------------------------------------------------------------------------------
+DGDATA/stag3/datafile/adbase_data.262.727278257
+DGDATA/stag3/datafile/adbase_data.263.727278741
+DGDATA/stag3/datafile/adbase_data.264.727280145
+DGDATA/stag3/datafile/adbase_data.265.727280683

[oracle(at)lpo-oracle-30 ~]$ $ORA_CRS_HOME/bin/crs_stat -l
NAME=ora.STAG3.STAG31.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.STAG3.STAG32.inst
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.STAG3.db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-30.ASM1.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.LISTENER_LPO-ORACLE-30.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-30.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-30

NAME=ora.lpo-oracle-31.ASM2.asm
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.LISTENER_LPO-ORACLE-31.lsnr
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

NAME=ora.lpo-oracle-31.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE on lpo-oracle-31

The only way to flush cache is the aforementioned "alter system"
command. AFAIK, Postgres doesn't have anything like that. Oracle uses
raw devices precisely to avoid double buffering.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-10-12 13:56:30 Re: Slow count(*) again...
Previous Message Devrim GÜNDÜZ 2010-10-12 13:50:14 Re: pgxs docdir question

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-10-12 13:56:30 Re: Slow count(*) again...
Previous Message Jon Nelson 2010-10-12 13:20:14 Re: Slow count(*) again...