[SunRescue] solaris7 i/o tuning

[James Lockwood]

On Mon, 21 May 2001, Dave McGuire wrote:

>   Hey folks...can anyone here with some solaris experience (James L?)
> give me any pointers for performance tuning on solaris7?  The machine
> is an Ultra30 (250MHz) w/512MB RAM.  The application is a somewhat
> large, primarily heavy-writing database application running MySQL
> v3.23.38.  Writes will be coming in in 30-50Kbyte chunks a few times
> per second from the network and the server must maintain real-time
> responsiveness.

First of all, MySQL performance tuning is a royal pain as it does
phenominally dumb filesystem I/O.  Still, there are things which can be
done.

>   Is there anything filesystem-wise that could be tuned to better
> accomodate this type of activity, given that I know the
> characteristics of the load?

I assume you're using UFS as the fs back end for the DB, correct?  I'm
assuming for the moment that this is a single spindle or some combination
of RAID 0/1 as s/w RAID 5 is a bad thing for guaranteed service times.
Do you need synchronous data commits in case of DB crash/hw failure/etc?

Assuming you don't, I'd stick to generic tuning steps first.  Crank the
UFS write throttle up to the moon to prevent synchronous commits from
screwing up your flow.  Mount the partition in question with either
noatime or dfratime to help with the same issues.  This should easily let
the DB return from write ops with near zero service times and do
write-behind as long as the data backlog stays below the high water mark.

Consider setting the executable bit on your DB files and enabling priority
paging.  This will give them favored status when paged in over other files
and pages won't be reclaimed unless there is a significant memory
deficiency.  An alternative if you have much more memory than DB space is
to use my "filelatch" program, this mmap's in the file in question
(MAP_SHARED), pages all of the file in and memcntl's the pages to a locked
in-core state.  This is really more useful for read-mostly apps, though,
and another alternative is to stuff your whole DB into a tmpfs filesystem
either regularly backed up or cachefs'd to a normal UFS backend
filesystem.  You definitely want striped swap for this though!  Worked
great for a 50GB Texis read-only database.

If you don't have Cockrofts book already, get it.  It's not the end-all of
Solaris tuning but it's a great place to start.

Without knowing more about the number of tables, size of the DB or
distribution of data over spindles I can't say much more, but following
these basic steps will almost always increase performance.

One last word to leave you with: Disksuite RAID 0 is a _pig_.  The amount
of CPU consumed in the act of simple striping is obscene:

http://groups.google.com/groups?ic=1&th=65f944b42d554e31,17

This will likely not be as much of a concern on Ultra hardware but it is
something to be aware of.  The lack of multithreading is also disturbing.
I have an LD_PRELOAD library which can outperform Disksuite RAID 0 by 500%
for large file writes where it's CPU bound (many fast disks, pre-Ultra
CPU), though it's not really suitable for general use.

-James