Re: [PERFORM] Cost estimate vs. actual - do I care?

;re fine. 2. You should try to ensure that costs go up linearly with actual time. 3. You should try to ensure that costs are as close as possible to actual time. 4. The number "4". Jay Levitt -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

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

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 Mielke -- Sent via pgsql-performance m

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

mitted, or because they are not yet vacuumed. Would somebody in the know please confirm the above understanding for my own piece of mind? Thanks, mark -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscrip

Re: [PERFORM] wal_synch_method = open_sync safe on RHEL 5.5?

-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Mark Mielke

Re: [PERFORM] SSD + RAID

On 02/23/2010 04:22 PM, da...@lang.hm wrote: On Tue, 23 Feb 2010, Aidan Van Dyk wrote: * da...@lang.hm [100223 15:05]: However, one thing that you do not get protection against with software raid is the potential for the writes to hit some drives but not others. If this happens the software

Re: [PERFORM] SSD + RAID

On 02/22/2010 08:04 PM, Greg Smith wrote: Arjen van der Meijden wrote: That's weird. Intel's SSD's didn't have a write cache afaik: "I asked Intel about this and it turns out that the DRAM on the Intel drive isn't used for user data because of the risk of data loss, instead it is used as memor

Re: [PERFORM] SATA drives performance

schedule - the databases are small enough to afford this :-) ) Cheers, mark -- Mark Mielke -- 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] SSD + RAID

le that the reduced power utilization of SSD allows for a capacitor to complete all scheduled writes, even with a large cache? Is it this particular drive you are suggesting that is known to be insufficient or is it really the technology or maturity of the technology? Cheers, mark -- Mark M

Re: [PERFORM] UUID as primary key

= 2 x UUID. If you want it to be seemless and fully optimal, you would introduce a new int256 type (or whatever the name of the type you are trying to represent). Adding new types to PostgreSQL is not that hard. This would allow queries (=, <>, <, >) as well. Cheers, mark --

Re: [PERFORM] UUID as primary key

UUID anyways. If you need UUID anyways - having two primary keys is probably not worth it. Cheers, mark -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Maybe OT, not sure Re: [PERFORM] Best suiting OS

This is kind of OT, unless somebody really is concerned with understanding the + and - of distributions, and is willing to believe the content of this thread as being accurate and objective... :-) On 10/04/2009 08:42 PM, Scott Marlowe wrote: On Sun, Oct 4, 2009 at 8:05 AM, Mark Mielke wrote

Re: [PERFORM] Best suiting OS

On 10/04/2009 01:55 PM, Devrim GÜNDÜZ wrote: On Sun, 2009-10-04 at 10:05 -0400, Mark Mielke wrote: So any comparisons between operating system *distributions* should be fair. Comparing a 2007 release to a 2009 release, for example, is not fair. RHEL / CentOS are basically out of the running

Re: [PERFORM] Best suiting OS

3 months. In the case of RHEL, waiting about 4 months will give you RHEL 6.0 which will be within 3 to 9 months of the leading edge. If you are planning a new deployment - this might be something to consider. I suggest not going with RHEL 5 at this time... Cheers, mark -- Mark Mie

Re: [PERFORM] Best suiting OS

imple to maintain. I recently documented the instructions for another team and they fit within about 10 lines that could be cut + pasted. Cheers, mark -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.pos

Re: [PERFORM] Best suiting OS

s how to manage. Just throwing that out there. :-) Cheers, mark -- Mark Mielke -- 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] What exactly is postgres doing during INSERT/UPDATE ?

ve may not be enough. RAID 1+0 on the other hand, has never disappointed me yet. Disks are cheap, and paying x2 for single disk redundancy is an acceptable price. Cheers, mark -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to you

Re: [PERFORM] Memory reporting on CentOS Linux

