date:20110204


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 only way 
around the actual bottlenecks in the software people are running into, 
someone will finish parallel query.  I am not a fan of speculative 
development in advance of real demand for it.  There are multiple much 
more serious bottlenecks impacting scalability in PostgreSQL that need 
to be addressed before this one is #1 on the development priority list 
to me.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books


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

[PERFORM] Query performance with disabled hashjoin and mergejoin

2011-02-04 Thread Ivan Voras

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
,   news.date_created
,   news.date_published
,   news.lastedit
,   news.lastedit_user_id
,   news.lastedit_date
,   news.approved_by
,   news.state
,   news.visible_from
,   news.visible_to
,   news.archived_by
,   news.archived_date
,   news.priority
,   news.collection_id
,   news.comment
,   news.keywords
,   news.icon
,   news.icon_url
,   news.icon_width
,   news.icon_height
,   news.icon_position
,   news.icon_onclick
,   news.icon_newwindow
,   news.no_lead
,   news.content_exists
, news.title, news.lead, news.content


,   author.public_name AS 
author_public_name
,   lastedit_user.public_name AS 
lastedit_user_public_name
,   approved_by_user.public_name AS 
approved_by_public_name
,   archived_by_user.public_name AS 
archived_by_public_name

FROM news
JOIN users AS author ON news.author_id 
= author.id
LEFT JOIN users AS lastedit_user ON 
news.lastedit_user_id = lastedit_user.id
LEFT JOIN users AS approved_by_user ON 
news.approved_by = approved_by_user.id
LEFT JOIN users AS archived_by_user ON 
news.archived_by = archived_by_user.id


WHERE (news.layout_id = 8980) AND (state = 
2) AND (date_published = 1296806570 AND (visible_from IS NULL OR 
1296806570 BETWEEN visible_f

rom AND visible_to))
ORDER BY priority DESC, date_published DESC
;

The vanilla plan, with default settings is:

 Sort  (cost=7325.84..7329.39 rows=1422 width=678) (actual 
time=100.846..100.852 rows=7 loops=1)

   Sort Key: news.priority, news.date_published
   Sort Method:  quicksort  Memory: 38kB
   -  Hash Left Join  (cost=2908.02..7251.37 rows=1422 width=678) 
(actual time=100.695..100.799 rows=7 loops=1)

 Hash Cond: (news.archived_by = archived_by_user.id)
 -  Hash Left Join  (cost=2501.75..6819.47 rows=1422 
width=667) (actual time=76.742..76.830 rows=7 loops=1)

   Hash Cond: (news.approved_by = approved_by_user.id)
   -  Hash Left Join  (cost=2095.48..6377.69 rows=1422 
width=656) (actual time=53.248..53.318 rows=7 loops=1)

 Hash Cond: (news.lastedit_user_id = lastedit_user.id)
 -  Hash Join  (cost=1689.21..5935.87 rows=1422 
width=645) (actual time=29.793..29.846 rows=7 loops=1)

   Hash Cond: (news.author_id = author.id)
   -  Bitmap Heap Scan on news 
(cost=1282.94..5494.05 rows=1422 width=634) (actual time=5.532..5.560 
rows=7 loops=1)
 Recheck Cond: ((layout_id = 8980) AND 
(state = 2) AND ((visible_from IS NULL) OR (1296806570 = visible_to)))
 Filter: ((date_published = 
1296806570) AND ((visible_from IS NULL) OR ((1296806570 = visible_from) 
AND (1296806570 = visible_to
 -  BitmapAnd  (cost=1282.94..1282.94 
rows=1430 width=0) (actual time=5.508..5.508 rows=0 loops=1)
   -  Bitmap Index Scan on 
news_index_layout_id_state  (cost=0.00..150.14 rows=2587 width=0) 
(actual time=0.909..0.909 rows=3464 loops=1)
 Index Cond: ((layout_id = 
8980) AND (state = 2))
   -  BitmapOr 
(cost=1132.20..1132.20 rows=20127 width=0) (actual time=4.136..4.136 
rows=0 loops=1)
 -  Bitmap Index Scan on 
news_visible_from  (cost=0.00..1122.09 rows=19976 width=0) (actual 
time=3.367..3.367 rows=19932 loops=1)

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Grant Johnson




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.  Otherwise we'll have to marshall our resources to do
the best we can on the project ,and that means avoiding maintenance
black holes and having the devs work on the things that give the most
benefit for the cost.  Hints are something only a tiny percentage of
users could actually use and use well.

Write a check, hire some developers and get the code done and present
it to the community.  If it's good and works it'll likely get
accepted.  Or use EDB, since it has oracle compatibility in it.

I have to disagree with you here.   I have never seen Oracle outperform 
PostgreSQL on complex joins, which is where the planner comes in.  
Perhaps on certain throughput things, but this is likely do to how we 
handle dead rows, and counts, which is definitely because of how dead 
rows are handled, but the easier maintenance makes up for those.  Also 
both of those are by a small percentage.


I have many times had Oracle queries that never finish (OK maybe not 
never, but not over a long weekend) on large hardware, but can be 
finished on PostgreSQL in a matter or minutes on cheap hardware.   This 
happens to the point that often I have set up a PostgreSQL database to 
copy the data to for querying and runnign the complex reports, even 
though the origin of the data was Oracle, since the application was 
Oracle specific.   It took less time to duplicate the database and run 
the query on PostgreSQL than it did to just run it on Oracle.


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

Re: [PERFORM] Talking about optimizer, my long dream

2011-02-04 Thread Mladen Gogala


Віталій Тимчишин 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 hints seems unyielding, so that's it. 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.


Mladen Gogala 
Sr. Oracle DBA

1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 



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

Re: [PERFORM] Talking about optimizer, my long dream


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 to enjoy berating the PostgreSQL community 
as if it owes you something.


Also, we don't care if you don't use PostgreSQL. If I put something up 
for free, some random guy not taking it won't exactly hurt my feelings.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Talking about optimizer, my long dream


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, for free, on these mailing lists, the main 
reason there is consulting work anyway is because some companies can't 
publish their queries or data publicly.  All of us doing PostgreSQL 
consulting regularly take those confidental reports and turn them into 
feedback to improve the core software.  That is what our clients want, 
too:  a better PostgreSQL capable of handling their problem, not just a 
hacked up application that works today, but will break later once data 
volume or distribution changes.


You really just don't get how open-source development works at all if 
you think money is involved in why people have their respective 
technical opinions on controversial subjects.  Try and hire the 
sometimes leader of this particular fatwa, Tom Lane, for a consulting 
gig if you think that's where his motivation lies.  I would love to have 
a recording of *that* phone call.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books


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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Kenneth Marshall

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,  da...@lang.hm 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, it
 makes a pass over the table (competing for I/O), finishes, you continue 
 to
 update the table, so it makes another pass, etc. As you say, this is a 
 bad
 thing to do. I am saying to have an option that ties the two togeather,
 essentially making the data feed into the Analyze run be a fork of the 
 data
 comeing out of the insert run going to disk. So the Analyze run doesn't 
 do
 any I/O and isn't going to complete until the insert is complete. At 
 which
 time it will have seen one copy of the entire table.

 Yeah, but you'll be passing the entire table through this separate
 process that may only need to see 1% of it or less on a large table.
 If you want to write the code and prove it's better than what we have
 now, or some other approach that someone else may implement in the
 meantime, hey, this is an open source project, and I like improvements
 as much as the next guy.  But my prediction for what it's worth is
 that the results will suck.  :-)

 I will point out that 1% of a very large table can still be a lot of disk 
 I/O that is avoided (especially if it's random I/O that's avoided)

 David Lang


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 samples based on the
statistics target. Where this would seem to help the most is in
temporary tables which currently do not work with autovacuum but it
would streamline their use for more complicated queries that need
an analyze to perform well.

Regards,
Ken

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

Re: [PERFORM] Talking about optimizer, my long dream

2011-02-04 Thread Mladen Gogala


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 
hints you seek, yet you seem to enjoy berating the PostgreSQL community 
as if it owes you something.


Also, we don't care if you don't use PostgreSQL. If I put something up 
for free, some random guy not taking it won't exactly hurt my feelings.


  
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 projects, tools provided by other major databases. This 
discussion served my purpose wonderfully.  Project is killed, here we 
part ways. No more problems for either of us. Good luck with the 
perfect optimizer and good riddance. My only regret is about the time 
I have wasted.


--
Mladen Gogala 
Sr. Oracle DBA

1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 



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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Vitalii Tymchyshyn


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 samples based on the
statistics target. Where this would seem to help the most is in
temporary tables which currently do not work with autovacuum but it
would streamline their use for more complicated queries that need
an analyze to perform well.

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 some threshold).
And you can't start after some threshold of data passed by since you may 
loose significant information (like minimal values).


Best regards, Vitalii Tymchyshyn

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

Re: [PERFORM] Query performance with disabled hashjoin and mergejoin


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.  The first things to try here are the list at 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad 
query here looks like it might benefit from a large increase to 
effective_cache_size, and possibly an increase to work_mem as well.  
Your bad plan here is doing a lot of sequential scans instead of 
indexed lookups, which makes me wonder if the change in join types 
you're forcing isn't fixing that part as a coincidence.


Note that the estimated number of rows coming out of each form of plan 
is off by a factor of about 200X, so it's not that the other plan type 
is better estimating anything.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books


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

[PERFORM] Really really slow select count(*)

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(*) 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.000 rows=302479 loops=1)
 Total runtime: *77250.000 ms*

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) (actual time=30.000..15570.000 rows=302479 loops=1)
 Total runtime: 15830.000 ms

