Re: [PERFORM] Areca 1260 Performance
On 6-Dec-06, at 5:26 PM, Ron wrote: At 06:40 PM 12/6/2006, Brian Wipf wrote: I appreciate your suggestions, Ron. And that helps answer my question on processor selection for our next box; I wasn't sure if the lower MHz speed of the Kentsfield compared to the Woodcrest but with double the cores would be better for us overall or not. Please do not misunderstand me. I am not endorsing the use of Kentsfield. I am recommending =evaluating= Kentsfield. I am also recommending the evaluation of 2C 4S AMD solutions. All this stuff is so leading edge that it is far from clear what the RW performance of DBMS based on these components will be without extensive testing of =your= app under =your= workload. I want the best performance for the dollar, so I can't rule anything out. Right now I'm leaning towards Kentsfield, but I will do some more research before I make a decision. We probably won't wait much past January though. One thing that is clear from what you've posted thus far is that you are going to needmore HDs if you want to have any chance of fully utilizing your Areca HW. Do you know off hand where I might find a chassis that can fit 24[+] drives? The last chassis we ordered was through Supermicro, and the largest they carry fits 16 drives. Hoping I'm being helpful I appreciate any help I can get. Brian Wipf [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?
On 7-12-2006 7:01 Jim C. Nasby wrote: Can you post them on the web somewhere so everyone can look at them? No, its not (only) the size that matters, its the confidentiality I'm not allowed to just break by myself. Well, at least not on a scale like that. I've been mailing off-list with Tom and we found at least one query that in some circumstances takes a lot more time than it should, due to it mistakenly chosing to do a bitmap index scan rather than a normal index scan. Also, are you looking at EXPLAIN or EXPLAIN ANALYZE? Explain analyze and normal query execution times of several millions of queries executed on both versions of postgresql, so we can say something about them statistically. Best regards, Arjen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Core 2 or Opteron
Hello, We're planning new server or two for PostgreSQL and I'm wondering Intel Core 2 (Woodcrest for servers?) or Opteron is faster for PostgreSQL now? When I look through hardware sites Core 2 wins. But I believe those tests mostly are being done in 32 bits. Does the picture change in 64 bits? And I also remember that in PostgreSQL Opteron earlier had huge advantage over older Xeons. But did Intel manage to change picture now? Thanks, Mindaugas ---(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] Core 2 or Opteron
These benchmarks are all done using 64 bit linux: http://tweakers.net/reviews/646 Best regards, Arjen On 7-12-2006 11:18 Mindaugas wrote: Hello, We're planning new server or two for PostgreSQL and I'm wondering Intel Core 2 (Woodcrest for servers?) or Opteron is faster for PostgreSQL now? When I look through hardware sites Core 2 wins. But I believe those tests mostly are being done in 32 bits. Does the picture change in 64 bits? And I also remember that in PostgreSQL Opteron earlier had huge advantage over older Xeons. But did Intel manage to change picture now? Thanks, Mindaugas ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Core 2 or Opteron
We're planning new server or two for PostgreSQL and I'm wondering Intel Core 2 (Woodcrest for servers?) or Opteron is faster for PostgreSQL now? When I look through hardware sites Core 2 wins. But I believe those tests mostly are being done in 32 bits. Does the picture change in 64 bits? We just migrated from a 4-way opteron @ 2 GHz with 8 GB ram to a DL380 G5 with a 4-way woodcrest @ 3 GHz and 16 GB ram. It was like night and day, system load dropped, not just quite a bit, but almost by a factor of 100 in worst case scenarios. Going from a 64 MB diskcontroller to a 256 MB ditto probably helped some and so did a speedup from 2 - 3 GHz, but overall it seems the new woodcrest cpu's feel at home doing db-stuff. This is on FreeBSD 6.2 RC1 and postgresql 7.4.14. And I also remember that in PostgreSQL Opteron earlier had huge advantage over older Xeons. But did Intel manage to change picture now? That was pre-woodcrest, aka. nocona and before. Horrible and the reason I went for opteron to begin with. But AMD probably wont sit idle. The link posted in another reply illustrates the current situation quite well. regards Claus ---(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] Core 2 or Opteron
These benchmarks are all done using 64 bit linux: http://tweakers.net/reviews/646 I see. Thanks. Now about 2 core vs 4 core Woodcrest. For HP DL360 I see similarly priced dual core [EMAIL PROTECTED] and four core [EMAIL PROTECTED] According to article's scaling data PostgreSQL performance should be similar (1.86GHz * 2 * 80% = ~3GHz). And quad core has slightly slower FSB (1066 vs 1333). So it looks like more likely dual core 5160 Woodrest is the way to go if I want ultimate performance on two sockets? Besides that I think it should consume a bit less power!? Mindaugas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Core 2 or Opteron
On 7-12-2006 12:05 Mindaugas wrote: Now about 2 core vs 4 core Woodcrest. For HP DL360 I see similarly priced dual core [EMAIL PROTECTED] and four core [EMAIL PROTECTED] According to article's scaling data PostgreSQL performance should be similar (1.86GHz * 2 * 80% = ~3GHz). And quad core has slightly slower FSB (1066 vs 1333). So it looks like more likely dual core 5160 Woodrest is the way to go if I want ultimate performance on two sockets? Besides that I think it should consume a bit less power!? I think that's the better choice yes. I've seen the X5355 (quad core 2.66Ghz) in work and that one is faster than the 5160 we tested. But its not as much faster as the extra ghz' could imply, so the 5320 would very likely not outperform the 5160. At least not in our postgresql benchmark. Besides that you end up with a slower FSB for more cores (1333 / 2 = 666 per core, 1066 / 4 = 266 per core!) while there will be more traffic since the seperate dual cores on the quad core communicate via the bus and there are more cores so there is also in an absolute sence more cache coherency traffic... So I'd definitely go with the 5160 or perhaps just the 5150 if the savings can allow for better I/O or more memory. Best regards, Arjen ---(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] Areca 1260 Performance
At 03:37 AM 12/7/2006, Brian Wipf wrote: On 6-Dec-06, at 5:26 PM, Ron wrote: All this stuff is so leading edge that it is far from clear what the RW performance of DBMS based on these components will be without extensive testing of =your= app under =your= workload. I want the best performance for the dollar, so I can't rule anything out. Right now I'm leaning towards Kentsfield, but I will do some more research before I make a decision. We probably won't wait much past January though. Kentsfield's outrageously high pricing and operating costs (power and cooling) are not likely to make it the cost/performance winner. OTOH, 1= ATM it is the way to throw the most cache per socket at a DBMS within the Core2 CPU line (Tulsa has even more at 16MB per CPU). 2= SSSE3 and other Core2 optimizations have led to some impressive performance numbers- unless raw clock rate is the thing that can help you the most. If what you need for highest performance is the absolute highest clock rate or most cache per core, then bench some Intel Tulsa's. Apps with memory footprints too large for on die or in socket caches or that require extreme memory subsystem performance are still best served by AMD CPUs. If you are getting the impression that it is presently complicated deciding which CPU is best for any specific pg app, then I am making the impression I intend to. One thing that is clear from what you've posted thus far is that you are going to needmore HDs if you want to have any chance of fully utilizing your Areca HW. Do you know off hand where I might find a chassis that can fit 24[+] drives? The last chassis we ordered was through Supermicro, and the largest they carry fits 16 drives. www.pogolinux.com has 24 and 48 bay 3.5 HD chassis'; and a 64 bay 2.5 chassis. Tell them I sent you. www.impediment.com are folks I trust regarding all things storage (and RAM). Again, tell them I sent you. www.aberdeeninc.com is also a vendor I've had luck with, but try Pogo and Impediment first. Good luck and please post what happens, Ron Peacetree ---(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
[PERFORM] SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can helpe me.
07/12/200604:31 SQL_CALC_FOUND_ROWS in POSTGRESQL In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax. SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name '' LIMIT 0, 10 to have the recorset data. and SELECT FOUND_ROWS(); to have the total of registers found. I dont want to use the command count(*), because the performance will fall down, depending of the quantyt of tables and "joins". The Data base postgresql have something similar ??? --- 07/12/200604:31 SQL_CALC_FOUND_ROWS no POSTGRESQL Dvida NINJA no POSTGRESQL No mysql utilizo o comando SQL_CALC_FOUND_ROWS na seguinte sintax SELECT SQL_CALC_FOUND_ROWS nome, email, telefone FROM tabela WHERE nome '' LIMIT 0, 10 para obter o meu recordset e SELECT FOUND_ROWS(); para obter o total de resgitros que realmente existem em minha tabela condicionado pelo WHERE, sem ser limitado pelo LIMIT. No quero usar o count(*) pois o desempenho cai dependendo da quantidade de tabelas selecionadas e quantidade de registros. O postgreSQL possui algo similar? Caso sim pode me informar qual e fornecer um exemplo.
Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?
Arjen van der Meijden [EMAIL PROTECTED] writes: I've been mailing off-list with Tom and we found at least one query that in some circumstances takes a lot more time than it should, due to it mistakenly chosing to do a bitmap index scan rather than a normal index scan. Just to clue folks in: the problem queries seem to be cases like WHERE col1 = 'const' AND col2 = othertab.colx AND col3 IN (several hundred integers) where the table has an index on (col1,col2,col3). 8.2 is generating a plan involving a nestloop with inner bitmap indexscan on this index, and using all three of these WHERE clauses with the index. The ability to use an IN clause (ie, ScalarArrayOpExpr) in an index condition is new in 8.2, and we seem to have a few bugs left in the cost estimation for it. The problem is that a ScalarArrayOpExpr effectively causes a BitmapOr across N index scans using each of the array elements as an individual scan qualifier. So the above amounts to several hundred index probes for each outer row. In Arjen's scenario it seems that the first two index columns are already pretty selective, and it comes out a lot faster if you just do one indexscan using the first two columns and then apply the IN-condition as a filter to the relatively small number of rows you get that way. What's not clear to me yet is why the 8.2dev code didn't fall into this same trap, because the ScalarArrayOpExpr indexing code was already there on 3-June. But we didn't and still don't have any code that considers the possibility that a potential indexqual condition should be deliberately *not* used with the index. regards, tom lane ---(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] Areca 1260 Performance
One thing that is clear from what you've posted thus far is that you are going to needmore HDs if you want to have any chance of fully utilizing your Areca HW. Do you know off hand where I might find a chassis that can fit 24[+] drives? The last chassis we ordered was through Supermicro, and the largest they carry fits 16 drives. Chenbro has a 24 drive case - the largest I have seen. It fits the big 4/8 cpu boards as well. http://www.chenbro.com/corporatesite/products_01features.php?serno=43 -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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] Areca 1260 Performance
I'm building a SuperServer 6035B server (16 scsi drives). My schema has basically two large tables (million+ per day) each which are partitioned daily, and queried independently of each other. Would you recommend a raid1 system partition and 14 drives in a raid 10 or should i create separate partitions/tablespaces for the two large tables and indexes? Thanks Gene On 12/7/06, Shane Ambler [EMAIL PROTECTED] wrote: One thing that is clear from what you've posted thus far is that you are going to needmore HDs if you want to have any chance of fully utilizing your Areca HW. Do you know off hand where I might find a chassis that can fit 24[+] drives? The last chassis we ordered was through Supermicro, and the largest they carry fits 16 drives. Chenbro has a 24 drive case - the largest I have seen. It fits the big 4/8 cpu boards as well. http://www.chenbro.com/corporatesite/products_01features.php?serno=43 -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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 -- Gene Hart cell: 443-604-2679
[PERFORM] Advice on selecting good values for work_mem?
I'm gearing up to do some serious investigation into performance for PostgreSQL with regard to our application. I have two issues that I've questions about, and I'll address them in two seperate emails. This email regards the tuning of work_mem. I'm planning on going through all of the queries our application does, under various load scenarios and approaching each performance issue as it appears. What I'm fuzzy on is how to discretely know when I'm overflowing work_mem? Obviously, if work_mem is exhausted by a particular query, temp files will be created and performance will begin to suck, but it would be nice to have some more information -- how large was the resultant temp file, for example. Does the creation of a temp file trigger any logging? I've yet to see any, but we may not have hit any circumstances where work_mem was exhausted. I've been looking through the docs at the various pg_stat* views and functions, but it doesn't look as if there's anything in there about this. That leads to my other question. Assuming I've got lots of connections (which I do), how can I determine if work_mem is too high? Do server processes allocated it even if they don't actually use it? Is the only way to find out to reduce it and see when it starts to be a problem? If so, that leads back to my first question: how can I be sure whether temp files were created or not? My goal is to set work_mem as small as is possible for the most common queries, then force the developers to use set work_mem to x to adjust it for big queries. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(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
[PERFORM] How to determine if my setting for shared_buffers is too high?
I'm gearing up to do some serious investigation into performance for PostgreSQL with regard to our application. I have two issues that I've questions about, and I'll address them in two seperate emails. This one regards tuning shared_buffers. I believe I have a good way to monitor database activity and tell when a database grows large enough that it would benefit from more shared_buffers: if I monitor the blks_read column of pg_stat_database, it should increase very slowly if there is enough shared_buffer space. When shared buffer space runs out, more disk read requests will be required and this number will begin to climb. If anyone sees a flaw in this approach, I'd be interested to hear it. The other tuning issue with shared_buffers is how to tell if I'm allocating too much. For example, if I allocate 1G of RAM to shared buffers, and the entire database can fit in 100M, that 900M might be better used as work_mem, or something else. I haven't been able to find anything regarding how much of the shared buffer space PostgreSQL is actually using, as opposed to simply allocating. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Advice on selecting good values for work_mem?
Bill Moran [EMAIL PROTECTED] writes: Does the creation of a temp file trigger any logging? No; but it wouldn't be hard to add some if you wanted. I'd do it at deletion, not creation, so you could log the size the file reached. See FileClose() in src/backend/storage/file/fd.c. That leads to my other question. Assuming I've got lots of connections (which I do), how can I determine if work_mem is too high? When you start swapping, you have a problem --- so watch vmstat or local equivalent. regards, tom lane ---(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] How to determine if my setting for shared_buffers is too high?
Bill Moran [EMAIL PROTECTED] writes: I haven't been able to find anything regarding how much of the shared buffer space PostgreSQL is actually using, as opposed to simply allocating. In 8.1 and up, contrib/pg_buffercache/ would give you some visibility of this. 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] Advice on selecting good values for work_mem?
* Bill Moran ([EMAIL PROTECTED]) wrote: What I'm fuzzy on is how to discretely know when I'm overflowing work_mem? Obviously, if work_mem is exhausted by a particular query, temp files will be created and performance will begin to suck, I don't believe this is necessairly *always* the case. There are instances in PostgreSQL where it will just continue to allocate memory beyond the work_mem setting. This is usually due to poor statistics (you changed the data in the table dramatically and havn't run analyze, or you never ran analyze on the table at all, or the statistics gathering values are set too low to capture enough information about the data, etc). It would nice if it was possible to have this detected and logged, or similar. Additionally, work_mem isn't actually a per-query thing, aiui, it's more like a per-node in the planner thing. That is to say that if you have multiple sorts going on, or a sort and a hash, that *both* of those expect to be able to use up to work_mem amount of memory. Also, another point you might want to consider how to handle is that work_mem has no bearing on libpq and I don't recall there being a way to constrain libpq's memory usage. This has been an issue for me just today when a forgot a couple parameters to a join which caused a cartesean product result and ended up running the box out of memory. Sure, it's my fault, and unlikely to happen in an application, but it still sucks. :) It also managed to run quickly enough that I didn't notice what was happening. :/ Of course, the server side didn't need much memory at all to generate that result. Also, libpq stores everything in *it's* memory before passing it to the client. An example scenario of this being kind of an issue is psql, you need double the memory size of a given result because the result is first completely grabbed and stored in libpq and then sent to your pager (eg: less) which then sucks it all into memory again. In applications (and I guess psql, though I never think of it, and it'd be nice to have as a configurable option if it isn't already...) you can use cursors to limit the amount of memory libpq uses. As these are new things (both the temp file creation logging and the work_mem overflow detection, I believe), this discussion is probably more appropriate for -hackers. That leads to my other question. Assuming I've got lots of connections (which I do), how can I determine if work_mem is too high? Do server processes allocated it even if they don't actually use it? Is the only way to find out to reduce it and see when it starts to be a problem? If so, that leads back to my first question: how can I be sure whether temp files were created or not? Yeah, look for swappiness... It'd be nice to be able to get memory statistics on queries which have been run though... My goal is to set work_mem as small as is possible for the most common queries, then force the developers to use set work_mem to x to adjust it for big queries. Sounds like an excellent plan. Be careful though, work_mem settings can affect query plans and they may discover that if set high enough the planner will, for example, do a hashjoin which is much faster than sorting and merge-joining, but takes alot of memory... They may say hey, I like it being fast but not consider what happens when alot of those queries run at once.. Thanks! Stephen signature.asc Description: Digital signature
Re: [PERFORM] File Systems Compared
On 12/6/06, Brian Wipf [EMAIL PROTECTED] wrote: Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~1300MBps respectively along with RAID 0 Sustained Rates of ~900MBps read, and ~850MBps write. Luke, I know you've managed to get higher IO rates than this with this class of HW. Is there a OS or SW config issue Brian should closely investigate? I wrote 1280 by a mistake. It's actually a 1260. Sorry about that. The IOP341 class of cards weren't available when we ordered the parts for the box, so we had to go with the 1260. The box(es) we build next month will either have the 1261ML or 1280 depending on whether we go 16 or 24 disk. I noticed Bucky got almost 800 random seeks per second on her 6 disk 1 RPM SAS drive Dell PowerEdge 2950. The random seek performance of this box disappointed me the most. Even running 2 concurrent bonnies, the random seek performance only increased from 644 seeks/ sec to 813 seeks/sec. Maybe there is some setting I'm missing? This card looked pretty impressive on tweakers.net. I've been looking a lot at the SAS enclosures lately and am starting to feel like that's the way to go. Performance is amazing and the flexibility of choosing low cost SATA or high speed SAS drives is great. not only that, but more and more SAS is coming out in 2.5 drives which seems to be a better fit for databases...more spindles. with a 2.5 drive enclosure they can stuff 10 hot swap drives into a 1u enclosure...that's pretty amazing. one downside of SAS is most of the HBAs are pci-express only, that can limit your options unless your server is very new. also you don't want to skimp on the hba, get the best available, which looks to be lsi logic at the moment (dell perc5/e is lsi logic controller as is the intel sas hba)...others? merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Disk storage and san questions (was File Systems Compared)
I was working on a project that was considering using a Dell/EMC (dell's rebranded emc hardware) and here's some thoughts on your questions based on that. 1. Is iscsi a decent way to do a san? How much performance do I loose vs connecting the hosts directly with a fiber channel controller? It's cheaper, but if you want any sort of reasonable performance, you'll need a dedicated gigabit network. I'd highly recommend a dedicated switch too, not just vlan. You should also have dual nics, and use one dedicated to iSCSI. Most all poweredges come with dual nics these days. 2. Would it be better to omit my database server from the san (or at least the database storage) and stick with local disks? If so what disks/controller card do I want? I use dell servers for everything so it would be nice if the recommendation is a dell system, but doesn't need to be. Overall I'm not very impressed with the LSI cards, but I'm told the new ones are much better. The new dell perc4, and perc5 to more extent, are reasonable performers in my experience. However, this depends on the performance needs of your database. You should be able to at least get better performance than onboard storage (Poweredges max out at 6 disks- 8 if you go 2.5 SATA, but I don't recommend those for reliability/performance reasons). If you get one of the better Dell/EMC combo sans, you can allocate a raid pool for your database and probably saturate the iSCSI interface. Next step might be the MD1000 15 disk SAS enclosure with Perc5/e cards if you're sticking with dell, or step up to multi-homed FC cards. (btw- you can split the MD1000 in half and share it across two servers, since it has two scsi cards. You can also daisy chain up to three of them for a total of 45 disks). Either way, take a good look at what the SAN chassis can support in terms of IO bandwidth- cause once you use it up, there's no more to allocate to the DB. 3. Anyone use the sanrad box? Is it any good? Seems like consolidating disk space and disk spares platform wide is good idea, but I've not used a san before so I'm nervous about it. If you haven't used a san, much less an enterprise grade one, then I'd be very nervous about them too. Optimizing SAN performance is much more difficult than attached storage simply due to the complexity factor. Definitely plan on a pretty steep learning curve, especially for something like EMC and a good number of servers. IMO, the big benefit to SAN is storage management and utilization, not necessarily performance (you can get decent performance if you buy the right hardware and tune it correctly). To your points- you can reduce the number of hot spares, and allocate storage much more efficiently. Also, you can allocate storage pools based on performance needs- slow SATA 500Gb drives for archive, fast 15K SAS for db, etc. There's some nice failover options too, as you mentioned boot from san allows you to swap hardware, but I would get a demonstration from the vendor of this working with your hardware/os setup (including booting up the cold spare server). I know this was a big issue in some of the earlier Dell/EMC hardware. Sorry for the long post, but hopefully some of the info will be useful to you. Bucky ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Areca 1260 Performance
At 11:02 AM 12/7/2006, Gene wrote: I'm building a SuperServer 6035B server (16 scsi drives). My schema has basically two large tables (million+ per day) each which are partitioned daily, and queried independently of each other. Would you recommend a raid1 system partition and 14 drives in a raid 10 or should i create separate partitions/tablespaces for the two large tables and indexes? Not an easy question to answer w/o knowing more about your actual queries and workload. To keep the math simple, let's assume each SCSI HD has and ASTR of 75MBps. A 14 HD RAID 10 therefore has an ASTR of 7* 75= 525MBps. If the rest of your system can handle this much or more bandwidth, then this is most probably the best config. Dedicating spindles to specific tables is usually best done when there is HD bandwidth that can't be utilized if the HDs are in a larger set +and+ there is a significant hot spot that can use dedicated resources. My first attempt would be to use other internal HDs for a RAID 1 systems volume and use all 16 of your HBA HDs for a 16 HD RAID 10 array. Then I'd bench the config to see if it had acceptable performance. If yes, stop. Else start considering the more complicated alternatives. Remember that adding HDs and RAM is far cheaper than even a few hours of skilled technical labor. Ron Peacetree ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] One table is very slow, but replicated table (same data) is fine
[EMAIL PROTECTED] writes: If anyone knows what may cause this problem, or has any other ideas, I would be grateful. Submit the command VACUUM ANALYZE VERBOSE locations; on both servers, and post the output of that. That might help us tell for sure whether the table is bloated (and needs VACUUM FULL/CLUSTER). The query plans are suggestive; on the 'master', the cost is 113921.40, whereas on the 'slave' it's 2185.09; I'll bet that those numbers are proportional to the number of pages assigned to the table on the respective servers... -- (reverse (concatenate 'string ofni.sesabatadxunil @ enworbbc)) http://cbbrowne.com/info/lsf.html We use Linux for all our mission-critical applications. Having the source code means that we are not held hostage by anyone's support department. -- Russell Nelson, President of Crynwr Software ---(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