SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Optimizing physical server performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/20/2004 :  09:52:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Rob writes "I have a database used for web log parsing. We average approximately 2 million raw hits a day. Log files have reached as much as 400 mg in 4 hours. I set up a continuous processing system by parsing log files every hour with two continuous datastreams.

I used to use perl and have moved to fpk pascal http://www.freepascal.org/fpc2.html for parsing the log files and query generation. This version of pascal compiles to assembler and is *very* fast. Rough benchmarks are about 70% faster parsing times over perl, better or equal parsig time as c++. Perl and FPK pascal both interface the MDAC ODBC. We are at MDAC 2.7/2.6.

However I noticed my sql server averages 120 transactions a second. It will occassionally peak to 200 transactions a second. This server is a Compaq DL380 with 2 processors, 2 gig ram, with 45 gig hd space in a raid 5 configuration.

We recently obtained new servers. I have ported this database over to a 4 processor server, 2 gig ram, with 175 gig hd space. I enabled all 4 processors and enabled the "use Fibers" box. I have a beefier box but am still getting an average of 120 transactions per second occasionally peaking to 200.

We will be bring in more clustered web servers with more log files. What I want to do is find a way to increase the number of transactions per second.

I'm concerned about replication bottlenecks if I bring another server online and do two way replication for load balancing. I'm thinking this may be my only option tho.

Any ideas?

Our platform is Win2000 Adv Server 2000 SP 4 with SQL Server 2000 operating on Compaq/HP DL 380's. Standard config is 2 processors, new config is 4 processors.

Thanks!
-Rob"

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 04/20/2004 :  14:07:08  Show Profile  Visit MichaelP's Homepage  Reply with Quote
What is your disk setup on this new Box? RAID 5 is NOT what you want to do for this type of database I/O. RAID 5 gives you "fast" reads and slow writes. You are doing a ton of writing, so you are taking a big hit on the disk subsystem performance. Move to a RAID 1/0 setup for your data, and a RAID 1 or RAID 1/0 for your TX logs.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Lewie
Starting Member

Australia
42 Posts

Posted - 04/24/2004 :  03:31:50  Show Profile  Reply with Quote
We had similar problems and found that the new server did not have the correct drivers installed for the hard drives. Also, if the hard drives where running high on the old server and the new hard drives are the same spec, not much will change even if you have more processes.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000