still very bad for a 300k row table

a similar table:

explain analyze select count(*) from fastadder_fastadderstatuslog;

 Aggregate  (cost=8332.53..8332.54 rows=1 width=0) (actual
time=1270.000..1270.000 rows=1 loops=1)
   -  Seq Scan on fastadder_fastadderstatuslog  (cost=0.00..7389.02
rows=377402 width=0) (actual time=0.000..910.000 rows=377033 loops=1)
 Total runtime: 1270.000 ms


It gets updated quite a bit each day, and this is perhaps the problem.
To me it doesn't seem like that many updates

100-500 rows inserted per day
no deletes

10k-50k updates per day
mostly of this sort:   set priority=1 where id=12345

is it perhaps this that is causing the performance problem ?

I could rework the app to be more efficient and do updates using batches
where id IN (1,2,3,4...)

I assume that means a more efficient index update compared to individual
updates.

There is one routine that updates position_in_queue using a lot (too many)
update statements.
Is that likely to be the culprit ?

*What else can I do to investigate ?*


   Table
public.fastadder_fastadderstatus
  Column   |   Type   |
  Modifiers
---+--+
 id| integer  | not null default
nextval('fastadder_fastadderstatus_id_seq'::regclass)
 apt_id| integer  | not null
 service_id| integer  | not null
 agent_priority| integer  | not null
 priority  | integer  | not null
 last_validated| timestamp with time zone |
 last_sent | timestamp with time zone |
 last_checked  | timestamp with time zone |
 last_modified | timestamp with time zone | not null
 running_status| integer  |
 validation_status | integer  |
 position_in_queue | integer  |
 sent  | boolean  | not null default false
 built | boolean  | not null default false
 webid_suffix  | integer  |
 build_cache   | text |
Indexes:
fastadder_fastadderstatus_pkey PRIMARY KEY, btree (id)
fastadder_fastadderstatus_apt_id_key UNIQUE, btree (apt_id,
service_id)
fastadder_fastadderstatus_agent_priority btree (agent_priority)
fastadder_fastadderstatus_apt_id btree (apt_id)
fastadder_fastadderstatus_built btree (built)
fastadder_fastadderstatus_last_checked btree (last_checked)
fastadder_fastadderstatus_last_validated btree (last_validated)
fastadder_fastadderstatus_position_in_queue btree (position_in_queue)
fastadder_fastadderstatus_priority btree (priority)
fastadder_fastadderstatus_running_status btree (running_status)
fastadder_fastadderstatus_service_id btree (service_id)
Foreign-key constraints:
fastadder_fastadderstatus_apt_id_fkey FOREIGN KEY (apt_id) REFERENCES
nsproperties_apt(id) DEFERRABLE INITIALLY DEFERRED
fastadder_fastadderstatus_service_id_fkey FOREIGN KEY (service_id)
REFERENCES fastadder_fastadderservice(id) DEFERRABLE INITIALLY DEFERRED


thanks !

Re: [PERFORM] Really really slow select count(*)

2011-02-04 Thread hubert depesz lubaczewski

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) (actual time=30.000..15570.000 rows=302479 loops=1)
  Total runtime: 15830.000 ms

do run vacuum of the table. reindex doesn't matter for seq scans, and
analyze, while can help choose different plan - will not help here
anyway.

Best regards,

depesz


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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Kenneth Marshall

On Thu, Feb 03, 2011 at 09:05:52PM -0500, Robert Haas wrote:
 On Thu, Feb 3, 2011 at 8:37 PM,  da...@lang.hm wrote:
  On Thu, 3 Feb 2011, Robert Haas wrote:
 
  On Thu, Feb 3, 2011 at 7:39 PM, ?da...@lang.hm wrote:
 
  Yeah, but you'll be passing the entire table through this separate
  process that may only need to see 1% of it or less on a large table.
  If you want to write the code and prove it's better than what we have
  now, or some other approach that someone else may implement in the
  meantime, hey, this is an open source project, and I like improvements
  as much as the next guy. ?But my prediction for what it's worth is
  that the results will suck. ?:-)
 
  I will point out that 1% of a very large table can still be a lot of disk
  I/O that is avoided (especially if it's random I/O that's avoided)
 
  Sure, but I think that trying to avoid it will be costly in other ways
  - you'll be streaming a huge volume of data through some auxiliary
  process, which will have to apply some algorithm that's very different
  from the one we use today. ?The reality is that I think there's little
  evidence that the way we do ANALYZE now is too expensive. ?It's
  typically very cheap and works very well. ?It's a bit annoying when it
  fires off in the middle of a giant data load, so we might need to
  change the time of it a little, but if there's a problem with the
  operation itself being too costly, this is the first I'm hearing of
  it. ?We've actually worked *really* hard to make it cheap.
 
  I could be misunderstanding things here, but my understanding is that it's
  'cheap' in that it has little impact on the database while it is running.
 
 I mean that it's cheap in that it usually takes very little time to complete.
 
  the issue here is that the workflow is
 
  load data
  analyze
  start work
 
  so the cost of analyze in this workflow is not 1% impact on query speed for
  the next X time, it's the database can't be used for the next X time while
  we wait for analyze to finish running
 
 OK.
 
  I don't understand why the algorithm would have to be so different than
  what's done today, surely the analyze thread could easily be tweaked to
  ignore the rest of the data (assuming we don't have the thread sending the
  data to analyze do the filtering)
 
 If you want to randomly pick 10,000 rows out of all the rows that are
 going to be inserted in the table without knowing in advance how many
 there will be, how do you do that?  Maybe there's an algorithm, but
 it's not obvious to me.  But mostly, I question how expensive it is to
 have a second process looking at the entire table contents vs. going
 back and rereading a sample of rows at the end.  I can't remember
 anyone ever complaining ANALYZE took too long to run.  I only
 remember complaints of the form I had to remember to manually run it
 and I wish it had just happened by itself.
 
 -- 
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

