RE: Database RAID Config w/Client Variables
Thanks for the help, everyone. Adam, we have Client vars timeout after 20 days. We have had trouble with the purging in the past. Last time I looked I believe we had put in the hotfix for this problem and it was doing fine. Matt -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Sunday, January 16, 2005 5:32 PM To: CF-Talk Subject: Re: Database RAID Config w/Client Variables Matt, I've run traced load tests on an isolated client variables database (i.e., separated from the transactional DB) with production-scale data, and the three non-clustered indexes that are created on the CDATA and CGLOBAL tables are about as efficient as can be. Post-test analysis didn't suggest any changes to the indexes in any way. The rows themselves appear to be retrieved as efficiently as possible. The only real bottleneck seems to be deserializing the key-value pairs back into Client variables once the text columns containing them are passed to ColdFusion on each retrieval. I didn't thoroughly read through all the earlier posts in this thread, but are you expiring your Client variables after one day rather than the default 90 days? Respectfully, Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191285 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Matt, There is a current thread running over at SSWUG titled "Raid 10 VS Raid 5 and ISCII". Login there or create a free account and search on the subject line: http://www.sswug.org/ Dan On Fri, 14 Jan 2005 15:55:34 -0400, Matt Wisdom <[EMAIL PROTECTED]> wrote: > I realize that the reader doesn't have all of the info for my situation, and > that there are ways to verify true bottlenecks in a databaes system with Perf > Mon and other tools that I will address when I can. That said, I'm hoping > that others can share their experiences if they've been down this path. Here > is my situation: > > I have one DB server that will host my main site DB and the CFSessions DB > that is created by the CF Servers to use database-based client variables. I > would like to maximize performance of the database but I am unsure as to the > best starting point for allocating hard drives to raid volumes. Client > variables get moderate usage, although the data contained within is not > mission critical. The MainDB gets relatively heavy usage, mostly reads. The > system looks like this: > > SQL Server 2000 > 10 drive raid > system memory maxed out > Fast CPUs > > The database files look like this: > > Main DB > Main DB Translog > CF Sessions DB > CF Sessions DB Tlog > > What RAID config would you use for the databases? > > Main DB = 4+1 parity RAID 5 > Main DB tlog = 1+1 mirrored RAID 1 > CF Sessions DB = 1 > CF Sessions DB tlog = 1 > Hot spare = 1 > > or splitting the drives based on sequential vs non-sequential reads: > > Main DB and CFSessions DB = 6+1 parity RAID 5 > Main DB tlog CF Sessions DB tlog = 1+1 mirrored RAID 1 > Hot spare = 1 > > or something else?? > > How important is (battery backed) write cache for the raid? The main database > doesn't write very much, but the CFSessions DB should be writing every > pageload. Would you use this? > > Thanks very much in advance, > Matt > > ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190713 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Database RAID Config w/Client Variables
If you have the disks, always choose a RAID10 config over a RAID5 config. The situations where RAID5 can compare to RAID10 are very little, and only few adapters are able to achieve it (LSI MegaRAID 320-2X). RAID10 gives you the combination of performance and redundancy. We run our replicated sql servers also with RAID10 configurations. The difference between RAID5 and RAID10 is immense. This is from the MS SQL Server notes: "To store your database log files (.ldf), the best performance is often gained by storing them using a RAID 1 (mirrored or duplexed) array. This assumes that there is only a single log file on the RAID 1 array. If there is only a single log file on the RAID 1 array, the file can be written to sequentially, speeding up log writes. But if there are multiple log files (from multiple databases) sharing the same RAID 1 array, then there is little or no advantage of using a RAID 1 array. This is because although writing to a log is done sequentially, multiple log files on the same array means that the array will no longer be able to write sequentially, but will have to write randomly, negating much of the benefits of a RAID 1 array. But note: a RAID 1 array might still be better than a RAID 5 array if it can process more random writes than the RAID 5 array. You will have to check your hardware to know for sure. Another option is to put each database log on its own separate RAID 1 array. One more option is to put the log on a RAID 10 array, which offers the best features of RAID 1 and RAID 5. While this is expensive, it will provide optimum performance. [6.5, 7.0, 2000] Updated 12-20-2004" Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 AL Amersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190694 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Database RAID Config w/Client Variables
I realize that the reader doesn't have all of the info for my situation, and that there are ways to verify true bottlenecks in a databaes system with Perf Mon and other tools that I will address when I can. That said, I'm hoping that others can share their experiences if they've been down this path. Here is my situation: I have one DB server that will host my main site DB and the CFSessions DB that is created by the CF Servers to use database-based client variables. I would like to maximize performance of the database but I am unsure as to the best starting point for allocating hard drives to raid volumes. Client variables get moderate usage, although the data contained within is not mission critical. The MainDB gets relatively heavy usage, mostly reads. The system looks like this: SQL Server 2000 10 drive raid system memory maxed out Fast CPUs The database files look like this: Main DB Main DB Translog CF Sessions DB CF Sessions DB Tlog What RAID config would you use for the databases? Main DB = 4+1 parity RAID 5 Main DB tlog = 1+1 mirrored RAID 1 CF Sessions DB = 1 CF Sessions DB tlog = 1 Hot spare = 1 or splitting the drives based on sequential vs non-sequential reads: Main DB and CFSessions DB = 6+1 parity RAID 5 Main DB tlog CF Sessions DB tlog = 1+1 mirrored RAID 1 Hot spare = 1 or something else?? How important is (battery backed) write cache for the raid? The main database doesn't write very much, but the CFSessions DB should be writing every pageload. Would you use this? Thanks very much in advance, Matt ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190687 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Matt Wisdom wrote: >>> 10 drive raid >> >> Which controller? Chipset? Battery backup? RAM? > > Looking at an adaptec U320, 256 MB w/battery backup for 72 hours. Cache size matters. See for instance the following tests with 128 vs 512 MB controller cache: http://www.tweakers.net/benchdb/testcombo/82 http://www.tweakers.net/benchdb/testcombo/197 If you can get more cache, it is probably worth it. >>>What RAID config would you use for the databases? >> >> Depends on what RAID controller you have. If it is IOP321 based, >> 10 disks in a RAID 6 or 9 disks in a RAID 5 and a hotspare. > > I didn't know what RAID 6 is -- "RAID-6. This type is similar to RAID-5 but > includes a second parity scheme that is distributed across different drives > and thus offers extremely high fault- and drive-failure tolerance. There are > few or no commercial examples currently." It is basically RAID 5 with 2 parities. In RAID 5 you can loose one disk without dataloss, in RAID 6 you can loose 2 disks. > So you would have only one data volume for the two databases? What about the > logs? All on the same volume. But remember the "If it is IOP321 based". Adaptec makes its own RAID XOR chips, and it looks like vendors buying their RAID XOR chips from Intel currently hold the performance crown, which is more important with RAID 5: http://www.tweakers.net/benchdb/test/80 >>> How important is (battery backed) write cache for the raid? >> >> It is the single most important way to improve performance on the >> hardware side. > > Sounds like this would be a hard limit on scalability for connections per > second because of the cfsessions client vars. Yes. That is why a battery backed cache is so important. Jochem ~| Protect Your PC from viruses, hackers, spam and more. Buy PC-cillin with Easy Installation & Support http://www.houseoffusion.com/banners/view.cfm?bannerid=61 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190651 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
> It's a little strange because since the indexing is handled automatically it > isn't obvious how optimized the processes are. Has this ever been a > bottleneck for scalability? Matt, I've run traced load tests on an isolated client variables database (i.e., separated from the transactional DB) with production-scale data, and the three non-clustered indexes that are created on the CDATA and CGLOBAL tables are about as efficient as can be. Post-test analysis didn't suggest any changes to the indexes in any way. The rows themselves appear to be retrieved as efficiently as possible. The only real bottleneck seems to be deserializing the key-value pairs back into Client variables once the text columns containing them are passed to ColdFusion on each retrieval. I didn't thoroughly read through all the earlier posts in this thread, but are you expiring your Client variables after one day rather than the default 90 days? Respectfully, Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ~| Purchase Studio MX from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=50 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190650 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Database RAID Config w/Client Variables
What is the most intense client vars database anyone here has set up? It's a little strange because since the indexing is handled automatically it isn't obvious how optimized the processes are. Has this ever been a bottleneck for scalability? Matt ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190646 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Database RAID Config w/Client Variables
>> 10 drive raid >Which controller? Chipset? Battery backup? RAM? Looking at an adaptec U320, 256 MB w/battery backup for 72 hours. The rest of the system is a SuperMicro serverworks dual xeon mobo + case. It has 10 drive bays in the 4U case with a backplane and hotswap. 4GB of ram. About $6,000 for the whole thing. >> What RAID config would you use for the databases? >Depends on what RAID controller you have. If it is IOP321 based, 10 disks in a RAID 6 or 9 disks in a RAID 5 and a hotspare. I didn't know what RAID 6 is -- "RAID-6. This type is similar to RAID-5 but includes a second parity scheme that is distributed across different drives and thus offers extremely high fault- and drive-failure tolerance. There are few or no commercial examples currently." So you would have only one data volume for the two databases? What about the logs? >> How important is (battery backed) write cache for the raid? >It is the single most important way to improve performance on the hardware side. Sounds like this would be a hard limit on scalability for connections per second because of the cfsessions client vars. Thanks, Matt ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190644 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Matt Wisdom wrote: > > 10 drive raid Which controller? Chipset? Battery backup? RAM? > What RAID config would you use for the databases? Depends on what RAID controller you have. If it is IOP321 based, 10 disks in a RAID 6 or 9 disks in a RAID 5 and a hotspare. > How important is (battery backed) write cache for the raid? It is the single most important way to improve performance on the hardware side. With a RAID system, every write command requires at least 2 physical writes: every bit has to be stored on at least 2 disks. Even if the write heads are perfectly positioned for the write operation, a disk rotating at 1 rpm will take on average 3 ms for the write. With 2 disks, that becomes 4.3 ms, and with a RAID configuration that requires you to write more disks that approaches asymptotically to 6 ms. And that is the theoretical optimum with perfectly positioned heads. Since the ACID Durability requirement mandates that all changes are logged to physical storage, you can not commit more then 160 transactions that require writes per second on such a system. It is physically impossible. A battery backed cache allows you to overcome this limitation because it allows the controller to group writes together in batches, without the database having to wait. Jochem ~| Now there’s a better way to fax. eFax makes it possible to use your existing email account to send and receive faxes. Try eFax free. http://www.houseoffusion.com/banners/view.cfm?bannerid=63 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190621 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Or you can just grab two boxes.. as a good dell box (from outlet) with 7X140GB(10K) dual etc.. etc.. costs around $4300.. but the setup described below does sound sweet.. mu.. Pete Ruckelshaus wrote: > Wow, that's one serious database server. How many transactions per > minute/hour/day/whatever will you be getting? > > Personally, I think you're putting all your eggs in one basket. I > would guess that server is going to cost you around $20k? I would > instead get a pair of identically configured boxes, probably dual > Xeons with 2-4GB RAM. Have the drives RAID5, which I think you can do > with 3 drives. Use 10k drives, since the servers will be doing the > database I/O to the DAS unit I'm going to mention in a second, if > possible have another standalone small 15K drive in the box to use for > pagefile. Then get a DAS (direct attach storage) unit like an EMC, > fiberchannel RAID (get a FC card in the servers), to use as your data > "drive" that is common to both of your database servers (which will be > operating in failover mode). If you've got a little budget left, also > get a less expensive NAS box that you will use to offload your > transaction logs to so you aren't filling your expensive DAS with that > sort of data. > > In the end, it MIGHT cost a touch more than the server you mentioned > in your post, but you'll have greater redundancy and flexibility, and > chances are you'd have better performance. > > Pete > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190613 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Wow, that's one serious database server. How many transactions per minute/hour/day/whatever will you be getting? Personally, I think you're putting all your eggs in one basket. I would guess that server is going to cost you around $20k? I would instead get a pair of identically configured boxes, probably dual Xeons with 2-4GB RAM. Have the drives RAID5, which I think you can do with 3 drives. Use 10k drives, since the servers will be doing the database I/O to the DAS unit I'm going to mention in a second, if possible have another standalone small 15K drive in the box to use for pagefile. Then get a DAS (direct attach storage) unit like an EMC, fiberchannel RAID (get a FC card in the servers), to use as your data "drive" that is common to both of your database servers (which will be operating in failover mode). If you've got a little budget left, also get a less expensive NAS box that you will use to offload your transaction logs to so you aren't filling your expensive DAS with that sort of data. In the end, it MIGHT cost a touch more than the server you mentioned in your post, but you'll have greater redundancy and flexibility, and chances are you'd have better performance. Pete ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190611 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Database RAID Config w/Client Variables
Hi, I've always gone with option 1.. listed below.. > Main DB = 4+1 parity RAID 5 > Main DB tlog = 1+1 mirrored RAID 1 however I never store client variables in DB as it's a very high overhead (atleast for me). but incase I had to.. I went with MySQL on diffrent box. -- Regards Matt Wisdom wrote: > I realize that the reader doesn't have all of the info for my situation, and > that there are ways to verify true bottlenecks in a databaes system with > Perf Mon and other tools that I will address when I can. That said, I'm > hoping that others can share their experiences if they've been down this > path. Here is my situation: > > I have one DB server that will host my main site DB and the CFSessions DB > that is created by the CF Servers to use database-based client variables. I > would like to maximize performance of the database but I am unsure as to the > best starting point for allocating hard drives to raid volumes. Client > variables get moderate usage, although the data contained within is not > mission critical. The MainDB gets relatively heavy usage, mostly reads. The > system looks like this: > > SQL Server 2000 > 10 drive raid > system memory maxed out > Fast CPUs > > The database files look like this: > > Main DB > Main DB Translog > CF Sessions DB > CF Sessions DB Tlog > > What RAID config would you use for the databases? > > Main DB = 4+1 parity RAID 5 > Main DB tlog = 1+1 mirrored RAID 1 > CF Sessions DB = 1 > CF Sessions DB tlog = 1 > Hot spare = 1 > > or splitting the drives based on sequential vs non-sequential reads: > > Main DB and CFSessions DB = 6+1 parity RAID 5 > Main DB tlog CF Sessions DB tlog = 1+1 mirrored RAID 1 > Hot spare = 1 > > or something else?? > > How important is (battery backed) write cache for the raid? The main > database doesn't write very much, but the CFSessions DB should be writing > every pageload. Would you use this? > > Thanks very much in advance, > Matt > > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190607 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Database RAID Config w/Client Variables
I realize that the reader doesn't have all of the info for my situation, and that there are ways to verify true bottlenecks in a databaes system with Perf Mon and other tools that I will address when I can. That said, I'm hoping that others can share their experiences if they've been down this path. Here is my situation: I have one DB server that will host my main site DB and the CFSessions DB that is created by the CF Servers to use database-based client variables. I would like to maximize performance of the database but I am unsure as to the best starting point for allocating hard drives to raid volumes. Client variables get moderate usage, although the data contained within is not mission critical. The MainDB gets relatively heavy usage, mostly reads. The system looks like this: SQL Server 2000 10 drive raid system memory maxed out Fast CPUs The database files look like this: Main DB Main DB Translog CF Sessions DB CF Sessions DB Tlog What RAID config would you use for the databases? Main DB = 4+1 parity RAID 5 Main DB tlog = 1+1 mirrored RAID 1 CF Sessions DB = 1 CF Sessions DB tlog = 1 Hot spare = 1 or splitting the drives based on sequential vs non-sequential reads: Main DB and CFSessions DB = 6+1 parity RAID 5 Main DB tlog CF Sessions DB tlog = 1+1 mirrored RAID 1 Hot spare = 1 or something else?? How important is (battery backed) write cache for the raid? The main database doesn't write very much, but the CFSessions DB should be writing every pageload. Would you use this? Thanks very much in advance, Matt ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190603 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
