Bruce Momjian | 1 Jul 02:42 2010
Picon

Re: Analysis Function

Tom Lane wrote:
> David Jarvis <thangalin <at> gmail.com> writes:
> >> Fair enough. How about something like make_timestamp? It's at least
> >> shorter and easier than construct :-)
> 
> > Agreed.
> 
> No objection here either.

Added to TODO:

    Add function to allow the creation of timestamps using parameters

    * http://archives.postgresql.org/pgsql-performance/2010-06/msg00232.php

-- 
  Bruce Momjian  <bruce <at> momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Greg Smith | 1 Jul 12:00 2010

Re: PostgreSQL as a local in-memory cache

  On 6/30/2010 2:21 PM, Jignesh Shah wrote:
> If the underlying WAL disk is SSD then it seems I can get 
> synchronous_commit=on to work faster than
> synchronous_commit=off..

The first explanation that pops to mind is that synchronous_commit is 
writing all the time, which doesn't have the same sort of penalty on 
SSD.  Whereas if you turn it off, then there are some idle periods where 
the SSD could be writing usefully, but instead it's buffering for the 
next burst instead.  The importance of that can be magnified on 
operating systems that do their own buffering and tend to lag behind 
writes until they see an fsync call, like is the case on Linux with ext3.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Rajesh Kumar Mallah | 1 Jul 11:41 2010
Picon

how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer

Hi,

this is not really a performance question , sorry if its bit irrelevant
to be posted here. We have a development environment and we want
to optimize the non-database parts of the application. The problem is
that subsequent run of  queries are execute very fast and makes the
performance analysis a trivial problem. We want that the subsequent runs
of query should take similar times as the first run so that we can work
on the optimizing the calling patterns to the database.

regds
Rajesh Kumar Mallah.

Rajesh Kumar Mallah | 1 Jul 11:49 2010
Picon

Re: Extremely high CPU usage when building tables

Hi,

1. Did you also check vmstat output , from sar output the i/o wait is not clear.
2.  i gues you must be populating the database between creating tables and creating
     indexes. creating indexes require sorting of data that may be cpu intensive, loading/populating
     the data may saturate the i/o bandwidth . I think you should check when the max cpu utilisation
     is taking place exactly.

regds
Rajesh Kumar Mallah.

On Sat, Jun 26, 2010 at 3:55 AM, Deborah Fuentes <dfuentes <at> eldocomp.com> wrote:
Hello,

When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server.

We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10.

I've attached the sar data at the time of the run- here's a snip-it below.

Any ideas would be greatly appreciated.

Thanks!
Deb

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

Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to run at any time with only eight CPU cores looks very busy.

root <at> core2 # sar -q 5 500

SunOS core2 5.10 Generic_142900-11 sun4u    06/17/2010

12:01:50 runq-sz %runocc swpq-sz %swpocc
12:01:55     1.8      80     0.0       0
12:02:00     1.0      20     0.0       0
12:02:05     1.0      20     0.0       0
12:02:10     0.0       0     0.0       0
12:02:15     0.0       0     0.0       0
12:02:21     3.3      50     0.0       0
12:02:26     1.0      20     0.0       0
12:02:31     1.0      60     0.0       0
12:02:36     1.0      20     0.0       0
12:02:42    27.0      50     0.0       0
12:02:49    32.8      83     0.0       0
12:02:55    76.0     100     0.0       0
12:03:01    66.1     100     0.0       0
12:03:07    43.8     100     0.0       0
12:03:13    52.0     100     0.0       0
12:03:19    91.2     100     0.0       0
12:03:26    97.8      83     0.0       0
12:03:33    63.7     100     0.0       0
12:03:39    67.4     100     0.0       0
12:03:47    41.5     100     0.0       0
12:03:53    82.0      83     0.0       0
12:03:59    88.7     100     0.0       0
12:04:06    87.7      50     0.0       0
12:04:12    41.3     100     0.0       0
12:04:17    94.3      50     0.0       0
12:04:22     1.0      20     0.0       0
12:04:27     3.3      60     0.0       0
12:04:32     1.0      20     0.0       0
12:04:38     0.0       0     0.0       0