Probably doomed to be shot down, but since you are effectively inline,
you could sample assuming a range of table row counts. Start at the
size of a table where random (index) lookups are faster than a sequential
scan and then at appropriate multiples, 100x, 100*100X,... then you should
be able to generate appropriate statistics. I have not actually looked at
how that would happen, but it would certainly allow you to process far, far
fewer rows than the entire table.

Regards,
Ken

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

Re: [PERFORM] Really really slow select count(*)


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.000 rows=302479 loops=1)

 Total runtime: *77250.000 ms*



PostgreSQL version?  If you're running on 8.3 or earlier, I would be 
suspicous that your Free Space Map has been overrun.


What you are seeing is that the table itself is much larger on disk than 
it's supposed to be.  That can be caused by frequent UPDATEs if you 
don't have vacuum cleanup working effectively, you'll get lots of dead 
sections left behind from UPDATEs in the middle.  The best way to fix 
all this is to run CLUSTER on the table.  That will introduce a bit of 
downtime while it holds a lock on the table (only a few minutes based on 
what you've shown here), but the copy you'll have afterwards won't be 
spread all over disk anymore.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books

Re: [PERFORM] Really really slow select count(*)


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) (actual time=50.000..76930.000 rows=302479 loops=1)
  Total runtime: *77250.000 ms*


How big is this table when it's acting all bloated and ugly?

SELECT relpages*8/1024 FROM pg_class
 WHERE relname='fastadder_fastadderstatus';

That's the number of MB it's taking up that would immediately affect a 
count statement.



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) (actual time=30.000..15570.000 rows=302479 loops=1)
  Total runtime: 15830.000 ms


That probably put it into cache, explaining the difference, but yeah... 
that is pretty darn slow. Is this the only thing running when you're 
doing your tests? What does your disk IO look like?



10k-50k updates per day
mostly of this sort:   set priority=1 where id=12345


Well... that's up to 16% turnover per day, but even then, regular 
vacuuming should keep it manageable.



I could rework the app to be more efficient and do updates using batches
where id IN (1,2,3,4...)


No. Don't do that. You'd be better off loading everything into a temp 
table and doing this:


UPDATE fastadder_fastadderstatus s
   SET priority = 1
  FROM temp_statuses t
 WHERE t.id=s.id;

It's a better practice, but still doesn't really explain your 
performance issues.



fastadder_fastadderstatus_pkey PRIMARY KEY, btree (id)
fastadder_fastadderstatus_apt_id_key UNIQUE, btree (apt_id, service_id)
fastadder_fastadderstatus_agent_priority btree (agent_priority)
fastadder_fastadderstatus_apt_id btree (apt_id)
fastadder_fastadderstatus_built btree (built)
fastadder_fastadderstatus_last_checked btree (last_checked)
fastadder_fastadderstatus_last_validated btree (last_validated)
fastadder_fastadderstatus_position_in_queue btree (position_in_queue)
fastadder_fastadderstatus_priority btree (priority)
fastadder_fastadderstatus_running_status btree (running_status)
fastadder_fastadderstatus_service_id btree (service_id)


Whoh! Hold on, here. That looks like *way* too many indexes. Definitely 
will slow down your insert/update performance. The index on 'built' for 
example, is a boolean. If it's evenly distributed, that's 150k matches 
for true or false, rendering it useless, yet still requiring space and 
maintenance. I'm guessing the story is similar for quite a few of the 
others.


It doesn't really explain your count speed, but it certainly isn't helping.

Something seems fishy, here.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)


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, If you're running a version before 8.4, what is your 
max_fsm_pages setting? If it's too low, autovacuum won't save you, and 
your tables will continue to grow daily unless you vacuum full 
regularly, and I wouldn't recommend that to my worst enemy. ;)


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Talking about optimizer, my long dream

2011-02-04 Thread Frank Heikens




On 04 Feb, 2011,at 02:56 PM, Mladen Gogala mladen.gog...@vmsinfo.com 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. And it doesn't 
have hints and everybody knows it.

Without hints Postgres will not get
used in the company that I work for, period. 


That's up to you, that's fine. But why did you start with PostgreSQL in the 
first place? You knew PostgreSQL doesn't have hints and the wiki told you hints 
are not wanted as well. When hints are an essential requirement for your 
company, you should pick another product, EnterpriseDB Postgres Plus for 
example.

I was willing to wait but
the fatwa against hints seems unyielding, 


There is no fatwa. The PostgreSQL project prefers to spend resources on a 
better optimizer to solve the real problems, not on hints for working around 
the problems. That has nothing to do with any fatwa or religion.

so that's it. 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.


The consulting guys are the ones who love hints: They know they have to come 
back the other month because the old hint does more harm than good when data 
changes. And data will change over time.

You said it's so simple to implement hints in PostgreSQL, so please, show us. 
Or ask/pay somebody to write this simple code for you to support hints, nobody 
will ever stop you from doing that. When you have a use case that proves the 
usage of hints will improve the performance of PostgreSQL and you have some 
code that can be maintained by the PostgreSQL project, it might be implemented 
in the contrib or even core. It's up to you, not somebody else.



Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com


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

Re: [PERFORM] Talking about optimizer, my long dream

2011-02-04 Thread Cédric Villemain

2011/2/4 Frank Heikens frankheik...@mac.com:


 On 04 Feb, 2011,at 02:56 PM, Mladen Gogala mladen.gog...@vmsinfo.com
 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. And it
 doesn't have hints and everybody knows it.

 Without hints Postgres will not get
 used in the company that I work for, period.


 That's up to you, that's fine. But why did you start with PostgreSQL in the
 first place? You knew PostgreSQL doesn't have hints and the wiki told you
 hints are not wanted as well. When hints are an essential requirement for
 your company, you should pick another product, EnterpriseDB Postgres Plus
 for example.

 I was willing to wait but
 the fatwa against hints seems unyielding,


 There is no fatwa. The PostgreSQL project prefers to spend resources on a
 better optimizer to solve the real problems, not on hints for working around
 the problems. That has nothing to do with any fatwa or religion.

 so that's it. 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.


 The consulting guys are the ones who love hints: They know they have to come
 back the other month because the old hint does more harm than good when data
 changes. And data will change over time.

 You said it's so simple to implement hints in PostgreSQL, so please, show
 us. Or ask/pay somebody to write this simple code for you to support hints,
 nobody will ever stop you from doing that. When you have a use case that
 proves the usage of hints will improve the performance of PostgreSQL and you
 have some code that can be maintained by the PostgreSQL project, it might be
 implemented in the contrib or even core. It's up to you, not somebody else.

Just in case you miss it:
http://www.sai.msu.su/~megera/wiki/plantuner

Btw feel free to do how you want, it is open source, and BSD, you can
take PostgreSQL, add hints, go and sell that to your boss.





 Mladen Gogala
 Sr. Oracle DBA
 1500 Broadway
 New York, NY 10036
 (212) 329-5251
 www.vmsinfo.com


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




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] [PERFORM] Slow count(*) again...