quot;cached"? It's hardly unusual for top to give bogus numbers in the presence of shared memory, of course, but this seems odd :-(. With such large amounts of RAM involved I wonder if there could be an overflow problem. You might file a bug against top in whatever distro you are using

Re: [PERFORM] Will Postgres ever lock with read only queries?

I've never had straight queries block each other. What is the query? What version of PostgreSQL? What operating system? Cheers, mark -- Mark Mielke -- 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] hyperthreaded cpu still an issue in 8.4?

ame as processor. X 2 threads is usually significantly less benefit than X 2 cores. X 2 cores is probably less benefit than X 2 processors. I think the Intel numbers says that Intel HT provides +15% performance on average. Cheers, mark -- Mark Mielke

Re: [PERFORM] Six PostgreSQL questions from a pokerplayer

ight with "low memory" vs "high memory", whereas 64-bit has a clean address space. All things being equal, I recommend 64-bit. Cheers, mark -- Mark Mielke

Re: [PERFORM] Bundling postgreSQL with my Java application

se maintenance. If autovacuum is really running for you - I would look as to whether you have the right indexes defined and/or whether you are actually using them? Cheers, mark -- Mark Mielke -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Bundling postgreSQL with my Java application

"autovacuum" to be enabled. Cheers, mark -- Mark Mielke -- 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] Nested Loop "Killer" on 8.1

ate Any reason why "like" with a constant string without % or _ is not optimized to = today? Cheers, mark -- Mark Mielke

Re: [PERFORM] Scalability in postgres

hange came from applied theory, not from profiling. Bitmap indexes are an example of this. Profiling tells you what - that large joins involving OR are slow? It takes theory to answer "why" and "so, what do we do about it?" Cheers, mark -- Mark Mielke -- 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] Scalability in postgres

da...@lang.hm wrote: On Thu, 4 Jun 2009, Mark Mielke wrote: You should really only have as 1X or 2X many threads as there are CPUs waiting on one monitor. Beyond that is waste. The idle threads can be pooled away, and only activated (with individual monitors which can be far more easily and

Re: [PERFORM] Scalability in postgres

cost of connection "pooling" in the sense of requests always being indirect through a proxy of some sort. Cheers, mark -- Mark Mielke

Re: [PERFORM] RAID arrays and performance

everything you want to query into a temporary table, then SELECT to join the results, and pull all of the results, doing additional processing (UPDATE) as you pull? Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] Postgres not using array

to be significant and possibly out-perform the 3.0 Ghz x 1. If you usually only have one query running at the same time, I expect the 3.0 Ghz x 1 to always win. PostgreSQL isn't good at splitting the load from a single client across multiple CPU cores. Cheers, mark -- Mark Mielke &l

Re: [PERFORM] file system and raid performance

ity requirements of these other purposes must be considered on their own." Personally, I use data=writeback for most purposes, but use data=journal for /mail and /home. In these cases, I find even the default ext3 mode to be fewer guarantees than I am comfortable with. :-) Cheers, mark

Re: [PERFORM] Using PK value as a String

of all-random 64-bit integers with a good random number source. :-) ) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- 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] Using PK value as a String

Gregory Stark wrote: "Mark Mielke" <[EMAIL PROTECTED]> writes: - Increased keyspace. Even if keyspace allocation is performed, an int4 only has 32-bit of keyspace to allocate. The IPv4 address space is already over 85% allocated as an example of how this can happen. 12

Re: [PERFORM] Using PK value as a String

in case" measure, that suffers the performance cost, "just in case." Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] file system and raid performance

ething like this. The effect is that modern Linux distributions do not benefit from "noatime" as much as they have in the past. In this case, "noatime" vs default would probably be measuring % noise. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] ??: Postgresql update op is very very slow

r stored in a built state, but is only kept as pointers that allow any part of the table to be re-built on access. The UPDATE statement could be recorded cheaply, but queries against the UPDATE statement might be very expensive. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> --

Re: [PERFORM] Posible planner improvement?

