On Sat, Jun 28, 2003 at 01:47:28PM -0400, Wendell Dingus wrote: > Hello, I've got a situation I'm hoping someone here can help me out with. We > have a web server serving up data via some PHP programs pulling from MySQL > (3.23.56 currently) in INNODB tables. There are 40+ (active) databases > totalling a bit over 28 Gigabytes at this point. As we add more users speed > is beginning to drop... > > Currently all this is on a single dual P3 1.26Ghz IBM eServer with 4GB of > RAM. 4 x 15K RPM drives RAID5. It's hitting load averages >12 during peak > times and database query speeds are really starting to drag...
Uh oh. > I've talked some with the consulting folks at MySQL and have the intention > of contracting them to help analyze our database layout, structure, and > queries to help optimize things. Smart move. > My quandry is this though.. The existing server has 4GB but I can't get > innodb_buffer_pool_size above about 1.2GB I'd buy tons more RAM and go that > route if it could be utilized, I've not figured out how though and assume > that's the deal with a 32-bit CPU. Reading some about 64-bit Opterons and a > 64-bit address space sounds good. Yeah, if you want more stuff in memory, look seriously at the Opterons. > I'm sure I can get the LVS approach with a load balancer and 3 or 4 back-end > "real" servers going without too much trouble. Where do I spend the money > most wisely though? More and more and more RAM in each backend server or is > anything over 2GB going to mostly not be used (Probably P4 2.4Ghz XEON > boxes). Drive speed? I'm thinking I'll test out using fast SCSI drives and > maybe some SATA drives RAID-0 stiped versus non-striped to see the speed > difference. My whole goal is speed, speed, and more speed out of > MySQL! That's what I'd suggest. Get some SCSI disks with really low seek times (4ms) and stripe using RAID-0. > The > actual web applications place a very small load on the server, viewing top > or mytop show that database access is where all the time is going and > primarily on the 2 largest databases. A query against one of the other > databases when the server is mainly idle happen in very few seconds or > tenths. The same query (data layout is basically identical across all the > databases) takes > 2 minutes sometimes on either of the largest 2 databases. Can you post the query and the result of running it thru EXPLAIN? > I have the bosses ear for hardware _now_ though and want to move > forward before that opportunity passes. I'll post the database specs > at the end of this. If you were hosting that much data without a ton > of inserts all day long but lots of selects where would you > concentrate your efforts? Speed of CPU or speed/quantity of RAM or > speed of drives or what? You haven't told us how busy the CPU gets, so I'm going to assume it's *not* the bottleneck. I'd focus ot disk performance and having sufficent memory. > Would you load balance a quantity of servers or lean more towards a > couple of much larger 64-bit servers? Running entirely out of RAM > is very appealing but somehow I suspect that will be cost > prohibitive :) Just buy reasonable hardware, load-balance it, and > then get the MySQL folks to help optimize things? That's the approach we use at work and it works well. Keeps the costs down and makes [most] scaling pretty easy. > Mytop output after about 28 hours of uptime so this is just Friday during > business hours access primarily: > MySQL on localhost (3.23.56-Max-log) > up 1+04:56:30 [13:29:15] > Queries Total: 1,783,317 Avg/Sec: 17.12 Now/Sec: 0.40 Slow: 1,046 > Threads Total: 33 Active: 1 Cached: 0 > Key Efficiency: 99.99% Bytes in: 779,059,153 Bytes out: 543,489,706 Ugh. Only 17 queries/sec average? Hmm. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 813,104,760 queries (362/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
