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 399m
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 sequentia
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 workstat
On January 27, 2011, Robert Schnabel 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 a geneticist who h
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, it'
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
hardware
Mladen Gogala 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 seq_page_cost and random_
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 pgsql-perform
On Thu, Jan 27, 2011 at 2:32 PM, Igor Neyman 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 by default.
> -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 ag
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 sca
On Thu, Jan 27, 2011 at 2:18 PM, Igor Neyman 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;
>> pgsql-performance@postgresql
On Thu, Jan 27, 2011 at 04:12:53PM -0500, Igor Neyman 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;
> > pgsql-p
> -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 ag
On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman 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;
>> pgsql-performance@postgres
> -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 ag
On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
wrote:
> On 1/27/2011 3:37 PM, Scott Marlowe wrote:
>>
>> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
>> wrote:
>>>
>>> There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
>>
>> That's because Oracle has covering indexes.
>>
> I am not
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
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 for
the larger table I
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
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 subscription:
http://www.postgr
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
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 t
> -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
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 shar
On Thursday, January 27, 2011 07:13:17 PM Scott Marlowe wrote:
> On Thu, Jan 27, 2011 at 10:54 AM, Stephen Frost 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
On Thu, Jan 27, 2011 at 10:20 AM, Andy Colson wrote:
> On 1/27/2011 9:09 AM, Michael Kohl wrote:
>>
>> On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson wrote:
>>>
>>> Have you run each of your queries through explain analyze lately?
>>
>> A code review including checking of queries is on our agenda.
On Thu, Jan 27, 2011 at 10:54 AM, Stephen Frost 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 another set of
> S
* 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 1/27/2011 9:09 AM, Michael Kohl wrote:
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson 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,
Mich
Jeff Janes wrote:
> On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian wrote:
> > Robert Haas wrote:
> >> On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian wrote:
>
> >> > ? ? ?
> >> > ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
> >>
> >> This sentence looks to me like it sho
On Thu, Jan 27, 2011 at 8:09 AM, Michael Kohl wrote:
> On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson 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 to start is to log long running queries
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 t
On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian wrote:
> Robert Haas wrote:
>> On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian wrote:
>> > ? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
>>
>> This sentence looks to me like it should be removed, or perhaps clarified:
>>
David Wilson 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, results on a toy-sized t
> -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 writes:
> > Came across a problem I find perplexing. I recreat
On Thu, Jan 27, 2011 at 10:56 AM, Mladen Gogala
wrote:
> 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);
> QUERY PLAN
>
>
>
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)
--
Mla
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 EX
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
Mladen Gogala 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 have an example
of that?
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
> --+-
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| sma
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 p
On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson 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 pgsql-performance mailing
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 se
Waldomiro 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 wondering if you followed s
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
David Greco 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 action would simply be
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.
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
> 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
On Thursday, January 27, 2011 02:23:48 PM Cédric Villemain wrote:
> 2011/1/27 Andres Freund :
> > 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 e
2011/1/27 Andres Freund :
> 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 regul
On Thu, Jan 27, 2011 at 1:30 PM, Justin Pitts 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 this gives
> them
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 lim
Hi,
Yesterday I perform a crash test, but I lost the last pg_xlog file.
Let me explain:
First I did the pg_start_backup
Second I copied the phisical files to the slave server
Third I did the pg_stop_backup
So I run a test,
1) In the master server I created a 100.000 records table
2) I Runned t
2011/1/27 Michael Kohl :
> Cédric, thanks a lot for your answer so far!
>
> On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
> 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
> MemTot
Cédric, thanks a lot for your answer so far!
On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
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: 16461012 kB
MemFree: 280440
2011/1/27 Michael Kohl :
> 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 b
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
59 matches
Mail list logo
