Re: [pgsql-www] [PERFORM] Help speeding up delete
Perhaps we should put a link on the home page underneath LATEST RELEASEs saying 7.2: de-supported with a link to a scary note along the lines of the above. ISTM that there are still too many people on older releases. We probably need an explanation of why we support so many releases (in comparison to licenced software) and a note that this does not imply the latest releases are not yet production (in comparison to MySQL or Sybase who have been in beta for a very long time). By the way, is anyone interested in creating some sort of online repository on pgsql.org or pgfoundry where we can keep statically compiled pg_dump/all for several platforms for 8.1? That way if someone wanted to upgrade from 7.2 to 8.1, they can just grab the latest dumper from the website, dump their old database, then upgrade easily. But if they're upgrading to 8.1, don't they already have the new pg_dump? How else are they going to dump their *new* database? In my experience not many pgsql admins have test servers or the skills to build up test machines with the latest pg_dump, I don't, but I still dump with the latest version - works fine both on linux and windows for me... etc. (Seriously.) In fact, few realise at all that they should use the 8.1 dumper. That most people don't know they should use the new one I understand though. But I don't see how this will help against that :-) //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Help speeding up delete
On Thu, Nov 17, 2005 at 09:40:42AM +0800, Christopher Kings-Lynne wrote: In my experience not many pgsql admins have test servers or the skills to build up test machines with the latest pg_dump, etc. (Seriously.) In fact, few realise at all that they should use the 8.1 dumper. Isn't your distribution supposed to do this for you? Mine does these days... /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Hardware/OS recommendations for large databases (
Welty, Richard wrote: David Boreham wrote: I guess I've never bought into the vendor story that there are two reliability grades. Why would they bother making two different kinds of bearing, motor etc ? Seems like it's more likely an excuse to justify higher prices. then how to account for the fact that bleeding edge SCSI drives turn at twice the rpms of bleeding edge consumer drives? The motors spin twice as fast? I'm pretty sure the original comment was based on drives w/ similar specs. E.g. 7200RPM enterprise drives versus 7200RPM consumer drives. Next time one of my 7200RPM SCSIs fail, I'll take it apart and compare the insides to an older 7200RPM IDE from roughly the same era. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware/OS recommendations for large databases (5TB)
On Nov 16, 2005, at 4:50 PM, Claus Guttesen wrote: I'm (also) FreeBSD-biased but I'm not shure whether the 5 TB fs will work so well if tools like fsck are needed. Gvinum could be one option but I don't have any experience in that area. Then look into an external filer and mount via NFS. Then it is not FreeBSD's responsibility to manage the volume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Help speeding up delete
Isn't your distribution supposed to do this for you? Mine does these days... A distribution that tries to automatically do a major postgresql update is doomed to fail - spectacularly... Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-www] [PERFORM] Help speeding up delete
That way if someone wanted to upgrade from 7.2 to 8.1, they can just grab the latest dumper from the website, dump their old database, then upgrade easily. But if they're upgrading to 8.1, don't they already have the new pg_dump? How else are they going to dump their *new* database? Erm. Usually when you install the new package/port for 8.1, you cannot have both new and old installed at the same time man. Remember they both store exactly the same binary files in exactly the same place. In my experience not many pgsql admins have test servers or the skills to build up test machines with the latest pg_dump, I don't, but I still dump with the latest version - works fine both on linux and windows for me... So you're saying you DO have the skills to do it then... etc. (Seriously.) In fact, few realise at all that they should use the 8.1 dumper. That most people don't know they should use the new one I understand though. But I don't see how this will help against that :-) It'll make it easy... Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-www] [PERFORM] Help speeding up delete
Christopher Kings-Lynne wrote: That most people don't know they should use the new one I understand though. But I don't see how this will help against that :-) It'll make it easy... As the miscreant that caused this thread to get started, let me *wholeheartedly* agree with Chris. An easy way to get the pg_dump for the upgrade target to run with the upgradable source would work wonders. (Instructions included, of course.) -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-www] [PERFORM] Help speeding up delete
That way if someone wanted to upgrade from 7.2 to 8.1, they can just grab the latest dumper from the website, dump their old database, then upgrade easily. But if they're upgrading to 8.1, don't they already have the new pg_dump? How else are they going to dump their *new* database? Erm. Usually when you install the new package/port for 8.1, you cannot have both new and old installed at the same time man. Remember they both store exactly the same binary files in exactly the same place. Urrk. Didn't think of that. I always install from source on Unix, which doesn't have the problem. And the Windows port doesn't have this problem - it will put the binaries in a version dependant directory. One could claim the packages are broken ;-), but that's not gonig to help here, I know... (I always install in pgsql-version, and then symlink pgsql there..) etc. (Seriously.) In fact, few realise at all that they should use the 8.1 dumper. That most people don't know they should use the new one I understand though. But I don't see how this will help against that :-) It'll make it easy... You assume they know enough to download it. If they don't know to look for it, they still won't find it. But the bottom line: I can see how it would be helpful if you're on a distro which packages postgresql in a way that prevents you from installing more than one version at the same time. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Help speeding up delete
On Wed, 2005-11-16 at 19:40, Christopher Kings-Lynne wrote: Perhaps we should put a link on the home page underneath LATEST RELEASEs saying 7.2: de-supported with a link to a scary note along the lines of the above. ISTM that there are still too many people on older releases. We probably need an explanation of why we support so many releases (in comparison to licenced software) and a note that this does not imply the latest releases are not yet production (in comparison to MySQL or Sybase who have been in beta for a very long time). By the way, is anyone interested in creating some sort of online repository on pgsql.org or pgfoundry where we can keep statically compiled pg_dump/all for several platforms for 8.1? That way if someone wanted to upgrade from 7.2 to 8.1, they can just grab the latest dumper from the website, dump their old database, then upgrade easily. In my experience not many pgsql admins have test servers or the skills to build up test machines with the latest pg_dump, etc. (Seriously.) In fact, few realise at all that they should use the 8.1 dumper. I would especially like such a thing available as an RPM. A pgsql-8.1-clienttools.rpm or something like that, with psql, pg_dump, pg_restore, and what other command line tools you can think of that would help. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Help speeding up delete
On Thu, Nov 17, 2005 at 11:07:42PM +0800, Christopher Kings-Lynne wrote: Isn't your distribution supposed to do this for you? Mine does these days... A distribution that tries to automatically do a major postgresql update is doomed to fail - spectacularly... Automatically? Well, you can install the two versions side-by-side, and do pg_upgradecluster, which ports your configuration to the new version and does a pg_dump between the two versions; exactly what a system administrator would do. Of course, stuff _can_ fail, but it works for the simple cases, and a great deal of the not-so-simple cases. I did this for our cluster the other day (130 wildly different databases, from 7.4 to 8.1) and it worked flawlessly. I do not really see why all the distributions could do something like this, instead of mucking around with special statically compiled pg_dumps and the like... /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Strange query plan invloving a view
Rich Doughty [EMAIL PROTECTED] writes: However, the following query (which i believe should be equivalent) SELECT * FROM tokens.ta_tokenhist h INNER JOIN tokens.ta_tokens t ON h.token_id = t.token_id LEFT JOIN tokens.ta_tokenhist i ON t.token_id = i.token_id AND i.status = 'issued' LEFT JOIN tokens.ta_tokenhist s ON t.token_id = s.token_id AND s.status = 'sold' LEFT JOIN tokens.ta_tokenhist r ON t.token_id = r.token_id AND r.status = 'redeemed' WHERE h.sarreport_id = 9 ; No, that's not equivalent at all, because the implicit parenthesization is left-to-right; therefore you've injected the constraint to a few rows of ta_tokenhist (and therefore only a few rows of ta_tokens) into the bottom of the LEFT JOIN stack. In the other case the constraint is at the wrong end of the join stack, and so the full view output gets formed before anything gets thrown away. Some day the Postgres planner will probably be smart enough to rearrange the join order despite the presence of outer joins ... but today is not that day. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Hardware/OS recommendations for large databases (
On 11/16/05, William Yu [EMAIL PROTECTED] wrote: Alex Turner wrote: Spend a fortune on dual core CPUs and then buy crappy disks... I bet for most applications this system will be IO bound, and you will see a nice lot of drive failures in the first year of operation with consumer grade drives. Spend your money on better Disks, and don't bother with Dual Core IMHO unless you can prove the need for it. I would say the opposite -- you always want Dual Core nowadays. DC Opterons simply give you better bang for the buck than single core Opterons. Price out a 1xDC system against a 2x1P system -- the 1xDC will be cheaper. Do the same for 2xDC versus 4x1P, 4xDC versus 8x1P, 8xDC versus 16x1P, etc. -- DC gets cheaper by wider and wider margins because those mega-CPU motherboards are astronomically expensive. Opteron 242 - $178.00 Opteron 242 - $178.00 Tyan S2882 - $377.50 Total: $733.50 Opteron 265 - $719.00 Tyan K8E - $169.00 Total: $888.00 Tyan K8E - doesn't have any PCI-X, so it's also not apples to apples. Infact I couldn't find a single CPU slot board that did, so you pretty much have to buy a dual CPU board to get PCI-X. 1xDC is _not_ cheaper. Our DB application does about 5 queries/second peak, plus a heavy insert job once per day. We only _need_ two CPUs, which is true for a great many DB applications. Unless you like EJB of course, which will thrash the crap out of your system. Consider the two most used regions for DBs: a) OLTP - probably IO bound, large number of queries/sec updating info on _disks_, not requiring much CPU activity except to retrieve item infomration which is well indexed and normalized. b) Data wharehouse - needs CPU, but probably still IO bound, large data set that won't fit in RAM will required large amounts of disk reads. CPU can easily keep up with disk reads. I have yet to come across a DB system that wasn't IO bound. DC also gives you a better upgrade path. Let's say you do testing and figure 2x246 is the right setup to handle the load. Well instead of getting 2x1P, use the same 2P motherboard but only populate 1 CPU w/ a DC/270. Now you have a server that can be upgraded to +80% more CPU by popping in another DC/270 versus throwing out the entire thing to get a 4x1P setup. No argument there. But it's pointless if you are IO bound. The only questions would be: (1) Do you need a SMP server at all? I'd claim yes -- you always need 2+ cores whether it's DC or 2P to avoid IO interrupts blocking other processes from running. At least 2CPUs is always good for precisely those reasons. More than 2CPUs gives diminishing returns. (2) Does a DC system perform better than it's Nx1P cousin? My experience is yes. Did some rough tests in a drop-in-replacement 1x265 versus 2x244 and saw about +10% for DC. All the official benchmarks (Spec, Java, SAP, etc) from AMD/Sun/HP/IBM show DCs outperforming the Nx1P setups. Maybe true, but the 265 does have a 25% faster FSB than the 244, which might perhaps play a role. (3) Do you need an insane amount of memory? Well here's the case where the more expensive motherboard will serve you better since each CPU slot has its own bank of memory. Spend more money on memory, get cheaper single-core CPUs. Remember - large DB is going to be IO bound. Memory will get thrashed for file block buffers, even if you have large amounts, it's all gonna be cycled in and out again. Of course, this doesn't apply if you are an Intel/Dell-only shop. Xeon DCs, while cheaper than their corresponding single-core SMPs, don't have the same performance profile of Opteron DCs. Basically, you're paying a bit extra so your server can generate a ton more heat. Dell/Xeon/Postgres is just a bad combination any day of the week ;) Alex. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware/OS recommendations for large databases (
Just pick up a SCSI drive and a consumer ATA drive. Feel their weight. You don't have to look inside to tell the difference. Alex On 11/16/05, David Boreham [EMAIL PROTECTED] wrote: I suggest you read this on the difference between enterprise/SCSI and desktop/IDE drives: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf This is exactly the kind of vendor propaganda I was talking about and it proves my point quite well : that there's nothing specific relating to reliability that is different between SCSI and SATA drives cited in that paper. It does have a bunch of FUD such as 'oh yeah we do a lot more drive characterization during manufacturing'. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware/OS recommendations for large databases (
On 11/16/05, Joshua D. Drake [EMAIL PROTECTED] wrote: The only questions would be: (1) Do you need a SMP server at all? I'd claim yes -- you always need 2+ cores whether it's DC or 2P to avoid IO interrupts blocking other processes from running. I would back this up. Even for smaller installations (single raid 1, 1 gig of ram). Why? Well because many applications are going to be CPU bound. For example we have a PHP application that is a CMS. On a single CPU machine, RAID 1 it takes about 300ms to deliver a single page, point to point. We are not IO bound. So what happens is that under reasonable load we are actually waiting for the CPU to process the code. This is the performance profile for PHP, not for Postgresql. This is the postgresql mailing list. A simple upgrade to an SMP machine literally doubles our performance because we are still not IO bound. I strongly suggest that everyone use at least a single dual core because of this experience. Performance of PHP, not postgresql. (3) Do you need an insane amount of memory? Well here's the case where the more expensive motherboard will serve you better since each CPU slot has its own bank of memory. Spend more money on memory, get cheaper single-core CPUs. Agreed. A lot of times the slowest dual-core is 5x what you actually need. So get the slowest, and bulk up on memory. If nothing else memory is cheap today and it might not be tomorrow. [snip] Running postgresql on a single drive RAID 1 with PHP on the same machine is not a typical installation. 300ms for PHP in CPU time? wow dude - that's quite a page. PHP typical can handle up to 30-50 pages per second for a typical OLTP application on a single CPU box. Something is really wrong with that system if it takes 300ms per page. Alex. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware/OS recommendations for large databases (
Remember - large DB is going to be IO bound. Memory will get thrashed for file block buffers, even if you have large amounts, it's all gonna be cycled in and out again. 'fraid I have to disagree here. I manage ERP systems for manufacturing companies of various sizes. My systems are all completely cpu bound...even though the larger database are well into two digit gigabyte sizes, the data turnover while huge is relatively constrained and well served by the O/S cache. OTOH, query latency is a *huge* factor and we do everything possible to lower it. Even if the cpu is not 100% loaded, faster processors make the application 'feel' faster to the client. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware/OS recommendations for large databases (
Alex Turner wrote: Opteron 242 - $178.00 Opteron 242 - $178.00 Tyan S2882 - $377.50 Total: $733.50 Opteron 265 - $719.00 Tyan K8E - $169.00 Total: $888.00 You're comparing the wrong CPUs. The 265 is the 2x of the 244 so you'll have to bump up the price more although not enough to make a difference. Looks like the price of the 2X MBs have dropped since I last looked at it. Just a few months back, Tyan duals were $450-$500 which is what I was basing my priced less statement from. Tyan K8E - doesn't have any PCI-X, so it's also not apples to apples. Infact I couldn't find a single CPU slot board that did, so you pretty much have to buy a dual CPU board to get PCI-X. You can get single CPU boards w/ PCIe and use PCIe controller cards. Probably expensive right now because they're so bleeding-edge new but definitely on the downswing. a) OLTP - probably IO bound, large number of queries/sec updating info on _disks_, not requiring much CPU activity except to retrieve item infomration which is well indexed and normalized. Not in my experience. I find on our OLTP servers, we run 98% in RAM and hence are 100% CPU-bound. Our DB is about 50GB in size now, servers run w/ 8GB of RAM. We were *very* CPU limited running 2x244. During busy hours of the day, our avg user transaction time were jumping from 0.8sec to 1.3+sec. Did the 2x265 and now we're always in the 0.7sec to 0.9sec range. DC also gives you a better upgrade path. Let's say you do testing and figure 2x246 is the right setup to handle the load. Well instead of getting 2x1P, use the same 2P motherboard but only populate 1 CPU w/ a DC/270. Now you have a server that can be upgraded to +80% more CPU by popping in another DC/270 versus throwing out the entire thing to get a 4x1P setup. No argument there. But it's pointless if you are IO bound. Why would you just accept we're IO bound, nothing we can do? I'd do everything in my power to make my app go from IO bound to CPU bound -- whether by optimizing my code or buying more hardware. I can tell you if our OLTP servers were IO bound, it would run like crap. Instead of 1 sec, we'd be looking at 5-10 seconds per user transaction and our users would be screaming bloody murder. In theory, you can always convert your IO bound DB to CPU bound by stuffing more and more RAM into your server. (Or partitioning the DB across multiple servers.) Whether it's cost effective depends on the DB and how much your users are paying you -- and that's a case-by-case analysis. Not a global statement of IO-bound, pointless. (2) Does a DC system perform better than it's Nx1P cousin? My experience is yes. Did some rough tests in a drop-in-replacement 1x265 versus 2x244 and saw about +10% for DC. All the official benchmarks (Spec, Java, SAP, etc) from AMD/Sun/HP/IBM show DCs outperforming the Nx1P setups. Maybe true, but the 265 does have a 25% faster FSB than the 244, which might perhaps play a role. Nope. There's no such thing as FSB on Opterons. On-die memory controller runs @ CPU speed and hence connects at whatever the memory runs at (rounded off to some multiplier math). There's the HT speed that controls the max IO bandwidth but that's based on the motherboard, not the CPU. Plus the 265 and 244 both run at 1.8Ghz so the memory multiplier HT IO are both the same. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] weird performances problem
Hi all, We are operating a 1.5GB postgresql database for a year and we have problems for nearly a month. Usually everything is OK with the database, queries are executed fast even if they are complicated but sometimes and for half an hour, we have a general slow down. The server is a dedicated quad xeon with 4GB and a RAID1 array for the system and a RAID10 one for postgresql data. We have very few updates/inserts/deletes during the day. Postgresql version is 7.4.8. - the database is vacuumed, analyzed regularly (but we are not using autovacuum) and usually performs pretty well ; - IOs are OK, the database is entirely in RAM (see top.txt and iostat.txt attached) ; - CPUs are usually 25% idle, load is never really growing and its maximum is below 5 ; - I attached two plans for a simple query, the one is what we have when the server is fast, the other when we have a slow down: it's exactly the same plan but, as you can see it, the time to fetch the first row from indexes is quite high for the slow query ; - during this slow down, queries that usually take 500ms can take up to 60 seconds (and sometimes even more) ; - we have up to 130 permanent connections from our apache servers during this slow down as we have a lot of apache children waiting for a response. I attached a vmstat output. Context switches are quite high but I'm not sure it can be called a context switch storm and that this is the cause of our problem. Thanks for any advice or idea to help us understanding this problem and hopefully solve it. Regards, -- Guillaume [EMAIL PROTECTED] root]# iostat 10 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 7.20 0.0092.00 0920 sda1 0.00 0.00 0.00 0 0 sda2 6.40 0.0078.40 0784 sda3 0.00 0.00 0.00 0 0 sda4 0.00 0.00 0.00 0 0 sda5 0.00 0.00 0.00 0 0 sda6 0.80 0.0013.60 0136 sdb 5.00 0.00 165.60 0 1656 sdb1 5.00 0.00 165.60 0 1656 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 1.30 0.0020.80 0208 sda1 0.00 0.00 0.00 0 0 sda2 0.70 0.00 9.60 0 96 sda3 0.00 0.00 0.00 0 0 sda4 0.00 0.00 0.00 0 0 sda5 0.00 0.00 0.00 0 0 sda6 0.60 0.0011.20 0112 sdb 5.40 0.00 173.60 0 1736 sdb1 5.40 0.00 173.60 0 1736 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 2.20 0.0028.00 0280 sda1 0.00 0.00 0.00 0 0 sda2 2.20 0.0028.00 0280 sda3 0.00 0.00 0.00 0 0 sda4 0.00 0.00 0.00 0 0 sda5 0.00 0.00 0.00 0 0 sda6 0.00 0.00 0.00 0 0 sdb 5.20 0.00 171.20 0 1712 sdb1 5.20 0.00 171.20 0 1712 QUERY PLAN -- Nested Loop (cost=0.00..13.52 rows=2 width=1119) (actual time=0.154..0.167 rows=1 loops=1) - Index Scan using pk_newslang on newslang nl (cost=0.00..3.87 rows=1 width=1004) (actual time=0.053..0.055 rows=1 loops=1) Index Cond: (((codelang)::text = 'FRA'::text) AND (3498704 = numnews)) - Nested Loop Left Join (cost=0.00..9.61 rows=2 width=119) (actual time=0.050..0.059 rows=1 loops=1) - Index Scan using pk_news on news n (cost=0.00..3.31 rows=2 width=98) (actual time=0.021..0.023 rows=1 loops=1) Index Cond: (numnews = 3498704) - Index Scan using pk_photo on photo p (cost=0.00..3.14 rows=1 width=25) (actual time=0.021..0.025 rows=1 loops=1) Index Cond: (p.numphoto = outer.numphoto) Total runtime: 0.320 ms (9 rows) QUERY PLAN
Re: [PERFORM] Hardware/OS recommendations for large databases (
On 11/17/05, William Yu [EMAIL PROTECTED] wrote: No argument there.But it's pointless if you are IO bound.Why would you just accept we're IO bound, nothing we can do? I'd doeverything in my power to make my app go from IO bound to CPU bound -- whether by optimizing my code or buying more hardware. I can tell you ifour OLTP servers were IO bound, it would run like crap. Instead of 1sec, we'd be looking at 5-10 seconds per user transaction and our users would be screaming bloody murder.In theory, you can always convert your IO bound DB to CPU bound bystuffing more and more RAM into your server. (Or partitioning the DBacross multiple servers.) Whether it's cost effective depends on the DB and how much your users are paying you -- and that's a case-by-caseanalysis. Not a global statement of IO-bound, pointless. We all want our systems to be CPU bound, but it's not always possible. Remember, he is managing a 5 TB Databse. That's quite a bit different than a 100 GB or even 500 GB database.
Re: [PERFORM] Hardware/OS recommendations for large databases (
Alan Stange wrote: Not sure I get your point. We would want the lighter one, all things being equal, right ? (lower shipping costs, less likely to break when dropped on the floor) Why would the lighter one be less likely to break when dropped on the floor? They'd have less kinetic energy upon impact. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Hardware/OS recommendations for large databases (
Joshua Marsh wrote: On 11/17/05, *William Yu* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: No argument there. But it's pointless if you are IO bound. Why would you just accept we're IO bound, nothing we can do? I'd do everything in my power to make my app go from IO bound to CPU bound -- whether by optimizing my code or buying more hardware. I can tell you if our OLTP servers were IO bound, it would run like crap. Instead of 1 sec, we'd be looking at 5-10 seconds per user transaction and our users would be screaming bloody murder. In theory, you can always convert your IO bound DB to CPU bound by stuffing more and more RAM into your server. (Or partitioning the DB across multiple servers.) Whether it's cost effective depends on the DB and how much your users are paying you -- and that's a case-by-case analysis. Not a global statement of IO-bound, pointless. We all want our systems to be CPU bound, but it's not always possible. Remember, he is managing a 5 TB Databse. That's quite a bit different than a 100 GB or even 500 GB database. I did say in theory. :) I'm pretty sure google is more CPU bound than IO bound -- they just spread their DB over 50K servers or whatever. Not everybody is willing to pay for that but it's always in the realm of plausibility. Plus we have to go back to the statement I was replying to which was I have yet to come across a DB system that wasn't IO bound. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] weird performances problem
On Thu, Nov 17, 2005 at 06:47:09PM +0100, Guillaume Smet wrote: queries are executed fast even if they are complicated but sometimes and for half an hour, we have a general slow down. Is it exactly half an hour? What changes at the time that happens (i.e. what else happens on the machine?). Is this a time, for example, when logrotate is killing your I/O with file moves? A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Perl DBD and an alarming problem
On Thu, Nov 17, 2005 at 01:04:21PM -0800, Craig A. James wrote: When I set statement_timeout in the config file, it just didn't do anything - it never timed out (PG 8.0.3). I finally found in the documentation that I can do set statement_timeout = xxx from PerlDBI on a per-client basis, and that works. You probably shouldn't set statement_timeout on a global basis anyway, but did you reload the server after you made the change? Setting statement_timeout in postgresql.conf and then reloading the server works here in 8.0.4. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] weird performances problem
Andrew, Andrew Sullivan wrote: Is it exactly half an hour? What changes at the time that happens (i.e. what else happens on the machine?). Is this a time, for example, when logrotate is killing your I/O with file moves? No, it's not exactly half an hour. It's just that it slows down for some time (10, 20, 30 minutes) and then it's OK again. It happens several times per day. I checked if there are other processes running when we have this slow down but it's not the case. There's not really a difference between when it's OK or not (apart from the number of connections because the db is too slow): load is still at 4 or 5, iowait is still at 0%, there's still cpu idle and we still have free memory. I can't find what is the limit and why there is cpu idle. I forgot to give our non default postgresql.conf parameters: shared_buffers = 28800 sort_mem = 32768 vacuum_mem = 32768 max_fsm_pages = 35 max_fsm_relations = 2000 checkpoint_segments = 16 effective_cache_size = 27 random_page_cost = 2 Thanks for your help -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] weird performances problem
I forgot to give our non default postgresql.conf parameters: shared_buffers = 28800 sort_mem = 32768 vacuum_mem = 32768 max_fsm_pages = 35 max_fsm_relations = 2000 checkpoint_segments = 16 effective_cache_size = 27 random_page_cost = 2 Isn't sort_mem quite high? Remember that sort_mem size is allocated for each sort, not for each connection. Mine is 4096 (4 MB). My effective_cache_size is set to 27462. What OS are you running? regards Claus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help speeding up delete
Christopher Kings-Lynne wrote: Quite seriously, if you're still using 7.2.4 for production purposes you could justifiably be accused of negligence Perhaps we should put a link on the home page underneath LATEST RELEASEs saying 7.2: de-supported with a link to a scary note along the lines of the above. I strongly support an explicit desupported notice for 7.2 and below on the website... I'd go so far as to say the version #s of supported versions is one of pieces of information I'd most expect to see on the main support page ( http://www.postgresql.org/support/ ). Perhaps it'd be nice to even show a table like VersionReleased On Support Ends 7.14 BCSep 3 1752 7.2Feb 31 1900 Jan 0 2000 7.42003-11-17 At least 2005-x-x 8.02005-01-19 At least 2006-x-x with a footnote saying that only the most recent dot release of each family is considered supported. It also might be nice to have a footnote saying that any of the commercical support companies might support the older versions for longer periods of time. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help speeding up delete
I do not really see why all the distributions could do something like this, instead of mucking around with special statically compiled pg_dumps and the like... Contrib modules and tablespaces. Plus, no version of pg_dump before 8.0 is able to actually perform such reliable dumps and reloads (due to bugs). However, that's probably moot these days. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware/OS recommendations for large databases (
Joshua Marsh [EMAIL PROTECTED] writes: We all want our systems to be CPU bound, but it's not always possible. Sure it is, let me introduce you to my router, a 486DX100... Ok, I guess that wasn't very helpful, I admit. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware/OS recommendations for large databases (
Joshua Marsh [EMAIL PROTECTED] writes: We all want our systems to be CPU bound, but it's not always possible. Remember, he is managing a 5 TB Databse. That's quite a bit different than a 100 GB or even 500 GB database. Ok, a more productive point: it's not really the size of the database that controls whether you're I/O bound or CPU bound. It's the available I/O bandwidth versus your CPU speed. If your I/O subsystem can feed data to your CPU as fast as it can consume it then you'll be CPU bound no matter how much data you have in total. It's harder to scale up I/O subsystems than CPUs, instead of just replacing a CPU it tends to mean replacing the whole system to get a better motherboard with a faster, better bus, as well as adding more controllers and more disks. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Perl DBD and an alarming problem
[Please copy the mailing list on replies.] On Thu, Nov 17, 2005 at 05:38:13PM -0800, Craig A. James wrote: You probably shouldn't set statement_timeout on a global basis anyway The server is a one trick pony so setting a global timeout value is actually appropriate. Beware that statement_timeout also applies to maintenance commands like VACUUM; it might be more appropriate to set per-user timeouts with ALTER USER. If you do set a global timeout then you might want to set a per-user timeout of 0 for database superusers so maintenance activities don't get timed out. ... but did you reload the server after you made the change? Setting statement_timeout in postgresql.conf and then reloading the server works here in 8.0.4. Yes. By reload I assume you mean restarting it from scratch. Either a restart or a pg_ctl reload, which sends a SIGHUP to the server. You can effect some changes by sending a signal to a running server without having to restart it entirely. In this case, I use /etc/init.d/postgresql restart It definitely had no effect at all. I tried values clear down to 1 millisecond, but the server never timed out for any query. Did you use SHOW statement_timeout to see if the value was set to what you wanted? Are you sure you edited the right file? As a database superuser execute SHOW config_file to see what file the server is using. What exactly did the line look like after you changed it? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