On Fri, Feb 4, 2011 at 6:05 AM, Grant Johnson gr...@amadensor.com 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 engineers and maybe we'll catch Oracle a
 little quicker.  Otherwise we'll have to marshall our resources to do
 the best we can on the project ,and that means avoiding maintenance
 black holes and having the devs work on the things that give the most
 benefit for the cost.  Hints are something only a tiny percentage of
 users could actually use and use well.

 Write a check, hire some developers and get the code done and present
 it to the community.  If it's good and works it'll likely get
 accepted.  Or use EDB, since it has oracle compatibility in it.

 I have to disagree with you here.   I have never seen Oracle outperform
 PostgreSQL on complex joins, which is where the planner comes in.  Perhaps
 on certain throughput things, but this is likely do to how we handle dead
 rows, and counts, which is definitely because of how dead rows are handled,
 but the easier maintenance makes up for those.  Also both of those are by a
 small percentage.

 I have many times had Oracle queries that never finish (OK maybe not never,
 but not over a long weekend) on large hardware, but can be finished on
 PostgreSQL in a matter or minutes on cheap hardware.   This happens to the
 point that often I have set up a PostgreSQL database to copy the data to for
 querying and runnign the complex reports, even though the origin of the data
 was Oracle, since the application was Oracle specific.   It took less time
 to duplicate the database and run the query on PostgreSQL than it did to
 just run it on Oracle.

It very much depends on the query.  With lots of tables to join, and
with pg 8.1 which is what I used when we were running Oracle 9, Oracle
won.  With fewer tables to join in an otherwise complex reporting
query PostgreSQL won.  I did the exact thing you're talking about. I
actually wrote a simple replication system fro Oracle to PostgreSQL
(it was allowed to be imperfect because it was stats data and we could
recreate at a moment).

PostgreSQL on a PIV workstation with 2G ram and 4 SATA drives in
RAID-10 stomped Oracle on much bigger Sun hardware into the ground for
reporting queries.  Queries that ran for hours or didn't finish in
Oracle ran in 5 to 30 minutes on the pg box.

But not all queries were like that.

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

Fwd: [PERFORM] Really really slow select count(*)

sorry, reply was meant to go to the list.

-- Forwarded message --
From: felix crucialfe...@gmail.com
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 stho...@peak6.com wrote:

 How big is this table when it's acting all bloated and ugly?

458MB

 Is this the only thing running when you're doing your tests? What does your
 disk IO look like?

this is on a live site.  best not to scare the animals.

I have the same config on the dev environment but not the same table size.

  10k-50k updates per day
 mostly of this sort:   set priority=1 where id=12345

 Well... that's up to 16% turnover per day, but even then, regular vacuuming
 should keep it manageable.

something is definitely amiss with this table.

I'm not sure if its something that happened at one point when killing an
task that was writing to it or if its something about the way the app is
updating.  it SHOULDN'T be that much of a problem, though I can find ways to
improve it.

No. Don't do that. You'd be better off loading everything into a temp table
 and doing this:

 UPDATE fastadder_fastadderstatus s
   SET priority = 1
  FROM temp_statuses t
  WHERE t.id=s.id;

ok, that is one the solutions I was thinking about.

are updates of the where id IN (1,2,3,4) generally not efficient ?
how about for select queries ?

 fastadder_fastadderstatus_pkey PRIMARY KEY, btree (id)
 fastadder_fastadderstatus_apt_id_key UNIQUE, btree (apt_id, service_id)
 fastadder_fastadderstatus_agent_priority btree (agent_priority)
 fastadder_fastadderstatus_apt_id btree (apt_id)
 fastadder_fastadderstatus_built btree (built)
 fastadder_fastadderstatus_last_checked btree (last_checked)
 fastadder_fastadderstatus_last_validated btree (last_validated)
 fastadder_fastadderstatus_position_in_queue btree (position_in_queue)
 fastadder_fastadderstatus_priority btree (priority)
 fastadder_fastadderstatus_running_status btree (running_status)
 fastadder_fastadderstatus_service_id btree (service_id)

 Whoh! Hold on, here. That looks like *way* too many indexes.

I actually just added most of those yesterday in an attempt to improve
performance. priority and agent_priority were missing indexes and that was a
big mistake.

overall performance went way up on my primary selects

 Definitely will slow down your insert/update performance.

there are a lot more selects happening throughout the day

 The index on 'built' for example, is a boolean. If it's evenly distributed,
 that's 150k matches for true or false,

ok,

built True is in the minority.

here is the test query that caused me to add indices to the booleans.  this
is a 30k table which is doing selects on two booleans constantly.  again:
True is the minority

explain analyze SELECT nsproperties_apt.id,
nsproperties_apt.display_address, nsproperties_apt.apt_num,
nsproperties_apt.bldg_id, nsproperties_apt.is_rental,
nsproperties_apt.is_furnished, nsproperties_apt.listing_type,
nsproperties_apt.list_on_web, nsproperties_apt.is_approved,
nsproperties_apt.status, nsproperties_apt.headline,
nsproperties_apt.slug, nsproperties_apt.cross_street,
nsproperties_apt.show_apt_num, nsproperties_apt.show_building_name,
nsproperties_apt.external_url, nsproperties_apt.listed_on,
nsproperties_bldg.id, nsproperties_bldg.name FROM nsproperties_apt
LEFT OUTER JOIN nsproperties_bldg ON (nsproperties_apt.bldg_id =
nsproperties_bldg.id) WHERE (nsproperties_apt.list_on_web = True AND
nsproperties_apt.is_available = True ) ;
   QUERY PLAN

 Hash Left Join  (cost=408.74..10062.18 rows=3344 width=152) (actual
time=12.688..2442.542 rows=2640 loops=1)
   Hash Cond: (nsproperties_apt.bldg_id = nsproperties_bldg.id)
   -  Seq Scan on nsproperties_apt  (cost=0.00..9602.52 rows=3344
width=139) (actual time=0.025..2411.644 rows=2640 loops=1)
 Filter: (list_on_web AND is_available)
   -  Hash  (cost=346.66..346.66 rows=4966 width=13) (actual
time=12.646..12.646 rows=4966 loops=1)
 -  Seq Scan on nsproperties_bldg  (cost=0.00..346.66 rows=4966
width=13) (actual time=0.036..8.236 rows=4966 loops=1)
 Total runtime: 2444.067 ms
(7 rows)

