Dan, thanks for your comments. I think we agree on how things should be configured. I'll keep the number of daemons to a minimum.
The sample data I reported earlier is from an existing production system. The new system that we are deploying will be in a 2-node HA Linux configuration. Each node will be: 2 x Dual-core Xeon 2.8GHz (4 processor cores, total). 8GB RAM RAID 10 array (300GB (usable) I prefer RAID 10 to RAID 0+1 because the former can survive the loss of 2 drives and read/write performance is about the same as RAID 0+1. Thanks again for your input. --Eric Robinson -----Original Message----- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 6:35 AM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's "High Performance MySQL" from O'Reilly. HTH, Dan Robinson, Eric wrote: > All customer sites will use the same application, but each will have > its own set of 3 databases. In believe the nature of the application > confines users to brief, bursty selects and updates except possibly > when they run reports. I have not specifically analyzed reporting, but > I ran a 2-hour sampling today of a client site with 25 users during a > period of typical workload. Here's the overall disk statistics: > > % Read Time: 5.26 > % Write Time: 5.00 > Avg Bytes/Read: 2918 > Avg Bytes/Write: 6563 > Avg Read Queue: .05 > Avg Write Queue: .013 > Avg Disk Secs/Read: .013 > Avg Disk Secs/Write: .004 > Avg Read Bytes/Sec: 15151 > Avg Write Bytes/Sec: 66904 > Avg Disk Reads/Sec: 3 > Avg Disk Writes/Sec: 9 > > The numbers are very interesting. The system writes to disk 3 times > more often than it reads, and the writes are more than double the > size. Bytes written per second is 4 times higher than bytes read. Yet, > on average, reads take longer than writes and they tend to stack up in > the queue a little more, which could explain why % Read Time is slightly higher. > This is a RAID 1 array. System has plenty of RAM and was not swapping. > > All in all, the application appears write-heavy, but I don't think > anyone can hog all the disk I/O. > > So, your opinion is that one instance of MySQL with a lot of databases > is just as efficient as multiple MySQL instances? (Note: I WILL have > to run separate instances in some cases because some customers are > using slightly different versions of the application.) > > --Eric > > > -----Original Message----- > From: Atle Veka [mailto:[EMAIL PROTECTED] > Sent: Saturday, June 17, 2006 12:14 PM > To: Robinson, Eric > Cc: mysql@lists.mysql.com > Subject: Re: MySQL Performance Question > > So, you're looking at 150-300 databases and ~31-62k tables based on > your numbers? MySQL should be able to handle that, as should your OS, > but the most important part IMO is how your clients will be using > their data(bases). What sort of queries, how many, etc. Will it be > possible for one client to hog all the disk IO? > > Ignoring the latter questions, with a properly designed database and > tuned queries this doesn't seem like an impossible setup on a single > database daemon. > > > Atle > - > Flying Crocodile Inc, Unix Systems Administrator > > On Fri, 16 Jun 2006, Robinson, Eric wrote: > >> Our server will be home to 50-100 separate clients. Each client will >> have their own set of databases that will be accessed by 10-60 users >> at each client's site. >> >> In terms of performance, is it better to have 1 instance of MySQL >> servicing multiple databases, or multiple instances of MySQL each >> serving 1 database? >> >> Here's some more information to work with: >> >> Each client has 3 databases. >> >> Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all >> less that 10MB. (This is the only database that is updated. The >> others > >> are just for reference.) Main table grows at a rate of a few hundred >> MB/year. >> >> Database2: 50 tables. 3 tables sized 10-100MB. All other tables less >> than 10MB. No data growth. >> >> Database3: 179 tables. 10 tables sized 1-15MB. All other tables less >> than 1MB. No data growth. >> >> --Eric >> >> >> Disclaimer - June 16, 2006 >> This email and any files transmitted with it are confidential and > intended solely for [EMAIL PROTECTED] If you are not the named > addressee you should not disseminate, distribute, copy or alter this > email. Any views or opinions presented in this email are solely those > of the author and might not represent those of Physician Select > Management > (PSM) or Physician's Managed Care (PMC). Warning: Although the message > sender has taken reasonable precautions to ensure no viruses are > present in this email, neither PSM nor PMC can accept responsibility > for any loss or damage arising from the use of this email or attachments. >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
