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

felix wrote: So is it normal for postgres to report that it failed to shut down, operate for an hour and then go ahead and restart itself ? You've already gotten a few explanations for why waiting for connections can cause this. I'll only add that it is critical to be watching the database l

Re: [PERFORM] Different execution plans for semantically equivalent queries

Hi Tom et al, Many thanks for your prompt reply - you wrote: >> SELECT * FROM table t1 WHERE 0 = (SELECT COUNT(*) FROM table t2 WHERE >> t2.type = t1.type AND t2.timestamp > t1.timestamp) > > I suspect that *any* database is going to have trouble optimizing that. Okay, I expected that much.

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

> I checked the site, everything was normal. I went in via psql and tried some > queries for about half an hour and continued to monitor the site. then I went > to bed at 7am (EU time). > > Why did it shutdown so much later ? That’s one of the things I talked about. To be safe, PG will start to

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

You really got screwed by the default settings. You don’t actually need to “hack” the kernel, but you do have to make these changes, because the amount of memory PG has on your system is laughable. That might actually be the majority of your problem. In your /etc/sysctl.conf, you need these lin

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

On Sun, Feb 6, 2011 at 6:52 PM, felix wrote: > On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe > wrote: >> >> Let's review: >> >> 1: No test or staging system used before production > > no, I do not have a full ubuntu machine replicating the exact memory and > application load of the production ser

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

On 07/02/11 09:52, felix wrote: > So is it normal for postgres to report that it failed to shut down, > operate for an hour and then go ahead and restart itself ? That's pretty wacky. Did you shut it down via pg_ctl or using an init script / "service" command in your OS? It shouldn't matter, bu

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

yeah, it already uses memcached with db save. nothing important in session anyway the session table is not the issue and I never clustered that one or ever will thanks for the tip, also the other one about HOT On Sun, Feb 6, 2011 at 8:19 PM, Pierre C wrote: > > I have clustered that table,

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

On Sun, Feb 6, 2011 at 4:23 PM, Scott Marlowe wrote: > Let's review: > 1: No test or staging system used before production > no, I do not have a full ubuntu machine replicating the exact memory and application load of the production server. this was changing one configuration parameter. someth

Re: [PERFORM] Need some help analyzing some benchmarks

Benjamin Krajmalnik wrote: My data partition is 12 drives in RAID 1+0 (2.7TB) running UFS2. Vfs.read_max has been set to 32, and no other tuning has been done... Not having anything to compare it to, I do not know if these are decent numbers or not -- they are definitely slower than a simi

Re: [PERFORM] general hardware advice

ah ok, i was not sure about this point, thanks. Regards, Miguel Angel El 06/02/11 21:38, Andy escribió: --- On Sun, 2/6/11, Linos wrote: I am studying too the possibility of use an OCZ Vertex 2 Pro with Flashcache or Bcache to use it like a second level filesystem cache, any comments on tha

Re: [PERFORM] Different execution plans for semantically equivalent queries

Mikkel Lauritsen writes: > I would like to do a query which retrieves the newest record for each > type, and the persistence framework that I'm using does something > which is structurally like > SELECT * FROM table t1 WHERE 0 = (SELECT COUNT(*) FROM table t2 WHERE > t2.type = t1.type AND t2.

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

Sorry for the misunderstaning: of course not default "normal" settings; shared buffers, work mem, wal segments and others have been tuned according to available hardware (e.g. 4 GB, 32 MB, 10 for these settings, respectively). I meant "planner default settings" in the post. -- Sent from my Andr

Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] [PERFORM] Slow count(*) again...)

Mark Mielke writes: > My understanding is: > 1) Background daemon wakes up and checks whether a number of changes > have happened to the database, irrelevant of transaction boundaries. > 2) Background daemon analyzes a percentage of rows in the database for > statistical data, irrelevant of ro

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

Please, don't include me on your emails. I unsubscribed from the list. Cédric Villemain wrote: 2011/2/4 Frank Heikens : On 04 Feb, 2011,at 02:56 PM, Mladen Gogala wrote: Віталій Тимчишин wrote: Hi, all. All this optimizer vs hint thread There is no "optimizer vs. hint". Hin

[PERFORM] Different execution plans for semantically equivalent queries

Hi all, I have a execution planner related issue that I'd like to have some help in understanding a bit deeper - I have a table which basically contains fields for a value, a timestamp and a record type which is an integer. I would like to do a query which retrieves the newest record for each typ

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

Informix IDS supports hints as well; normally the only need for hints in this engine is when the Table/Index statistics are not being updated on a regular basis (ie: lazy DBA). On 3 February 2011 22:17, Mark Kirkwood wrote: > On 04/02/11 11:08, Josh Berkus wrote: > >> I don't think that's actual

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

On 02/04/2011 02:32 AM, da...@lang.hm wrote: when a copy command is issued, I assume that there is some indication of how much data is going to follow. No of course there isn't. How would we do that with a stream like STDIN? Read the code. cheers andrew -- Sent via pgsql-performance

Re: [PERFORM] general hardware advice

--- On Sun, 2/6/11, Linos wrote: > I am studying too the possibility of use an OCZ Vertex 2 > Pro with Flashcache or Bcache to use it like a second level > filesystem cache, any comments on that please? > OCZ Vertex 2 Pro is a lot more expensive than other SSD of comparable performances beca

