Hi all,
we are running a fairly big Ruby on Rails application on Postgres 8.4.
Our traffic grew quite a bit lately, and since then we are facing DB
performance issues. System load occasionally explodes (around 170
yesterday on a 16 core system), which seems to be caused by disk I/O
(iowait in our
2011/1/27 Michael Kohl michael.k...@tupalo.com:
Hi all,
we are running a fairly big Ruby on Rails application on Postgres 8.4.
Our traffic grew quite a bit lately, and since then we are facing DB
performance issues. System load occasionally explodes (around 170
yesterday on a 16 core
Cédric, thanks a lot for your answer so far!
On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
you have swap used, IO on the swap partition ?
Memory-wise we are fine.
can you paste the /proc/meminfo ?
Sure:
# cat /proc/meminfo
MemTotal:
2011/1/27 Michael Kohl michael.k...@tupalo.com:
Cédric, thanks a lot for your answer so far!
On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
you have swap used, IO on the swap partition ?
Memory-wise we are fine.
can you paste the /proc/meminfo
On Thursday, January 27, 2011 12:24:10 PM Cédric Villemain wrote:
maintenance_work_mem = 512MB
128MB is usualy enough
Uhm, I don't want to be picky, but thats not really my experience. Sorts for
index creation are highly dependent on a high m_w_m. Quite regularly I find the
existing 1GB limit
On Thu, Jan 27, 2011 at 1:30 PM, Justin Pitts justinpi...@gmail.com wrote:
That is a foot-gun waiting to go off.
Thanks, I had already changed this after Cedric's mail.
HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1
random_page_cost = 2.0
I thought these drives were a lot better at random IO than
2011/1/27 Andres Freund and...@anarazel.de:
On Thursday, January 27, 2011 12:24:10 PM Cédric Villemain wrote:
maintenance_work_mem = 512MB
128MB is usualy enough
Uhm, I don't want to be picky, but thats not really my experience. Sorts for
index creation are highly dependent on a high m_w_m.
On Thursday, January 27, 2011 02:23:48 PM Cédric Villemain wrote:
2011/1/27 Andres Freund and...@anarazel.de:
On Thursday, January 27, 2011 12:24:10 PM Cédric Villemain wrote:
maintenance_work_mem = 512MB
128MB is usualy enough
Uhm, I don't want to be picky, but thats not really my
Number of logical CPUs: 16 (4x Quadcore Xeon E5520 @ 2.27GHz)
RAM: 16GB
Concurrent connections (according to our monitoring tool): 7 (min), 74
(avg), 197 (max)
Your current issue may be IO wait, but a connection pool isn't far off
in your future either.
max_connections = 200
work_mem =
Right you are. Kettle is turning the number(11) field from Oracle into a
BigNumber, which is a decimal. If I cast the field into an Integer in Kettle
and keep the field an integer in Postgres, I get good performance. Suspect the
correct course of action would simply be to make number(11) fields
On 01/27/2011 06:10 AM, Waldomiro wrote:
3) I check the pg_xlog, there was created 6 logs, but in the archive
there was only 5.
xlogs are only archived when they'd normally be deleted. If you have
really high data turnover or very frequent checkpoints, that effectively
happens constantly.
David Greco david_gr...@harte-hanks.com writes:
Right you are. Kettle is turning the number(11) field from Oracle into
a BigNumber, which is a decimal. If I cast the field into an Integer
in Kettle and keep the field an integer in Postgres, I get good
performance. Suspect the correct course of
On 01/27/2011 08:18 AM, Tom Lane wrote:
Not if you can persuade the client-side code to output integers as
integers. numeric type is orders of magnitude slower than integers.
I sadly have to vouch for this. My company converted an old Oracle app
and they changed all their primary keys (and
Waldomiro waldom...@shx.com.br wrote:
Yesterday I perform a crash test, but I lost the last pg_xlog
file.
Did you follow the steps laid out in the documentation?:
http://www.postgresql.org/docs/current/interactive/continuous-archiving.html#BACKUP-PITR-RECOVERY
In particular, I'm
On 1/27/2011 4:31 AM, Michael Kohl wrote:
Hi all,
we are running a fairly big Ruby on Rails application on Postgres 8.4.
Our traffic grew quite a bit lately, and since then we are facing DB
performance issues. System load occasionally explodes (around 170
yesterday on a 16 core system), which
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson a...@squeakycode.net wrote:
Have you run each of your queries through explain analyze lately?
A code review including checking of queries is on our agenda.
You are vacuuming/autovacuuming, correct?
Sure :-)
Thank you,
Michael
--
Sent via
On 1/27/2011 9:30 AM, Shaun Thomas wrote:
I'm not sure about orders of magnitude on the storage/index side, but my
tests gave us a 10% boost if just the keys are switched over to INT or
BIGINT.
Well, it depends on what you're doing. Searching by an integer vs.
searching by a text string will
I have a table EMP, with 14 rows and a description like this:
scott= \d+ emp
Table public.emp
Column |Type | Modifiers | Storage |
Description
--+-+---+--+-
empno|
On Thu, Jan 27, 2011 at 10:41:08AM -0500, Mladen Gogala wrote:
I have a table EMP, with 14 rows and a description like this:
scott= \d+ emp
Table public.emp
Column |Type | Modifiers | Storage |
Description
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
The optimizer will not use index, not even when I turn off both
hash and merge joins. This is not particularly important for a
table with 14 rows, but for a larger table, this is a problem.
If it still does that with a larger table. Do you
Odds are that a table of 14 rows will more likely be cached in RAM
than a table of 14 million rows. PostgreSQL would certainly be more
openminded to using an index if chances are low that the table is
cached. If the table *is* cached, though, what point would there be
in reading an index?
Also,
On 1/27/2011 10:45 AM, Kenneth Marshall wrote:
PostgreSQL will only use an index if the planner thinks that it
will be faster than the alternative, a sequential scan in this case.
For 14 rows, a sequential scan is 1 read and should actually be
faster than the index. Did you try the query using
On 1/27/2011 10:51 AM, J Sisson wrote:
Also, if random_page_cost is set to default (4.0), the planner will
tend towards sequential scans.
scott= show random_page_cost;
random_page_cost
--
1
(1 row)
scott= show seq_page_cost;
seq_page_cost
---
2
(1 row)
--
On Thu, Jan 27, 2011 at 10:56 AM, Mladen Gogala
mladen.gog...@vmsinfo.comwrote:
I even tried with an almost equivalent outer join:
explain analyze select e1.empno,e1.ename,e2.empno,e2.ename
from emp e1 left outer join emp e2 on (e1.mgr=e2.empno);
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wednesday, January 26, 2011 5:12 PM
To: David Greco
Cc: pgsql-performance@postgresql.org
Subject: Re: Real vs Int performance
David Greco david_gr...@harte-hanks.com writes:
Came across a problem I find
David Wilson david.t.wil...@gmail.com writes:
You're still using a 14 row table, though.
Exactly. Please note what it says in the fine manual:
It is worth noting that EXPLAIN results should not be extrapolated
to situations other than the one you are actually testing; for
example,
On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian br...@momjian.us wrote:
? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
This sentence looks to me like it should be removed,
On 1/27/2011 11:40 AM, Tom Lane wrote:
It is worth noting that EXPLAIN results should not be extrapolated
to situations other than the one you are actually testing; for
example, results on a toy-sized table cannot be assumed to apply to
large tables.
Well, that's precisely what I
On Thu, Jan 27, 2011 at 8:09 AM, Michael Kohl michael.k...@tupalo.com wrote:
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson a...@squeakycode.net wrote:
Have you run each of your queries through explain analyze lately?
A code review including checking of queries is on our agenda.
A good method
Jeff Janes wrote:
On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian br...@momjian.us wrote:
? ? ?
?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
This sentence looks to me
On 1/27/2011 9:09 AM, Michael Kohl wrote:
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colsona...@squeakycode.net wrote:
Have you run each of your queries through explain analyze lately?
A code review including checking of queries is on our agenda.
You are vacuuming/autovacuuming, correct?
Sure
* Michael Kohl (michael.k...@tupalo.com) wrote:
HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1
I'm amazed no one else has mentioned this yet, but you should look into
splitting your data and your WALs. Obviously, having another set of
SSDs to put your WALs on would be ideal.
You should probably also
On Thu, Jan 27, 2011 at 10:54 AM, Stephen Frost sfr...@snowman.net wrote:
* Michael Kohl (michael.k...@tupalo.com) wrote:
HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1
I'm amazed no one else has mentioned this yet, but you should look into
splitting your data and your WALs. Obviously, having
On Thursday, January 27, 2011 07:13:17 PM Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 10:54 AM, Stephen Frost sfr...@snowman.net wrote:
* Michael Kohl (michael.k...@tupalo.com) wrote:
HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1
I'm amazed no one else has mentioned this yet, but you should
Pg 9.0.2 is performing better than pg8.4.1
There are more transactions per second in pg9.0.2 than in pg8.4.1, which is
a better thing.
also below are kernel parameters that i used.
-- Shared Memory Limits
max number of segments = 4096
max seg size (kbytes) = 15099492
max total
-Original Message-
From: Mladen Gogala [mailto:mladen.gog...@vmsinfo.com]
Sent: Thursday, January 27, 2011 12:00 PM
To: Tom Lane
Cc: David Wilson; Kenneth Marshall; pgsql-performance@postgresql.org
Subject: Re: Postgres 9.0 has a bias against indexes
On 1/27/2011 11:40 AM, Tom
Another advice is to look the presentation of Alexander Dymo, on the
RailsConf2009 called: Advanced Performance Optimization of Rails Applications
available on
http://en.oreilly.com/rails2009/public/schedule/detail/8615
This talk are focused on Rails and PostgreSQL, based on the development of
On 1/27/2011 3:10 PM, Igor Neyman wrote:
Mladen,
I don't think, this is exclusive Postgres feature.
I'm pretty sure, Oracle optimizer will do TABLE ACCESS (FULL) instead
of using index on 14-row table either.
Regards,
Igor Neyman
Well, lets' see:
SQL select * from v$version;
BANNER
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle has covering indexes.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle has covering indexes.
I am not sure what you mean by covering indexes but I hope that
On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 3:59 PM
To: Mladen Gogala
Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres 9.0 has a bias against
On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman iney...@perceptron.com wrote:
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 3:59 PM
To: Mladen Gogala
Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 4:16 PM
To: Igor Neyman
Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres 9.0 has a bias against
On Thu, Jan 27, 2011 at 2:18 PM, Igor Neyman iney...@perceptron.com wrote:
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 4:16 PM
To: Igor Neyman
Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall;
On 1/27/2011 4:20 PM, Kenneth Marshall wrote:
Interesting. Can you force it to use a Seqential Scan and if so, how
does that affect the timing? i.e. Is the index scan actually faster?
Cheers,
Ken
Yes, Oracle can be forced into doing a sequential scan and it is
actually faster than an index
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 4:25 PM
To: Igor Neyman
Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgres 9.0 has a bias against
On Thu, Jan 27, 2011 at 2:32 PM, Igor Neyman iney...@perceptron.com wrote:
On Oracle? Then how can it get the values it needs without
having to hit the data store?
It doesn't.
It does INDEX UNIQUE SCAN and then TABLE ACCESS BY INDEX ROWID.
Ahhh, ok. I thought Oracle used covering indexes
On 1/27/2011 4:25 PM, Scott Marlowe wrote:
On Oracle? Then how can it get the values it needs without having to
hit the data store?
It can't. It does hit the data store.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
--
Sent via
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
Yes, Oracle can be forced into doing a sequential scan and it is
actually faster than an index scan:
And PostgreSQL can be coerced to use an indexed scan. Its plans are
cost-based, with user configurable cost factors; so if you tell it
that
HI,
I use PostgreSQL basically as a data warehouse to store all the genetic
data that our lab generates. The only person that accesses the database
is myself and therefore I've had it housed on my workstation in my
office up till now. However, it's getting time to move it to bigger
On Thu, 27 Jan 2011, Robert Schnabel wrote:
HI,
I use PostgreSQL basically as a data warehouse to store all the genetic data
that our lab generates. The only person that accesses the database is myself
and therefore I've had it housed on my workstation in my office up till now.
However,
On January 27, 2011, Robert Schnabel schnab...@missouri.edu wrote:
So my questions are 1) am I'm crazy for doing this, 2) would you change
anything and 3) is it acceptable to put the xlog wal (and perhaps tmp
filespace) on a different controller than everything else? Please keep
in mind I'm
On 1/27/2011 5:19 PM, da...@lang.hm wrote:
On Thu, 27 Jan 2011, Robert Schnabel wrote:
HI,
I use PostgreSQL basically as a data warehouse to store all the genetic data
that our lab generates. The only person that accesses the database is myself
and therefore I've had it housed on my
sorry for not replying properly to your response, I managed to delete the
mail.
as I understand your data access pattern it's the following:
for the main table space:
bulk loads every couple of weeks. if the data is lost you can just reload
it.
searches tend to be extracting large
Scott,
Thanks for your response.
We are over NFS for our storage ...
Here is what we see during our performance testing:
This is about 7 seconds after the query was sent to postgres:
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
7090 root 25 0 689m
56 matches
Mail list logo
