On Fri, 4 Feb 2011, Chris Browne wrote:
2. The query needs to NOT be I/O-bound. If it's I/O bound, then your
system is waiting for the data to come off disk, rather than to do
processing of that data.
yes and no on this one.
it is very possible to have a situation where the process generati
On Sat, Feb 5, 2011 at 12:46 AM, wrote:
>> Actually for me the main "con" with streaming analyze is that it adds
>> significant CPU burden to already not too fast load process. Especially if
>> it's automatically done for any load operation performed (and I can't see
>> how it can be enabled on s
On Fri, 4 Feb 2011, Vitalii Tymchyshyn wrote:
04.02.11 16:33, Kenneth Marshall ???(??):
In addition, the streaming ANALYZE can provide better statistics at
any time during the load and it will be complete immediately. As far
as passing the entire table through the ANALYZE process, a simple
O
If Oracle can patch their query planner for you in 24 hours, and you
can apply patch with confidence against your test then production
servers in an hour or so, great. Til then I'll stick to a database
that has the absolutely, without a doubt, best coder support of any
project I've ever used.
Tobias Brox wrote:
I implemented table partitioning, and it caused havoc with a "select
max(id)" on the parent table - the query plan has changed from a
lightningly fast backwards index scan to a deadly seq scan.
This problem was fixed in the upcoming 9.1:
http://archives.postgresql.org/pgs
2011/2/4 Mark Kirkwood :
> Given that there are no hints, what do I do to solve the problem of a slow
> query suddenly popping up in production? If and when this situation occurs,
> see how quickly the community steps in to help you solve it (and it'd bet it
> will solved be very quickly indeed).
On Fri, Feb 4, 2011 at 5:17 PM, Bruce Momjian wrote:
> Mladen Gogala wrote:
>> characteristic of a religious community chastising a sinner. Let me
>> remind you again: all other major databases have that possibility:
>> Oracle, MySQL, DB2, SQL Server and Informix. Requiring burden of proof
>> abou
This is a known limitation of partitioning. One solution is to use a
recursive stored proc, which can use indexes. Such a solution is
discussed here:
http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php
Regards,
Ken
http://archives.postgresql.org/pgsql-performance/2009-09/msg00
On 04/02/2011 15:44, Greg Smith wrote:
Ivan Voras wrote:
The "vanilla" plan, with default settings is:
Pause here for a second: why default settings? A default PostgreSQL
configuration is suitable for systems with about 128MB of RAM. Since you
say you have "good enough hardware", I'm assuming
On 02/04/2011 10:41 AM, Tom Lane wrote:
1. Autovacuum fires when the stats collector's insert/update/delete
counts have reached appropriate thresholds. Those counts are
accumulated from messages sent by backends at transaction commit or
rollback, so they take no account of what's been done by tr
gnuo...@rcn.com writes:
> Time for my pet meme to wiggle out of its hole (next to Phil's, and a
> day later). For PG to prosper in the future, it has to embrace the
> multi-core/processor/SSD machine at the query level. It has to. And
> it has to because the Big Boys already do so, to some exten
Mladen Gogala wrote:
> Actually, it is not unlike a religious dogma, only stating that "hints
> are bad". It even says so in the wiki. The arguments are
> 1) Refusal to implement hints is motivated by distrust toward users,
> citing that some people may mess things up.
> Yes, they can, with a
On 05/02/11 03:36, Mladen Gogala wrote:
Shaun, I don't need to convince you or the Postgres community. I
needed an argument to convince my boss.
My argument was that the sanctimonious and narrow minded Postgres
community is unwilling to even consider creating the tools I need for
large porting
I implemented table partitioning, and it caused havoc with a "select
max(id)" on the parent table - the query plan has changed from a
lightningly fast backwards index scan to a deadly seq scan. Both
partitions are set up with primary key index and draws new IDs from
the same sequence ... "select m
Mladen Gogala wrote:
> Chris Browne wrote:
> > Well, the community declines to add hints until there is actual
> > consensus on a good way to add hints.
> >
> OK. That's another matter entirely. Who should make that decision? Is
> there a committee or a person who would be capable of making t
Greg Smith wrote:
> Check out
> http://www.indeed.com/jobtrends?q=postgres%2C+mysql%2C+oracle&relative=1&relative=1
>
> if you want to see the real story here. Oracle has a large installed
> base, but it's considered a troublesome legacy product being replaced
+1 for Oracle being a "troubles
On Fri, Feb 4, 2011 at 2:18 PM, Mark Stosberg wrote:
> It looks like it's going to be trivial-- Divide up the data with a
> modulo, and run multiple parallel cron scripts that each processes a
> slice of the data. A benchmark showed that this approach sped up our
> processing 3x when splitting the
Greg (Smith),
Given your analysis of fsync'ing behavior on Ext3, would you say that it
is better to set checkpoint_completion_target to 0.0 on Ext3?
--
-- Josh Berkus
PostgreSQL Experts Inc.
On 02/03/2011 10:57 AM, gnuo...@rcn.com wrote:
> For PG to prosper in the future, it has to embrace the
> multi-core/processor/SSD machine at the query level
As the person who brought up the original concern, I'll add that
"multi-core at the query level" really isn't important for us. Most of
our
On 02/04/2011 02:14 PM, felix wrote:
oh and there in the footnotes to django they say "dont' forget to run
the delete expired sessions management every once in a while".
thanks guys.
Oh Django... :)
it won't run now because its too big, I can delete them from psql though
You might be bette
it probably has good reason to hate me.
ns=> SELECT n.nspname AS schema_name, c.relname AS table_name,
ns-> c.reltuples AS row_count,
ns-> c.relpages*8/1024 AS mb_used,
ns-> pg_total_relation_size(c.oid)/1024/1024 AS total_mb_used
ns-> FROM pg_class c
ns-> JOIN pg_namespace n
On 02/04/2011 01:59 PM, felix wrote:
still no advice on the pages
I think it just hates you.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com
__
See http://www.peak6.com/email_dis
ah right, duh.
yes, I did it as -U postgres, verified as a superuser
just now did it from inside psql as postgres
\c djns4
vacuum verbose analyze;
still no advice on the pages
On Fri, Feb 4, 2011 at 8:34 PM, Scott Marlowe wrote:
> On Fri, Feb 4, 2011 at 12:26 PM, felix wrote:
> > I just sud
On 02/04/2011 01:26 PM, felix wrote:
because I asked it to: -W on the production server I need to enter
password and I'm testing on dev first.
Right. I'm just surprised it threw up the prompt so many times.
I just sudo tried it but still no report
Nono... you have to run the vacuum command
On Fri, Feb 4, 2011 at 12:26 PM, felix wrote:
> I just sudo tried it but still no report
It's not about who you are in Unix / Linux, it's about who you are in
Postgresql. \du will show you who is a superusr. psql -U username
will let you connect as that user.
--
Sent via pgsql-performance mai
On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas wrote:
> Why is it asking for the password over and over again? It shouldn't be
> doing that.
>
because I asked it to: -W
on the production server I need to enter password and I'm testing on dev
first.
I just sudo tried it but still no report
and
On 02/04/2011 01:01 PM, Scott Marlowe wrote:
begin;
select * into temporaryholdingtable order by somefield;
truncate oldtable;
insert into oldtables select * from temporaryholdingtable;
commit;
That's usually how I do it, except for larger tables, I also throw in a
DROP INDEX for all the inde
All,
Seeing an issue which is new on me. On a mostly idle PostgreSQL server,
the stats collector is rewriting the entire stats file twice per second.
Version: 8.4.4
Server: Ubuntu, kernel 2.6.32
Server set up: ApacheMQ server. 25 databases, each of which hold 2-3
tables.
Filesystem: Ext4, defau
On Fri, Feb 4, 2011 at 11:38 AM, Greg Smith wrote:
> You don't turn it on; it's a one time operation that does a cleanup. It is
> by far the easiest way to clean up the mess you have right now. Moving
> forward, if you have max_fsm_pages set to an appropriate number, you
> shouldn't end up back
On Fri, Feb 4, 2011 at 4:19 AM, Vitalii Tymchyshyn wrote:
> Why do you expect such a invasive code changes? I know little about
> postgresql code layering, but what I propose (with changing delete to
> truncate) is:
> 1) Leave tuple addressing as it is now
i.e. a block number and a slot position
On Fri, Feb 4, 2011 at 9:38 AM, Vitalii Tymchyshyn wrote:
> Actually for me the main "con" with streaming analyze is that it adds
> significant CPU burden to already not too fast load process.
Exactly.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
-
felix wrote:
and do you agree that I should turn CLUSTER ON ?
I have no problem to stop all tasks to this table at night and just
reload it
You don't turn it on; it's a one time operation that does a cleanup. It
is by far the easiest way to clean up the mess you have right now.
Moving forw
On 02/04/2011 12:14 PM, felix wrote:
do you think its possible that it just doesn't have anything to
complain about ? or the password is affecting it ?
Why is it asking for the password over and over again? It shouldn't be
doing that. And also, are you running this as a user with superuser
p
You can run vacuum verbose on just the postgres database and get the
global numbers at the end. gotta be a superuser as well.
# \c postgres postgres
postgres=# vacuum verbose;
lots deleted.
DETAIL: A total of 7664 page slots are in use (including overhead).
7664 page slots are required to t
vacuumdb -a -v -z -U postgres -W &> vacuum.log
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
Password:
cruxnu:nsbuildout crucial$
do you think its possible that it just doesn't have anything to complain
about ?
or the password is affecting it ?
On 02/04/2011 11:38 AM, felix wrote:
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.fastadder_fastadderstatus"
INFO: "fastadder_fastadderstatus": scanned 2492 of 2492 pages,
containing 154378 live rows and 0 dead rows; 3 rows in sample,
154378 estimated total rows
and there'
vacuumdb -a -v -z -U postgres -W &> vacuum.log
that's all, isn't it ?
it did each db
8.3 in case that matters
the very end:
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.seo_partnerlinkcategory"
INFO: "seo_partner
On Fri, Feb 4, 2011 at 10:38 AM, felix wrote:
>
>
> On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas wrote:
>>
>>
>> vacuumdb -a -v -z &>vacuum.log
>>
>> And at the end of the log, it'll tell you how many pages it wants, and how
>> many pages were available.
>
> this is the dev, not live. but this is
On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas wrote:
>
>
> vacuumdb -a -v -z &>vacuum.log
>
> And at the end of the log, it'll tell you how many pages it wants, and how
> many pages were available.
>
this is the dev, not live. but this is after it gets done with that table:
CPU 0.00s/0.00u sec e
On 02/04/2011 10:03 AM, felix wrote:
max_fsm_pages | 153600 | Sets the
maximum number of disk pages for which free space is tracked.
max_fsm_relations | 1000 | Sets the
maximum number of tables and indexes for which free space is trac
On 02/04/2011 10:17 AM, felix wrote:
> How big is this table when it's acting all bloated and ugly?
458MB
Wow! There's no way a table with 300k records should be that big unless
it's just full of text. 70-seconds seems like a really long time to read
half a gig, but that might be because it
On Fri, Feb 04, 2011 at 05:20:27PM +0100, felix wrote:
> reply was meant for the list
>
> -- Forwarded message --
> From: felix
> Date: Fri, Feb 4, 2011 at 4:39 PM
> Subject: Re: [PERFORM] Really really slow select count(*)
> To: Greg Smith
>
>
>
>
> On Fri, Feb 4, 2011 at 3:
reply was meant for the list
-- Forwarded message --
From: felix
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith
On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith wrote:
> PostgreSQL version? If you're running on 8.3 or e
sorry, reply was meant to go to the list.
-- Forwarded message --
From: felix
Date: Fri, Feb 4, 2011 at 5:17 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: stho...@peak6.com
On Fri, Feb 4, 2011 at 4:00 PM, Shaun Thomas wrote:
> How big is this table when it
On Fri, Feb 4, 2011 at 6:05 AM, Grant Johnson wrote:
>
>> Yes. And this has little to do with hints. It has to do with years
>> of development lead with THOUSANDS of engineers who can work on the
>> most esoteric corner cases in their spare time. Find the pg project a
>> couple hundred software
2011/2/4 Frank Heikens :
>
>
> On 04 Feb, 2011,at 02:56 PM, Mladen Gogala
> wrote:
>
> Віталій Тимчишин wrote:
>> Hi, all.
>>
>> All this optimizer vs hint thread
> There is no "optimizer vs. hint". Hints are a necessary part of the
> optimizer in all other databases.
>
>
> That has nothing to do
On 04 Feb, 2011,at 02:56 PM, Mladen Gogala wrote:
Віталій Тимчишин wrote:
> Hi, all.
>
> All this optimizer vs hint thread
There is no "optimizer vs. hint". Hints are a necessary part of the
optimizer in all other databases.
That has nothing to do with PostgreSQL: PostgreSQL = PostgreSQL.
On 02/04/2011 08:56 AM, Greg Smith wrote:
PostgreSQL version? If you're running on 8.3 or earlier, I would be
suspicous that your Free Space Map has been overrun.
That's my first inclination. If he says autovacuum is running, there's
no way it should be bloating the table that much.
Felix,
On 02/04/2011 08:46 AM, felix wrote:
explain analyze select count(*) from fastadder_fastadderstatus;
Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual
time=77130.000..77130.000 rows=1 loops=1)
-> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18
rows=303018 width=0) (ac
felix wrote:
explain analyze select count(*) from fastadder_fastadderstatus;
Aggregate (cost=62458.73..62458.74 rows=1 width=0) (actual
time=77130.000..77130.000 rows=1 loops=1)
-> Seq Scan on fastadder_fastadderstatus (cost=0.00..61701.18
rows=303018 width=0) (actual time=50.000..76930.
On Thu, Feb 03, 2011 at 09:05:52PM -0500, Robert Haas wrote:
> On Thu, Feb 3, 2011 at 8:37 PM, wrote:
> > On Thu, 3 Feb 2011, Robert Haas wrote:
> >
> >> On Thu, Feb 3, 2011 at 7:39 PM, ? wrote:
>
> Yeah, but you'll be passing the entire table through this separate
> process that m
On Fri, Feb 04, 2011 at 03:46:35PM +0100, felix wrote:
> directly after REINDEX and ANALYZE:
>
> Aggregate (cost=62348.70..62348.71 rows=1 width=0) (actual
> time=15830.000..15830.000 rows=1 loops=1)
>-> Seq Scan on fastadder_fastadderstatus (cost=0.00..61613.16
> rows=294216 width=0) (act
I am having huge performance problems with a table. Performance deteriorates
every day and I have to run REINDEX and ANALYZE on it every day. auto
vacuum is on. yes, I am reading the other thread about count(*) :)
but obviously I'm doing something wrong here
explain analyze select count(*) fro
Ivan Voras wrote:
The "vanilla" plan, with default settings is:
Pause here for a second: why default settings? A default PostgreSQL
configuration is suitable for systems with about 128MB of RAM. Since
you say you have "good enough hardware", I'm assuming you have a bit
more than that. Th
04.02.11 16:33, Kenneth Marshall написав(ла):
In addition, the streaming ANALYZE can provide better statistics at
any time during the load and it will be complete immediately. As far
as passing the entire table through the ANALYZE process, a simple
counter can be used to only send the required s
Shaun Thomas wrote:
On 02/04/2011 07:56 AM, Mladen Gogala wrote:
Hints are a necessary part of the
optimizer in all other databases. Without hints Postgres will not get
used in the company that I work for, period.
I've said repeatedly that EnterpriseDB, a fork of PostgreSQL, has the
On Thu, Feb 03, 2011 at 04:39:12PM -0800, da...@lang.hm wrote:
> On Thu, 3 Feb 2011, Robert Haas wrote:
>
>> On Thu, Feb 3, 2011 at 3:54 PM, wrote:
>>> with the current code, this is a completely separate process that knows
>>> nothing about the load, so if you kick it off when you start the load
Mladen Gogala wrote:
I am even inclined to believe that deep down under the hood, this
fatwa has an ulterior motive, which disgusts me deeply. With hints,
there would be far fewer consulting gigs.
Now you're just being rude. Given that you have direct access to the
developers of the software
On 02/04/2011 07:56 AM, Mladen Gogala wrote:
Hints are a necessary part of the
optimizer in all other databases. Without hints Postgres will not get
used in the company that I work for, period.
I've said repeatedly that EnterpriseDB, a fork of PostgreSQL, has the
hints you seek, yet you seem
Віталій Тимчишин wrote:
Hi, all.
All this optimizer vs hint thread
There is no "optimizer vs. hint". Hints are a necessary part of the
optimizer in all other databases. Without hints Postgres will not get
used in the company that I work for, period. I was willing to wait but
the fatwa against
Yes. And this has little to do with hints. It has to do with years
of development lead with THOUSANDS of engineers who can work on the
most esoteric corner cases in their spare time. Find the pg project a
couple hundred software engineers and maybe we'll catch Oracle a
little quicker. Otherw
I'm running all this on a 9.0 server with good enough hardware. The
query is:
SELECT news.id AS news_id
, news.layout_id
, news.news_relation_id
, news.author_id
Andy Colson wrote:
Yes, I agree... for today. If you gaze into 5 years... double the
core count (but not the speed), double the IO rate. What do you see?
Four more versions of PostgreSQL addressing problems people are having
right now. When we reach the point where parallel query is the onl
03.02.11 20:42, Robert Haas написав(ла):
2011/1/30 Віталій Тимчишин:
I was thinking if a table file could be deleted if it has no single live
row. And if this could be done by vacuum. In this case vacuum on table that
was fully updated recently could be almost as good as cluster - any scan
would
Mladen Gogala schrieb:
Well, the problem will not go away. As I've said before, all other
databases have that feature and none of the reasons listed here
convinced me that everybody else has a crappy optimizer. The problem
may go away altogether if people stop using PostgreSQL.
A common pr
Hi, all.
All this optimizer vs hint thread reminded me about crazy idea that got to
my head some time ago.
I currently has two problems with postgresql optimizer
1) Dictionary tables. Very usual thing is something like "select * from
big_table where distionary_id = (select id from dictionary where
66 matches
Mail list logo