=

 Hash Left Join  (cost=1232.45..9784.18 rows=5690 width=173) (actual
time=30.000..100.000 rows=5076 loops=1)
   Hash Cond: (nsproperties_apt.bldg_id = nsproperties_bldg.id)
   -  Bitmap Heap Scan on nsproperties_apt  (cost=618.23..9075.84 rows=5690
width=157) (actual time=10.000..60.000 rows=5076 loops=1)
 Filter: (list_on_web AND is_available)
 -  BitmapAnd  (cost=618.23..618.23 rows=5690 width=0) (actual
time=10.000..10.000 rows=0 loops=1)
   -  Bitmap Index Scan on nsproperties_apt_is_available
 (cost=0.00..131.81 rows=6874 width=0) (actual time=0.000..0.000

[PERFORM] Really really slow select count(*)

reply was meant for the list

-- Forwarded message --
From: felix crucialfe...@gmail.com
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith g...@2ndquadrant.com

On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith g...@2ndquadrant.com wrote:

 PostgreSQL version?  If you're running on 8.3 or earlier, I would be
 suspicous that your Free Space Map has been overrun.

8.3

 What you are seeing is that the table itself is much larger on disk than
 it's supposed to be.

which part of the explain told you that ?

 shaun thomas

SELECT relpages*8/1024 FROM pg_class
 WHERE relname='fastadder_fastadderstatus';

458MB

way too big. build_cache is text between 500-1k chars

  That can be caused by frequent UPDATEs if you don't have vacuum cleanup
 working effectively, you'll get lots of dead sections left behind from
 UPDATEs in the middle.

ok, I just vacuumed it (did this manually a few times as well). and auto is
on.

still:
32840.000ms
and still 458MB

 The best way to fix all this is to run CLUSTER on the table.

http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

now that would order the data on disk by id (primary key)
the usage of the table is either by a query or by position_in_queue which is
rewritten often (I might change this part of the app and pull it out of this
table)

is this definitely the best way to fix this ?

thanks for your help !

That will introduce a bit of downtime while it holds a lock on the table
 (only a few minutes based on what you've shown here), but the copy you'll
 have afterwards won't be spread all over disk anymore.

 --
 Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
 PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
 PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books

Re: [PERFORM] Really really slow select count(*)

2011-02-04 Thread Kenneth Marshall

On Fri, Feb 04, 2011 at 05:20:27PM +0100, felix wrote:
 reply was meant for the list

 -- Forwarded message --
 From: felix crucialfe...@gmail.com
 Date: Fri, Feb 4, 2011 at 4:39 PM
 Subject: Re: [PERFORM] Really really slow select count(*)
 To: Greg Smith g...@2ndquadrant.com

 On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith g...@2ndquadrant.com wrote:

  PostgreSQL version?  If you're running on 8.3 or earlier, I would be
  suspicous that your Free Space Map has been overrun.

 8.3

  What you are seeing is that the table itself is much larger on disk than
  it's supposed to be.

 which part of the explain told you that ?

  shaun thomas

 SELECT relpages*8/1024 FROM pg_class
  WHERE relname='fastadder_fastadderstatus';

 458MB

 way too big. build_cache is text between 500-1k chars

As has been suggested, you really need to CLUSTER the table
to remove dead rows. VACUUM will not do that, VACUUM FULL will
but will take a full table lock and then you would need to
REINDEX to fix index bloat. CLUSTER will do this in one shot.
You almost certainly have your free space map way too small,
which is how you bloated in the first place.

Cheers,
Ken

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

Re: [PERFORM] Really really slow select count(*)


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's fighting for IO with other 
processes.


For perspective, we have several 1-2 million row tables smaller than 
that. Heck, I have a 11-million row table that's only 30% larger.



are updates of the where id IN (1,2,3,4) generally not efficient ?
how about for select queries ?


Well, IN is notorious for being inefficient. It's been getting better, 
but even EXISTS is a better bet than using IN. We've got a lot of stuff 
using IN here, and we're slowly phasing it out. Every time I get rid of 
it, things get faster.



I actually just added most of those yesterday in an attempt to improve
performance. priority and agent_priority were missing indexes and that
was a big mistake.


Haha. Well, that can always be true. Ironically one of the things you 
actually did by creating the indexes is create fast lookup values to 
circumvent your table bloat. It would help with anything except sequence 
scans, which you saw with your count query.



ok,
built True is in the minority.


Ok, in that case, use a partial index. If a boolean value is only 1% of 
your table or something, why bother indexing the rest anyway?


CREATE INDEX fastadder_fastadderstatus_built
 ON fastadder_fastadderstatus
  WHERE built;

But only if it really is the vast minority. Check this way:

SELECT built, count(1)
   FROM fastadder_fastadderstatus
  GROUP BY 1;

We used one of these to ignore a status that was over 90% of the table, 
where the other statuses combined were less than 10%. The index was 10x 
smaller and much faster than before.


If you know both booleans are used together often, you can combine them 
into a single index, again using a partial where it only indexes if both 
values are true. Much smaller, much faster index if it's more selective 
than the other indexes.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)


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 tracked.

how do I determine the best size or if that's the problem ?


Well, the best way is to run:

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.


 From the sounds of your database, 150k is way too small. If a single 
table is getting 10-50k updates per day, it's a good chance a ton of 
other tables are getting similar traffic. With max_fsm_pages at that 
setting, any update beyond 150k effectively gets forgotten, and 
forgotten rows aren't reused by new inserts or updates.


Your database has probably been slowly expanding for months without you 
realizing it. The tables that get the most turnover will be hit the 
hardest, as it sounds like what happened here.


You can stop the bloating by setting the right max_fsm_pages setting, 
but you'll either have to go through and VACUUM FULL every table in your 
database, or dump/restore to regain all the lost space and performance 
(the later would actually be faster). Before I even touch an older 
PostgreSQL DB, I set it to some value over 3-million just as a starting 
value to be on the safe side. A little used memory is a small price to 
pay for stopping gradual expansion.


Your reindex was a good idea. Indexes do sometimes need that. But your 
base tables need work too. Unless you're on 8.4 or above, auto_vacuum 
isn't enough.


Just to share an anecdote, I was with a company about five years ago and 
they also used the default max_fsm_pages setting. Their DB had expanded 
to 40GB and was filling their disk, only a couple weeks before 
exhausting it. I set the max_fsm_pages setting to 2-million, set up a 
bunch of scripts to vacuum-full the tables from smallest to largest (to 
make enough space for the larger tables, you see) and the database ended 
up at less than 20GB.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas stho...@peak6.com 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 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's nothing at the end of the whole vacuum output about pages

actual command:

vacuumdb -U postgres -W -v -z djns4  vacuum.log

I tried it with all databases too

?

thanks

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 10:38 AM, felix crucialfe...@gmail.com wrote:


 On Fri, Feb 4, 2011 at 5:35 PM, Shaun Thomas stho...@peak6.com 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 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's nothing at the end of the whole vacuum output about pages
 actual command:
 vacuumdb -U postgres -W -v -z djns4  vacuum.log
 I tried it with all databases too

I believe you have to run it on the whole db to get that output.

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

Re: [PERFORM] Really really slow select count(*)

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_partnerlinkcategory: scanned 0 of 0 pages, containing 0 live
rows and 0 dead rows; 0 rows in sample, 0 estimated total rows



On Fri, Feb 4, 2011 at 6:40 PM, Scott Marlowe scott.marl...@gmail.comwrote:


  I tried it with all databases too

 I believe you have to run it on the whole db to get that output.

Re: [PERFORM] Really really slow select count(*)


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's nothing at the end of the whole vacuum output about pages


I'm not sure if it gives it to you if you pick a single DB, but if you 
use -a for all, you should get something at the very end like this:


INFO:  free space map contains 1365918 pages in 1507 relations
DETAIL:  A total of 1326656 page slots are in use (including overhead).
1326656 page slots are required to track all free space.
Current limits are:  300 page slots, 3500 relations, using 38784 kB.
VACUUM

That's on our dev system. Your dev table seems properly sized, but prod 
probably isn't. If you run an all-database vacuum after-hours, you'll 
see the stuff at the end. And if your 'page slots are required' is 
greater than your 'page slots are in use,' you've got a problem.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)

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 ?

In any case I'm not sure I want to run this even at night on production.

what is the downside to estimating max_fsm_pages too high ?

300 should be safe
its certainly not 150k

I have one very large table (10m) that is being analyzed before I warehouse
it.
that could've been the monster that ate the free map.
I think today I've learned that even unused tables affect postgres
performance.


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



On Fri, Feb 4, 2011 at 6:47 PM, Shaun Thomas stho...@peak6.com wrote:

 On 02/04/2011 11:44 AM, felix wrote:

  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_partnerlinkcategory: scanned 0 of 0 pages, containing 0 live
 rows and 0 dead rows; 0 rows in sample, 0 estimated total rows


 That looks to me like it didn't finish. Did you fork it off with '' or run
 it and wait until it gave control back to you?

 It really should be telling you how many pages it wanted, and are in use.
 If not, something odd is going on.


 --
 Shaun Thomas
 OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
 312-676-8870
 stho...@peak6.com

 __

 See  http://www.peak6.com/email_disclaimer.php
 for terms and conditions related to this email

Re: [PERFORM] Really really slow select count(*)

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 track all free space.
Current limits are:  1004800 page slots, 5000 relations, using 6426 kB.

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

Re: [PERFORM] Really really slow select count(*)


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 
privileges? You might want to think about setting up a .pgpass file, or 
setting up local trust for the postgres user so you can run maintenance 
without having to manually enter a password.



In any case I'm not sure I want to run this even at night on
production.


You should be. Even with auto vacuum turned on, all of our production 
systems get a nightly vacuum over the entire list of databases. It's non 
destructive, and about the only thing that happens is disk IO. If your 
app has times where it's not very busy, say 3am, it's a good time.


This is especially true since your free space map is behind.

We actually turn off autovacuum because we have a very transactionally 
intense DB, and if autovacuum launches on a table in the middle of the 
day, our IO totally obliterates performance. We only run a nightly 
vacuum over all the databases when very few users or scripts are using 
anything.



what is the downside to estimating max_fsm_pages too high ?


Nothing really. It uses more memory to track it, but on modern servers, 
it's not a concern. The only risk is that you don't know what the real 
setting should be, so you may not completely stop your bloating.



and do you agree that I should turn CLUSTER ON ?


Cluster isn't really something you turn on, but something you do. It's 
like vacuum full, in that it basically rebuilds the table and all 
indexes from scratch. The major issue you'll run into is that it 
reorders the table by the index you chose, so you'd best select the 
primary key unless you have reasons to use something else. And you have 
to do it table by table, which will really suck since we already know 
your whole db has bloated, not just one or two tables.


You're going to be doing some scripting, buddy. :) Well, unless you just 
do a dump/restore and start over with sane postgresql.conf settings.



I have no problem to stop all tasks to this table at night and just
reload it


That will work for this table. Just keep in mind all your tables have 
been suffering since you installed this database. Tables with the 
highest turnover were hit hardest, but they all have non-ideal sizes 
compared to what they would be if your maintenance was working.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)


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 forward, if you have max_fsm_pages set to an appropriate number, 
you shouldn't end up back in this position again.  But VACUUM along 
won't get you out of there, and VACUUM FULL is always a worse way to 
clean this up than CLUSTER.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books


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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Robert Haas