first or second, or is a third speed entirely? If t1.id = t2.id, I would expect the planner to substitute them freely in terms of identities? Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To mak

Re: [PERFORM] Group by more efficient than distinct?

Matthew Wakeling wrote: On Tue, 22 Apr 2008, Mark Mielke wrote: The poster I responded to said that the memory required for a hash join was relative to the number of distinct values, not the number of rows. They gave an example of millions of rows, but only a few distinct values. Above, you

Re: [PERFORM] Group by more efficient than distinct?

Matthew Wakeling wrote: On Mon, 21 Apr 2008, Mark Mielke wrote: This surprises me - hash values are lossy, so it must still need to confirm against the real list of values, which at a minimum should require references to the rows to check against? Is PostgreSQL doing something beyond my

Re: [PERFORM] Group by more efficient than distinct?

Mark Mielke wrote: PFC wrote: Actually, the memory used by the hash depends on the number of distinct values, not the number of rows which are processed... Consider : SELECT a GROUP BY a SELECT a,count(*) GROUP BY a In both cases the hash only holds discinct values. So if you

Re: [PERFORM] Group by more efficient than distinct?

ist of values, which at a minimum should require references to the rows to check against? Is PostgreSQL doing something beyond my imagination? :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make cha

Re: [PERFORM] SQL Function Slowness, 8.3.0

functions? Thanks, Gavin -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] POSIX file updates

your email relies on the premise that POSIX enforces such a thing, or that systems are POSIX compliant. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.pos

Re: [PERFORM] count * performance issue

s not influence this truth. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] count * performance issue

her you are saying it still has to scan the index - which can take time if the index is large (therefore not instantaneous). Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] count * performance issue

help from the only place you know to get it from, but some of your recent answers have shown that you are either not reading the helpful responses provided to you, or you are unwilling to do your own research. If that continues, I won't be posting to aid you. Cheers, mark -- Mark Miel

Re: [PERFORM] count * performance issue

han a table scan for databases that can accurately determine counts using only the index, but it's still a relatively slow operation, and people don't normally need an accurate count for records in the range of 100,000+? :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] How to allocate 8 disks

Matthew wrote: On Mon, 3 Mar 2008, Mark Mielke wrote: Has anybody been able to prove to themselves that RAID 0 vs RAID 1+0 is faster for these sorts of loads? My understanding is that RAID 1+0 *can* reduce latency for reads, but that it relies on random access, whereas RAID 0 performs best

Re: [PERFORM] How to allocate 8 disks

ess to make RAID 1+0 shine? Curious. Thanks, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

Bill Moran wrote: In response to Mark Mielke <[EMAIL PROTECTED]>: Bill Moran wrote: I'm fairly sure that FreeBSD's GEOM does. Of course, it couldn't be doing consistency checking at that point. According to this: http://www.freebsd.org/cgi/man.cgi?qu

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

system (either cost, or because of the system size), I would suggest RAID 1+0 on all four as sensible compromise. If you can put more in - start to consider breaking it up. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)-

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

(either on the controller or a solid-state drive on a journaled filesystem would work) Yep. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

active components. This is the default balance algorithm. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

all. I note that you also disagree with Dave, in that you are not claiming it performs consistency checks on read. No system does this as performance would go to the crapper. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of bro

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

d be silliness. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

[EMAIL PROTECTED] wrote: On Wed, 26 Dec 2007, Mark Mielke wrote: Florian Weimer wrote: seek/read/calculate/seek/write since the drive moves on after the read), when you read you must read _all_ drives in the set to check the data integrity. I don't know of any RAID implementation

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

tup - I use RAID 1 across all four drives for the OS, RAID 1+0 for the database, wal, and other parts, and RAID 0 for a "build" partition. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

Dave had too much egg nog... :-) Yep - checking consistency on read would eliminate the performance benefits of RAID under any redundant configuration. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

