Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Optimizing physical server performance

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-20 : 09:52:15
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

2489 Posts

Posted - 2004-04-20 : 14:07:08
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

42 Posts

Posted - 2004-04-24 : 03:31:50
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
   

- Advertisement -