On Fri, Jun 4, 2010 at 12:40 AM, Yogesh Naik
wrote:
> I am performing a DB insertion and update for 3000+ records and while doing
> so i get CPU utilization
> to 100% with 67% of CPU used by postgres
That sounds normal to me. What would you expect to happen?
--
Robert Haas
EnterpriseDB: htt
Is this a bulk insert? Are you wrapping your statements within a
transaction(s)?
How many columns in the table? What do the table statistics look like?
On Fri, Jun 4, 2010 at 9:21 AM, Michael Gould <
mgo...@intermodalsoftwaresolutions.net> wrote:
> In my opinion it depends on the application,
In my opinion it depends on the application, the priority of the application
and whether or not it is a commercially sold product, but depending on your
needs you might want to consider having a 3rd party vendor who has expertise
in this process review and help tune the application. One vendor tha
Yogesh Naik wrote:
> I am performing a DB insertion and update for 3000+ records and
> while doing so i get CPU utilization to 100% with 67% of CPU used
> by postgres
>
> I have also done optimization on queries too...
>
> Is there any way to optimized the CPU utilization for postgres
Hi
I am performing a DB insertion and update for 3000+ records and while doing so
i get CPU utilization
to 100% with 67% of CPU used by postgres
I have also done optimization on queries too...
Is there any way to optimized the CPU utilization for postgres
I am currently using postgres
On 26/03/10 03:56, Eliot Gable wrote:
I really am chasing milliseconds here, and I appreciate all your feedback.
You've given me a relatively large number of possible optimizations I can
try out. I will definitely try out the libpqtypes. That sounds like a
promising way to further cut down on ex
On Thu, Mar 25, 2010 at 11:56 PM, Eliot Gable
wrote:
>>
>> How many rows min/max/avg are coming back in your refcursors? Are you
>> using cursors in order to return multiple complex data structures
>> (sets, etc) in a single function call?
>>
>
> I think the largest number of rows is around 40. M
On Thu, Mar 25, 2010 at 10:00 PM, Merlin Moncure wrote:
> On Tue, Mar 23, 2010 at 5:00 PM, Eliot Gable
> >
> wrote:
> > The complex type contains roughly 25 fields, mostly text, plus another 10
> > REFCURSORs.
>
> How many rows min/max/avg are coming back in your refcursors? Are you
> using curs
On Tue, Mar 23, 2010 at 5:00 PM, Eliot Gable
wrote:
> The complex type contains roughly 25 fields, mostly text, plus another 10
> REFCURSORs.
How many rows min/max/avg are coming back in your refcursors? Are you
using cursors in order to return multiple complex data structures
(sets, etc) in a s
I would greatly appreciate any advice anyone could give me in terms of
performance tuning a large PL/PGSQL stored procedure. First, I should point
out that I have read a considerable amount of information in the online
PostgreSQL documentation and on Google about optimizing SQL queries and
PostgreS
Kevin Kempter schrieb:
Hi All;
I'm looking for tips / ideas per performance tuning some specific queries.
These are generally large tables on a highly active OLTP system
(100,000 - 200,000 plus queries per day)
First off, any thoughts per tuning inserts into large tables. I have a large
tab
am Wed, dem 26.11.2008, um 21:21:04 -0700 mailte Kevin Kempter folgendes:
> Next we have a select count(*) that also one of the top offenders:
>
> select count(*) from public.tab3 where user_id=31
> and state='A'
> and amount>0;
>
> QUERY PLAN
First off, any thoughts per tuning inserts into large tables. I have a
large
table with an insert like this:
insert into public.bigtab1 (text_col1, text_col2, id) values ...
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0)
(1 ro
Hi All;
I'm looking for tips / ideas per performance tuning some specific queries.
These are generally large tables on a highly active OLTP system
(100,000 - 200,000 plus queries per day)
First off, any thoughts per tuning inserts into large tables. I have a large
table with an insert like thi
On Tue, 4 Mar 2008, Ivan Voras wrote:
I'm curious about the math behind this - is ~4000 burst or sustained
rate?
Average, which is not quite burst or sustained. No math behind it, just
looking at a few samples of pgbench data on similar hardware. A system
like this one is profiled at
http
On Tue, 4 Mar 2008, Ivan Voras wrote:
I'm curious about the math behind this - is ~4000 burst or sustained
rate? For common BBU cache sizes (256M, 512M), filling that amount with
data is pretty trivial. When the cache is full, new data can enter the
cache only at a rate at which old data is evacu
Greg Smith wrote:
> On Mon, 3 Mar 2008, alan bryan wrote:
>
>>> pgbench -c 100 -t 1000 testdb
>> tps = 558.013714 (excluding connections establishing)
>>
>> Just for testing, I tried turning off fsync and got:
>> tps = 4061.662041 (excluding connections establishing)
>
> This is odd. ~500 is wha
alan bryan wrote:
> File './Bonnie.2551', size: 104857600
> Writing with putc()...done
> Rewriting...done
> Writing intelligently...done
> Reading with getc()...done
> Reading intelligently...done
> Seeker 1...Seeker 2...Seeker 3...start 'em...done...done...done...
> ---Sequential
On Tue, 4 Mar 2008, alan bryan wrote:
There seems to be something really wrong with disk performance. Here's
the results from bonnie
So input speed is reasonable but write throughput is miserable--<10MB/s.
I'd suggest taking this to one of the FreeBSD lists; this doesn't look
like a Postgre
On Mon, Mar 3, 2008 at 5:11 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Mon, 3 Mar 2008, alan bryan wrote:
>
> >> pgbench -c 100 -t 1000 testdb
>
> > tps = 558.013714 (excluding connections establishing)
> >
> > Just for testing, I tried turning off fsync and got:
>
> > tps = 4061.662041 (exc
On Mon, 3 Mar 2008, alan bryan wrote:
pgbench -c 100 -t 1000 testdb
tps = 558.013714 (excluding connections establishing)
Just for testing, I tried turning off fsync and got:
tps = 4061.662041 (excluding connections establishing)
This is odd. ~500 is what I expect from this test when there
On Mon, Mar 3, 2008 at 4:26 PM, Bill Moran
<[EMAIL PROTECTED]> wrote:
> > > cat /boot/loader.conf
> > kern.ipc.semmni=256
> > kern.ipc.semmns=512
> > kern.ipc.semmnu=256
> >
> > > cat /etc/sysctl.conf
> > kern.ipc.shmall=393216
> > kern.ipc.shmmax=1610612736
>
> I would just set this to 2
"alan bryan" <[EMAIL PROTECTED]> wrote:
>
> I've got a new server and am myself new to tuning postgres.
>
> Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/
> BBU.
>
> It's serving as the DB for a fairly write intensive (maybe 25-30%) Web
> application in PHP. We are
I've got a new server and am myself new to tuning postgres.
Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU.
It's serving as the DB for a fairly write intensive (maybe 25-30%) Web
application in PHP. We are not using persistent connections, thus the
high max conne
On Sun, Mar 18, 2007 at 06:45:34AM -0600, Barry Moore wrote:
Does anyone know how I can repeatedly run the same query in the
"worst case scenario" of no postgres data in the disk cache (e.g.,
clear the page cache or force it to be ignored)?
Depends on your OS. On linux you can run:
echo 1 >
If you are running on a Linux kernel, try /proc/sys/vm/drop_caches. I
believe the appropriate command is "echo 3 > /proc/sys/vm/drop_caches".
Since Postgres has its own cache of data, the above followed by a PG
restart should do what you are looking for.
Ranga
> Barry Moore wrote:
>
>> I have a
Barry Moore wrote:
I have a very slow query that I'm trying to tune. I think my
performance tuning is being complicated by the system's page cache.
If a run the query after the system has been busy with other tasks
for quite a long time then the query can take up to 8-10 minutes to
compl
On 3/18/07, Barry Moore <[EMAIL PROTECTED]> wrote:
Does anyone know how I can repeatedly run the same query in the
"worst case scenario" of no postgres data in the disk cache (e.g.,
clear the page cache or force it to be ignored)?
try to disconnect from postgresql, reconnect, rerun the query.
i
I have a very slow query that I'm trying to tune. I think my
performance tuning is being complicated by the system's page cache.
If a run the query after the system has been busy with other tasks
for quite a long time then the query can take up to 8-10 minutes to
complete. If I then rerun
--Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Frank Wiles
Sent: Wednesday, June 22, 2005 10:52 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Performance Tuning Article
Hi Everyone,
I've put together a short article and posted it o
Dave Cramer <[EMAIL PROTECTED]> writes:
> AFAIK, the problem was the buffer manager
The buffer manager was the place that seemed to be hit hardest by Xeon's
problems with spinlock contention. I think we've partially fixed that
issue in 8.1, but as we continue to improve the system's performance,
AFAIK, the problem was the buffer manager
Dave
On 23-Jun-05, at 9:46 AM, Radu-Adrian Popescu wrote:
Dave Cramer wrote:
My understanding is that it isn't particularly XEON processors
that is the problem
Any dual processor will exhibit the problem, XEON's with
hyperthreading exacerbate the
On Wed, Jun 22, 2005 at 10:31:29PM -0400, Keith Worthington wrote:
Use RAID 10 (striping across mirrored disks)
or RAID 0+1 (mirror a striped array) for your data.
yikes! never tell an unsuspecting person to use mirred stripes--that
configuration has lower reliability and performance than stri
Dave Cramer wrote:
My understanding is that it isn't particularly XEON processors that is
the problem
Any dual processor will exhibit the problem, XEON's with hyperthreading
exacerbate the problem though
and the good news is that it has been fixed in 8.1
Where's that ? The only informat
On Wed, 22 Jun 2005 22:31:29 -0400
Keith Worthington <[EMAIL PROTECTED]> wrote:
> Frank,
>
> A couple of things I wish I had been told when I started asking how to
>
> configure a new machine.
>
> Use RAID 10 (striping across mirrored disks)
> or RAID 0+1 (mirror a striped array) for your dat
My understanding is that it isn't particularly XEON processors that
is the problem
Any dual processor will exhibit the problem, XEON's with
hyperthreading exacerbate the problem though
and the good news is that it has been fixed in 8.1
Dave
On 23-Jun-05, at 8:16 AM, Keith Worthington wrote
Radu-Adrian Popescu wrote:
There have been problems with Xeon processors.
Can you elaborate on that please ?
Thanks,
Not really as I do not understand the issue.
Here is one post from the archives.
http://archives.postgresql.org/pgsql-performance/2005-05/msg00441.php
If you search the ar
There have been problems with Xeon processors.
Can you elaborate on that please ?
Thanks,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
smime.p7s
Description: S/MIME Cryptographic Signature
I've put together a short article and posted it online regarding
performance tuning PostgreSQL in general. I believe it helps to
bring together the info in a easy to digest manner. I would
appreciate any feedback, comments, and especially any technical
corrections.
Looks nice. You shoul
[Frank Wiles - Wed at 09:52:27AM -0500]
> I've put together a short article and posted it online regarding
> performance tuning PostgreSQL in general. I believe it helps to bring
> together the info in a easy to digest manner. I would appreciate any
> feedback, comments, and especially any
On Wed, 2005-06-22 at 09:52 -0500, Frank Wiles wrote:
> I've put together a short article and posted it online regarding
> performance tuning PostgreSQL in general.
Nice work! Some minor issues I saw:
* section "Understanding the process", para 5:
"Now that PostgreSQL has a plan of what it b
On Wed, 22 Jun 2005 10:16:03 -0700
Josh Berkus wrote:
> Frank,
>
> > I've put together a short article and posted it online regarding
> > performance tuning PostgreSQL in general. I believe it helps to
> > bring together the info in a easy to digest manner. I would
> > appreciate any fe
Frank,
> I've put together a short article and posted it online regarding
> performance tuning PostgreSQL in general. I believe it helps to bring
> together the info in a easy to digest manner. I would appreciate any
> feedback, comments, and especially any technical corrections.
Looks n
Hi Everyone,
I've put together a short article and posted it online regarding
performance tuning PostgreSQL in general. I believe it helps to bring
together the info in a easy to digest manner. I would appreciate any
feedback, comments, and especially any technical corrections.
T
Jacques Caron wrote:
I'm preparing a set of servers which will eventually need to handle a
high volume of queries (both reads and writes, but most reads are very
simple index-based queries returning a limited set of rows, when not
just one), and I would like to optimize things as much as possibl
Hi all,
I'm preparing a set of servers which will eventually need to handle a high
volume of queries (both reads and writes, but most reads are very simple
index-based queries returning a limited set of rows, when not just one),
and I would like to optimize things as much as possible, so I have
On Wed, 2005-02-09 at 15:01 -0500, Chris Kratz wrote:
> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is because
> we have postgres configured incorrectly in some way, or if we really need
The world rejoiced as [EMAIL PROTECTED] (PFC) wrote:
>> As a side note, I learned something very interesting for our
>> developers here.
>> We had been doing a drop database and then a reload off a db dump
>> from our
>> live server for test data. This takes 8-15 minutes depending on the
>> serv
2. Moving to more materialized views and prepared statements where we
can.
Definitely worth investigating. I wish I could, but I can't get my
customers to even consider slightly out of date stats :(
Put a button 'Stats updated every hour', which gives the results in 0.1
seconds, and a but
On Wed, 9 Feb 2005 17:30:41 -0500, Chris Kratz
<[EMAIL PROTECTED]> wrote:
> The solutions appear to primarily be:
> 1. Going to faster hardware of which probably Opterons would be about the only
> choice. And even that probably won't be a huge difference.
I'd beg to differ on that last part. The
As a side note, I learned something very interesting for our developers
here.
We had been doing a drop database and then a reload off a db dump from
our
live server for test data. This takes 8-15 minutes depending on the
server
(the one above takes about 8 minutes). I learned through testi
On Wednesday 09 February 2005 05:08 pm, Merlin Moncure wrote:
> > Hello All,
> >
> > In contrast to what we hear from most others on this list, we find our
> > database servers are mostly CPU bound. We are wondering if this is
> > because
> > we have postgres configured incorrectly in some way, or
On Wednesday 09 February 2005 03:27 pm, you wrote:
---snip---
> > We continue to tune our individual queries where we can, but it seems we
> > still are waiting on the db a lot in our app. When we run most queries,
> > top shows the postmaster running at 90%+ constantly during the duration
> > of
On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote:
> Chris Kratz <[EMAIL PROTECTED]> writes:
> > We continue to tune our individual queries where we can, but it seems we
> > still are waiting on the db a lot in our app. When we run most queries,
> > top shows the postmaster running at 90%+
> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is
> because
> we have postgres configured incorrectly in some way, or if we really
need
> more powerfull processor(s) to gain more performanc
On Wednesday 09 February 2005 03:38 pm, John Arbash Meinel wrote:
>...
> I'm very surprised you are doing RAID 0. You realize that if 1 drive
> goes out, your entire array is toast, right? I would recommend doing
> either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes.
Yeah, we kn
Chris Kratz <[EMAIL PROTECTED]> writes:
> We continue to tune our individual queries where we can, but it seems we
> still
> are waiting on the db a lot in our app. When we run most queries, top shows
> the postmaster running at 90%+ constantly during the duration of the request.
>
> The d
Chris Kratz wrote:
Hello All,
In contrast to what we hear from most others on this list, we find our
database servers are mostly CPU bound. We are wondering if this is because
we have postgres configured incorrectly in some way, or if we really need
more powerfull processor(s) to gain more perform
Hello All,
In contrast to what we hear from most others on this list, we find our
database servers are mostly CPU bound. We are wondering if this is because
we have postgres configured incorrectly in some way, or if we really need
more powerfull processor(s) to gain more performance from postg
59 matches
Mail list logo
