"Jeff" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:47:52 AM:
> > > Jeff wrote: > > > > > > Ugh... > > > mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" > > > And it returned 200 sleeping connections, all persistant > > connections > > > from our app servers and 4 threads_running > > > Also a show status gave me a max_used_connections of 236. > > > If that's the case then I can probably only set it to about > > 250 which > > > means if I set my innodb_buffer_pool_size = 100M and dropping my > > > key_buffer_size to 250, I'll need 1884M of ram according to the > > > formula above, which is dangerously close to the 2G limit > > specified in > > > the warning on the link above. > > > > > > Currently the key_reads to Key_reads_requests is about > > 1:1970 with the > > > key_buffer_size of 384M, so I guess I can safely drop this to 250M > > > > > > Even if I changed the entire DB over to InnoDB, and pushed the > > > key_buffer_size down really low it wouldn't drop the total memory > > > usage below 1600M. > > > > > > So what is this telling me? I need more ram or less > > connections or I > > > should just stay with MyISAM? > > > > > > Thanks, > > > > > > Jeff > > > > > > > I would suggest taking a hard look at why your application > > servers are > > creating 200 sleeping connections, and if that is necessary. You may > > also be able to reduce sort_ and read_buffer_size to 1M each, but I > > couldn't tell you how that might affect your application, so > > you may not > > want to do that. (Does anyone on the list have experience > > modifying these?) > > > > I think the biggest issue will be the system's RAM - the 2G limit on > > MySQL's total allocated RAM is a per-process hard limit on 32-bit > > architecture, but most 32-bit systems benefit greatly from > > having more > > than 2G total RAM (the OS may use the rest for disk caching, > > etc). If, > > say, your server had 4G RAM, then you could safely configure MySQL to > > use very close to 2G, and performance should fly. With only 2G in the > > system, setting MySQL to use as much RAM as possible would > > leave next to > > nothing for the OS or other processes, and that is the > > problem (as I see > > it). > > > > However, that said, more RAM is not always the answer. You > > may get much > > more of a performance increase by modifying your application code so > > that it doesn't "waste" so many connections (thus allowing you to > > allocate plenty of RAM to the innodb_buffer_pool). > > > > Of course, you can do both (just to play it safe, right?). ;) > > > > Well the applications with persistant connections is a touchy subject. > Our apps send and rec data over satelite links which are very expensive. > The shorter the duration of the link the less it costs us. So the > pervailing theory is that with persistant connections the apps will > spend less time re-connecting/dis-connecting from the db. Even > fractions of a second counts when you're talking about thousands of > connections a day and we are charged by the second for airtime. And all of those sleeping connections are costing you how much in unused air time? Compared with many other databases, the cost (time and data) of making and breaking a MySQL connection is cheap. Try a small set of test cases and see for yourself. Maybe you could move 10 of your normal clients from using your persistent connections into a connect-as-needed model and see what that does to your air-time, sleeping connection counts, and total throughput. The only way to know for certain is to try it in your environment but I know that in the world of web development (where connections are also precious and throughput is king) that being connected only when necessary usually works much better than trying to stay connected all of the time. By minimizing the communications overhead imposed on the server by maintaining unused open connections, the server should be able to respond better. You should not only have less "dead air" but each connection itself will take less time as the server will be more responsive. Remember, I recommend making and breaking connections around blocks of execution not per-statement. Let's say you have a "lookup" routine that uses 6 queries and massages the data into something useful client-side. It makes no sense to flip a connection 6 times for those 6 queries as they are all part of one larger process. Prepare your SQL statements as much as possible, make one connection, run the 6 queries, cache the results, drop the connection, process the results from cache. Another trick to maximizing connection usage is to make a few trips to the server as necessary. Using the same scenario I just described, if 4 of those queries did not contain data useful to the user but were used primarily to build the results of the final 2 queries, you may be able to cache the results of the first queries server-side, minimizing the # of frames sent across your satellite link. >That's > the whole driving force behind wanting to switch over to InnoDB. The > thought is it would give us faster writes when we have a hundred apps > trying to write at or very near the same time because of the record > level locking as opposed to the MyISAM Table level locking during writes > and updates. It sounds as though you do a lot of "burst" processing. Your client apps collect information from the user then interacts with the database and waits for more user input. It's that "dead air" time (waiting on the live people to do something) that is costing you a small fortune in unused connection time and consuming valuable server-side resources. > > Now, the question is, if we need to drop the persistant connections in > order to move to an InnoDB engine, will the speed benefit of record > level locking outweigh what is lost by not having persistant > connections? Dropping the persistent connections are not necessary to move to InnoDB. I think many of us believe that 200 sleeping connections out of 236 total are worrisome no matter which storage engine you are using. > > That being said and having just looked at our connections for the past > 60 minutes during what is our roughly our peak time I only see about 350 > which is roughly one every 10 seconds with a rough avg connection time > of about 28 seconds most of which is transfer of data and not db > read/write/updates. So, I believe, from that information I can make an > educated guess that the MyISAM table locking is not the real bottleneck > here and therefore it's probably not going to do us a lot of good to > switch to InnoDB, especially with our current hardware and application > behavior. Thoughts? The three primary benefits of InnoDB: Row level locking (more concurrency for tables with heavy writes or updates), transactional support (for action atomicity, data consistency, and process isolation), and foreign keys (also consistency). If you need any of those features for your application design, that would also be a compelling reason to change engines. > > At some point however, as our traffic grows we probably will hit a point > where the db read/write/updates will start to become a bottleneck and > we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB > engine. What status variables should I be looking at to see if we have > a lot of read/write/updates being delayed? > > > Thanks, > > Jeff > Shawn Green Database Administrator Unimin Corporation - Spruce Pine