--
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Srikanth Kata | 1 Jul 07:19 2010
Picon

What is the best way to optimize this query


Please tell me What is the best way to optimize this query

select
s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr
as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner
join
account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner
join (select subsno from
getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as
(subsno int,expirydt timestamp without time zone,balcpt double precision))
as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by
d.domname,s.expirydt,a.actname 
-- 
View this message in context: http://old.nabble.com/What-is-the-best-way-to-optimize-this-query-tp29041515p29041515.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Rajesh Kumar Mallah | 1 Jul 14:40 2010
Picon

Re: What is the best way to optimize this query

Dear Sri,

Please post at least  the Explain Analyze output . There is a nice posting guideline
also regarding on how to post query optimization questions.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

On Thu, Jul 1, 2010 at 10:49 AM, Srikanth Kata <srikanth <at> inventum.net> wrote:

Please tell me What is the best way to optimize this query

select
s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr
as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner
join
account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner
join (select subsno from
getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as
(subsno int,expirydt timestamp without time zone,balcpt double precision))
as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by
d.domname,s.expirydt,a.actname
--
View this message in context: http://old.nabble.com/What-is-the-best-way-to-optimize-this-query-tp29041515p29041515.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


--
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Thom Brown | 1 Jul 14:51 2010
Picon

Re: What is the best way to optimize this query

On 1 July 2010 06:19, Srikanth Kata <srikanth <at> inventum.net> wrote:
>
> Please tell me What is the best way to optimize this query
>
> select
> s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr
> as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner
> join
> account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner
> join (select subsno from
> getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as
> (subsno int,expirydt timestamp without time zone,balcpt double precision))
> as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by
> d.domname,s.expirydt,a.actname
> --
> View this message in context: http://old.nabble.com/What-is-the-best-way-to-optimize-this-query-tp29041515p29041515.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>

Might help if the query were a bit more readable too:

select
	s.*,
	a.actid,
	a.phone,
	d.domid,
	d.domname,
	d.domno,
	a.actno,
	a.actname,
	p.descr as svcdescr
from
	vwsubsmin s
inner join
	packages p
	on s.svcno=p.pkgno inner
join
	account a
	on a.actno=s.actno
inner join
	ssgdom d on a.domno=d.domno
inner join
	(select
		subsno
	from
		getexpiringsubs(
			$1,
			cast($2 as integer),
			cast($3 as double precision),
			$4
			) as
			(subsno int,
			expirydt timestamp without time zone,
			balcpt double precision)
	) as e
	on s.subsno=e.subsno
where
	s.status <=15
and
	d.domno=$5
order by
	d.domname,
	s.expirydt,
	a.actname;

And it would also help if the table names, column names and aliases
were more self-documenting.

As Rajesh said, an EXPLAIN ANALYZE output is needed, as we don't yet
know where your indexes are.

Regards

Thom

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Joachim Worringen | 1 Jul 15:14 2010
Picon

optimal ZFS filesystem on JBOD layout

Greetings,

we are running a few databases of currently 200GB (growing) in total for 
data warehousing:
- new data via INSERTs for (up to) millions of rows per day; sometimes 
with UPDATEs
- most data in a single table (=> 10 to 100s of millions of rows)
- queries SELECT subsets of this table via an index
- for effective parallelisation, queries create (potentially large) 
non-temporary tables which are deleted at the end of the query => lots 
of simple INSERTs and SELECTs during queries
- large transactions: they may contain millions of INSERTs/UPDATEs
- running version PostgreSQL 8.4.2

We are moving all this to a larger system - the hardware is available, 
therefore fixed:
- Sun X4600 (16 cores, 64GB)
- external SAS JBOD with 24 2,5" slots:
   - 18x SAS 10k 146GB drives
   - 2x SAS 10k 73GB drives
   - 4x Intel SLC 32GB SATA SSD