Re: [PERFORM] general hardware advice

El 06/02/11 20:24, Pierre C escribió: On Sun, 06 Feb 2011 19:16:23 +0100, Linos wrote: I am searching what would be the best hardware combination to a new server i have to install, the new server will have a Postgresql 9.0 with a database of about 10gb, the database part it is not the problem

Re: [PERFORM] general hardware advice

El 06/02/11 20:05, Scott Marlowe escribió: On Sun, Feb 6, 2011 at 11:16 AM, Linos wrote: I am searching what would be the best hardware combination to a new server i have to install, the new server will have a Postgresql 9.0 with a database of about 10gb, the database part it is not the problem

Re: [PERFORM] general hardware advice

On Sun, 06 Feb 2011 19:16:23 +0100, Linos wrote: I am searching what would be the best hardware combination to a new server i have to install, the new server will have a Postgresql 9.0 with a database of about 10gb, the database part it is not the problem for me, in this size almost, the p

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

On Sun, Feb 6, 2011 at 12:19 PM, Pierre C wrote: > >>> I have clustered that table, its still unbelievably slow. >> >> Did you actually delete the old entries before clustering it?  if it's >> still got 4G of old sessions or whatever in it, clustering ain't gonna >> help. > > Also, IMHO it is a lo

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

I have clustered that table, its still unbelievably slow. Did you actually delete the old entries before clustering it? if it's still got 4G of old sessions or whatever in it, clustering ain't gonna help. Also, IMHO it is a lot better to store sessions in something like memcached, rather

Re: [PERFORM] general hardware advice

On Sun, Feb 6, 2011 at 11:16 AM, Linos wrote: > I am searching what would be the best hardware combination to a new server i > have to install, the new server will have a Postgresql 9.0 with a database > of about 10gb, the database part it is not the problem for me, in this size > almost, the part

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

On Fri, Feb 4, 2011 at 1:14 PM, felix wrote: >  schema_name |            table_name            |  row_count  | mb_used | > total_mb_used > -+--+-+-+--- >  public      | django_session                   | 1.47843e+07 |    4

[PERFORM] general hardware advice

I am searching what would be the best hardware combination to a new server i have to install, the new server will have a Postgresql 9.0 with a database of about 10gb, the database part it is not the problem for me, in this size almost, the part where i am a bit lost is that i have to share from

Re: [PERFORM] copy command and blobs

On Sat, Jan 22, 2011 at 8:41 PM, Robert Haas wrote: > On Fri, Jan 21, 2011 at 5:10 PM, Madhu Ramachandran > wrote: > > i was looking at > > http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html > > when they talk about using OID type to store large blobs (in my case .jpg > > f

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

On Sun, Feb 6, 2011 at 3:48 AM, felix wrote: > BRUTAL > SNIP > OK, my mistake.   probably I have to disconnect all clients.  I don't want > to do a "planned maintenance" right now. > so I go to sleep > the server restarts itself an hour later. > but no, it fails to restart because this memory set

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

On Sun, Feb 06, 2011 at 11:48:50AM +0100, felix wrote: > BRUTAL > Did the changes work in your test environment? -- 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] Which RAID Controllers to pick/avoid?

On Sun, 6 Feb 2011, Scott Marlowe wrote: On Sun, Feb 6, 2011 at 2:39 AM, Royce Ausburn wrote: On Wed, Feb 2, 2011 at 7:00 PM, Craig Ringer wrote: Whatever RAID controller you get, make sure you have a battery backup unit (BBU) installed so you can safely enable write-back caching. Without

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

BRUTAL http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html max_fsm_pages See Section 17.4.1 for information on how to adjust those parameters, if necessary. I see absolutely nothing in there a

Re: [PERFORM] Which RAID Controllers to pick/avoid?

On Sun, Feb 6, 2011 at 2:39 AM, Royce Ausburn wrote: > >> On Wed, Feb 2, 2011 at 7:00 PM, Craig Ringer >> wrote: >>> Whatever RAID controller you get, make sure you have a battery backup >>> unit (BBU) installed so you can safely enable write-back caching. >>> Without that, you might as well use

Re: [PERFORM] Which RAID Controllers to pick/avoid?

> On Wed, Feb 2, 2011 at 7:00 PM, Craig Ringer > wrote: >> Whatever RAID controller you get, make sure you have a battery backup >> unit (BBU) installed so you can safely enable write-back caching. >> Without that, you might as well use software RAID - it'll generally be >> faster (and cheaper) t

Re: [PERFORM] checkpoint_completion_target and Ext3

Josh Berkus wrote: 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? Setting that to 0.0 gives the same basic behavior as in 8.2 and earlier versions. Those had even worst I/O spikes issues. Even on ext3,

[PERFORM] Need some help analyzing some benchmarks

Before I deploy some new servers, I figured I would do some benchmarking. Server is a Dual E5620, 96GB RAM, 16 x 450GB SAS(15K) drives. Controller is an Areca 1680 with 2GB RAM and battery backup. So far I have only run bonie++ since each cycle is quite long (writing 192GB). My data partitio