Re: [PERFORM] WAL + SSD = slow inserts?
On Thu, Dec 5, 2013 at 9:55 PM, Skarsol skar...@gmail.com wrote: The rule is being used to return the id of the insert... Take a look at the RETURNING clause of the INSERT statement. That should meet your needs here without having to bother with rules. rls -- :wq
Re: [PERFORM] Triggers and transactions
On Mon, Jan 28, 2013 at 10:54 AM, Craig James cja...@emolecules.com wrote: But if the drop-and-restore-trigger operation blocks all access to the tables, that's a problem. Were the triggers in question created with CREATE CONSTRAINT TRIGGER? If not, ALTER TABLE foo DISABLE TRIGGER USER may do what you need here. rls -- :wq
Re: [PERFORM] Do I have a hardware or a software problem?
On Tue, Dec 11, 2012 at 5:17 PM, Evgeny Shishkin itparan...@gmail.comwrote: Actually most of low-end SSDs don't do write caching, they do not have enough ram for that. AIUI, *all* SSDs do write-caching of a sort: writes are actually flushed to the NAND media by erasing, and then overwriting the erased space, and erasing is done in fixed-size blocks, usually much larger than a filesystem's pages. The drive's controller accumulates writes in an on-board cache until it has an erase block's worth of them, which are then flushed. From casual searching, a common erase block size is 256 kbytes, while filesystem-level pages are usually 4k. Most low-end (and even many mid-range) SSDs, including Sandforce-based drives, don't offer any form of protection (e.g., supercaps, as featured on the Intel 320 and 710-series drives) for the data in that write cache, however, which may be what you're thinking of. I wouldn't let one of those anywhere near one of my servers, unless it was a completely disposable, load-balanced slave, and probably not even then. rls -- :wq
Re: [PERFORM] SSD selection
On Tue, May 15, 2012 at 8:21 AM, Віталій Тимчишин tiv...@gmail.com wrote: We are using Areca controller with BBU. So as for me, question is: Can 520 series be set up to handle fsyncs correctly? No. The cause for capacitors on SSD logic boards is that fsyncs aren't flushed to NAND media, and hence persisted, immediately. SSDs are divided into pages, called erase blocks (usually much larger than the filesystem-level block size; I don't know offhand what the block size is on the 710, but on the older X-25 drives, it was 128K). All writes are accumulated in the on-board cache into erase block sized chunks, and *then* flushed to the NAND media. In a power-loss situation, the contents of that cache won't be preserved unless you have a capacitor. In some drives, you can disable the on-board cache, but that does absolutely atrocious things both to your drive's performance, and its longevity. As the other posters in this thread have said, your best bet is probably the Intel 710 series drives, though I'd still expect some 320-series drives in a RAID configuration to still be pretty stupendously fast. rls -- :wq -- 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 improve insert speed with index on text column
On Mon, Jan 30, 2012 at 9:46 AM, Saurabh saurabh@gmail.com wrote: I can not create the index after insertion because user can search the data as well while insertion. Remember, DDL is transactional in PostgreSQL. In principle, you should be able to drop the index, do your inserts, and re-create the index without affecting concurrent users, if you do all of that inside an explicit transaction. Doing the inserts inside a transaction may speed them up, as well. rls -- :wq -- 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] Linux I/O schedulers - CFQ random seeks
On Fri, Mar 4, 2011 at 10:34 AM, Glyn Astill glynast...@yahoo.co.uk wrote: I'm wondering (and this may be a can of worms) what peoples opinions are on these schedulers? I'm going to have to do some real world testing myself with postgresql too, but initially was thinking of switching from our current CFQ back to deadline. It was a few years ago now, but I went through a similar round of testing, and thought CFQ was fine, until I deployed the box. It fell on its face, hard. I can't find a reference offhand, but I remember reading somewhere that CFQ is optimized for more desktop type workloads, and that in its efforts to ensure fair IO access for all processes, it can actively interfere with high-concurrency workloads like you'd expect to see on a DB server -- especially one as big as your specs indicate. Then again, it's been a few years, so the scheduler may have improved significantly in that span. My standard approach since has just been to use no-op. We've shelled out enough money for a RAID controller, if not a SAN, so it seems silly to me not to defer to the hardware, and let it do its job. With big caches, command queueing, and direct knowledge of how the data is laid out on the spindles, I'm hard-pressed to imagine a scenario where the kernel is going to be able to do a better job of IO prioritization than the controller. I'd absolutely recommend testing with pg, so you can get a feel for how it behaves under real-world workloads. The critical thing there is that your testing needs to create workloads that are in the neighborhood of what you'll see in production. In my case, the final round of testing included something like 15-20% of the user-base for the app the db served, and everything seemed fine. Once we opened the flood-gates, and all the users were hitting the new db, though, nothing worked for anyone. Minute-plus page-loads across the board, when people weren't simply timing out. As always, YMMV, the plural of anecdote isn't data, c. rls -- :wq -- 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] prepared query performs much worse than regular query
On Fri, May 21, 2010 at 4:53 PM, Richard Yen d...@richyen.com wrote: Any ideas why the query planner chooses a different query plan when using prepared statements? A prepared plan is the best one the planner can come up with *in general* for the query in question. If the distribution of the values you're querying against -- in your case, owner and assignment -- aren't relatively uniform, that plan is going to be suboptimal, if not downright pathological, for the more outlying-ly distributed values. Looking at your prepared plan, it seems that, on average, there are 177 rows for every assignment, and 184 per owner. As it turns out, though, nearly a quarter of your table has an owner of -1. It's not terribly surprising, with a table that big and a distribution skew of that magnitude, that this query plan, with these arguments, ends up pretty firmly in the pathological category. rls -- :wq -- 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] PgPool II configuration with PostgreSQL 8.4
On Wed, May 5, 2010 at 10:17 PM, Neha Mehta neha.me...@lntinfotech.comwrote: I am trying to have synchronous master-master replication in PostgreSQL8.4 using PgPool II. I am not able to configure PgPool on the system, it gives me an error, libpq is not installed or libpq is old. (FYI: This topic is probably more germane to the -ADMIN list, or at least -GENERAL, than it is to -PERFORM.) Is there a particular reason you're building pgpool, rather than installing it via your distribution's package manager? Most distributions have it available these days. (At a minimum, any distribution that's widely-used and well-enough understood to warrant hosting something as critical as your RDBMS should have it.) FWIW, I'm successfully using pgpool-II against a pair of 8.4 instances (in the connection pool mode, not replication, and all installed from the PGDG RPM repository). I'm also using Bucardo (in its multi-master/swap mode) to handle the replication, as suggested by someone else down-thread. So there's an existence proof that it *can* work. Finally, when PostgreSQL is installed, libpq.so.N is usually put under /usr/lib(64)/, not under the postgres install directory. Your distribution should have a postgresql-devel package available which will provide a pg_config command that can be used to pass the *actual* installed locations to a configure invocation, as in: ./configure --with-pgsql-libdir=`pg_config --libdir`... rls -- :wq
Re: [PERFORM] DB is slow until DB is reloaded
On Mon, Jan 4, 2010 at 2:57 PM, Madison Kelly li...@alteeve.com wrote: As for autovacuum, I assumed (yes, I know) that all v8.x releases enabled it by default. How would I confirm that it's running or not? I believe it's not enabled by default in 8.1-land, and is as of 8.2 and later. Whether it's running or not, try SELECT * FROM pg_autovacuum;. If that returns the null set, it's not doing anything, as it hasn't been told it has anything to do. IME, however, if you really want to benefit from the autovacuum daemon, you probably do want to be on something more recent than 8.1. (And, yes, this is a bit of the pot calling the kettle black: I have a mixed set of 8.1 and 8.3 hosts. Autovacuum is only running on the latter, while the former are queued for an upgrade.) rls -- :wq -- 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] performance drop on RAID5
On 8/24/05, Alexandre Barros [EMAIL PROTECTED] wrote: i wouldn't be so stunned if the newer machine was ( say ) twice faster than the older server, but over three times faster is disturbing. RAID5 on so few spindles is a known losing case for PostgreSQL. You'd be far, far better off doing a pair of RAID1 sets or a single RAID10 set. /rls -- :wq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [SQL] ORDER BY Optimization
while you weren't looking, Derek Buttineau|Compu-SOLVE wrote: I'm hoping this is the right place to send this. The PostgreSQL Performance list, pgsql-performance@postgresql.org would be more appropriate. I'm copying my followup there, as well. As for your query, almost all the time is actually spent in the nestloop, not the sort. Compare: - Sort (cost=31402.85..31405.06 rows=886 width=306) (actual time=87454.187..87454.240 rows=10 loops=1) vs. - Nested Loop (cost=0.00..31359.47 rows=886 width=306) (actual time=4.740..86430.468 rows=26308 loops=1) That's 50-ish ms versus 80-odd seconds. It seems to me a merge join might be more appropriate here than a nestloop. What's your work_mem set at? Off-the-cuff numbers show the dataset weighing in the sub-ten mbyte range. Provided it's not already at least that big, and you don't want to up it permanently, try saying: SET work_mem = 10240; -- 10 mbytes immediately before running this query (uncached, of course) and see what happens. Also, your row-count estimates look pretty off-base. When were these tables last VACUUMed or ANALYZEd? /rls -- :wq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to improve db performance with $7K?
while you weren't looking, Kevin Brown wrote: [reordering bursty reads] In other words, it's a corner case that I strongly suspect isn't typical in situations where SCSI has historically made a big difference. [...] But I rather doubt that has to be a huge penalty, if any. When a process issues an fsync (or even a sync), the kernel doesn't *have* to drop everything it's doing and get to work on it immediately. It could easily gather a few more requests, bundle them up, and then issue them. To make sure I'm following you here, are you or are you not suggesting that the kernel could sit on -all- IO requests for some small handful of ms before actually performing any IO to address what you strongly suspect is a corner case? /rls -- :wq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Preventing query from hogging server
while you weren't looking, Matthew Nuzum wrote: select accountid, min(atime) as atime, sessionid from usage_access group by accountid,sessionid; Try something along the lines of: select ua.accountid , (select atime from usage_access where sessionid = ua.sessionid and accountid = ua.accountid order by atime asc limit 1 ) as atime , ua.sessionid from usage_access ua group by accountid , sessionid min() and max() currently do table scans, which, on large tables, or even moderately sized tables with large numbers of accounts/sessions, can add up. You'll need to replace asc with desc in the subquery for the max() version. This form cheats a bit and uses the index to find the highest and lowest values, provided you've created the appropriate indices. This is, IIRC, in the FAQ. /rls -- :wq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
while you weren't looking, Merlin Moncure wrote: 2 way or 4 way Opteron depending on needs (looking on a price for 4-way? Go here: http://www.swt.com/qo3.html). Try also the Appro 1U 4-way Opteron server, at: http://www.appro.com/product/server_1142h.asp I specced a 4-way 842 (1.6 GHz: little to none of our db work is CPU bound; there's just a lot of it going on at once) with 32G core for within delta of what SWT wants /just/ for the 32G -- the price of the box itself and anything else atop that. Stepping up to a faster CPU should increase the cost directly in line with the retail price for the silicon. We haven't yet ordered the machine (and the quote was from early last month, so their prices will have fluctuated) and consequently, I can't comment on their quality. Their default warranty is three years, rapid exchange, though, and they offer on-site service for only nominally more, IIRC. Some slightly more than cursory googling hasn't turned up anything overly negative, either. As a 1U, the box has no appreciable storage of its own but we're shopping for a competent, non bank-breaking fibre setup right now, so that's not an issue for our situation. While on the subject, anyone here have anything to say about JMR fibre raid cabinets? (Fibre-to-fibre, not fibre-to-SATA or the like.) /rls -- :wq ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
while you weren't looking, Greg Stark wrote: Back in the day, we used to have problems with our 1U dual pentiums. We attributed it to heat accelerating failure. I would fear four opterons in 1U would be damned hard to cool effectively, no? Opterons actually run pretty coolly, comparatively. If it's a big concern, you can always drop a few more clams for the low-voltage versions -- available in 1.4 and 2.0 GHz flavors, and of which I've heard several accounts of their being run successfully /without/ active cooling -- or punt until later this year, when they ship Winchester core Opterons (90nm SOI -- the current, uniprocessor silicon fabbed with that process has some 3W heat dissipation idle, ~30W under full load; as a point of contrast, current 90nm P4s have 34W idle dissipation, and some 100W peak). We have a number of 1U machines (P4s, I believe), and a Dell blade server (six or seven P3 machines in a 3U cabinet) as our webservers, and none of them seem to have any trouble with heat. That's actually a bigger deal than it might first seem, given how frighteningly crammed with crap our machine room is. /rls -- :wq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
while you weren't looking, Gary Doades wrote: The .NET Runtime will be a part of the next MS SQLServer engine. It won't be long before someone writes a procedural language binding to PostgreSQL for Parrot [1]. That should offer us a handful or six more languages that can be used, including BASIC, Ruby and Scheme, Perl (5 and 6), Python and TCL for more or less free, and ... wait for it, BrainF***. IIRC, people have talked about porting C# to Parrot, as well. /rls [1] The new VM for Perl 6, c: http://www.parrotcode.org -- :wq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] create index with substr function
while you weren't looking, Ray wrote: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree ((SUBSTR(doc_urn,10))); You need an additional set of parens around the SUBSTR() call. /rls -- :wq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Understanding explains
while you weren't looking, Francisco Reyes wrote: Is there any disadvantage of having the enable_seqscan off? Plenty. The planner will choose whichever plan looks cheapest, based on the information it has available (table size, statistics, c). If a sequential scan looks cheaper, and in your case above it clearly is, the planner will choose that query plan. Setting enable_seqscan = false doesn't actually disable sequential scans; it merely makes them seem radically more expensive to the planner, in hopes of biasing its choice towards another query plan. In your case, that margin made an index scan look less expensive than sequential scan, but your query runtimes clearly suggest otherwise. In general, it's best to let the planner make the appropriate choice without any artificial constraints. I've seen pathalogical cases where the planner makes the wrong choice(s), but upon analysis, they're almost always attributable to poor statistics, long un-vacuumed tables, c. /rls -- :wq ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] finding a max value
On Fri, 02 Jul 2004 20:50:26 +0200, Edoardo Ceccarelli [EMAIL PROTECTED] wrote: This is the query: select max(KA) from annuncio wasn't supposed to do an index scan? it takes about 1sec to get the result. TIP 5: Have you checked our extensive FAQ? I believe this is a FAQ. See: http://www.postgresql.org/docs/faqs/FAQ.html#4.8 Try select KA from annuncio order by KA desc limit 1; /rls -- :wq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL caching
while you weren't looking, Vitaly Belman wrote: So, I guess it has to do with PostgreSQL caching.. But how exactly does it work? What does it cache? And how can I control it? PostgreSQL uses the operating system's disk cache. You can hint to the postmaster how much memory is available for caching with the effective_cache_size directive in your postgresql.conf. If you're running a *nix OS, you can find this by watching `top` for a while; in the header, there's a cached value (or something to that effect). Watching this value, you can determine a rough average and set your effective_cache_size to that rough average, or perhaps slightly less. I'm not sure how to get this value on Windows. Pgsql uses the OS's disk cache instead of its own cache management because the former is more likely to persist. If the postmaster managed the cache, as soon as the last connection died, the memory allocated for caching would be released, and all the cached data would be lost. Relying instead on the OS to cache data means that, whether or not there's a postmaster, so long as there has been one, there'll be some data cached. You can prepopulate the OS disk cache by periodically running a handful of SELECT queries that pull from your most commonly accessed tables in a background process. (A good way of doing that is simply to run your most commonly executed SELECTS.) Those queries should take the performance hit of fetching from disk, while your regular queries hit the cache. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] atrocious update performance
After deinstalling and scrubbing PostgreSQL from my server and doing a clean build using a vanilla 7.4.2 tree, I'm rather more confident that foreign key validation is at cause in my performance problems. I recreated my schemas and ran the original update, with foreign keys referring to the identity column of the target table. The update took roughly two days, as I'd predicted based on my analysis of the previous installation. (I can't say how long with certainty, beyond that it finished some time between when I left work one night and came in the next morning, the second day after starting the query.) I'm not sure what was wrong with the previous install, such that the update took several days; two-ish days is long enough. Just this morning, however, I created a copy of the target table (all 4.7M rows), with absolutely no foreign keys referring to it, and ran the update against the copy. That update took 2300 seconds. The join columns were indexed in both cases. I'm in the process of migrating the machine to run kernel 2.6.4, following the thread started by Gary, though I suspect that the kernel revision is moot with respect to whether or not foreign keys are being incorrectly validated. I can keep the 2.4 kernel and modules around to run using the current versions for testing purposes, though any such work would necessarily be off-hours. Please advise of anything I can do to help narrow down the specific cause of the issue; I know just enough C to be mildly dangerous. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] atrocious update performance
while you weren't looking, Kevin Barnard wrote: Have you added indexes for the custid column for tables account.acct accunt.orgacct and note? They were indexed in the original case, yes. There was no need to index them in today's test case, as that was done purely in attempt to rule in or out foreign key validation as the cause of the performance hit. No foreign keys that might be validated, no need to index the foreign key columns. I haven't followed the entire thread but it you have cascading FK on those tables without an index on the column that could cause your delay. The issue is that the foreign keys are being validated at all, when the column being referenced by those foreign keys (account.cust.custid) is never touched. Regardless of whether or not the referencing columns are indexed, validating them at all--in this specific case--is broken. The column they refer to is never touched; they should remain utterly ignorant of whatever happens to other columns in the same row. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] atrocious update performance
Greg Spiegelberg wrote: Will advise. After creating 100, 1K, 10K, 100K and 1M-row subsets of account.cust and the corresponding rows/tables with foreign key constraints referring to the table, I'm unable to reproduce the behavior at issue. explain analyze looks like the following, showing the query run with the join column indexed and not, respectively: # explain analyze update test.cust100 set prodid = tempprod.prodid, subprodid = tempprod.subprodid where origid = tempprod.debtid; -- with index QUERY PLAN --- Merge Join (cost=0.00..25.64 rows=500 width=220) (actual time=0.241..13.091 rows=100 loops=1) Merge Cond: ((outer.origid)::text = (inner.debtid)::text) - Index Scan using ix_origid_cust100 on cust100 (cost=0.00..11.50 rows=500 width=204) (actual time=0.125..6.465 rows=100 loops=1) - Index Scan using ix_debtid on tempprod (cost=0.00..66916.71 rows=4731410 width=26) (actual time=0.057..1.497 rows=101 loops=1) Total runtime: 34.067 ms (5 rows) -- without index QUERY PLAN -- Merge Join (cost=7.32..16.71 rows=100 width=220) (actual time=4.415..10.918 rows=100 loops=1) Merge Cond: ((outer.debtid)::text = inner.?column22?) - Index Scan using ix_debtid on tempprod (cost=0.00..66916.71 rows=4731410 width=26) (actual time=0.051..1.291 rows=101 loops=1) - Sort (cost=7.32..7.57 rows=100 width=204) (actual time=4.311..4.450 rows=100 loops=1) Sort Key: (cust100.origid)::text - Seq Scan on cust100 (cost=0.00..4.00 rows=100 width=204) (actual time=0.235..2.615 rows=100 loops=1) Total runtime: 25.031 ms (7 rows) With the join column indexed, it takes roughly .32ms/row on the first four tests (100.. 100K), and about .48ms/row on 1M rows. Without the index, it runs 100 rows @ .25/row, 1000 @ .26, 1 @ .27, 10 @ .48 and .5 @ 1M rows. In no case does the query plan reflect foreign key validation. Failing any other suggestions for diagnosis in the soon, I'm going to nuke the PostgreSQL install, scour it from the machine and start from scratch. Failing that, I'm going to come in some weekend and re-do the machine. Problem is when I recreate the indexes and add the constraints back on ORIG I end up with the same long running process. The original UPDATE runs for about 30 minutes on a table of 400,000 with the WHERE matching about 70% of the rows. The above runs for about 2 minutes without adding the constraints or indexes however adding the constraints and creating the dropped indexes negates any gain. Is this a frequently-run update? In my experience, with my seemingly mutant install, dropping indices and constraints to shave 14/15 off the update time would be worth the effort. Just script dropping, updating and recreating into one large transaction. It's a symptom-level fix, but re-creating the fifteen indices on one of our 5M row tables doesn't take 28 minutes, and your hardware looks to be rather less IO and CPU bound than ours. I'd also second Tom's suggestion of moving to 7.4. /rls ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] atrocious update performance
Greg Spiegelberg wrote: I've been following this thread closely as I have the same problem with an UPDATE. Everything is identical here right down to the strace output. Has anyone found a workaround or resolved the problem? If not, I have test systems here which I can use to help up test and explore. I'm still gathering data. The explain analyze I'd expected to finish Thursday afternoon hasn't yet. I'm going to kill it and try a few smaller runs, increasing in size, until the behavior manifests. Will advise. /rls ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] atrocious update performance
while you weren't looking, Tom Lane wrote: Hm. It looks like you mistakenly traced psql rather than the backend, but since the delay went away we wouldn't have learned anything anyhow. Have you got any idea what conditions may have changed between seeing delay and not seeing delay? None, offhand. I have noticed that when a large query is running, the machine can sporadically just freeze--or at least take inordinately long for some other process, be it top or ls, another query, or whatever to start. Nothing looks saturated when it happens, and, while you can count on it to happen, it's not consistent enough to reproduce. This is pretty odd too. It looks like it's doing checkpoints every so often (look for the writes to pg_control), which a backend engaged in a long-running query surely ought not be doing. Need to think about why that might be... Does the fact that all the reads and writes are 32K mean anything out of the ordinary? $PGSRC/src/include/pg_config_manual.h has BLCKSZ #defined to 16384. I was running previously with a 32K BLCKSZ, but that turned out to be rather sub-optimal for as heavily indexed as our tables are. I've dumped and rebuilt several times since then. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] atrocious update performance
I wrote: Regardless, something thinks it's still there. Is there any way that it is, and that I've somehow been running 7.3.2 all along? `which psql`, c show the bindir from my configure, but I'm not sure that's sufficient. The weird thing is that I know I never built 7.3.anything with 32K BLCKSZ, never built 7.3.anything at all. If 7.3 were installed, would it have any problem reading a 7.4 cluster? /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] atrocious update performance
joining to get the new values for prodid and subprodid: # \d tempprod Table public.tempprod Column | Type | Modifiers ---+---+--- debtid| character varying(10) | not null pool | character varying(10) | not null port | character varying(10) | not null subprodid | bigint| prodid| bigint| Indexes: ix_debtid btree (debtid) /rls -- Rosser Schwarz Total Card, Inc. ---(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] atrocious update performance
while you weren't looking, Tom Lane wrote: EXPLAIN won't tell you anything about triggers that might get fired during the UPDATE, so it's not much help for investigating possible FK performance problems. EXPLAIN ANALYZE will give you some indirect evidence: the difference between the total query time and the total time reported for the topmost plan node represents the time spent running triggers and physically updating the tuples. I suspect we are going to see a big difference. It's still running. It took 20 seconds to EXPLAIN? That's pretty darn odd in itself. It struck me, too. I'm starting to think there must be something quite whacked-out about your installation, but I haven't got any real good ideas about what. Built from source. configure arguments: ./configure --prefix=/var/postgresql --bindir=/usr/bin --enable-thread-safety --with-perl --with-python --with-openssl --with-krb5=/usr/kerberos I can answer more specific questions; otherwise, I'm not sure what to look for, either. If we could take the machine out of production (oh, hell; I think I just volunteered myself for weekend work) long enough to reinstall everything to get a fair comparison... So far as I know, though, it's a more or less stock Red Hat. 2.4.20- something. (I'm assuming of course that there weren't a ton of other jobs eating CPU while you tried to do the EXPLAIN.) CPU's spiked sopradically, which throttled everything else, but it never stays high. top shows the current explain analyze running between 50- ish% and negligible. iostat -k 3 shows an average of 3K/sec written, for a hundred-odd tps. I can't get any finer-grained than that, unfortunately; the machine was handed to me with a single, contiguous filesystem, in production use. [ thinks for awhile... ] The only theory that comes to mind for making the planner so slow is oodles of dead tuples in pg_statistic. Could I trouble you to run vacuum full verbose pg_statistic; and send along the output? INFO: vacuuming pg_catalog.pg_statistic INFO: pg_statistic: found 215 removable, 349 nonremovable row versions in 7 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 72 to 8132 bytes long. There were 3 unused item pointers. Total free space (including removable row versions) is 91572 bytes. 0 pages are or will become empty, including 0 at the end of the table. 7 pages containing 91572 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.71 sec. INFO: index pg_statistic_relid_att_index now contains 349 row versions in 2 pages DETAIL: 215 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_statistic: moved 120 row versions, truncated 7 to 5 pages DETAIL: CPU 0.03s/0.01u sec elapsed 0.17 sec. INFO: index pg_statistic_relid_att_index now contains 349 row versions in 2 pages DETAIL: 120 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming pg_toast.pg_toast_16408 INFO: pg_toast_16408: found 12 removable, 12 nonremovable row versions in 5 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 660 to 8178 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 91576 bytes. 2 pages are or will become empty, including 0 at the end of the table. 5 pages containing 91576 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.27 sec. INFO: index pg_toast_16408_index now contains 12 row versions in 2 pages DETAIL: 12 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: pg_toast_16408: moved 10 row versions, truncated 5 to 3 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: index pg_toast_16408_index now contains 12 row versions in 2 pages DETAIL: 10 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. Having never more than glanced at the output of vacuum verbose, I can't say whether that makes the cut for oodles. My suspicion is no. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] atrocious update performance
}, NULL) = 0
brk(0) = 0x82f2000
brk(0x82f4000) = 0x82f4000
brk(0) = 0x82f4000
brk(0x82f6000) = 0x82f6000
brk(0) = 0x82f6000
brk(0x82fa000) = 0x82fa000
brk(0) = 0x82fa000
brk(0x8302000) = 0x8302000
_llseek(37, 79331328, [79331328], SEEK_SET) = 0
write(37, \342\1\0\0\200\3747\10\24\0\0\0\300\0068N\360\177\1\200...,
32768)
= 32768
open(/var/lib/pgsql/data/base/495616/16653, O_RDWR|O_LARGEFILE) = 41
read(41, \0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1..., 32768)
=
32768
open(/var/lib/pgsql/data/base/495616/6834168, O_RDWR|O_LARGEFILE) = 42
_llseek(42, 262144, [262144], SEEK_SET) = 0
write(42, \342\1\0\\3758\10\24\0\0\0\0\6Xb\360\177\1\200\320...,
32768)
= 32768
read(41, \340\1\0\0\224*\262\241\24\0\0\0\210\4T+\360\177\1\200..., 32768)
=
32768
brk(0) = 0x8302000
brk(0x8304000) = 0x8304000
gettimeofday({1079482178, 957454}, NULL) = 0
gettimeofday({1079482178, 957580}, NULL) = 0
send(6, \4\0\0\0\334\3\0\0\7\0\0\0zU\0\0\0\220\7\0\1\0\0\0\16\0..., 988,
0)
= 988
send(6, \4\0\0\0\334\3\0\0\7\0\0\0zU\0\0\0\220\7\0\1\0\0\0\16\0..., 988,
0)
= 988
send(6, \4\0\0\0\334\3\0\0\7\0\0\0zU\0\0\0\220\7\0\1\0\0\0\16\0..., 988,
0)
= 988
send(6, \4\0\0\0\274\1\0\0\7\0\0\0zU\0\0\0\220\7\0\1\0\0\0\6\0..., 444, 0)
=
444
send(9, T\0\0\0#\0\1QUERY PLAN\0\0\0\0\0\0\0\0\0\0\31\377\377\377..., 394,
0) = 394
recv(9, X\0\0\0\4, 8192, 0) = 5
exit_group(0) = ?
/rls
--
Rosser Schwarz
Total Card, Inc.
---(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
[PERFORM] atrocious update performance
checkpoint_segments = 64 checkpoint_timeout = 1800 checkpoint_warning = 30 commit_delay = 5 effective_cache_size = 131072 Any advice, suggestions or comments of the You bleeding idiot, why do you have frob set to x?! sort welcome. Unfortunately, if we can't improve this, significantly, the powers what be will probably pass on PostgreSQL, even though everything we've done so far--with this marked exception--performs pretty spectacularly, all told. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] atrocious update performance
# explain update account.cust set prodid = tempprod.prodid where tempprod.did = origid; Merge Join (cost=0.00..232764.69 rows=4731410 width=252) Merge Cond: ((outer.origid)::text = (inner.did)::text) - Index Scan using ix_origid on cust (cost=0.00..94876.83 rows=4731410 width=244) - Index Scan using ix_did on tempprod (cost=0.00..66916.71 rows=4731410 width=18) I'm going to hazard a guess and say you have a number of foreign keys that refer to account.cust.prodid? This is probably the time consuming part -- perhaps even a missing index on one of those keys that refers to this field. Actually, there are no foreign keys to those columns. Once they're populated, I'll apply a foreign key constraint and they'll refer to the appropriate row in the prod and subprod tables, but nothing will reference account.cust.[sub]prodid. There are, of course, several foreign keys referencing account.cust.custid. Going the other way should be just as good for your purposes, and much faster since you're not updating several foreign key'd fields bound to account.cust.prodid. UPDATE tempprod.prodid = prodid FROM account.cust WHERE temprod.did = cust.origid; Not quite. Without this update, acount.cust.[sub]prodid are null. The data was strewn across multiple tables in MS SQL; we're normalizing it into one, hence the need to populate the two columns independently. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] atrocious update performance
You can create a new table using 'create table as' to produce your target results. This is real fast ... I often see 2 orders of magnitude improvement doing this, and no need to vacuum. Indeed: Query returned successfully with no result in 582761 ms. Though I must say, ten minutes is nominally more than two orders of mangitude performance improvement, versus several days. Many thanks, Aaron. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
