[PERFORM] Trigger or Function

2011-07-14 Thread alan
us the sum of DayVal for the previous 6 rows. MonthAvg = current row's DayVal plus the sum of DayVal for the previous 29 rows. Should I place the logic in a Trigger or in a Function? Does someone have an example or a link showing how I could set this up? Regards, Alan -- Sent via pgsql-perf

Re: [PERFORM] Trigger or Function

2011-07-29 Thread alan
7 | 2011-07-07 | hostb | 1.4286 |40. Alan -- 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] insert

2011-07-29 Thread alan
so cascaded to the product table. testdb=# UPDATE category SET category_id = 2 WHERE category_id = 1; UPDATE 1 testdb=# SELECT * FROM products; product_id | name | category ++-- 1 | Postgresql for Dummies |2 Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] insert

2011-07-29 Thread alan
more code on my client app (if ($cat_id = get_cat_id($cat)) }else { $cat_id = insert_cat($cat)}) Can I write a BEFORE ROW trigger for the products table to runs on INSERT or UPDATE to 1. insert a new category & return the new category_id OR 2. return the existing category_id fo

[PERFORM] delete/recreate indexes

2011-10-19 Thread alan
atum); COMMIT; But 1. it’s taking forever and 2. I’m seeing that my disk is filling up real fast. Any suggestions? Alan -- 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] delete/recreate indexes

2011-10-25 Thread alan
ext(CURRENT_DATE - interval '1 day'),'-MM-DD HH24:MI:SS')"; $insert->execute($device,$groupid,$timestamp,$val1,$val2)); Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] how to use explain analyze

2011-10-25 Thread alan
, and notify the user once resuilts are available } else { run the query and wait for the results in real time. } Thanks, Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

2012-09-14 Thread McKinzie, Alan (Alan)
Key/Unique Key is changed to deferred, due to assumptions the optimizer can or cannot make regarding whether the associated index is unique. But I have not found any negatives in regard to changing foreign key definitions to be deferrable. Thanks, Alan

Re: [PERFORM] Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

2012-09-17 Thread McKinzie, Alan (Alan)
Thanks for the information guys. And Yes, I am only updating the Foreign Key definitions to be deferrable. I am not modifying the Unique/Primary Key definitions. Thanks again, Alan -Original Message- From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Sunday, September 16, 2012

[PERFORM] How is memory allocated/used by Postgresql Database connections

2013-07-25 Thread McKinzie, Alan (Alan)
memory that would be allocated for a session (e.g. there is no multiplying factor like the work_mem for a session)? We are using Postgres 9.0.13 shared_buffers = 800MB work_mem = 1MB temp_buffers = 8MB (our applications do not use temp tables) effective_cache_size = 1500MB Thanks, Alan

Re: [PERFORM] How to improve db performance with $7K?

2005-04-15 Thread Alan Stange
he average seek time might be faster just because the 15K drives are smaller with fewer number of cylinders. -- Alan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [

Re: [PERFORM] How to improve db performance with $7K?

2005-04-18 Thread Alan Stange
The system will be somewhat unreliable because of the device count, additional SCSI buses, etc., but that too is life in the high performance world. -- Alan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[PERFORM] BG writer question?

2005-08-11 Thread Alan Stange
p and munmap each time?mmap and munmap are fairly expensive operations (on some systems), especially on multi cpu machines. munmap in particular generally needs to issue cross calls to the other cpus to ensure any page mappings are invalidated. Just curious. Than

[PERFORM] limit number of concurrent callers to a stored proc?

2005-08-17 Thread Alan Stange
time would be a good thing. Thanks! -- Alan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[PERFORM] unused item pointers?

2005-08-22 Thread Alan Stange
mount in the vacuum output. I don't think this is an issue here though as the large number of unused item pointers has been present for a while. Thanks! -- Alan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Read/Write block sizes

2005-08-23 Thread Alan Stange
rmance hit in user space with 4K pages compared to the kernel which would be mapping the "segmap" (in Solaris parlance) with 4MB pages. Anyway, I guess my point is that the balance between kernel managed vs. postgresql managed buffer isn't obvious at all. -- Alan ---

Re: [PERFORM] Caching by Postgres

2005-08-24 Thread Alan Stange
just because the time was taken to support large page sizes and more efficient data structures. It's nothing intrinsic to a 32 vs 64 bit kernel. -- Alan ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [PERFORM] Caching by Postgres

2005-08-24 Thread Alan Stange
[EMAIL PROTECTED] wrote: On Wed, Aug 24, 2005 at 02:47:09PM -0400, Alan Stange wrote: At least on Sparc processors, v8 and newer, any double precision math (including longs) is performed with a single instruction, just like for a 32 bit datum. Loads and stores of 8 byte datums are also

[PERFORM] difference in plan between 8.0 and 8.1?

2005-08-26 Thread Alan Stange
s) I'm hoping someone can explain the new query plan (as I'm not sure I understand what it is doing). Thanks! -- Alan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] difference in plan between 8.0 and 8.1?