believe hardware RAID 5 is also horrible, but since the hardware hides it from the application, a hardware RAID 5 user might not care. Software RAID 1+0 works fine on Linux with 4 disks. This is the setup I use for my personal server. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] Combining two bitmap scans out performs a single regular index scan?

Tom Lane wrote: Mark Mielke <[EMAIL PROTECTED]> writes: To find records after a certain time, I must do one of: select * from icpric where audtdate > ? or (audtdate = ? and audttime > ?) In recent releases (at least 8.2, don't remember about 8.1), a row comparison

[PERFORM] Combining two bitmap scans out performs a single regular index scan?

tisfactory amount of time - so I really don't care which I use. I thought these results might be interesting to somebody? The good thing is that bitmap scan seems to be well optimized. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] RAID arrays and performance

James Mansion wrote: Mark Mielke wrote: PostgreSQL or the kernel should already have the hottest pages in memory, so the value of doing async I/O is very likely the cooler pages that are unique to the query. We don't know what the cooler pages are until we follow three tree down.

Re: [PERFORM] RAID arrays and performance

James Mansion wrote: Mark Mielke wrote: At a minimum, this breaks your query into: 1) Preload all the index pages you will need Isn't this fairly predictable - the planner has chosen the index so it will be operating on a bounded subset. What is the bounded subset? It is bounded by the

Re: [PERFORM] RAID arrays and performance

Both look sexy on paper, neither may be the solution to your problem. Or they may be. We wouldn't know without numbers. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] RAID arrays and performance

Matthew wrote: On Tue, 4 Dec 2007, Mark Mielke wrote: The larger the set of requests, the closer the performance will scale to the number of discs This assumes that you can know which pages to fetch ahead of time - which you do not except for sequential read of a single table

Re: [PERFORM] RAID arrays and performance

James Mansion wrote: Mark Mielke wrote: This assumes that you can know which pages to fetch ahead of time - which you do not except for sequential read of a single table. Why doesn't it help to issue IO ahead-of-time requests when you are scanning an index? You can read-ahead in index

Re: [PERFORM] RAID arrays and performance

ad the wrong pages, it is wasting it's time. I am not trying to discourage you - only trying to ensure that you have reasonable expectations. 12X is far too optimistic. Please show one of your query plans and how you as a person would design which pages to request reads for. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] RAID arrays and performance

Matthew wrote: On Tue, 4 Dec 2007, Mark Mielke wrote: The bitmap scan method does ordered reads of the table, which can partially take advantage of sequential reads. Not sure whether bitmap scan is optimal for your situation or whether your situation would allow this to be taken advantage of

Re: [PERFORM] RAID arrays and performance

ing about a nearly non-existence problem. Just a suggestion... I recall talk of more intelligent table scanning algorithms, and the use of asynchronous I/O to benefit from RAID arrays, but the numbers prepared to convince people that the change would have effect have been less than impressiv

Re: [PERFORM] Union within View vs.Union of Views

have anything of value to provide either. :-) Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

Decibel! wrote: On Mon, Sep 10, 2007 at 06:22:06PM -0400, Mark Mielke wrote: In my case, I set effective_cache_size to 25% of the RAM available to the system (256 Mbytes), for a database that was about 100 Mbytes or less. I found performance to increase when reducing random_page_cost from

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

using 8.2. The last time I checked may have been 8.1. I am also curious to see what the current algorithm is with regard to effective_cache_size. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

th tagged queueing?) schedule which pages should be served best first. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

me. I think this means that the planner doesn't understand my database size : effective memory size ratio. :-) Anyways - my point is that if you change the default to 10 you may hurt people like me. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end o

Re: [PERFORM] Transaction Log

drive, but are filled with commodity RAM instead of a magnetic platter, and a battery that lasts a few weeks without external power. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] io storm on checkpoints, postgresql 8.2.4, linux

Are you able to show that the dirty pages are all coming from postgres? Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings