jwcolby
jwcolby at colbyconsulting.com
Tue Jan 8 09:17:15 CST 2008
Azul actually died the other day. The computer shut down suddenly and when I opened it up the Heat Sink Fan was just hanging loose inside of the case. It turns out that two of the three plastic tabs on one side of the foundation that holds the HSF in place had broken off. This is the second time this has happened to me! To be honest though, I think this is my fault. The HSF I use is a rather large affair, heat pipes to a large radiator etc. It is very tough to see down under it and it is very tough to get the retaining bracket correctly hooked over the tabs when the motherboard is mounted in the case. I think I probably did not get the retaining bracket square so the tabs that broke off had a larger shear load. Sigh. Anyway, the motherboard is not usable. I might be able to get the plastic HSF foundation part, it just bolts onto the motherboard. We'll see. I wanted to use one of these motherboards to replace the aging board that runs my Address validation process. If I can't get the bracket, I will likely just buy the same little motherboard that I used for the Windows Home Server. In the meantime I decided to just go with it and put the new motherboard I bought last week into the case that Azul occupied. It has the first Areca Raid controller that I purchased and the eight 320 gig drives. I also decided to install the quad core processor onto the motherboard BEFORE putting the motherboard into the case so that I can inspect the mounting bracket, get is square and hopefully avoid the issues I had with Azul. When I brought up the quad core, the motherboard complained about the two different pairs of memory sticks so I had to pull one out to get it running. The sticks are different brands and timings. Both are DDR2 800 but one set was a CAS 5 and the other set CAS 6. People advise against using mismatched sticks like that but my experience has been that if you use "worst case", in this case set the motherboard to use CAS 6, then it works just fine. I will probably drop the other set back in later but to get it to boot and run I had to pull it out. So... W2k3x64 now lives in the case where Azul formerly resided. I got the Raid drivers installed so that W2k3x64 can see the raid array, and I am copying the databases that I was testing in the x64 tests below onto that raid array. I should soon have a benchmark with Windows 2003x64 and SQL Server 2005x64, using 4 gigs of RAM. The 8 gig benchmark will follow if the memory will place nice together in this motherboard and with this processor. Unfortunately this also means I will not be getting a Windows x64 / Sql Server 2005 X64 benchmark on a dual core. The processor socket on this new motherboard is turned 90 degrees from the old motherboard and is even more difficult to get the HSF seated properly with the board installed, and it is a ROYAL PITA to pull the motherboard so it just isn't worth the pain to me just for a benchmark. >>>>>>>>>>> So the answer is... 6:36 seconds to run the count query on the new system. >>>>>>>>>>> >W2k3x64 runs the query in 9:26 (9 minutes and 26 seconds) and counts 630,025 records (just a check that we are counting the same thing). This was the previous run for W2k3x64. As discussed in the previous email, the old W2Kx64 only had a pair of IDE 100 drives, and Windows and SQL Server were installed on one, while the DATA database files were installed on the other. In the new system Windows boots off of an IDE 100 drive, AND the SQL Server is installed there and the SQL Server system databases reside there. Basically I just moved the old Boot drive into the new chassis (where the raid array lives), and the boot drive already had Windows 2k3x64 and SQL Server 2005x64 installed on that C: drive. Because SQL Server installed on C: it put its system databases on that drive as well. The main databases now reside on the raid array as opposed to being on the second IDE drive in the old W2Kx64 system. So while I did pick up a 33% decrease in the time to do the count by moving the database from a single IDE drive onto the RAID array, it is still well below the 5:26 required by Stonehenge which is Windows 2K3x32 and SQL Server 2005x32. It does show however what a fast RAID array does for the system! And in the end I still don't know anything, such is the problem with doing benchmarks in the real world. Is it the fact that Windows is running off an IDE instead of the raid? Or the SQL Server system databases running on the IDE? Or is W2K3x64 and SQL Server 2005x64 just slower than the 32 bit brethren? Or is it something else entirely? You really HAVE to change one variable at a time and circumstances made that inconvenient to say the least. According to benchmarks the quad core I am using should be somewhat faster that the dual core I am using. It is quad core rather than dual core so if Windows and SQL Server can use the extra cores it SHOULD give the process a boost. It is pretty hard to sort out what the problem is though and a bit discouraging because of that. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, January 05, 2008 9:13 PM To: 'Access Developers discussion and problem solving' Cc: 'Discussion of Hardware and Software issues'; dba-sqlserver at databaseadvisors.com Subject: [AccessD] x64 tests OK, I have the x64 system up and running. I am doing tests on the same four databases. I have: HSID which contains data used in selection criteria, with a PK. 50 million records. AZHSID contains address validated records of name / address fields originally in HSID. With a PK matching the PKs in HSID. 50 million records ZipCodeWorld contains about 80K records of valid zip codes. It has information that allows me to exclude prisons, military FPOs etc. PSM11132 contains all of the queries required for me to fill an actual order of addresses (112000) going out to a client. The order asks for: Source File FIELD Select Codes Description Geography HSID/INF 074 2, 3, 6 OR Female 35 44 HSID/INF 073 2, 3, 6 Female 45-54 AND HSID/INF 091 7-9, A-T INC $50,000+ AND HSID/INF 282 Y Tennis HSID/INF 283 Y OR Golf HSID/INF 284 Y OR Snow Skiing HSID/INF 288 Y OR Bicycling HSID/INF 292 Y OR Running HSID/INF 294 Y OR Horseback Riding HSID/INF 298 Y OR Power Boating HSID/INF 299 Y Sailing AND Zip Code World 015 16980 CBSA Chicago Zip Code World 015 19820 OR CBSA Detroit Zip Code World 015 12580 OR CBSA Baltimore Zip Code World 015 31100 OR CBSA Los Angeles Zip Code World 015 35620 OR CBSA New York Zip Code World 015 37980 OR CBSA Philadelphia Zip Code World 015 47900 CBSA Washington DC As you can see basically they want women 35 to 54, income > 50K who participate in various hobbies, and live in selected big metropolitan areas. In order to prep the order I had to build cover indexes (field and PK) on the hobby fields. I already had cover indexes on the income and PK, and a single cover index on ALL of the age fields plus the PK. I then built FOUR different views. One for Age/Sex, one for income, and one for hobbies. Those three are ANDS so by pulling the PKs for each of those views I could do an inner join to get the AND. From these queries I actually only pulled the PK field itself to minimize data pulled out of the table. I did not need the select data itself, just the PKs for the joins. I then built a fourth view of Zip Code World (ZCW) using the CBSA code that selected ZIPS in specific metropolitan areas. From this view I pulled ONLY the zip field. I built a FIFTH master view that pulled in the four views above, PLUS the AZHSID table. AZHSID contains the PK to match the HSID select views, plus address validated name / address information. That information will be actually extracted and written into a flat file on disk, zipped and sent to the client. So I have age/sex, income, hobbies and AZHSID inner joined. The data pulled is just the PKID and name / address which includes Zip. I then inner join the zip from the ZCW view to limit the addresses to those in the right zip codes. Whew. OK, so the thing I am going to time is the COUNT of the PKs in this big query (how many people match all the criteria and live in the right zips). I am going to time the count on my original system that I actually used to run this order, and then on the new system. The original system (Stonehenge) is a AMD "3800" X2 with 4 gigs ram, Windows 2003 x32, SQL Server 2005 x32, with a dedicated raid controller card with an 8 drive raid 6 array. The new system (W2k3x64) is (currently) an AMD "3800" x2 with 4 gigs ram, Windows 2003 x64, SQL Server 2005 x64, with a pair of 650 gig IDE 100 hard disks. No raid. The databases reside on one of the 650 gig drives and the OS resides on the other. Stonehenge runs the count query in 5:32 (5 minutes, 32 seconds) and counts 630,025 valid addresses with the criteria above, in the zips specified. W2k3x64 runs the query in 9:26 (9 minutes and 26 seconds) and counts 630,025 records (just a check that we are counting the same thing). So the first thing that we see is that the new system is slower. The difference is likely caused by the fact that the Raid array in Stonehenge STREAMS data at 400 mbytes / sec whereas the single disk in W2k3x64 streams MUCH slower, probably around 50 mbytes / second. We do not KNOW that is the reason for the difference at this point, but it is a pretty good bet. I "sacrificed" the video controller from Azul to build the new system (yea, poor planning, forgot the damned video card) so I will probably move the raid array into the new system, or more likely move the new system (and boot disk) into the cabinet that Azul currently occupies. Moving a raid array with 8 disks is not something I want to do if I can avoid it. Next, I am going to pull the other 4 gigs (two 2 gig sticks) out of Azul and move it into W2k3x64. I have a video card on order, will be here next week but for now Azul is dead anyway so I might as well scrounge the memory as well. I did not take CPU and memory usage readings with just the 4 gigs, but with 8 in there both processors are pegging (100%) and the memory usage is maximum (only about 200 megs available) and it is using about 8 gigs of page file as well. W2k3x64 runs the same query in 9:11 after adding two additional sticks of memory and boosting the total to 8 gigs. Oddly, SQL Server has a little control for telling it how much it can have max, and it is maxed at ~2 gig bytes. I was under the impression that it would now allow SQL Server to natively use more ram. Sigh. there is an unchecked box for "Use AWE to allocate memory" which I just checked and am now rerunning the query a third time. With AWE memory enabled, the query ran in 8:27. A slight boost but certainly not stellar. So why is SQL Server not using more memory natively? What I really need to do is create a dual boot with Windows 2003 x32 and SQL Server 2003 x32, and then run the count query again for that. I suspect though that the Raid card and array would do more than anything to bump the speed back up. I am going to swap the quad core processor from Azul and see what that does for me. I think I will swap the view card and memory back in to Azul and try running the same database on Azul. It is virtually identical to Stonehenge. It has a quad core AMD, 4 gigs of memory and an 8 disk raid array on the same controller card that Stonehenge has. I could also TRY to do a dual boot to W2K3x64 but it currently boots off the raid which makes installing the new OS difficult (adds the issue of x64 raid drivers at install time). F6, floppy and all that. I hate that stuff. Well, more later. John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com