2005-08-26 Thread Alan Stange
Tom Lane wrote: Alan Stange <[EMAIL PROTECTED]> writes: Unique (cost=2717137.08..2771407.21 rows=10854026 width=8) -> Sort (cost=2717137.08..2744272.14 rows=10854026 width=8) Sort Key: timeseriesid -> Bitmap Heap Scan on tbltimeseries (cost=48714.09

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

2005-09-12 Thread Alan Stange
that the XFS problems have all been corrected in newer kernels, but I'm not going to put too much effort into trying that again. I recently built a postgres with 32KB block sizes and have been doing some testing. For our particular workloads it has been a win. -- Alan

Re: [PERFORM] Performance on SUSE w/ reiserfs

2005-10-11 Thread Alan Stange
this can be quite beneficial depending on the memory patterns in your programs. -- Alan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Performance on SUSE w/ reiserfs

2005-10-11 Thread Alan Stange
Alex Turner wrote: Perhaps this is true for 1.5 on x86-32 (I've only used it on x86-64) but I was more thinking 1.4 which many folks are still using. The 1.4.x JVM's will also work just fine with much more than 1GB of memory. Perhaps you'd like to try again? -- Alan On

Re: [PERFORM] Is There Any Way ....

2005-10-24 Thread Alan Stange
y thought it would be good to have the backend doing a vacuum or analyze also call priocntl() prior to doing any real work to lower its priority. We'll be switching to the 8.1 release ASAP just because the direct IO capabilities are appearing to be a win on our development system. -- Alan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-17 Thread Alan Stange
the lighter one be less likely to break when dropped on the floor? -- Alan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 5:41 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: That's interesting, as I occasionally see more than 110MB/s of postgresql IO on our system. I'm using a 32KB block size, which has been a huge win in performance for o

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
lyze now takes about 3 hours, which is much shorter than before. Postgresql 8.1, dual opteron, 8GB memory, Linux 2.6.11, FC drives. -- Alan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subs

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 8:13 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: I told you in my initial post that I was observing numbers in excess of what you claiming, but you seemed to think I didn't know how to measure an IO rate. Prov

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1) on a 238GB table. avg-cpu: %user %nice%sys %iowait %idle 0.99

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
reading the data any more slowly than does any other program. And we don't have the time to experiment with the box. I know it should be better, but it's good enough for our purposes at this time. -- Alan ---(end of broadcast)--- TIP 5:

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Alan Stange
Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1)

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-19 Thread Alan Stange
Luke Lonergan wrote: Alan, On 11/18/05 11:39 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Yes and no. The one cpu is clearly idle. The second cpu is 40% busy and 60% idle (aka iowait in the above numbers). The "aka iowait" is the problem here - iowait

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-19 Thread Alan Stange
g of 120MB/s because I really don't understand? -- Alan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-20 Thread Alan Stange
William Yu wrote: Alan Stange wrote: Luke Lonergan wrote: The "aka iowait" is the problem here - iowait is not idle (otherwise it would be in the "idle" column). Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system,

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-20 Thread Alan Stange
Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system, and during your scan, as predicted, one CPU went 100% busy on the seq scan. During iowait periods, the CPU can be c

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-21 Thread Alan Stange
excess memory copies are being done and up this result substantially. I hope postgresql is always using the libc memcpy as that's going to be a lot faster then some private routine. -- Alan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-21 Thread Alan Stange
ant to use 1GB files for dd as well. Luke Lonergan wrote: Alan, On 11/21/05 6:57 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: $ time dd if=/dev/zero of=/fidb1/bigfile bs=8k count=80 80+0 records in 80+0 records out real0m13.780s user0m0.134s sys 0m1

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-22 Thread Alan Stange
tle too old school on this, but I wouldn't spend a dime until you've done the measurements correctly. Good Luck. -- Alan Luke Lonergan wrote: Alan, Looks like Postgres gets sensible scan rate scaling as the filesystem speed increases, as shown below. I'll drop my 120M

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-22 Thread Alan Stange
Bruce Momjian wrote: Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: The point your making doesn't match my experience with *any* storage or program I've ever used, including postgresql. Your point suggests that the storage system is idle and that post

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-23 Thread Alan Stange
7;s keeping up nicely. Hope this helps. Happy Thanksgiving! -- Alan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] postgresql performance tuning

