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...
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. Dealing with belt-tightening management on
the other end though I want to get rolled out some new software and hardware
before doing that. I've been spending time working with a test setup using
the load-balancer routines at www.linuxvirtualserver.com and like what I see
so far.
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.
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! 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.
So you see my concern? A 2 minute query on an _unloaded_ server is not going
to improve no matter how many servers I put behind a load balancer. Again
I'll pay the MySQL folks to analyze and hopefully come up with lots of
braindead things we're doing and help performance a lot. 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? 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? I sure don't want
to spend a ton and then be told later that it would have been much better
had I went with a different setup if you know what I mean :) Thanks in
advance! If any additional statistics would help let me know!
PS. Any pointers to any approaches to what I think I read about Google,
_striping_ database data across multiple servers for speed? Also, I'm
unfamiliar with them, but do PostgreSQL or Oracle or DB2 or something like
that have anything that would specifically work better in this scenario?
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
The databases themselves from phpMyAdmin:
(This one is the main killer: xxxxxxxxxxxx 40 1.8 GB 5.5 GB
7.3
GB )
Database Table(s) Data Indexes Total
xxxxxxx 0 0 Bytes 0 Bytes 0 Bytes
xxxxxx 44 88.4 KB 182.0 KB 270.4 KB
xxxxx 21 14.8 MB 10.0 MB 24.8 MB
mysql 6 1.5 KB 9.0 KB 10.5 KB
xxxxxxxxxxx 17 1.3 GB 2.6 GB 3.9 GB
test 0 0 Bytes 0 Bytes 0 Bytes
xxxxxxxxxxxxx 9 205.8 MB 92.1 MB 297.8 MB
xxxxxxxxxxxxxx 16 465.4 KB 184.0 KB 649.4 KB
xxxxxxxxxxx 63 441.7 MB 581.8 MB 1.0 GB
xxxxxxxxxxx 59 103.2 MB 188.3 MB 291.5 MB
xxxxxxxxx 59 0 Bytes 59.0 KB 59.0 KB
xxxxxxxxxxx 53 14.6 MB 4.6 MB 19.2 MB
xxxxxxxxxxx 59 139.8 MB 249.5 MB 389.3 MB
xxxxxxxxxxxx 63 2.1 MB 2.1 MB 4.1 MB
xxxxxxxxxx 55 129.6 MB 80.4 MB 210.0 MB
xxxxxxxxxxx 59 38.2 MB 72.5 MB 110.7 MB
xxxx 53 14.1 MB 4.6 MB 18.6 MB
xxxxxxx 59 64.3 MB 120.2 MB 184.5 MB
xxxxxxxxx 59 102.1 MB 181.8 MB 283.9 MB
xxxxxxxxxx 59 328.5 MB 570.6 MB 899.1 MB
xxxxxxxxxxx 59 28.5 MB 58.0 MB 86.5 MB
xxxxxxxx 53 6.5 MB 1.3 MB 7.8 MB
xxxxxxxxxx 59 85.9 MB 139.0 MB 224.9 MB
xxxxxxxxxx 63 75.9 MB 104.0 MB 179.9 MB
xxxxxxxxxx 53 34.5 MB 11.5 MB 46.0 MB
xxxxxxxxxx 55 189.8 MB 61.1 MB 250.9 MB
xxxxx 59 106.2 MB 188.1 MB 294.3 MB
xxxxxxxxxx 53 31.5 MB 8.6 MB 40.0 MB
xxxxxxxxxx 53 167.4 MB 89.4 MB 256.8 MB
xxxxxxxxxx 59 55.6 MB 111.1 MB 166.7 MB
xxxxxxxxxx 58 326.8 MB 565.4 MB 892.2 MB
xxxxxxxxxx 63 26.6 MB 25.9 MB 52.4 MB
xxxxxxxxx 59 37.0 MB 62.2 MB 99.3 MB
xxxxxxxxxxxx 59 68.3 MB 109.9 MB 178.3 MB
xxxxxxxxxxx 53 29.9 MB 8.6 MB 38.4 MB
xxxxxxxxxxx 58 99.7 MB 273.3 MB 372.9 MB
xxxxxxxxx 63 4.2 MB 3.8 MB 8.0 MB
xxxxxxxxxxxx 63 65.5 MB 121.1 MB 186.6 MB
xxxxxxxxxx 53 76.0 MB 17.6 MB 93.6 MB
xxxxxxxxxxx 63 5.0 MB 2.9 MB 7.9 MB
xxxxxxxxx 53 44.0 MB 11.5 MB 55.5 MB
xxxxxxxxxx 59 436.1 MB 796.9 MB 1.2 GB
xxxxxxxxxx 59 99.8 MB 169.9 MB 269.6 MB
xxxxxxxxxx 59 71.1 MB 127.4 MB 198.5 MB
xxxxxxx 63 208.4 MB 281.9 MB 490.3 MB
xxxxxxxxxx 59 65.0 MB 121.6 MB 186.6 MB
xxxxxxx 53 7.0 MB 3.2 MB 10.2 MB
xxxxxx 63 8.1 MB 5.2 MB 13.3 MB
xxxxxx 63 2.8 MB 2.4 MB 5.1 MB
xxxxxxx 63 89.4 MB 112.0 MB 201.4 MB
xxxxxxxxxx 53 20.9 MB 11.1 MB 32.0 MB
xxxxxxxxx 63 689.1 MB 1.2 GB 1.9 GB
xxxxxxxxx 61 766.1 MB 1.7 GB 2.4 GB
xxxxxxxxxxxx 40 1.8 GB 5.5 GB 7.3 GB
xxxxxxx 59 31.6 MB 57.9 MB 89.5 MB
xxxxxxx 53 28.6 MB 8.6 MB 37.2 MB
xxxxxxxxx 53 13.5 MB 3.5 MB 17.0 MB
xxxxxxxxxx 59 582.9 MB 1.0 GB 1.5 GB
xxxxxxx 59 27.1 MB 51.3 MB 78.3 MB
xxxxxxxxx 63 112.2 MB 196.6 MB 308.8 MB
xxxxxxx 59 99.2 MB 159.7 MB 258.9 MB
xxxxxxxxxx 63 466.1 MB 753.8 MB 1.2 GB
xxxxx 38 1.3 MB 103.0 KB 1.4 MB
xxxx 51 49.8 MB 96.0 KB 49.9 MB
xxxxxx 61 108.0 MB 128.0 KB 108.2 MB
xxxxxxxxxx 0 0 Bytes 0 Bytes 0 Bytes
Sum: 66 3400 10.0 GB 18.8 GB 28.8 GB
With selected:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
