Re: [PERFORM] [OT] RAID controllers blocking one another?
On Jan 18, 2008 2:14 PM, Greg Smith [EMAIL PROTECTED] wrote: On Fri, 18 Jan 2008, Sean Davis wrote: FYI, here are the specs on the server. http://www.thinkmate.com/System/8U_Dual_Xeon_i2SS40-8U_Storage_Server Now we're getting somewhere. I'll dump this on-list as it's a good example of how to fight this class of performance problems. The usual troubleshooting procedure is to figure out how the motherboard is mapping all the I/O internally and then try to move things out of the same path. That tells us that you have an Intel S5000PSL motherboard, and the tech specs are at http://support.intel.com/support/motherboards/server/s5000psl/sb/CS-022619.htm What you want to stare at is the block diagram that's Figure 10, page 27 (usually this isn't in the motherboard documentation, and instead you have to drill down into the chipset documentation to find it). Slots 5 and 6 that have PCI Express x16 connectors (but run at x8 speed) both go straight into the memory hub. Slots 3 and 4, which are x8 but run at x4 speed, go through the I/O controller first. Those should be slower, so if you put a card into there it will have a degraded top-end performance compared to slots 5/6. Line that up with the layout in Figure 2, page 17, and you should be able to get an idea what the possibilities are for moving the cards around and what the trade-offs involved are. Ideally you'd want both 3Ware cards to be in slots 5+6, but if that's your current configuration you could try moving the less important of the two (maybe the one with less drives) to either slot 3/4 and see if the contention you're seeing drops. Greg, this is GREAT information and I'm glad you stepped through the process. It is really interesting to see to what extent these slots that have similar names (or the same names) should be expected to behave so differently. We'll try some of the things you suggest (although it will probably take a while to do) and if we come to any conclusions will let everyone know our conclusions. Sean
Re: [PERFORM] [OT] RAID controllers blocking one another?
On Jan 17, 2008 6:23 PM, Greg Smith [EMAIL PROTECTED] wrote: On Thu, 17 Jan 2008, Scott Marlowe wrote: On Jan 17, 2008 2:17 PM, Sean Davis [EMAIL PROTECTED] wrote: two3-ware cards, one 9640SE-24 and one 9640SE-16 Sounds like they're sharing something they shouldn't be. I'm not real familiar with PCI-express. Aren't those the ones that use up to 16 channels for I/O? Can you divide it to 8 and 8 for each PCI-express slot in the BIOS maybe, or something like that? I can't find the 9640SE-24/16 anywhere, but presuming these are similar to (or are actually) the 9650SE cards then each of them is using 8 lanes of the 16 available. I'd need to know the exact motherboard or system to even have a clue what the options are for adjusting the BIOS and whether they are shared or independant. But I haven't seen one where there's any real ability to adjust how the I/O is partitioned beyond adjusting what slot you plug things into so that's probably a dead end anyway. Given the original symptoms, one thing I would be suspicious of though is whether there's some sort of IRQ conflict going on. Sadly we still haven't left that kind of junk behind even on current PC motherboards. Thanks, Greg. After a little digging, 3-ware suggested moving one of the cards, also. We will probably give that a try. I'll also look into the bios, but since the machine is running as a fileserver, there is precious little time for downtime tinkering. FYI, here are the specs on the server. http://www.thinkmate.com/System/8U_Dual_Xeon_i2SS40-8U_Storage_Server Sean
[PERFORM] [OT] RAID controllers blocking one another?
We have a machine that serves as a fileserver and a database server. Our server hosts a raid array of 40 disk drives, attached to two3-ware cards, one 9640SE-24 and one 9640SE-16. We have noticed that activity on one controller blocks access on the second controller, not only for disk-IO but also the command line tools which become unresponsive for the inactive controller. The controllers are sitting in adjacent PCI-express slots on a machine with dual-dual AMD and 16GB of RAM. Has anyone else noticed issues like this? Throughput for either controller is a pretty respectable 150-200MB/s writing and somewhat faster for reading, but the blocking is problematic, as the machine is serving multiple purposes. I know this is off-topic, but I know lots of folks here deal with very large disk arrays; it is hard to get real-world input on machines such as these. Thanks, Sean
Re: [PERFORM] Bytea poor performance
On 10/15/05 9:20 AM, NSO [EMAIL PROTECTED] wrote: Hello, I am trying to select form table with bytea field. And queries runs very slow. My table: CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; Query: select * from files where nr 1450 (I have total 1500 records in it, every holds picture of 23kB size) Query runs very long: Total query runtime: 23625 ms. Data retrieval runtime: 266 ms. 50 rows retrieved. explain: Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) Index Cond: (nr 1450) Is it possible to do something with it? or it is normal? Our server is fast, and all other tables work fine.. How about some explain analyze output? Have you done a full vacuum lately? How about reindexing? Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Bytea poor performance
On 10/15/05 10:00 AM, NSO [EMAIL PROTECTED] wrote: Hello, How about some explain analyze output? Explain analyse select * from files where nr 1450 Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) (actual time=0.000..0.000 rows=50 loops=1) I may not be understanding the output, but your actual time reports 0 for the query. And the total runtime is 23 seconds? Sean On 10/15/05 9:20 AM, NSO [EMAIL PROTECTED] wrote: Hello, I am trying to select form table with bytea field. And queries runs very slow. My table: CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; Query: select * from files where nr 1450 (I have total 1500 records in it, every holds picture of 23kB size) Query runs very long: Total query runtime: 23625 ms. Data retrieval runtime: 266 ms. 50 rows retrieved. explain: Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) Index Cond: (nr 1450) Is it possible to do something with it? or it is normal? Our server is fast, and all other tables work fine.. How about some explain analyze output? Have you done a full vacuum lately? How about reindexing? Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend -- This message has been scanned for viruses and dangerous content, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Massive delete performance
On 10/11/05 3:47 AM, Andy [EMAIL PROTECTED] wrote: Hi to all, I have the following problem: I have a client to which we send every night a dump with a the database in which there are only their data's. It is a stupid solution but I choose this solution because I couldn't find any better. The target machine is a windows 2003. So, I have a replication only with the tables that I need to send, then I make a copy of this replication, and from this copy I delete all the data's that are not needed. How can I increase this DELETE procedure because it is really slow??? There are of corse a lot of data's to be deleted. Do you have foreign key relationships that must be followed for cascade delete? If so, make sure that you have indices on them. Are you running any type of vacuum after the whole process? What kind? Sean ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Massive delete performance
On 10/11/05 8:05 AM, Andy [EMAIL PROTECTED] wrote: Do you have foreign key relationships that must be followed for cascade delete? If so, make sure that you have indices on them. Yes I have such things. Indexes are on these fields. To be onest this delete is taking the longest time, but it involves about 10 tables. Can you post explain analyze output of the next delete? Sean ---(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] [SQL] OFFSET impact on Performance???
On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote: Hi, What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id. This is what I do, but I use two columns for indexing the original query, a user_id (not session-id) and an index to the query_id that is unique within user. This query_id is a foreign key to another table that describes the query (often just a name). I allow the user only a fixed number of stored queries and recycle after hitting the maximum. You can timestamp your queries so that when you recycle you drop the oldest one first. If you don't need multiple stored query results, then using the user_id is probably adequate (assuming the user is not logged on in several locations simultaneously). This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is). See above. The query refresh issue remains. And of course better performance cannot be guaranteed until you try it. For the standard operating procedure of perform query===view results, I have found this to be a nice system. The user is accustomed to queries taking a bit of time to perform, but then wants to be able to manipulate and view data rather quickly; this paradigm is pretty well served by making a separate table of results, particularly if the original query is costly. Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare? This question still applies Sean -Original Message- From: [EMAIL PROTECTED] on behalf of Andrei Bintintan Sent: Wed 1/26/2005 11:11 AM To: [EMAIL PROTECTED]; Greg Stark Cc: Richard Huxton; pgsql-sql@postgresql.org; pgsql-performance@postgresql.org Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? The problems still stays open. The thing is that I have about 20 - 30 clients that are using that SQL query where the offset and limit are involved. So, I cannot create a temp table, because that means that I'll have to make a temp table for each session... which is a very bad ideea. Cursors somehow the same. In my application the Where conditions can be very different for each user(session) apart. The only solution that I see in the moment is to work at the query, or to write a more complex where function to limit the results output. So no replace for Offset/Limit. Best regards, Andy. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