2005-12-06 Thread Alan Stange
over the weekend and a vacuum was completed on the table about 2 hours ago. The two indices are now 3.4GB smaller. I don't think this counts as bloat, because of our use case. Even so, we reindex our whole database every weekend. -- Alan ---(e

Re: [PERFORM] postgresql performance tuning

2005-12-06 Thread Alan Stange
Tom Lane wrote: Alan Stange <[EMAIL PROTECTED]> writes: Vivek Khera wrote: what evidence do you have that you are suffering index bloat? The files for the two indices on a single table used 7.8GB of space before a reindex, and 4.4GB after. That'

Re: [PERFORM] PostgreSQL and Ultrasparc T1

2005-12-20 Thread Alan Stange
the current systems (assuming the application can scale). -- Alan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] PostgreSQL and Ultrasparc T1

2005-12-20 Thread Alan Stange
David Lang wrote: On Tue, 20 Dec 2005, Alan Stange wrote: Jignesh K. Shah wrote: I guess it depends on what you term as your metric for measurement. If it is just one query execution time .. It may not be the best on UltraSPARC T1. But if you have more than 8 complex queries running

Re: [PERFORM] CPU Usage

2007-02-13 Thread Alan Hodgson
On Tuesday 13 February 2007 10:36, "Campbell, Lance" <[EMAIL PROTECTED]> wrote: > We have 12+ schemas in 1 database. When I do a unix "top" command I > notice one postmaster process has 100% CPU usage. This process just > stays at 100% to 99% CPU usage. There are other postmaster processes > tha

Re: [PERFORM] Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"

2007-04-03 Thread Alan Hodgson
On Tuesday 03 April 2007 12:47, "A.M." <[EMAIL PROTECTED]> wrote: > On Apr 3, 2007, at 15:39 , C. Bergström wrote: > I would like to use transactional semantics over tables that can > disappear whenever the server fails. memcached does not offer that. How would temporary tables? -- Ginsberg's Th

Re: [PERFORM] 121+ million record table perf problems

2007-05-18 Thread Alan Hodgson
On Friday 18 May 2007 11:51, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > > The update query that started this all I had to kill after 17hours. It > > should have updated all 121+ million records. That brought my select > > count down to 19 minutes, but still a far cry from acceptable. You're

Re: [PERFORM] WALL on controller without battery?

2007-07-11 Thread Alan Hodgson
On Wednesday 11 July 2007 08:36, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > Put the WAL where the battery is. Even if it's slower (and I don't > know whether it will be), I assume that having the right data more > slowly is better than maybe not having the data at all, quickly. > Presumably he

Re: [PERFORM] Insert Statements Hanging

2007-07-25 Thread Alan Hodgson
On Wednesday 25 July 2007 13:27, Pallav Kalva <[EMAIL PROTECTED]> wrote: > I am hoping "SELECT 1 FROM ONLY "provisioning"."account" x WHERE > "accountid" = $1 FOR UPDATE OF x" is causing the problem. If that is the > case why doesnt it show in the pg_stat_activity view ? or am I missing > somethin

Re: [PERFORM] cpu throttling

2007-08-02 Thread Alan Hodgson
On Thursday 02 August 2007 09:02, "Bryan Murphy" <[EMAIL PROTECTED]> wrote: > My question: Is there a way I can decrease the priority of a specific > query, or determine the PID of the process it is running in? I'd like > to throw together a quick shell script if at all possible, as right > now

Re: [PERFORM] Update table performance

2007-08-07 Thread Alan Hodgson
On Tuesday 07 August 2007 05:58, Mark Makarowsky <[EMAIL PROTECTED]> wrote: > I have a table with 4,889,820 records in it. The > table also has 47 fields. I'm having problems with > update performance. Just as a test, I issued the > following update: > > update valley set test='this is a test'

Re: [PERFORM] Transaction Log

2007-08-29 Thread Alan Hodgson
On Wednesday 29 August 2007, Steve Atkins <[EMAIL PROTECTED]> wrote: > There are higher end ones that do have ECC RAM (and backup drives and > stuff) but they're spectacularly more expensive than the cheapo > consumer ones. > Yeah the good ones look more like http://ramsan.com/ . -- "Pulling tog

Re: [PERFORM] SAN vs Internal Disks

2007-09-07 Thread Alan Hodgson
On Friday 07 September 2007 10:56, "Bryan Murphy" <[EMAIL PROTECTED]> wrote: > Our database server connects to the san via iSCSI over Gig/E using > jumbo frames. File system is XFS (noatime). > > Throughput, however, kinda sucks. I just can't get the kind of > throughput to it I was hoping to ge

Re: [PERFORM] Index files

2007-09-14 Thread Alan Hodgson
On Friday 14 September 2007, "Harsh Azad" <[EMAIL PROTECTED]> wrote: > Great, creating new tablespace for indexes worked! Now the question is > whether existing tables/index can be moved to the new tablespace using an > alter command or the only way possible is to drop and recreate them? > ALTER T

Re: [PERFORM] How to speed up min/max(id) in 50M rows table?

2007-10-12 Thread Alan Hodgson
On Friday 12 October 2007, henk de wit <[EMAIL PROTECTED]> wrote: > > select payment_id from transactions order by payment_id desc limit 1; > > This one is indeed instant! Less than 50ms. In my case I can't use it for > max though because of the fact that payment_id can be null (which is an > unf

Re: [PERFORM] Occasional giant spikes in CPU load

2010-04-08 Thread Alan Hodgson
On Wednesday 07 April 2010, Craig James wrote: > I thought so too, except that I can't login during the flood. If the > CPUs were all doing iowaits, logging in should be easy. Busying out the drives is about the most reliable way to make logging in very slow (especially, but not only, if it's d

Re: [PERFORM] File system choice for Red Hat systems

2010-06-02 Thread Alan Hodgson
On Tuesday 01 June 2010, Mark Kirkwood wrote: > I'm helping set up a Red Hat 5.5 system for Postgres. I was going to > recommend xfs for the filesystem - however it seems that xfs is > supported as a technology preview "layered product" for 5.5. This > apparently means that the xfs tools are only

Re: [PERFORM] Advice configuring ServeRAID 8k for performance

2010-08-05 Thread Alan Hodgson
On Thursday, August 05, 2010, "Kenneth Cox" wrote: > 1) Should I switch to RAID 10 for performance? I see things like "RAID 5 > is bad for a DB" and "RAID 5 is slow with <= 6 drives" but I see little > on RAID 6. RAID 6 was the original choice for more usable space with > good redundancy. My cu

Re: [PERFORM] Advice configuring ServeRAID 8k for performance

2010-08-05 Thread Alan Hodgson
On Thursday, August 05, 2010, Mark Kirkwood wrote: > Normally I'd agree with the others and recommend RAID10 - but you say > you have an OLAP workload - if it is *heavily* read biased you may get > better performance with RAID5 (more effective disks to read from). > Having said that, your sequent

Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

2010-10-25 Thread Alan Hodgson
On October 25, 2010 11:36:24 am Divakar Singh wrote: > Above results show good INSERT performance of PG when using SQL procedures. > But performance when I use C++ lib is very bad. I did that test some time > back so I do not have data for that right now. Wrap it in a transaction. -- Sent via pg

Re: [PERFORM] Hardware recommendations

2010-12-09 Thread alan bryan
On Wed, Dec 8, 2010 at 3:03 PM, Benjamin Krajmalnik wrote: > I need to build a new high performance server to replace our current > production database server. We run FreeBSD 8.1 with PG 8.4 (soon to upgrade to PG 9). Hardware is: Supermicro 2u 6026T-NTR+ 2x Intel Xeon E5520 Nehalem 2.26GHz

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

2011-01-27 Thread Alan Hodgson
On January 27, 2011, Robert Schnabel wrote: > So my questions are 1) am I'm crazy for doing this, 2) would you change > anything and 3) is it acceptable to put the xlog & wal (and perhaps tmp > filespace) on a different controller than everything else? Please keep > in mind I'm a geneticist who h

Re: [PERFORM] Huge Data sets, simple queries

2006-02-02 Thread Alan Stange
ric". It's been doing this for a good 10 years now (back when it was called DiskSuite), so it's nothing new. -- Alan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Why so slow?

2006-04-28 Thread Alan Hodgson
On April 28, 2006 04:41 am, "Bealach-na Bo" <[EMAIL PROTECTED]> wrote: > INFO: index "job_log_id_pkey" now contains 10496152 row versions in > 59665 pages See the 10496152 above? That means you have 10496152 rows of data in your table. If those, only 365000 are alive. That means you have ba

Re: [PERFORM] Why so slow?

2006-04-28 Thread Alan Hodgson
On April 28, 2006 10:31 am, "Bealach-na Bo" <[EMAIL PROTECTED]> wrote: > The exclusive lock is going to cause problems for me since the table is > very active. Is there a way of getting around that or do I need to > schedule the application that accesses this table? If you don't need access to th

Re: [PERFORM] VACUUM killing my CPU

2006-05-09 Thread Alan Hodgson
On May 9, 2006 02:45 am, [EMAIL PROTECTED] wrote: > What I am worry about is "93.5% wa" ... > > Could someone explain me what is the VACUUM process waiting for ? > Disk I/O. -- In a truly free society, "Alcohol, Tobacco and Firearms" would be a convenience store chain. ---

Re: [PERFORM] is it possible to make this faster?

2006-05-25 Thread Alan Hodgson
to seq_scan) Try it with an index on a,b,c. -- Alan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] PostgreSQL performance issues

2006-08-30 Thread Alan Hodgson
On Wednesday 30 August 2006 03:48, Willo van der Merwe <[EMAIL PROTECTED]> wrote: > Hi Rusty, > > Good ideas and I've implemented some of them, and gained about 10%. I'm > still sitting on a load avg of about 60. > > Any ideas on optimizations on my postgresql.conf, that might have an > effect? I

Re: [PERFORM] RAID 0 not as fast as expected

2006-09-14 Thread Alan Hodgson
;t plan to run RAID 0 in production, > probably RAID 10, so no need to comment on the failure rate of RAID 0.) > Are those PCI cards? If yes, it's just a bus bandwidth limit. -- Alan ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] Large tables (was: RAID 0 not as fast as expected)

2006-09-18 Thread Alan Hodgson
On Monday 18 September 2006 13:56, "Merlin Moncure" <[EMAIL PROTECTED]> wrote: > just another fyi, if you have a really big database, you can forget > about doing pg_dump for backups (unless you really don't care about > being x day or days behind)...you simply have to due some type of > replicati

Re: [PERFORM] autovacuum on a -mostly- r/o table

2006-09-27 Thread Alan Hodgson
On Wednesday 27 September 2006 09:08, Edoardo Ceccarelli <[EMAIL PROTECTED]> wrote: > > How can I configure the vacuum to run after the daily batch > insert/update? > If you really only want it to run then, you should disable autovacuum and continue to run the vacuum manually. You might also in

[PERFORM] vacuum performance

2004-03-18 Thread Alan Stange
e cwd and then open without the path in the file name. You're forcing the kernel to do a lot of work walking the path, checking for nfs mounts, symlinks, etc. Thanks! -- Alan open64("/export/nst1/fi/pg/data1/base/91488/42064889.3", O_RDWR) = 47 llseek(47,

Re: [PERFORM] linux distro for better pg performance

2004-05-03 Thread Alan Stange
raid5 config should be faster for writes. Note also that the mirror has 2 copies of the data, so that the read IOs would be divided across 2 (or more) spindles using round robin or a more advanced algorithm to reduce seek times. Of course, I might be completely wrong... --

Re: [PERFORM] Anyone familiar with Apple Xserve RAID

2004-08-26 Thread Alan Stange
ch note on write cache flushing. A bit dated now, but perhaps some other tech note from Apple has more recent information. -- Alan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some

2004-10-15 Thread Alan Stange
pleasant to chase that problem down... -- Alan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-05 Thread Alan Stange
27;t care to go into the whole debate of Xeon v. Opteron here. We also have a lot of dual Xeon systems. In every comparison I've done with our codes, the dual Opteron clearly outperforms the dual Xeon, when running on one and both cpus. -- Alan Josh Berkus wrote: Bill, I'd be

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-06 Thread Alan Stange
Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: A few quick random observations on the Xeon v. Opteron comparison: - running a dual Xeon with hyperthreading turned on really isn't the same as having a quad cpu system. I haven't seen postgresql specific benchmarks, but

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-06 Thread Alan Stange
ems and make a simple (and naive) comparisons. Just to stay on topic: vmstat reported about 30K cs / second while this was running the 1 and 2 client cases. -- Alan ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-07 Thread Alan Stange
Bill Montgomery wrote: Alan Stange wrote: Here's a few numbers from the Opteron 250. If I get some time I'll post a more comprehensive comparison including some other systems. The system is a Sun v20z. Dual Opteron 250, 2.4Ghz, Linux 2.6, 8 GB memory. I did a compile and install

Re: [PERFORM] Swapping on Solaris

2005-01-19 Thread Alan Stange
27;re seeing (as they will be paged out on an active system with some memory pressure) Finally, just as everyone suggests upgrading to newer postgresql releases, you probably want to get to a newer Solaris release. -- Alan ---(end of broadcast)--- T

Re: [PERFORM] Swapping on Solaris

2005-01-19 Thread Alan Stange
size and the memory given to postgresql. But if postgresql doesn't ask for or use the memory, then solaris is going to use it for something else. There's nothing in Solaris that doesn't "allow" postgresql to use more RAM. -- Alan ---(end of br

Re: [PERFORM] Swapping on Solaris

2005-01-19 Thread Alan Stange
mbers include file IO in Solaris, because of the unified VM and file systems. -- Alan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your me

Re: [PERFORM] Swapping on Solaris

2005-01-19 Thread Alan Stange
be evicted to swap to make room for the new process or some pages had to be reserved in swap for future use. Typically a new process won't be paged out unless something else is causing enormous memory pressure... -- Alan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] multi billion row tables: possible or insane?

2005-03-01 Thread Alan Stange
Isn't that 385 rows/second. Presumably one can insert more than one row in a transaction? -- Alan Vig, Sandor (G/FI-2) wrote: 385 transaction/sec? fsync = false risky but fast. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Arbash Meinel

Re: [PERFORM] multi billion row tables: possible or insane?

2005-03-01 Thread Alan Stange
ped do some design work for a high energy physics experiment: petabytes of data, big tape robots, etc., the usual Big Science toys. You might take a look at ROOT and some of the activity from those folks if you don't need transactions and all the features of a general database like po

Re: [PERFORM] PostgreSQL on Solaris 8 and ufs

2005-03-22 Thread Alan Stange
ch would be much better for this sort of thing. Hope this helps. -- Alan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] dell versus hp

2007-11-08 Thread Alan Hodgson
5 or RAID 6 is to get more capacity out of the same drives. -- Alan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] dell versus hp