- JBOD connected to Adaptec SAS HBA with BBU
- Internal storage via on-board RAID HBA:
   - 2x 73GB SAS 10k for OS (RAID1)
   - 2x Intel SLC 32GB SATA SSD for ZIL (RAID1) (?)
- OS will be Solaris 10 to have ZFS as filesystem (and dtrace)
- 10GigE towards client tier (currently, another X4600 with 32cores and 
64GB)

What would be the optimal storage/ZFS layout for this? I checked 
solarisinternals.com and some PostgreSQL resources and came to the 
following concept - asking for your comments:
- run the JBOD without HW-RAID, but let all redundancy be done by ZFS 
for maximum flexibility
- create separate ZFS pools for tablespaces (data, index, temp) and WAL 
on separate devices (LUNs):
- use the 4 SSDs in the JBOD as Level-2 ARC cache (can I use a single 
cache for all pools?) w/o redundancy
- use the 2 SSDs connected to the on-board HBA as RAID1 for ZFS ZIL

Potential issues that I see:
- the ZFS ZIL will not benefit from a BBU (as it is connected to the 
backplane, driven by the onboard-RAID), and might be too small (32GB for 
~2TB of data with lots of writes)?
- the pools on the JBOD might have the wrong size for the tablespaces - 
like: using the 2 73GB drives as RAID 1 for temp might become too small, 
but adding a 146GB drive might not be a good idea?
- with 20 spindles, does it make sense at all to use dedicated devices 
for the tabelspaces, or will the load be distributed well enough across 
the spindles anyway?

thanks for any comments & suggestions,

   Joachim

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Greg Smith | 1 Jul 16:15 2010

Re: optimal ZFS filesystem on JBOD layout

Joachim Worringen wrote:
> Potential issues that I see:
> - the ZFS ZIL will not benefit from a BBU (as it is connected to the 
> backplane, driven by the onboard-RAID), and might be too small (32GB 
> for ~2TB of data with lots of writes)?

This is a somewhat unpredictable setup.  The conservative approach would 
be to break two disks out of the larger array for the ZIL, running 
through the battery-backed cache, rather than using the SSD drives for 
that.  The best way IMHO to use SSD for PostgreSQL is to put your large 
indexes on it, so that even if the drive does the wrong thing when you 
write and the index gets corrupted you can always rebuild them rather 
than suffer data loss.  Also, index writes really benefit from being on 
something with low seek times, moreso than the ZIL or WAL.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg <at> 2ndQuadrant.com   www.2ndQuadrant.us

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Craig Ringer | 1 Jul 18:37 2010
Picon

Re: how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer

On 01/07/10 17:41, Rajesh Kumar Mallah wrote:
> Hi,
> 
> this is not really a performance question , sorry if its bit irrelevant
> to be posted here. We have a development environment and we want
> to optimize the non-database parts of the application. The problem is
> that subsequent run of  queries are execute very fast and makes the
> performance analysis a trivial problem. We want that the subsequent runs
> of query should take similar times as the first run so that we can work
> on the optimizing the calling patterns to the database.

You can get rid of PostgreSQL's caches in shared_buffers by restarting
the PostgreSQL server. I don't know if there's any more convenient way.
Alternately, just set a really minimal shared_buffers that's just enough
for your connections so there's not much room for cached data.

If you are running a Linux server (as you didn't mention what you're
running on) you can drop the OS disk cache quite easily:

  http://linux-mm.org/Drop_Caches
  http://www.linuxinsight.com/proc_sys_vm_drop_caches.html

AFAIK for most other platforms you have to use a tool that gobbles
memory to force caches out. On Windows, most of those garbage tools that
claim to "free" memory do this - it's about the only time you'd ever
want to use one, since they do such horrid things to performance.

--
Craig Ringer

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Gmane