Disk Performance

[lgarner]

We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries. Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration: Raid5 or Raid10? Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server. Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control. We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

Thanks.
Lee.

 

[ericbrunson]

Does it get hung up on a single query or are multiple queries contending for the same disk io?

 

[lgarner]

It appears to be single queries. top indicates very high cpu utilization, between user and iowait, and then an answer is returned. Then the load shifts to another cpu for the next question.

 

[ingresman]

have you looked at the plan i.e. have you tried to tune the query ?

 

[lgarner]

As I said, that's beyond our control. The app is not developed in-house. I'm aware that it performs fairly complex statistical analyses in the SQL statements, but without getting a new application there's not a lot we can do. When the tables get over about 15mil records, it can take up to 5 minutes for a page to be displayed. The first step that I want to take is to make sure that the server is tuned well.

Thanks

 

[hvass]

I use mysql for analysis on large data sets and get good performance from average machines (RAID0 single CPU AMD64). In general we can only get minor improvements in performance from tweaking the mysql set up when compared to optimising or rewriting the queries.

If you can get to the mysql code then 'explain' every select statement. If you cannot then you should write some code to test performance - or adapt the benchmark code examples that comes with mysql - trying out math vs record processing tasks.

Even if this checks out they may have extended mysql with their own bespoke stats functions (mysql only has limited stats functions). These might have worked fine when they wrote them using small test data sets but ... with stats processing time tends to grow exponentially with cases and stats routines can be very CPU intensive depending on what they are doing.

Mysql code issues we can probably help with - unfortunately you are probably a bit stuck unless you can get to see the sql.

 

[ingresman]

if the disk is flat out its being asked to do a lot. Eiotehr that is the nature of the query or it can be tund Evn if it ias a package you should be able to ee what the SQL is that is being issuedYou can run that query in the mysql command tool and see what the plan is . You mught have to put and index in perhaps just for this particular query.