On Fri, Feb 4, 2011 at 9:38 AM, Vitalii Tymchyshyn tiv...@gmail.com 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

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

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

2011-02-04 Thread Robert Haas

On Fri, Feb 4, 2011 at 4:19 AM, Vitalii Tymchyshyn tiv...@gmail.com 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 within the block?

Seems like you'd need file,block,slot.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 11:38 AM, Greg Smith g...@2ndquadrant.com 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 in this position again.  But VACUUM along won't get
 you out of there, and VACUUM FULL is always a worse way to clean this up
 than CLUSTER.

note that for large, randomly ordered tables, cluster can be pretty
slow, and you might want to do the old:

begin;
select * into temporaryholdingtable order by somefield;
truncate oldtable;
insert into oldtables select * from temporaryholdingtable;
commit;

for fastest performance.  I've had Cluster take hours to do that the
above does in 1/4th the time.

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

[PERFORM] Write-heavy pg_stats_collector on mostly idle server

2011-02-04 Thread Josh Berkus

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, defaults
Active connections: around 15
Autovacuum settings: defaults

Symptoms: on a server which gets around 20 reads and 15 writes per
minute, we are seeing average 500K/second writes by the stats collector
to pg_stat.tmp.  pg_stat.tmp is around 270K.

An strace of the stats collector process shows that the stats collector
is, in fact, rewriting the entire stats file twice per second.

Anyone seen anything like this before?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [PERFORM] Really really slow select count(*)


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 indexes on the table before the insert, and 
CREATE INDEX statements afterwards.


Which actually brings up a question I've been wondering to myself that I 
may submit to [HACKERS]: Can we add a a parallel option to the reindexdb 
command? We added one to pg_restore, so we already know it works.


I have a bunch of scripts that get all the indexes in the database and 
order them by size (so they're distributed evenly), round-robin them 
into separate REINDEX sql files, and launches them all in parallel 
depending on how many threads you want, but that's so hacky I feel dirty 
every time I use it.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas stho...@peak6.com 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 do you agree that I should turn CLUSTER ON ?

Cluster isn't really something you turn on, but something you do.

djns4=# cluster fastadder_fastadderstatus;
ERROR: there is no previously clustered index for table
fastadder_fastadderstatus

http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

djns4=# alter table fastadder_fastadderstatus CLUSTER ON
fastadder_fastadderstatus_pkey; ALTER TABLE djns4=# CLUSTER
fastadder_fastadderstatus; CLUSTER

ok, that's why I figured I was turning something on. the table has been
altered.

it will be pk ordered, new entries always at the end and no deletes

but this means I have to manually run cluster from time to time, right ? not
that there will be much or any reordering. or it should be fine going
forward with vacuum and enlarging the free space memory map.

It's like vacuum full, in that it basically rebuilds the table and all
indexes from scratch. The major issue you'll run into is that it reorders
the table by the index you chose, so you'd best select the primary key
unless you have reasons to use something else. And you have to do it table
by table, which will really suck since we already know your whole db has
bloated, not just one or two tables.

do we know that ? many of the tables are fairly static.

only this one is seriously borked, and yet other related tables seem to be
fine.

You're going to be doing some scripting, buddy. :) Well, unless you just do
a dump/restore and start over with sane postgresql.conf settings.

well who knew the defaults were unsane ? :)

scripting this is trivial, I already have the script

I have made the mistake of doing VACUUM FULL in the past. in fact on this
table, and it had to be killed because it took down my entire website !
that may well be the major borking event. a credit to postgres that the
table still functions if that's the case.

scott marlowe:

begin;
select * into temporaryholdingtable order by somefield;
truncate oldtable;
insert into oldtables select * from temporaryholdingtable;
commit;

that sounds like a good approach.

gentlemen, 300,000 + thanks for your generous time !
(a small number, I know)

-felix

Re: [PERFORM] Really really slow select count(*)

On Fri, Feb 4, 2011 at 12:26 PM, felix crucialfe...@gmail.com 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 mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Really really slow select count(*)


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 with the -U for a superuser 
in the database. Like the postgres user.



but this means I have to manually run cluster from time to time, right ?
not that there will be much or any reordering.  or it should be fine
going forward with vacuum and enlarging the free space memory map.


It should be fine going forward. You only need to re-cluster if you want 
to force the table to remain in the order you chose, since it doesn't 
maintain the order for updates and new inserts. Since you're only doing 
it as a cleanup, that's not a concern for you.



do we know that ?  many of the tables are fairly static. only this
one is seriously borked, and yet other related tables seem to be
fine.


Probably not in your case. I just mean that any non-static table is 
going to have this problem. If you know what those are, great. I don't 
usually have that luxury, so I err on the side of assuming the whole DB 
is borked. :)


Also, here's a query you may find useful in the future. It reports the 
top 20 tables by size, but also reports the row counts and what not. 
It's a good way to find possibly bloated tables, or tables you could 
archive:


SELECT n.nspname AS schema_name, c.relname AS table_name,
   c.reltuples AS row_count,
   c.relpages*8/1024 AS mb_used,
   pg_total_relation_size(c.oid)/1024/1024 AS total_mb_used
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid=c.relnamespace)
 WHERE c.relkind = 'r'
 ORDER BY total_mb_used DESC
 LIMIT 20;

The total_mb_used column is the table + all of the indexes and toast 
table space. The mb_used is just for the table itself. This will also 
help you see index bloat, or if a table has too much toasted data.



well who knew the defaults were unsane ? :)


Not really unsane, but for any large database, they're not ideal. This 
also goes for the default_statistics_target setting. If you haven't 
already, you may want to bump this up to 100 from the default of 10. Not 
enough stats can make the planner ignore indexes and other bad things, 
and it sounds like your DB is big enough to benefit from that.


Later versions have made 100 the default, so you'd just be catching up. :)

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)

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 scott.marl...@gmail.comwrote:

 On Fri, Feb 4, 2011 at 12:26 PM, felix crucialfe...@gmail.com 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.

