Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
Presumably it can't _ever_ know without being explicitly told, because even for a plain SELECT there might be triggers involved that update tables, or it might be a select of a stored proc, etc. So in the general case, you can't assume that a select doesn't cause an update, and you can't be sure that the table list in an update is a complete list of the tables that might be updated. Tatsuo Ishii wrote: Can I ask a question? Suppose table A gets updated on the master at time 00:00. Until 00:03 pgpool needs to send all queries regarding A to the master only. My question is, how can pgpool know a query is related to A? -- Tatsuo Ishii ---(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] PostgreSQL clustering VS MySQL clustering
Yes, I wasn't really choosing my examples particularly carefully, but I think the conclusion stands: pgpool (or anyone/thing except for the server) cannot in general tell from the SQL it is handed by the client whether an update will occur, nor which tables might be affected. That's not to say that pgpool couldn't make a good guess in the majority of cases! M Joshua D. Drake wrote: Matt Clark wrote: Presumably it can't _ever_ know without being explicitly told, because even for a plain SELECT there might be triggers involved that update tables, or it might be a select of a stored proc, etc. So in the general case, you can't assume that a select doesn't cause an update, and you can't be sure that the table list in an update is a complete list of the tables that might be updated. Uhmmm no :) There is no such thing as a select trigger. The closest you would get is a function that is called via select which could be detected by making sure you are prepending with a BEGIN or START Transaction. Thus yes pgPool can be made to do this. Sincerely, Joshua D. Drake ---(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] Swapping on Solaris
This page may be of use: http://www.serverworldmagazine.com/monthly/2003/02/solaris.shtml From personal experience, for god's sake don't think Solaris' VM/swap implementation is easy - it's damn good, but it ain't easy! Matt Kevin Schroeder wrote: I think it's probably just reserving them. I can't think of anything else. Also, when I run swap activity with sar I don't see any activity, which also points to reserved swap space, not used swap space. swap -s reports total: 358336k bytes allocated + 181144k reserved = 539480k used, 2988840k available Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 11:04 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. The test you're doing doesn't measure what you think you're measuring. First, what else is running on the machine?Note that some shared memory allocations do reserve backing pages in swap, even though the pages aren't currently in use. Perhaps this is what you're measuring? swap -s has better numbers than top. You'd be better by trying a reboot then starting pgsql and seeing what memory is used. Just because you start a process and see the swap number increase doesn't mean that the new process is in swap. It means some anonymous pages had to 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 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 ---(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] Speed in V8.0
Another man working to the bitter end this Christmas! There could be many reasons, but maybe first you should look at the amount of RAM available? If the tables fit in RAM on the production server but not on the dev server, then that will easily defeat the improvement due to using the native DB version. Why don't you install cygwin on the dev box and do the comparison using the same hardware? M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Wegner Sent: 21 December 2004 23:03 To: pgsql-performance@postgresql.org Subject: [PERFORM] Speed in V8.0 Hello, i have a problem between V7.4.3 Cygwin and V8.0RC2 W2K. I have 2 systems: 1. Production Machine - Dual P4 3000MHz - 2 GB RAM - W2K - PostgreSQL 7.4.3 under Cygwin - i connect to it over a DSL Line 2. Develop Machine - P4 1800MHz - 760 MB RAM - PostgreSQL Native Windows - local connection 100MB/FD Both systems use the default postgresql.conf. Now the problem. I have an (unoptimized, dynamic) query wich was execute on the production machine over DSL in 2 seconds and on my develop machine, connected over local LAN, in 119 seconds! Whats this? I can not post the query details here public, its a commercial project. Any first idea? I execute on both machine the same query with the same database design! - Thomas Wegner CabrioMeter - The Weather Plugin for Trillian http://www.wegner24.de/cabriometer ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] ext3 journalling type
Am I right to assume that writeback is both fastest and at the same time as safe to use as ordered? Maybe any of you did some benchmarks? It should be fastest because it is the least overhead, and safe because postgres does it's own write-order guaranteeing through fsync(). You should also mount the FS with the 'noatime' option. But For some workloads, there are tests showing that 'data=journal' can be the fastest! This is because although the data is written twice (once to the journal, and then to its real location on disk) in this mode data is written _sequentially_ to the journal, and later written out to its destination, which may be at a quieter time. There's a discussion (based around 7.2) here: http://www.kerneltraffic.org/kernel-traffic/kt20020401_160.txt M ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Strange (?) Index behavior?
For some reason it's a requirement that partial wildcard searches are done on this field, such as SELECT ... WHERE field LIKE 'A%' I thought an interesting way to do this would be to simply create partial indexes for each letter on that field, and it works when the query matches the WHERE clause in the index exactly like above. The problem is thus: I thought PG could use an ordinary index for 'like' conditions with just a terminating '%'? My other thought is that like 'A%' should grab about 1/26th of the table anyway (if the initial character distribution is random), and so a sequential scan might be the best plan anyway... M ---(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] Strange (?) Index behavior?
With that many rows, and a normal index on the field, postgres figures the best option for say I% is not an index scan, but a sequential scan on the table, with a filter -- quite obviously this is slow as heck, and yes, I've run analyze several times and in fact have the vacuum analyze automated. Ah, so like 'I%' uses a very slow seq scan, but like 'ABC%' uses an ordinary index OK? If so then... The planner would usually assume (from what Tom usually says) that 1/26 selectivity isn't worth doing an index scan for, but in your case it's wrong (maybe because the rows are very big?) You may be able to get the planner to go for an index scan on like 'I%' by tweaking the foo_cost variables in postgresql.conf Or you could have the app rewrite like 'I%' to like 'IA%' or like 'IB%' ... , or do that as a stored proc. With the partial index the index scan is used and the cost drops from 0..2million to 0..9000 -- a vast improvement. So there are really only 9000 rows out of 76 million starting with 'I'? How about combining some techniques - you could create an index on the first two chars of the field (should be selective enough to give an index scan), select from that, and select the actual data with the like clause. CREATE INDEX idx_firstletters ON table (substr(field, 1, 2)); CREATE INDEX idx_all ON table (field); SELECT field FROM (SELECT field FROM table WHERE substr(field, 1, 2) = 'DE') AS approx WHERE field LIKE 'DE%'; Any good? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Restricting Postgres
I have a dual processor system that can support over 150 concurrent connections handling normal traffic and load. Now suppose I setup Apache to spawn all of it's children instantly, what will ... This will spawn 150 children in a short order of time and as this takes Doctor, it hurts when I do this! Well, don't do that then... Sorry, couldn't resist ;-) Our Apache/PG driven website also needs to be able to deal with occasional large peaks, so what we do is: StartServers 15 # Don't create too many children initially MinSpareServers 10 # Always have at least 10 spares lying around MaxSpareServers 20 # But no more than 20 MaxClients 150 # Up to 150 - the default 256 is too much for our RAM So on server restart 15 Apache children are created, then one new child every second up to a maximum of 150. Apache's 'ListenBackLog' is around 500 by default, so there's plenty of scope for queuing inbound requests while we wait for sufficient children to be spawned. In addition we (as _every_ high load site should) run Squid as an accelerator, which dramatically increases the number of client connections that can be handled. Across 2 webservers at peak times we've had 50,000 concurrently open http https client connections to Squid, with 150 Apache children doing the work that squid can't (i.e. all the dynamic stuff), and PG (on a separate box of course) whipping through nearly 800 mixed selects, inserts and updates per second - and then had to restart Apache on one of the servers for a config change... Not a problem :-) One little tip - if you run squid on the same machine as apache, and use a dual-proc box, then because squid is single-threaded it will _never_ take more than half the CPU - nicely self balancing in a way. M ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Restricting Postgres
Apache::DBI overall works better to what I require, even if it is not a pool per sey. Now if pgpool supported variable rate pooling like Apache does with it's children, it might help to even things out. That and you'd still get the spike if you have to start the webserver and database server at or around the same time. I still don't quite get it though - you shouldn't be getting more than one child per second being launched by Apache, so that's only one PG postmaster per second, which is really a trivial load. That is unless you have 'StartServers' set high, in which case the 'obvious' answer is to lower it. Are you launching multiple DB connections per Apache process as well? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Restricting Postgres
Case in point: A first time visitor hits your home page. A dynamic page is generated (in about 1 second) and served (taking 2 more seconds) which contains links to 20 additional The gain from an accelerator is actually even more that that, as it takes essentially zero seconds for Apache to return the generated content (which in the case of a message board could be quite large) to Squid, which can then feed it slowly to the user, leaving Apache free again to generate another page. When serving dialup users large dynamic pages this can be a _huge_ gain. I think Martin's pages (dimly recalling another thread) take a pretty long time to generate though, so he may not see quite such a significant gain. ---(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] Restricting Postgres
Correct the 75% of all hits are on a script that can take anywhere from a few seconds to a half an hour to complete.The script essentially auto-flushes to the browser so they get new information as it arrives creating the illusion of on demand generation. This is more like a streaming data server, which is a very different beast from a webserver, and probably better suited to the job. Usually either multithreaded or single-process using select() (just like Squid). You could probably build one pretty easily. Using a 30MB Apache process to serve one client for half an hour seems like a hell of a waste of RAM. A squid proxy would probably cause severe problems when dealing with a script that does not complete output for a variable rate of time. No, it's fine, squid gives it to the client as it gets it, but can receive from the server faster. ---(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] Restricting Postgres
1- You have a query that runs for half an hour and you spoon feed the results to the client ? (argh) 2- Your script looks for new data every few seconds, sends a packet, then sleeps, and loops ? If it's 2 I have a readymade solution for you, just ask. I'm guessing (2) - PG doesn't give the results of a query in a stream. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Restricting Postgres
These are CGI scripts at the lowest level, nothing more and nothing less. While I could probably embed a small webserver directly into the perl scripts and run that as a daemon, it would take away the portability that the scripts currently offer. If they're CGI *scripts* then they just use the CGI environment, not Apache, so a daemon that accepts the inbound connections, then compiles the scripts a-la Apache::Registry, but puts each in a separate thread would be, er, relatively easy for someone better at multithreaded stuff than me. This should be my last question on the matter, does squid report the proper IP address of the client themselves?That's a critical requirement for the scripts. In the X-Forwarded-For header. Not that you can be sure you're seeing the true client IP anyway if they've gone through an ISP proxy beforehand. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Restricting Postgres
In your webpage include an iframe with a Javascript to refresh it every five seconds. The iframe fetches a page from the server which brings in the new data in form of generated JavaScript which writes in the parent window. Thus, you get a very short request every 5 seconds to fetch new data, and it is displayed in the client's window very naturally. ... Yup. If you go the JS route then you can do even better by using JS to load data into JS objects in the background and manipulate the page content directly, no need for even an Iframe. Ignore the dullards who have JS turned off - it's essential for modern web apps, and refusing JS conflicts absolutely with proper semantic markup. http://developer.apple.com/internet/webcontent/xmlhttpreq.html is a good starting point. It's clear that this discussion has moved way away from PG! Although in the context of DB backed web apps I guess in remains a bit on-topic... M ---(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] appropriate indexing
- ITEM table will, grow, grow, grow (sold items are not deleted) WHERE PRODUCT.SECTION_USED_FK IS NOT NULL AND ITEM.STATUS=1 and (ITEM.KIND=2 or ITEM.KIND=3) Partial index on item.status ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Better Hardware, worst Results
All 3 plans have crappy estimates. Run ANALYZE in production, then send another explain analyze (as an attachment please, to avoid linewrap). Er, no other possible answer except Rod's :-) ---(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] Restricting Postgres
Javascript is too powerful to turn for any random web page. It is only essential for web pages because people write their web pages to only work with javascript. Hmm... I respectfully disagree. It is so powerful that it is impossible to ignore when implementing a sophisticated app. And it is not dangerous to the user so long as they have a popup blocker. Commercially, I can ignore the people who turn it off, and I can gain a huge benefit from knowing that 95% of people have it turned on, because it gives my users a hugely better experience than the equivalent XHTML only page (which I deliver, and which works, but which is a fairly depressing experience compared to the JS enabled version). It is _amazing_ how much crud you can take out of a page if you let JS do the dynamic stuff (with CSS still in full control of the styling). Nice, clean, semantically sensible XHTML, that can be transformed for multiple devices - it's great. An example: a class=preview_link/previews/foo.wmv/a But we want it to appear in a popup when viewed in certain devices Easy - Attach an 'onclick' event handler (or just set the target attribute) when the device has a suitable screen media player, but leave the markup clean for the rest of the world. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Restricting Postgres
A note though : you'll have to turn off HTTP persistent connections in your server (not in your proxy) or youre back to square one. I hadn't considered that. On the client side it would seem to be up to the client whether to use a persistent connection or not. If it does, then yeah, a request every 5 seconds would still just hold open a server. One more reason to use a proxy I s'pose. It's clear that this discussion has moved way away from PG! Although in the context of DB backed web apps I guess in remains a bit on-topic... I find it very on-topic as - it's a way to help this guy solve his pg problem which was iin fact a design problem - it's the future of database driven web apps (no more reloading the whole page !) I think in the future there will be a good bit of presentation login in the client... Not if Bruno has his way ;-) ---(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] Restricting Postgres
Pierre-Frédéric Caillaud wrote: check this marvelus piece of 5 minutes of work : http://boutiquenumerique.com/test/iframe_feed.html cela m'a fait le sourire :-) (apologies for bad french) M ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] preloading indexes
Title: Message The best way to get all the stuff needed by a query into RAM is to run the query. Is it more that you want to 'pin' the data in RAM so it doesn't get overwritten by other queries? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: 03 November 2004 17:31To: [EMAIL PROTECTED]Subject: [PERFORM] preloading indexes I am working with some pretty convoluted queries that work very slowly the first time theyre called but perform fine on the second call. I am fairly certain that these differences are due to the caching. Can someone point me in a direction that would allow me to pre-cache the critical indexes?
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
and certainly anyone who's been around a computer more than a week or two knows which direction in and out are customarily seen from. regards, tom lane Apparently not whoever wrote the man page that everyone copied ;-) Interesting. I checked this on several machines. They actually say different things. Redhat 9- bi: Blocks sent to a block device (blocks/s). Latest Cygwin- bi: Blocks sent to a block device (blocks/s). Redhat 7.x- bi: Blocks sent to a block device (blocks/s). Redhat AS3- bi: blocks sent out to a block device (in blocks/s) I would say that I probably agree, things should be relative to the cpu. However, it doesn't seem to be something that was universally agreed upon. Or maybe the man-pages were all wrong, and only got updated recently. Looks like the man pages are wrong, for RH7.3 at least. It says bi is 'blocks written', but an actual test like 'dd if=/dev/zero of=/tmp/test bs=1024 count=16384' on an otherwise nearly idle RH7.3 box gives: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 75936 474704 230452 953580 0 0 0 0 106 2527 0 0 99 0 0 0 75936 474704 230452 953580 0 0 0 16512 376 2572 0 2 98 0 0 0 75936 474704 230452 953580 0 0 0 0 105 2537 0 0 100 Which is in line with bo being 'blocks written'. M ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
I don't have iostat on that machine, but vmstat shows a lot of writes to the drives, and the runnable processes are more than 1: 6 1 0 3617652 292936 279192800 0 52430 1347 4681 25 19 20 37 Assuming that's the output of 'vmstat 1' and not some other delay, 50MB/second of sustained writes is usually considered 'a lot'. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Anything to be gained from a 'Postgres Filesystem'?
I suppose I'm just idly wondering really. Clearly it's against PG philosophy to build an FS or direct IO management into PG, but now it's so relatively easy to plug filesystems into the main open-source Oses, It struck me that there might be some useful changes to, say, XFS or ext3, that could be made that would help PG out. I'm thinking along the lines of an FS that's aware of PG's strategies and requirements and therefore optimised to make those activities as efiicient as possible - possibly even being aware of PG's disk layout and treating files differently on that basis. Not being an FS guru I'm not really clear on whether this would help much (enough to be worth it anyway) or not - any thoughts? And if there were useful gains to be had, would it need a whole new FS or could an existing one be modified? So there might be (as I said, I'm not an FS guru...): * great append performance for the WAL? * optimised scattered writes for checkpointing? * Knowledge that FSYNC is being used for preserving ordering a lot of the time, rather than requiring actual writes to disk (so long as the writes eventually happen in order...)? Matt Matt Clark Ymogen Ltd P: 0845 130 4531 W: https://ymogen.net/ M: 0774 870 1584 ---(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] OS desicion
You are asking the wrong question. The best OS is the OS you (and/or the customer) knows and can administer competently. The real performance differences between unices are so small as to be ignorable in this context. The context switching bug is not OS-dependent, but varys in severity across machine architectures (I understand it to be mostly P4/Athlon related, but don't take my word for it). M Tom Fischer wrote: Hi List, I have a Dual-Xeon 3Ghz System with with GB RAM and an Adaptec 212ß SCSI RAID with 4 SCA Harddiscs. Our customer wants to have the Machine tuned for best Database performance. Which OS should we used? We are tending between Linux 2.6 or FreeBSD. The Database Size is 5GB and ascending. Most SQL-Queries are Selects, the Tablesizes are beetween 300k and up to 10 MB. I've read the Hardware Performance Guide and the result was to take FreeBSD in the Decision too :) And what is on this Context Switiching Bug i have read in the Archive? Hope you can help me Regards Tom ---(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 ---(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] how much mem to give postgres?
How would I turn that off? In the kernel config? Not too familiar with that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres, so I hope HT isn't a problem. If HT is turned off, does it just not use the other half of the processor? Or does the processor just work as one unit? You turn it off in the BIOS. There is no 'other half', the processor is just pretending to have two cores by shuffling registers around, which gives maybe a 5-10% performance gain in certain multithreaded situations. opinionA hack to overcome marchitactural limitations due to the overly long pipeline in the Prescott core./opinion. Really of most use for desktop interactivity rather than actual throughput. M ---(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] OS desicion
The real performance differences between unices are so small as to be ignorable in this context. <> Well, at least the difference between Linux and BSD. There are substantial tradeoffs should you chose to use Solaris or UnixWare. Yes, quite right, I should have said 'popular x86-based unices'.
Re: [PERFORM] how much mem to give postgres?
OT Hyperthreading is actually an excellent architectural feature that can give significant performance gains when implemented well and used for an appropriate workload under a decently HT aware OS. IMO, typical RDBMS streams are not an obviously appropriate workload, Intel didn't implement it particularly well and I don't think there are any OSes that support it particularly well. /OT But don't write off using it in the future, when it's been improved at both the OS and the silicon levels. You are quite right of course - unfortunately the current Intel implementation meets nearly none of these criteria! As Rod Taylor pointed out off-list, IBM's SMT implementation on the Power5 is vastly superior. Though he's also just told me that Sun is beating IBM on price/performance for his workload, so who knows how reliable a chap he is... ;-) M ---(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] Select with qualified join condition / Batch inserts
SELECT cmp.WELL_INDEX, cmp.COMPOUND, con.CONCENTRATION FROM SCR_WELL_COMPOUND cmp, SCR_WELL_CONCENTRATION con WHERE cmp.BARCODE=con.BARCODE AND cmp.WELL_INDEX=con.WELL_INDEX AND cmp.MAT_ID=con.MAT_ID AND cmp.MAT_ID = 3 AND cmp.BARCODE='910125864' AND cmp.ID_LEVEL = 1; Quick guess - type mismatch forcing sequential scan. Try some quotes: AND cmp.MAT_ID = '3' AND cmp.BARCODE='910125864' AND cmp.ID_LEVEL = '1'; M ---(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] Opteron vs RHAT
trainwreck... If you're going through IBM, then they won't want to respond to any issues if you're not running a bog-standard RHAS/RHES release from Red Hat. ... To be fair, we keep on actually running into things that _can't_ be backported, like fibrechannel drivers that were written to take advantage of changes in the SCSI support in 2.6. I thought IBM had good support for SUSE? I don't know why I thought that... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] IBM P-series machines
As for vendor support for Opteron, that sure looks like a trainwreck... If you're going through IBM, then they won't want to respond to any issues if you're not running a bog-standard RHAS/RHES release from Red Hat. And that, on Opteron, is preposterous, because there's plenty of the bits of Opteron support that only ever got put in Linux 2.6, whilst RHAT is still back in the 2.4 days. To be fair, they have backported a boatload of 2.6 features to their kernel: http://www.redhat.com/software/rhel/kernel26/ And that page certainly isn't an exhaustive list... M ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Which plattform do you recommend I run PostgreSQL for
In the MySQL manual it says that MySQL performs best with Linux 2.4 with ReiserFS on x86. Can anyone official, or in the know, give similar information regarding PostgreSQL? I'm neither official, nor in the know, but I do have a spare moment! I can tell you that any *NIX variant on any modern hardware platform will give you good performance, except for Cygwin/x86. Any differences between OSes on the same hardware are completely swamped by far more direct concerns like IO systems, database design, OS tuning etc. Pick the OS you're most familiar with is usually a good recommendation (and not just for Postgres). ---(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] Caching of Queries
OK, that'd work too... the point is if you're re-connecting all the time it doesn't really matter what else you do for performance. Yeah, although there is the chap who was asking questions on the list recently who had some very long-running code on his app servers, so was best off closing the connection because he had far too many postmaster processes just sitting there idle all the time! But you're right, it's a killer usually. M ---(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] Caching of Queries
Basically you set a default in seconds for the HTML results to be cached, and then have triggers set that force the cache to regenerate (whenever CRUD happens to the content, for example). Can't speak for Perl/Python/Ruby/.Net/Java, but Cache_Lite sure made a believer out of me! Nice to have it in a library, but if you want to be that simplistic then it's easy in any language. What if a process on server B modifies a n important value that server A has cached though? Coherency (albeit that the client may choose to not use it) is a must for a general solution. ---(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] Caching of Queries
More to the point though, I think this is a feature that really really should be in the DB, because then it's trivial for people to use. How does putting it into PGPool make it any less trivial for people to use? The answers are at http://www2b.biglobe.ne.jp/~caco/pgpool/index-e.html . Specifically, it's a separate application that needs configuration, the homepage has no real discussion of the potential pitfalls of pooling and what this implementation does to get around them, you get the idea. I'm sure it's great software, but it doesn't come as part of the DB server, so 95% of people who would benefit from query caching being implemented in it never will. If it shipped with and was turned on by default in SUSE or RedHat that would be a different matter. Which I realise makes me look like one of those people who doesn't appreciate code unless it's 'popular', but I hope I'm not *that* bad... Oh OK, I'll say it, this is a perfect example of why My*** has so much more mindshare. It's not better, but it sure makes the average Joe _feel_ better. Sorry, I've got my corporate hat on today, I'm sure I'll feel a little less cynical tomorrow. M
Re: [PERFORM] Caching of Queries
Any competently written application where caching results would be a suitable performance boost can already implement application or middleware caching fairly easily, and increase performance much more than putting result caching into the database would. I guess the performance increase is that you can spend $10,000 on a developer, or $10,000 on hardware, and for the most part get a more reliable result the second way. MemcacheD is fine(ish), but it's not a panacea, and it's more than easy to shoot yourself in the foot with it. Caching is hard enough that lots of people do it badly - I'd rather use an implementation from the PG team than almost anywhere else. I don't see caching results in the database as much of a win for most well written applications. Toy benchmarks, sure, but for real apps it seems it would add a lot of complexity, and violate the whole point of using an ACID database. Well the point surely is to _remove_ complexity from the application, which is written by God Knows Who, and put it in the DB, which is written by God And You. And you can still have ACID (cached data is not the same as stale data, although once you have the former, the latter can begin to look tempting sometimes). M ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Caching of Queries
I've looked at PREPARE, but apparently it only lasts per-session - that's worthless in our case (web based service, one connection per data-requiring connection). That's a non-sequitur. Most 'normal' high volume web apps have persistent DB connections, one per http server process. Are you really dropping DB connections and reconnecting each time a new HTTP request comes in? M ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] insert
It is likely that you are missing an index on one of those foreign key'd items. I don't think that is too likely as a foreign key reference must be a unique key which would have an index. I think you must be thinking of primary keys, not foreign keys. All one-to-many relationships have non-unique foreign keys. ---(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] Performance Bottleneck
And this is exactly where the pgpool advantage lies. Especially with the TPC-W, the Apache is serving a mix of PHP (or whatever CGI technique is used) and static content like images. Since the 200+ Apache kids serve any of that content by random and the emulated browsers very much encourage it to ramp up MaxClients children by using up to 4 concurrent image connections, one does end up with MaxClients DB connections that are all relatively low frequently used. In contrast to that the real pgpool causes lesser, more active DB connections, which is better for performance. There are two well-worn and very mature techniques for dealing with the issue of web apps using one DB connection per apache process, both of which work extremely well and attack the issue at its source. 1) Use a front-end caching proxy like Squid as an accelerator. Static content will be served by the accelerator 99% of the time. Additionally, large pages can be served immediately to the accelerator by Apache, which can then go on to serve another request without waiting for the end user's dial-up connection to pull the data down. Massive speedup, fewer apache processes needed. 2) Serve static content off an entirely separate apache server than the dynamic content, but by using separate domains (e.g. 'static.foo.com'). Personally I favour number 1. Our last biggish peak saw 6000 open HTTP and HTTPS connections and only 200 apache children, all of them nice and busy, not hanging around on street corners looking bored. During quiet times Apache drops back to its configured minimum of 40 kids. Option 2 has the advantage that you can use a leaner build for the 'dynamic' apache server, but with RAM so plentiful these days that's a less useful property. Basically this puts the 'pooling' back in the stateless HTTP area where it truly belongs and can be proven not to have any peculiar side effects (especially when it comes to transaction safety). Even better, so long as you use URL parameters for searches and the like, you can have the accelerator cache those pages for a certain time too so long as slightly stale results are OK. I'm sure pgpool and the like have their place, but being band-aids for poorly configured websites probably isn't the best use for them. M ---(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] Swapping in 7.4.3
This is normal. My personal workstation has been up for 16 days, and it shows 65 megs used for swap. The linux kernel looks for things that haven't been accessed in quite a while and tosses them into swap to free up the memory for other uses. This isn't PostgreSQL's fault, or anything elses. It's how a typical Unix kernel works. I.e. you're seeing a problem that simply isn't there. Actually it (and other OSes) does slightly better than that. It _copies_ the least recently used pages into swap, but leaves them in memory. Then when there really is a need to swap stuff out there is no need to actually write to swap because it's already been done, and conversely if those pages are wanted then they don't have to be read from disk because they were never removed from memory. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Postgres over Linux NBD or NFS
How about iSCSI? This is exactly what it's for - presenting a bunch of remote SCSI hardware as if it were local. There are several reference implementations on SourceForge from Intel, Cisco others. I've never tried it myself, but I would if I had the need. And let's face it there are some very big players selling very pricey kit that uses it, so you should have pretty high confidence that the fundamentals are strong. M The other goal is to be able to stick LOTS of disk into one box, and dole it out to multiple servers. It's more expensive to set up and manage 3 RAID arrays than it is to set up and manage just 1, because you have to manage 3 sets of disk hardware rather than 1. [snip] The poor man's approach involves trying to fake this by building a disk box running Linux that exports the storage either as a filesystem (using NFS) or as disk blocks (NBD). NFS clearly doesn't provide the filesystem semantics needed to get decent reliability; with NBD, it's not clear what happens :-(. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Wierd context-switching issue on Xeon
As a cross-ref to all the 7.4.x tests people have sent in, here's 7.2.3 (Redhat 7.3), Quad Xeon 700MHz/1MB L2 cache, 3GB RAM. Idle-ish (it's a production server) cs/sec ~5000 3 test queries running: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 3 0 0 23380 577680 105912 2145140 0 0 0 0 107 116890 50 14 35 2 0 0 23380 577680 105912 2145140 0 0 0 0 114 118583 50 15 34 2 0 0 23380 577680 105912 2145140 0 0 0 0 107 115842 54 14 32 2 1 0 23380 577680 105920 2145140 0 0 032 156 117549 50 16 35 HTH Matt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: 20 April 2004 01:02 To: [EMAIL PROTECTED] Cc: Joe Conway; scott.marlowe; Bruce Momjian; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Neil Conway Subject: Re: [PERFORM] Wierd context-switching issue on Xeon Here is a test case. To set up, run the test_setup.sql script once; then launch two copies of the test_run.sql script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: vmstat 1 shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. I haven't bothered to check how long the test_run query takes, but if it ends while you're still examining the behavior, just start it again. Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux
Now if these vendors could somehow eliminate downtime due to human error we'd be talking *serious* reliablity. You mean making the OS smart enough to know when clearing the arp cache is a bonehead operation, or just making the hardware smart enough to realise that the keyswitch really shouldn't be turned while 40 people are logged in? (Either way, I agree this'd be an improvement. It'd sure make colocation a lot less painful.) Well I was joking really, but those are two very good examples! Yes, machines should require extra confirmation for operations like those. Hell, even a simple 'init 0' would be well served by a prompt that says There are currently 400 network sockets open, 50 remote users logged in, and 25 disk IOs per second. What's more, there's nobody logged in at the console to boot me up again afterwards - are you _sure_ you want to shut the machine down?. It's also crazy that there's no prompt after an 'rm -rf' (we could have 'rm -rf --iacceptfullresponsibility' for an unprompted version). Stuff like that would have saved me from a few embarrassments in the past for sure ;-) It drives me absolutely nuts every time I see a $staggeringly_expensive clustered server whose sysadmins are scared to do a failover test in case something goes wrong! Or which has worse uptime than my desktop PC because the cluster software's poorly set up or administered. Or which has both machines on the same circuit breaker. I could go on but it's depressing me. Favourite anecdote: A project manager friend of mine had a new 'lights out' datacenter to set up. The engineers, admins and operators swore blind that everything had been tested in every possible way, and that incredible uptime was guaranteed. 'So if I just pull this disk out everything will keep working?' he asked, and then pulled the disk out without waiting for an answer... Ever since he told me that story I've done exactly that with every piece of so-called 'redundant' hardware a vendor tries to flog me. Ask them to set it up, then just do nasty things to it without asking for permission. Less than half the gear makes it through that filter, and actually you can almost tell from the look on the technical sales rep's face as you reach for the drive/cable/card/whatever whether it will or won't. M ---(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] [ADMIN] Benchmarking postgres on Solaris/Linux
If it's going to be write intensive then the RAID controller will be the most important thing. A dual p3/500 with a write-back cache will smoke either of the boxes you mention using software RAID on write performance. As for the compute intensive side (complex joins sorts etc), the Dell will most likely beat the Sun by some distance, although what the Sun lacks in CPU power it may make up a bit in memory bandwidth/latency. Matt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Subbiah, Stalin Sent: 23 March 2004 18:41 To: 'Andrew Sullivan'; '[EMAIL PROTECTED]' Subject: Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux We are looking into Sun V210 (2 x 1 GHz cpu, 2 gig ram, 5.8Os) vs. Dell 1750 (2 x 2.4 GHz xeon, 2 gig ram, RH3.0). database will mostly be write intensive and disks will be on raid 10. Wondering if 64bit 1 GHz to 32bit 2.4 GHz make a big difference here. Thanks! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Andrew Sullivan Sent: Tuesday, March 23, 2004 9:37 AM To: '[EMAIL PROTECTED]' Subject: Re: [PERFORM] [ADMIN] Benchmarking postgres on Solaris/Linux On Mon, Mar 22, 2004 at 04:05:45PM -0800, Subbiah, Stalin wrote: being the key performance booster for postgres. what is the preferred OS for postgres deployment if given an option between linux and solaris. As One thing this very much depends on is what you're trying to do. Suns have a reputation for greater reliability. While my own experience with Sun hardware has been rather shy of sterling, I _can_ say that it stands head and shoulders above a lot of the x86 gear you can get. If you're planning to use Solaris on x86, don't bother. Solaris is a slow, bloated pig compared to Linux, at least when it comes to managing the largish number of processes that Postgres requires. If pure speed is what you're after, I have found that 2-way, 32 bit Linux on P-IIIs compares very favourably to 4 way 64 bit Ultra SPARC IIs. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] insert speed - Mac OSX vs Redhat
On a variety of hardware with Redhat, and versions of postgres, we're not getting much better than 50 inserts per second. This is prior to moving WAL to another disk, and fsync is on. However, with postgres 7.4 on Mac OSX 10.2.3, we're getting an amazing 500 inserts per second. We can only put this down to the OS. ^^^ You haven't really produced much evidence to support that statement. Given that the differences in performance between Postgres running on *BSD and Linux on Intel hardware are not large at all, it seems to be almost certainly false in fact. It may of course be due to some settings of the different OSes, but not the OSes themselves. It would help if you gave a straight PG7.4 comparison with hardware specs as well, and config file differences if any. One thought: assuming the Apple has IDE disks, then the disks probably have write caching turned on, which is good for speed, but not crash-safe. matt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Adding RAM: seeking advice warnings of hidden gotchas
If you have 3 1.5GB tables then you might as well go for 4GB while you're at it. Make sure you've got a bigmem kernel either running or available, and boost effective_cache_size by whatever amount you increase the RAM by. We run a Quad Xeon/4GB server on Redhat 7.3 and it's solid as a rock. There is no way I know of to get indexes preferentially cached over data though. Matt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Nick Fankhauser Sent: 17 December 2003 19:57 To: [EMAIL PROTECTED] Org Subject: [PERFORM] Adding RAM: seeking advice warnings of hidden gotchas Hi- After having done my best to squeeze better performance out of our application by tuning within our existing resources, I'm falling back on adding memory as a short-term solution while we get creative for a long-term fix. I'm curious about what experiences others have had with the process of adding big chunks of RAM. In particular, if I'm trying to encourage the OS to cache more of my index information in RAM, what sort of configuration should I do at both the PostgreSQL and OS level? In a slightly off-topic vein, I'd also like to hear about it if anyone knows about any gotchas at the OS level that might become a problem. The server is a dual processor Athlon 1.2GHz box with hardware SCSI RAID. It currently has 1 GB RAM, and we're planning to add one GB more for a total of 2GB. The OS is Debian Linux Kernel 2.4.x, and we're on PostgreSQL v7.3.2 My current memory related settings are: SHMMAX and SHMALL set to 128MB (OS setting) shared buffers 8192 (64MB) sort_mem 16384 (16MB) effective_cache_size 65536 (512MB) We support up to 70 active users, sharing a connection pool of 16 connections. Most of the queries center around 3 tables that are about 1.5 GB each. Thanks. -Nick - Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] tuning questions
I ended up going back to a default postgresql.conf and reapplying the various tunings one-by-one. Turns out that while setting fsync = false had little effect on the slow IDE box, it had a drastic effect on this faster SCSI box and performance is quite acceptable now (aside from the expected falloff of about 30% after the first twenty minutes, which I believe comes from growing and shrinking tables without vacuumdb --analyzing). Hmm. I wonder if that could be related to the issue where many IDE drives have write-caching enabled. With the write cache enabled fsyncs are nearly immediate, so setting fsync=false makes little difference... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] reindex/vacuum locking/performance?
On Sat, Oct 04, 2003 at 12:29:55AM +0100, Matt Clark wrote: My real world experience on a *very* heavily updated OLTP type DB, following advice from this list (thanks guys!), is that there is essentially zero cost to going ahead and vacuuming as often as you feel like it. Go crazy, and speed up your DB! That's not quite true. If vacuums start running into each other, you can very easily start eating up all your I/O bandwidth. Even if you gots lots of it. Very true, which is why all my scripts write a lockfile and delete it when they're finished, to prevent that happening. I should have mentioned that. Also, a vacuum pretty much destroys your shared buffers, so you have to be aware of that trade-off too. Vacuum is not free. It's _way_ cheaper than it used to be, though. That's _very_ interesting. I've never been quite clear what's in shared buffers apart from scratch space for currently running transactions. Also the docs imply that vacuum uses it's own space for working in. Do you have more info on how it clobbers shared_buffers? M ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] reindex/vacuum locking/performance?
The point is that a big seqscan (either VACUUM or a plain table scan) hits a lot of pages, and thereby tends to fill your cache with pages that aren't actually likely to get hit again soon, perhaps pushing out pages that will be needed again soon. This happens at both the shared-buffer and kernel-disk-cache levels of caching. OK, I had thought (wrongly it seems, as usual, but this is how we learn!) that a plain VACUUM did not incur a read of all pages. I still don't understand *why* it does, but I'll take your word for it. Clearly if it distorts the 'normal' balance of pages in any caches, PG's or the OS's, that's a _bad thing_. I am currently in the nice position of having a DB that (just about) fits in RAM, so I pretty much don't care about read performance, but I will have to soon as it grows beyond 3GB :-( These conversations are invaluable in planning for that dread time... It would be good to find some way to prevent big seqscans from populating cache, but I don't know of any portable way to tell the OS that we don't want it to cache a page we are reading. Quite. The only natural way would be to read those pages through some special device, but then you might as well do raw disk access from the get-go. Portability vs. Performance, the age old quandary. FWIW I and many others stand back in pure amazement at the sheer _quality_ of PostgreSQL. Rgds, Matt ---(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] reindex/vacuum locking/performance?
vacuum full does require exclusive lock, plain vacuum does not. I think I need full, because there are updates on the table. As I understand it, an update in pg is an insert/delete, so it needs to be garbage collected. Yes and no. You only need a plain VACUUM that is run often enough to recover space as fast as you need to grab it. For heavily updated tables run it often - I run it every 5 minutes on some tables. A VACUUM FULL is only needed if you haven't been running VACUUM often enough in the first place. The description of vacuum full implies that is required if the db is updated frequently. This db gets about 1 txn a second, possibly more at peak load. Assuming you mean 1 update/insert per second that is an absolutely _trivial_ load on any reasonable hardware. You can do thousands of updates/second on hardware costing less than $2000. If you vacuum every hour then you will be fine. IOW, vacuum+reindex is faster than dump+restore? I didn't see this, then again, I had this locking problem, so the stats are distorted. REINDEX also locks tables like VACUUM FULL. Either is terribly slow, but unless you turn off fsync during the restore it's unlikely to be slower than dump restore. Matt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] reindex/vacuum locking/performance?
In summary, I suspect that it is better from a UI perspective to bring down the app on Sat at 3 a.m and reimport with a fixed time period than to live through reindexing/vacuuming which may deadlock. Am I missing something? Consider running pg_autovacuum, and thereby do a little bit of vacuuming here and there all the time. It DOESN'T block, so unless your system is really busy, it shouldn't slow things down to a major degree. My real world experience on a *very* heavily updated OLTP type DB, following advice from this list (thanks guys!), is that there is essentially zero cost to going ahead and vacuuming as often as you feel like it. Go crazy, and speed up your DB! OK, that's on a quad CPU box with goodish IO, so maybe there are issues on very slow boxen, but in a heavy-update environment the advantages seem to easily wipe out the costs. Matt p.s. Sorry to sound like a Shake'n'Vac advert. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] reindex/vacuum locking/performance?
Also, if you find that you need to run VACUUM FULL often, then you need to raise your max_fsm_pages. Yes and no. If it's run often enough then the number of tracked pages shouldn't need to be raised, but then again... ...max_fsm_pages should be raised anyway. I'm about to reclaim a Pentium 166 w/ 64MB of RAM from a friend I lent it to _many_ years ago, and I suspect PG would run happily on it as configured by default. Set it to at least 50,000 I say. What do you have to lose, I mean if they're not free then they're not tracked in the FSM right? Of course if anyone knows a reason _not_ to raise it then I'm all ears! Matt -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] reindex/vacuum locking/performance?
Also, if you find that you need to run VACUUM FULL often, then you need to raise your max_fsm_pages. Yes and no. If it's run often enough then the number of tracked pages shouldn't need to be raised, but then again... Oops, sorry, didn't pay attention and missed the mention of FULL. My bad, ignore my OT useless response. ---(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] advice on raid controller
As others have mentioned, you really ought to get battery-backed cache if you're doing any volume of writes. The ability to do safe write-back caching makes an *insane* difference to write performance. The site you link to also has that for only 15% more money: http://uk.azzurri.com/product/product.cgi?productId=80 No experience with the card(s) I'm afraid. In general though, U320 will only be faster than U160 for large sequential reads, or when you have silly numbers of disks on a channel (i.e. more than 4/channel). If you have silly numbers of disks, then RAID5 will probably be better, if you have 4 disks total then RAID1+0 will probably be better. In between it depends on all sorts of other factors. Bear in mind though that if you *do* have silly numbers of disks then more channels and more cache will count for more than anything else, so spend the money on that rather than latest-and-greatest performance for a single channel. HTH Matt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Jones Sent: 27 September 2003 18:25 To: [EMAIL PROTECTED] Subject: [PERFORM] advice on raid controller Hi, i'm on the verge of buying a MegaRAID SCSI 320-2 raid controller. I need it to build a db server using 4x ultra320 scsi disks i'm thinking raid 1+0 but will try with raid5 too and compare Does anyone have any experience with this model, good or bad i'd like to know.. thanks :) as seen: http://uk.azzurri.com/product/product.cgi?productId=188 Regards, Richard. PS: whoever mentioned starting a site with raid controller reviews, excellent idea - its hard to find decent info on which card to buy. ---(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 ---(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] Index problem
There are about 2500 rows in that table. 1st query explain analyze: Seq Scan on PRIORITY_STATISTICS (cost=0.00..491.44 rows=127 width=12) (actual time=98.58..98.58 rows=0 loops=1) Total runtime: 98.74 msec 2nd query explain analyze: NOTICE: QUERY PLAN: Index Scan using PRIORITY_STATISTICS_reo_id, PRIORITY_STATISTICS_reo_id, [snip] PRIORITY_STATISTICS_reo_id on PRIORITY_STATISTICS (cost=0.00..394.06 rows=102 width=12) (actual time=20.93..20.93 rows=0 loops=1) Total runtime: 21.59 msec With only 2500 rows the planner could be deciding that it's going to have to read every disk block to do an index scan anyway, so it might as well do a sequential scan. If the pages are in fact in the kernel cache then the compute time will dominate, not the IO time, so it ends up looking like a bad plan, but it's probably not really such a bad plan... Is your effective_cache_size set to something sensibly large? You could also try decreasing cpu_index_tuple_cost and cpu_tuple_cost. These will affect all your queries though, so what you gain on one might be lost on another. Matt ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Is there a reason _not_ to vacuum continuously?
I'm running a load of stress scripts against my staging environment to simulate user interactions, and watching the various boxen as time goes by. I noticed that the CPU utilisation on the DB server (PG 7.2.3, RH7.3, Dual PII 550MHz, 1GB RAM, 1GB database on disk, Single 10k SCSI drive) was increasing over time, and manually launched a vacuum analyze verbose. A typical output from the VAV is: NOTICE: --Relation mobilepm-- NOTICE: Index mobilepm_ownerid_idx: Pages 1103; Tuples 32052: Deleted 46012. CPU 0.15s/0.66u sec elapsed 14.82 sec. NOTICE: Index mobilepm_id_idx: Pages 1113; Tuples 32143: Deleted 46012. CPU 0.33s/1.08u sec elapsed 45.89 sec. NOTICE: Index mobilepm_ownerid_status_idx: Pages 1423; Tuples 32319: Deleted 46 012. CPU 0.52s/1.05u sec elapsed 54.59 sec. NOTICE: Index mobilepm_number_idx: Pages 1141; Tuples 32413: Deleted 46012. CPU 0.26s/0.61u sec elapsed 16.13 sec. NOTICE: Removed 46012 tuples in 2548 pages. CPU 0.88s/0.79u sec elapsed 75.57 sec. NOTICE: Pages 3188: Changed 10, Empty 0; Tup 32007: Vac 46012, Keep 11, UnUsed 0. Total CPU 2.56s/4.25u sec elapsed 216.50 sec. NOTICE: --Relation pg_toast_112846940-- NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. NOTICE: Analyzing mobilepm So you can see that some tables are seeing a hell of a lot of updates. That's life, and yes, I do need all those indexes :-) Now I see no drop in performance while the VAV is running, the CPU utilisation gradually drops from 80% to 30% on the DB server, and life in general improves. On the live server (PG 7.2.3, RH7.3, Quad Xeon 700Mhz 1MB cache, 4Gb RAM, 256MB write-back RAID10 over 4 10K disks) I vacuum analyze daily, and vacuum analyze a couple of key tables every 15 minutes, but my question is... *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per table affect that? Cheers Matt Postscript: I may have answered my own question while writing this mail. Under the current stress test load about 10% of the key tables' tuples are updated between sequential vacuum-analyzes, so the received wisdom on intervals suggests '0' in my case anyway... ---(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] Is there a reason _not_ to vacuum continuously?
Yes, that makes sense. My worry is really the analyzes. I gather/imagine that: 1) Indexes on fields that are essentially random gain little from being analyzed. 2) Fields that increase monotonically with insertion order have a problem with index growth in 7.2. There may be a performance issue connected with this, although indexes on these fields also gain little from analysis. So if I can't vacuum full I'm SOL anyway and should upgrade to 7.4.1 when available? Further data: When I run a vacuum analyze my app servers do see an increase in response time from PG, even though the DB server is under no more apparent load. I can only assume some kind of locking issue. Is that fair? M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of scott.marlowe Sent: 17 September 2003 20:55 To: Matt Clark Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Is there a reason _not_ to vacuum continuously? On Wed, 17 Sep 2003, Matt Clark wrote: *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per table affect that? Generally, the only time continuous vacuuming is a bad thing is when you are I/O bound. If you are CPU bound, then continuous vacuuming is usually acceptable. ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
2) Are you sure that ANALYZE is needed? Vacuum is required whenever lots of rows are updated, but analyze is needed only when the *distribution* of values changes significantly. You are right. I have a related qn in this thread about random vs. monotonic values in indexed fields. 3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes often (daily?). This issue will go away in 7.4, which should make you an early adopter of 7.4. I understand this needs an exclusive lock on the whole table, which is simply not possible more than once a month, if that... Workarounds/hack suggestions are more than welcome :-) Ta M ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Inconsistent performance
... #effective_cache_size = 1000# typically 8KB each That's horribly wrong. It's telling PG that your OS is only likely to cache 8MB of the DB in RAM. If you've got 1GB of memory it should be between 64000 and 96000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] best arrangement of 3 disks for (insert) performance
the machine will be dealing with lots of inserts, basically as many as we can throw at it If you mean lots of _transactions_ with few inserts per transaction you should get a RAID controller w/ battery backed write-back cache. Nothing else will improve your write performance by nearly as much. You could sell the RAM and one of the CPU's to pay for it ;-) If you have lots of inserts but all in a few transactions then it's not quite so critical. M ---(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] slow plan for min/max
Actually, referring down to later parts of this thread, why can't this optimisation be performed internally for built-in types? I understand the issue with aggregates over user-defined types, but surely optimising max() for int4, text, etc is safe and easy? Sorry, missed the bit about user-defined functions. So I should have said built-in functions operating over built-in types. Which does sound more complicated, but anyone redefining max() is surely not in a position to seek sympathy if they lose performance? ---(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] slow plan for min/max
Matt Clark [EMAIL PROTECTED] writes: Actually, referring down to later parts of this thread, why can't this optimisation be performed internally for built-in types? I understand the issue with aggregates over user-defined types, but surely optimising max() for int4, text, etc is safe and easy? I can't see that the datatype involved has anything to do with it. None of the issues that come up in making the planner do this are datatype-specific. You could possibly avoid adding some columns to pg_aggregate if you instead hard-wired the equivalent knowledge (for builtin types only) into some code somewhere, but a patch that approached it that way would be rejected as unmaintainable. I don't pretend to have any useful knowledge of the internals of this, so much of what I write may seem like noise to you guys. The naive question is 'I have an index on X, so finding max(X) should be trivial, so why can't the planner exploit that triviality?'. AFAICS the short sophisticated answer is that it just isn't trivial in the general case. Upon rereading the docs on aggregates I see that it really isn't trivial at all. Not even knowing things like 'this index uses the same function as this aggregate' gets you very far, because of the very general nature of the implementation of aggs. So it should be flagged very prominently in the docs that max() and min() are almost always not what 90% of people want to use 90% of the time, because indexes do the same job much better for anything other than tiny tables. Know what we (OK, I) need? An explicitly non-aggregate max() and min(), implemented differently, so they can be optimised. let's call them idx_max() and idx_min(), which completely bypass the standard aggregate code. Because let's face it, in most cases where you regularly want a max or a min you have an index defined, and you want the DB to use it. And I would volunteer to do it, I would, but you really don't want my C in your project ;-) I do volunteer to do some doc tweaking though - who do I talk to? M ---(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] Hardware recommendations to scale to silly load
Just how big do you expect your DB to grow? For a 1GB disk-space database, I'd probably just splurge for an SSD hooked up either via SCSI or FibreChannel. Heck, up to about 5Gb or so it is not that expensive (about $25k) and adding another 5Gb should set you back probably another $20k. I use an SSD from Imperial Technology ( http://www.imperialtech.com/ ) for mail spools. My database is way to big for my budget to put in SSD. I may well be able to split some tables that aren't used in joins into a separate DB, and could well use an SSD for those. In fact two of the inserts per user interaction could be split off, and they're not financially important tables, so fsync=false could be enabled for those, in which case an SSD might be overkill... The whole thing will definitely *not* fit in an SSD for a sensible price, but the WAL might well! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware recommendations to scale to silly load
Ok.. I would be surprised if you needed much more actual CPU power. I suspect they're mostly idle waiting on data -- especially with a Quad Xeon (shared memory bus is it not?). In reality the CPUs get pegged: about 65% PG and 35% system. But I agree that memory throughput and latency is an issue. Write performance won't matter very much. 3000 inserts/second isn't high -- some additional battery backed write cache may be useful but not overly important with enough ram to hold the complete dataset. I suspect those are slow due to things like foreign keys -- which of course are selects. 3000 inserts/sec isn't high when they're inside one transaction, but if each is inside its own transaction then that's 3000 commits/second. case, additional ram will keep the system from hitting the disk for writes as well. How does that work? You may want to play around with checkpoints. Prevention of a checkpoint during this hour will help prevent peaks. Be warned though, WAL will grow very large, and recovery time should a crash occur could be painful. Good point. I'll have a think about that. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
