Seems to me like a better architecture might be: N apache servers with mysql clients 1 Master Mysql Server 1 Slave Mysql Server/admin/backup server
If you have 5 slave servers (one on each apache server), that would cause much more traffic on the internal network than each apache machine just being a client and running the queries on the master server. We do the kind of traffic you're talking about with the above architecture and it works great (plus you're not administering N+1 mysql servers with all their security/maintenance issues). Having the database totally removed from the internet is definitely safer. I highly recommend the DL360 G2 from compaq/HP with dual 1.4Ghz processors and 1 GB ram and raid 1 scsi drives. This machine is more expensive than the generic equivalent, but it's 100% worth it. If you're looking to double or triple your traffic, you may need to think about a dl380 with quad processors (but that's probably overkill -----Original Message----- From: Jeremy Hiatt [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 11:29 PM To: [EMAIL PROTECTED] Subject: Re: MySQL hardware concerns >Can you translate 100,000 users into database numbers? How many SELECTs >per second, UPDATEs per second, and so on? That'd help a lot. I believe roughly 70% of our queries are SELECTs, 29% UPDATEs, and less than a percent for both INSERTs and DELETEs. MySQL on localhost (3.23.46) up 4+22:08:37 Queries Total: 38,217,014 Avg/Sec: 89.86 Now/Sec: 131.80 Slow: 0 Threads Total: 1 Active: 1 Cached: 0 Key Efficiency: 99.98% Bytes in: 3,696,152,003 Bytes out: 4,006,033,106 +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Handler_delete | 2056 | | Handler_read_first | 86116 | | Handler_read_key | 38126269 | | Handler_read_next | 66568466 | | Handler_read_prev | 0 | | Handler_read_rnd | 26653054 | | Handler_read_rnd_next | 4229676008 | | Handler_update | 12940207 | | Handler_write | 171166 | | Key_blocks_used | 15582 | | Key_read_requests | 77465425 | | Key_reads | 14742 | | Key_write_requests | 470685 | | Key_writes | 468967 | | Uptime | 425398 | +--------------------------+------------+ This is from a production server and isn't as efficient as the work in progress on development servers (note handler_read_rnd_next), but these are accurate enough for scaling. Note that many queries take place server-side in automatic calculations (cron). We're leaning towards a dual 1GHz (512k cache) box with 4GB memory and SCSI raid 1-0. Comparitively the figures above are from a dual 1GHz (256k cache) running both apache+mysql, with 1GB memory and SCSI also. As I mentioned before this will be our main DB. Do you think this the best move for our money? How do I calculate how many "Apache/MySQL Slave" sub-servers I can add before our master maxes out? This seems like a hit and miss way to scale, hardware wise. For a few thousand $$$ in new hardware expenses I'd much prefer a hit. Thanks, Jeremy _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
