Geeky question
Apr 4, 2004 | 1:56 am
#16
Join Date: Jan 2001
Location: UK
Posts: 2,499
Here's my 2 cents......obviously I know nothing of the FT DB design and may be way wide of the mark but if a customer came to me with a DB based system running a bit slow this is the first place I would look......
My gut reaction is the DB server is disk I/O constrained. SCSI disks are fast but if you only have a couple of them (presumably mirrored) you have very limited I/O.
My background is mainly Microsoft however the fundementals of Databases and storage design are presumably pretty similar if you compare, say, SQL 2000 with Linux/MySQL
So....in Microsoft land we would always look to do the following basics to ensure optimum performance with a complex DB (rules of thumb in order of importance)
1 - Seperate the transaction logs from the main DB. Running transaction logs on seperate spindles usually yields significant performance benefits. A serial stream of write activity contends very badly with the completely random I/O of DB access so seperate them!
2 - Turn on Write caching, don't bother with read caching. You can't really do read ahead cache with a DB - data is typically requested from blocks randomly spread all over the place. For this reason, Write ahead caching is very important. You don't want to be waiting for a write operation to complete to disk before moving to the next I/O. (For DL360's you need to buy and install a Battery Backed Write Cache widget to accomplish this)
3 - Put your DB on as many (fast 15k) spindles as you can afford.
4 - If you have a lot of Write activity on your DB, avoid RAID 5, use RAID 1+0 instead.
5 - Stick TembDB on yet another seperate spindle if lots of queries are creating a lot of activity on TempDB (don;t know what the MYSQL equivilant is)
I don't know whether the FT DB is trivial and small or large and complex. If the latter then I think storage design is going to dictate performance.
There's a good whitepaper on all this here: http://h71019.www7.hp.com/ActiveAnsw...0-225-1,00.htm - quite MS SQL centric (isn't everything!) but a good read for those with a technical interest in this sort of stuff!
(Edited to add - sorry if this post goes slightly OT and more toward "what can we do to make FT faster" - I'm sure the team at FT are doing a great job with this major upgrade - I just wanted to re-iterate there are a bunch of people out here who use FT on a regular basis who are happy to do whatever they can to help out)
My gut reaction is the DB server is disk I/O constrained. SCSI disks are fast but if you only have a couple of them (presumably mirrored) you have very limited I/O.
My background is mainly Microsoft however the fundementals of Databases and storage design are presumably pretty similar if you compare, say, SQL 2000 with Linux/MySQL
So....in Microsoft land we would always look to do the following basics to ensure optimum performance with a complex DB (rules of thumb in order of importance)
1 - Seperate the transaction logs from the main DB. Running transaction logs on seperate spindles usually yields significant performance benefits. A serial stream of write activity contends very badly with the completely random I/O of DB access so seperate them!
2 - Turn on Write caching, don't bother with read caching. You can't really do read ahead cache with a DB - data is typically requested from blocks randomly spread all over the place. For this reason, Write ahead caching is very important. You don't want to be waiting for a write operation to complete to disk before moving to the next I/O. (For DL360's you need to buy and install a Battery Backed Write Cache widget to accomplish this)
3 - Put your DB on as many (fast 15k) spindles as you can afford.
4 - If you have a lot of Write activity on your DB, avoid RAID 5, use RAID 1+0 instead.
5 - Stick TembDB on yet another seperate spindle if lots of queries are creating a lot of activity on TempDB (don;t know what the MYSQL equivilant is)
I don't know whether the FT DB is trivial and small or large and complex. If the latter then I think storage design is going to dictate performance.
There's a good whitepaper on all this here: http://h71019.www7.hp.com/ActiveAnsw...0-225-1,00.htm - quite MS SQL centric (isn't everything!) but a good read for those with a technical interest in this sort of stuff!
(Edited to add - sorry if this post goes slightly OT and more toward "what can we do to make FT faster" - I'm sure the team at FT are doing a great job with this major upgrade - I just wanted to re-iterate there are a bunch of people out here who use FT on a regular basis who are happy to do whatever they can to help out)
Last edited by matthewuk; Apr 4, 2004 at 3:11 am Reason: edited to clarify reason for posting!