Re: [PERFORM] Really really slow select count(*)


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_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Really really slow select count(*)

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 (n.oid=c.relnamespace)
ns-  WHERE c.relkind = 'r'
ns-  ORDER BY total_mb_used DESC
ns-  LIMIT 20;
 schema_name |table_name|  row_count  | mb_used |
total_mb_used
-+--+-+-+---
 public  | django_session   | 1.47843e+07 |4122 |
  18832
 public  | traffic_tracking2010 | 9.81985e+06 | 811 |
   1653
 public  | mailer_mailingmessagelog | 7.20214e+06 | 441 |
   1082
 public  | auth_user| 3.20077e+06 | 572 |
791
 public  | fastadder_fastadderstatus|  302479 | 458 |
693
 public  | registration_registrationprofile | 3.01345e+06 | 248 |
404
 public  | reporting_dp_6c93734c|  1.1741e+06 |  82 |
224
 public  | peoplez_contact  |   79759 |  18 |
221
 public  | traffic_tracking201101   | 1.49972e+06 | 163 |
204
 public  | reporting_dp_a3439e2a| 1.32739e+06 |  82 |
187
 public  | nsproperties_apthistory  |   44906 |  69 |
126
 public  | nsproperties_apt |   30780 |  71 |
125
 public  | clients_showingrequest   |   85175 |  77 |
103
 public  | reporting_dp_4ffe04ad|  330252 |  26 |
 63
 public  | fastadder_fastadderstatuslog |  377402 |  28 |
 60
 public  | nsmailings_officememotoagent |  268345 |  15 |
 52
 public  | celery_taskmeta  |5041 |  12 |
 32
 public  | mailer_messagelog|  168298 |  24 |
 32
 public  | datapoints_job   |9167 |  12 |
 23
 public  | fastadder_fastadderstatus_errors |  146314 |   7 |
 21

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.

it won't run now because its too big, I can delete them from psql though

well just think how sprightly my website will run tomorrow once I fix these.




On Fri, Feb 4, 2011 at 9:00 PM, Shaun Thomas stho...@peak6.com wrote:

 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_disclaimer.php
 for terms and conditions related to this email

Re: [PERFORM] Really really slow select count(*)


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 better off deleting the inverse. You know, start a 
transaction, select all the sessions that *aren't* expired, truncate the 
table, insert them back into the session table, and commit.


BEGIN;
CREATE TEMP TABLE foo_1 AS
SELECT * FROM django_session WHERE date_expired  CURRENT_DATE;
TRUNCATE django_session;
INSERT INTO django_session SELECT * from foo_1;
COMMIT;

Except I don't actually know what the expired column is. You can figure 
that out pretty quick, I assume. That'll also have the benefit of 
cleaning up the indexes and the table all at once. If you just do a 
delete, the table won't change at all, except that it'll have less 
active records.



well just think how sprightly my website will run tomorrow once I fix
these.


Maybe. :)

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

[PERFORM] Re: getting the most of out multi-core systems for repeated complex SELECT statements

2011-02-04 Thread Mark Stosberg

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 PostgreSQL usage is through a web application which fairly
automatically takes advantage of multiple cores, because there are
several parallel connections.

A smaller but important piece of what we do is run this cron script
needs to run hundreds of thousands of variations of the same complex
SELECT as fast it can.

What honestly would have helped most is not technical at all-- it would
have been some documentation on how to take advantage of multiple cores
for this case.

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 application 4 ways across 4 processors.
(I think we failed to achieve a 4x improvement because the server was
already busy handling some other tasks).

Part of our case is likely fairly common *today*: many servers are
multi-core now, but people don't necessarily understand how to take
advantage of that if it doesn't happen automatically.

Mark

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

[PERFORM] checkpoint_completion_target and Ext3

2011-02-04 Thread Josh Berkus

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.
 http://www.pgexperts.com

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

Re: [PERFORM] Re: getting the most of out multi-core systems for repeated complex SELECT statements

On Fri, Feb 4, 2011 at 2:18 PM, Mark Stosberg m...@summersault.com 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 application 4 ways across 4 processors.
 (I think we failed to achieve a 4x improvement because the server was
 already busy handling some other tasks).

I once had about 2 months of machine work ahead of me for one server.
Luckily it was easy to break up into chunks and run it on all the
workstations at night in the office, and we were done in  1 week.
pgsql was the data store for it, and it was just like what you're
talking about, break it into chunks, spread it around.

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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Bruce Momjian

Greg Smith wrote:
 Check out 
 http://www.indeed.com/jobtrends?q=postgres%2C+mysql%2C+oraclerelative=1relative=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 troublesome legacy product.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Bruce Momjian

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 that decision?
 
  Nobody has ever proposed a way to add hints where consensus was arrived
  at that the way was good, so...

 
 So, I will have to go back on my decision to use Postgres and 
 re-consider MySQL? I will rather throw away the effort invested in 

You want to reconsider using MySQL because Postgres doesn't have hints. 
Hard to see how that logic works.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

[PERFORM] table partitioning and select max(id)

2011-02-04 Thread Tobias Brox

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 max(id) on both partitions are fast.
Are there any tricks I can do to speed up this query?  I can't add the
ID to the table constraints, we may still get in old data causing
rows with fresh IDs to get into the old table.

(I decided to keep this short rather than include lots of details -
but at least worth mentioning that we're using PG9)

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

Re: [PERFORM] Talking about optimizer, my long dream

2011-02-04 Thread Mark Kirkwood


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 projects, tools provided by other major databases. This 
discussion served my purpose wonderfully.  Project is killed, here we 
part ways. No more problems for either of us. Good luck with the 
perfect optimizer and good riddance. My only regret is about the 
time I have wasted.




I think it is unlikely that your boss is going to dismiss Postgres on 
the basis of some minor technical point (no optimizer hints). Bosses 
usually (and should) care about stuff like reference sites, product 
pedigree and product usage in similar sized companies to theirs. 
Postgres will come out rather well if such an assessment is actually 
performed I would think.


The real question you should be asking is this:

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).


Best wishes

Mark




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

Re: [HACKERS] [PERFORM] Slow count(*) again...

2011-02-04 Thread Bruce Momjian

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 and without hints.
 2) All other databases have them. This is a major feature and if I were 
 in the MySQL camp, I would use it as an
argument. Asking me for some proof is missing the point. All other 
 databases have hints precisely because
they are useful. Assertion that only Postgres is so smart that can 
 operate without hints doesn't match the
reality. As a matter of fact, Oracle RDBMS on the same machine will 
 regularly beat PgSQL in performance.
That has been my experience so far.   I even posted counting query 
 results.
 3) Hints are make it or break it feature. They're absolutely needed in 
 the fire extinguishing situations.
 
 I see no arguments to say otherwise and until that ridiculous we don't 
 want hints dogma is on wiki, this is precisely what it is:  a dogma. 

Uh, that is kind of funny considering that text is on a 'wiki', meaning
everything there is open to change if the group agrees.

 Dogmas do not change and I am sorry that you don't see it that way. 
 However, this discussion
 did convince me that I need to take another look at MySQL and tone down 
 my engagement with PostgreSQL community. This is my last post on the 
 subject because posts are becoming increasingly personal. This level of 
 irritation is also
 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 
 about hints is equivalent to saying that all these databases are 
 developed by idiots and have a crappy optimizer.

You need to state the case for hints independent of what other databases
do, and indepdendent of fixing the problems where the optimizer doesn't
match reatility.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

2011-02-04 Thread Chris Browne

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 extent, and
 they've realized that the BCNF schema on such machines is supremely
 efficient.  PG/MySql/OSEngineOfChoice will get left behind simply
 because the efficiency offered will be worth the price.

 I know this is far from trivial, and my C skills are such that I can
 offer no help.  These machines have been the obvious current machine
 in waiting for at least 5 years, and those applications which benefit
 from parallelism (servers of all kinds, in particular) will filter out
 the winners and losers based on exploiting this parallelism.

 Much as it pains me to say it, but the MicroSoft approach to software:
 write to the next generation processor and force users to upgrade,
 will be the winning strategy for database engines.  There's just way
 too much to gain.

