Maybe my question is too obvious but, since you have several databases instead of only one, have you considered moving some of them to a separate server ? Also, have you checked MySQL replication capabilities ? It is a very efficient way to distribute the load across several servers unless you do a lot of insert/updates and very few selects.
Regards, Joseph Bueno
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...
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]