2007-11-13 Thread Alan Hodgson
On November 9, 2007, Vivek Khera <[EMAIL PROTECTED]> wrote: > On Nov 8, 2007, at 3:56 PM, Alan Hodgson wrote: > > You can't touch RAID 10 for performance or reliability. The only > > reason to > > use RAID 5 or RAID 6 is to get more capacity out of the same > &

Re: [PERFORM] dell versus hp

2007-11-14 Thread Alan Hodgson
On Tuesday 13 November 2007, Jeff Frost <[EMAIL PROTECTED]> wrote: > Ok, Areca ARC1261ML. Note that results were similar for an 8 drive RAID6 > vs 8 drive RAID10, but I don't have those bonnie results any longer. > > Version 1.03 --Sequential Output-- --Sequential Input- > --Random-

Re: [PERFORM] dell versus hp

2007-11-15 Thread Alan Hodgson
On Wednesday 14 November 2007, Jeff Frost <[EMAIL PROTECTED]> wrote: > > OK, impressive RAID-6 performance (not so impressive RAID-10 > > performance, but that could be a filesystem issue). Note to self; try > > an Areca controller in next storage server. > > I believe these were both on ext3. I

Re: [PERFORM] big database performance

2008-01-09 Thread Alan Hodgson
On Wednesday 09 January 2008, Adrian Moisey <[EMAIL PROTECTED]> wrote: > > Also, we're running the db on ext3 with noatime. Should I look at > changing or getting rid of journaling ? No (unless you like really long fsck times). data=writeback is safe with PostgreSQL, though.

[PERFORM] Performance tuning on FreeBSD

2008-03-03 Thread alan bryan
I've got a new server and am myself new to tuning postgres. Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU. It's serving as the DB for a fairly write intensive (maybe 25-30%) Web application in PHP. We are not using persistent connections, thus the high max conne

Re: [PERFORM] Performance tuning on FreeBSD

2008-03-03 Thread alan bryan
benchmarks and make sure disk is near where it should be. Should turning off fsync make things roughly 8x-10x faster? Or is that indicative of something not being correct or tuned quite right in the rest of the system? I'll have to run in production with fsync on but was just testing to s

Re: [PERFORM] Performance tuning on FreeBSD

2008-03-04 Thread alan bryan
On Mon, Mar 3, 2008 at 5:11 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Mon, 3 Mar 2008, alan bryan wrote: > > >> pgbench -c 100 -t 1000 testdb > > > tps = 558.013714 (excluding connections establishing) > > > > Just for testing, I tried turning

Re: [PERFORM] Optimisation help

2008-03-04 Thread Alan Hodgson
nly do maybe 150 seeks per second. Oh, and updates in PostgreSQL are expensive. But mostly I'd say it's your drive. -- Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_www

Re: [PERFORM] how can a couple of expensive queries drag my system down?

2008-03-26 Thread Alan Hodgson
On Wednesday 26 March 2008, "p prince" <[EMAIL PROTECTED]> wrote: > Is this a sign of disk contention? Yes. > How does CPU load come into play? Processes waiting for disk I/O generally show up as load. -- Alan -- Sent via pgsql-performance mailing list (pgsql-performa

Re: [PERFORM] Creating indexes

2008-05-08 Thread Alan Hodgson
notice any differences in execution time. A primary key is a unique btree index, and it's as about as good as it gets for a bigint. -- Alan signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] Tsearch2 Initial Search Speed

2008-06-16 Thread Alan Hodgson
e > email_fts_index is cached. It's because everything is cached, in particular the relevant rows from the "email" table (accessing which took 22 of the original 27 seconds). The plan looks good for what it's doing. I don't see that query getting much faster unle

Re: [PERFORM] Tsearch2 Initial Search Speed

2008-06-17 Thread Alan Hodgson
On Tuesday 17 June 2008, Howard Cole <[EMAIL PROTECTED]> wrote: > This misses out the random access of the email table, turning my 27 > second query into 6 seconds. It took less time because it retrieved a lot less data - it still has to look at the table. -- Alan -- Se

Re: [PERFORM] Tsearch2 Initial Search Speed

2008-06-17 Thread Alan Hodgson
t, you'll need to reboot. -- Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

  1   2   >