I'm not sure how true that is, really.  (e.g. - too much to gain.)

I know that Jan Wieck and I have been bouncing thoughts on valid use of
threading off each other for *years*, now, and it tends to be
interesting but difficult to the point of impracticality.

But how things play out are quite fundamentally different for different
usage models.

It's useful to cross items off the list, so we're left with the tough
ones that are actually a problem.

1.  For instance, OLTP applications, that generate a lot of concurrent
connections, already do perfectly well in scaling on multi-core systems.
Each connection is a separate process, and that already harnesses
multi-core systems perfectly well.  Things have improved a lot over the
last 10 years, and there may yet be further improvements to be found,
but it seems pretty reasonable to me to say that the OLTP scenario can
be treated as solved in this context.

The scenario where I can squint and see value in trying to multithread
is the contrast to that, of OLAP.  The case where we only use a single
core, today, is where there's only a single connection, and a single
query, running.

But that can reasonably be further constrained; not every
single-connection query could be improved by trying to spread work
across cores.  We need to add some further assumptions:

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.

That condition can be somewhat further strengthened...  It further needs
to be a query where multi-processing would not increase the I/O burden.

Between those two assumptions, that cuts the scope of usefulness to a
very considerable degree.

And if we *are* multiprocessing, we introduce several new problems, each
of which is quite troublesome:

 - How do we decompose the query so that the pieces are processed in
   ways that improve processing time?

   In effect, how to generate a parallel query plan?

   It would be more than stupid to consider this to be obvious.  We've
   got 15-ish years worth of query optimization efforts that have gone
   into Postgres, and many of those changes were not obvious until
   after they got thought through carefully.  This multiplies the
   complexity, and opportunity for error.

 - Coordinating processing

   Becomes quite a bit more complex.  Multiple threads/processes are
   accessing parts of the same data concurrently, so a parallelized
   query that harnesses 8 CPUs might generate 8x as many locks and
   analogous coordination points.

 - Platform specificity

   Threading is a problem in that each OS platform has its own
   implementation, and even when they claim to conform to common
   standards, they still have somewhat different interpretations.  This
   tends to go in one of the following directions:

a) You have to pick one platform to do threading on.

   Oops.  There's now PostgreSQL-Linux, that is the only platform
   where our multiprocessing thing works.  It could be worse than
   that; it might work on a particular version of a particular OS...

b) You follow some apparently portable threading standard

   And find that things are hugely buggy because the platforms
   follow the standard a bit differently.  And perhaps this means
   that, analogous to a), you've got a set of platforms where this
   works (for some value of works), and others where it can't.
   That's almost as evil as a).

c) You follow some apparently portable threading standard

   And need to wrap things in a pretty thick safety blanket to make
   sure it is compatible with all the bugs in interpretation and
   implementation.  Complexity++, and performance probably suffers.

   None of these are particularly palatable, which is why threading
   proposals get a lot of pushback.

At the end of the day, if this is

Re: [PERFORM] Does auto-analyze work on dirty writes?

2011-02-04 Thread Mark Mielke


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 transactions
still in progress.

2. Only live rows are included in the stats computed by ANALYZE.
(IIRC it uses SnapshotNow to decide whether rows are live.)

Although the stats collector does track an estimate of the number of
dead rows for the benefit of autovacuum, this isn't used by planning.
Table bloat is accounted for only in terms of growth of the physical
size of the table in blocks.


Thanks, Tom.

Does this un-analyzed bloat not impact queries? I guess the worst case 
here is if autovaccum is disabled for some reason and 99% of the table 
is dead rows. If I understand the above correctly, I think analyze might 
generate a bad plan under this scenario, thinking that a value is 
unique, using the index - but every tuple in the index has the same 
value and each has to be looked up in the table to see if it is visible?


Still, I guess the idea here is not to disable autovacuum, making dead 
rows insignificant in the grand scheme of things. I haven't specifically 
noticed any performance problems here - PostgreSQL is working great for 
me as usual. Just curiosity...


Cheers,
mark

--
Mark Mielkem...@mielke.cc


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

Re: [PERFORM] Query performance with disabled hashjoin and mergejoin

2011-02-04 Thread Ivan Voras


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 you have a bit more
than that. The first things to try here are the list at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad
query here looks like it might benefit from a large increase to
effective_cache_size, and possibly an increase to work_mem as well. Your
bad plan here is doing a lot of sequential scans instead of indexed
lookups, which makes me wonder if the change in join types you're
forcing isn't fixing that part as a coincidence.


My earlier message didn't get through so here's a repeat:

Sorry for the confusion, by default settings I meant planner default 
settings not generic shared buffers, wal logs, work memory etc. - which 
are adequately tuned.



Note that the estimated number of rows coming out of each form of plan
is off by a factor of about 200X, so it's not that the other plan type
is better estimating anything.


Any ideas how to fix the estimates? Or will I have to simulate hints by 
issuing set enable_hashjoin=f; set enable_mergejoin=f; for this query? :)




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

Re: [PERFORM] table partitioning and select max(id)

2011-02-04 Thread Ken Cox

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/msg00036.php

On Fri, Feb 4, 2011 at 6:24 PM, Tobias Brox tobi...@gmail.com 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.  Both
 partitions are set up with primary key index and draws new IDs from
 the same sequence ... select max(id) on both partitions are fast.
 Are there any tricks I can do to speed up this query?  I can't add the
 ID to the table constraints, we may still get in old data causing
 rows with fresh IDs to get into the old table.

 (I decided to keep this short rather than include lots of details -
 but at least worth mentioning that we're using PG9)

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




-- 
-Ken

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

Re: [HACKERS] [PERFORM] Slow count(*) again...

On Fri, Feb 4, 2011 at 5:17 PM, Bruce Momjian br...@momjian.us 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
 about hints is equivalent to saying that all these databases are
 developed by idiots and have a crappy optimizer.

 You need to state the case for hints independent of what other databases
 do, and indepdendent of fixing the problems where the optimizer doesn't
 match reatility.

And that kind of limits to an area where we would the ability to nudge
costs instead of just set them for an individual part of a query.
i.e. join b on (a.a=b.b set selectivity=0.01) or (a.a=b.b set
selectivity=1.0) or something like that.  i.e. a.a and b.b have a lot
of matches or few, etc.  If there's any thought of hinting it should
be something that a DBA, knowing his data model well, WILL know more
than the current planner because the planner can't get cross table
statistics yet.

But then, why not do something to allow cross table indexes and / or
statistics?  To me that would go much further to helping fix the
issues where the current planner flies blind.

-- 
To understand recursion, one must first understand recursion.

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

Re: [PERFORM] Talking about optimizer, my long dream

2011/2/4 Mark Kirkwood mark.kirkw...@catalyst.net.nz:
 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).

That is EXACTLY what happened to me.  I had a query killing my
production box because it was running VERY long by picking the wrong
plan.  Turned out it was ignoring the number of NULLs and this led to
it thinking one access method that was wrong was the right one.  I had
a patch within 24 hours of identifying the problem, and it took me  1
hour to have it applied and running in production.

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.

My point in the other thread is that if you can identify a point where
a hint would help, like my situation above, you're often better off
presenting a test case here and getting a patch to make it smarter.

However, there are places where the planner just kind of guesses.  And
those are the places to attack when you find a pathological behaviour.
 Or to rewrite your query or use a functional index.

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

Re: [PERFORM] table partitioning and select max(id)