What is Digg?40 Comments
- konforce, on 10/12/2007, -0/+7The article isn't very informative, as he doesn't mention why you should use settings. (This is not a bash on the author, as his intention was just to share what settings worked for him.) Of course, the MySQL docs aren't much better. They typically say something like, "increase value foo if status variable bar is high" without ever mentioning what "high" is or the exact relation between the two...
A tool I have found useful in monitoring a live server is: http://mtop.sourceforge.net/
Also, I think the best advice for those writing a MySQL application is to enable "slow query" and "no index" logging. You'll quickly find the bad queries that are killing performance - gmillerd, on 10/12/2007, -0/+5Big credit should go to http://hackmysql.com/mysqlreport but it seemed to not get it in the article.
- timdorr, on 10/12/2007, -0/+5It's not really guide, though. It's just an example my.cnf file. It gives no reasoning for any of the values, no explanations of the kinds of queries he's running (read heavy or write heavy?), no benchmarks for improvement, and no guide for how to handle other kinds of hardware.
Plus, I'm guessing he doesn't have 1800 tables in his database (if he does, he'd be better off federating the data), so his table_cache doesn't need to be that high... - konforce, on 10/12/2007, -0/+5Sounds like you're just trolling, but for the sake of entertainment:
1) MySQL's primary target is websites (and to a lesser degree, web applications) where all you really care about is the speed of SELECTs. For the typical website, I would much rather use a database with super fast SELECTs and slow INSERT/DELETE/UPDATEs than one that was just average all around, considering 95 % of the queries are the former.
2) It sounds like you are confusing MySQL with MyISAM. The InnoDB engine is much more robust and fit for more complex tasks. - BrokenDrum, on 10/12/2007, -0/+3Yahoo make heavy use of MySQL (in Yahoo Finance) along with Digg...
Real low traffic sites! - GeneralFailure, on 10/12/2007, -0/+3Why is it that everybody who manages to get a small amount of traffic has to write a guide about it? If he has 500 open connections he is doing something wrong. I handle at least 5 times that traffic (all dynamic) and the most I get is 30 open connections to the database.
- seanmc303, on 10/12/2007, -0/+2This is a nice light guide. I have made many of these same tweaks and the performance boost on my web applications was huge. I would highly recommend giving some of these performance tweaks a try.
- konforce, on 10/12/2007, -0/+2Me too, but keep in mind that budget web hosting nearly always uses MySQL. So you are probably comparing a few SQL Servers on high-end hardware with lots of MySQL servers on budget hardware. Also, novice web developers (who don't really know what they are doing) are more likely to use MySQL than SQL Server. So your observation is far from a scientifically valid conclusion...
You would have to compare the same style of application written by programmers with similar experience on the same hardware before your observations meant anything. - toveling, on 10/12/2007, -0/+2Throw a memcached server at it and your problems will likely go away. It's even suggested to run memcached on top of the web server (with the database separate, but not required).
With 4gb of ram, just 512 of it going to memcached would save almost all the database hits, especially since it's a blog. - crbaker, on 10/12/2007, -1/+3Rather useless guide. It concerns me that 500 connections are required for 2000 users? What about pooling of connecitons?
- paradoxic, on 10/12/2007, -1/+2I agree, not much is explained and I didn't learn anything here. He basically just says to increase numbers without giving real reason why or any insight.
- ceeam, on 10/12/2007, -0/+1@konforce: but what is the future of InnoDB? IMO MySQL (company) ***** up quite a big time letting them slip away.
- Dotnetsky, on 10/12/2007, -0/+1I don't go for that cost thing. You can get SQLExpress, which is the full SQL Server 2005 engine, for free. Even the fulltext search option is free. You have stored procedures, triggers, views, even CLR-hosted assemblies, no throttle on performance, and a 4GB max database size. The only requirement is that you put it on a windows box.
- fenris6644, on 10/12/2007, -0/+1Kind of a disappointing article, there isn't much explanation as to exactly what each of the parameters do and how you'd tune them depending on various situations.
- jcmia1, on 02/07/2009, -0/+1The key to any database tuning is 1) whether db is fully utilizing memory available (buffer pools etc) and 2) is disk io good enough for your transaction rate (e.g., your app generates 100MB/sec random rw but your disks can only handle 80MB/sec, then you have a bottleneck). To monitoring disk io on Windows, Linux, AIX, and Solaris, check out this good article:
http://www.performancewiki.com/diskio-monitoring.h ... - surfshaker, on 10/12/2007, -2/+3200K page views per day on a 3.2 ghz dual xeon, 4 gigs ram! Dude that's like saying you just tunned your ferrari and are now able to go 60mi/h.
Personally I run a fully dynamic site that gets 2 million page views per day and thats only on a 2.4 ghz with 2 gigs ram. Even on that setup I have plenty of room to spare. Though there is some application side search caching going on....similar to what all the latest bulletin software apps do. - Scatterblak, on 10/12/2007, -0/+1mySQL: Cursors suck, transactional rollback is shaky at best, and it sucks at the 92 ANSI standard, regardless of what people say. Tuning mySQL for performance is like tuning training wheels for speed.
- inactive, on 10/12/2007, -1/+2don't let them fool you into thinking it's because mysql is more popular either - that's not why you see more errors, the errors are almost always related to mysql not dropping connections correctly
- ceeam, on 10/12/2007, -0/+1Dunno about 2005 but previous MSSQL engine was dead SLOOOOOW on selects. IIRC 50ms MINIMUM for every simpliest select, i.e. you could get 20 selects per second max on decent hardware. Or was that 20ms and 50 selects? Don't remember. Does not matter either. Inserts and deletes though were much faster and almost on par with other DBs. Such poor performance on simple queries was the killer for me at the last project. This and Windows requirement.
- NuPi, on 10/12/2007, -0/+1I would agree on the number of open connections. If your code is DB friendly then 30-40 simultaneous connections is what you should be seeing with moderate to heavy load.
Not to mention that if you have a single machine setup for the webserver, the webserver itself should be able to handle an average of 30-40 simultaneous HTTP connections without issue. If it can't then either your load is too high for one machine, or your webapp has heavy pages. - milezteg, on 10/12/2007, -0/+1credit given...I forgot where I got it!
- milezteg, on 10/12/2007, -0/+1Thanks for showing me mtop. I added it to the article. I honestly never heard of it before today ;)
- Ythan, on 10/12/2007, -0/+1I agree, the article's good to get people to start thinking about optimizing mySQL, but it doesn't really offer any useful advice.
To really optimize you need the mySQL runtime information from your own server. The easiest way to get it is with " Show MySQL runtime information" link in phpMyAdmin. The most important settings in your my.cnf are:
query_cache_size: as big as you can get it without swapping.
key_buffer_size: divide key_read by key_reads_requests, the value should be less than .01. key_writes / key_writes_requests should be < 1. If not, increase key_buffer_size.
table_cache: check the value of open_tables during maximum load, it if's more than your table_cache increase the value.
read_rnd_buffer_size: allocate 1KB for every 1MB of RAM.
There are other settings which can have a major affect depending on what type of queries you're running, but this is a good start. - brashquido, on 10/12/2007, -0/+1"Why is it that everybody who manages to get a small amount of traffic has to write a guide about it?"
Because they want to. I often write guides on this sort of thing, as much for myself for future reference as to offer a resource for everyone else in a similar boat. Though I actually found this guide to be rather shallow and irrelevant for users looking for a guide to tune their MySQL servers by. - NuPi, on 10/12/2007, -0/+1If your article can help others that are using vBulletin, and have to work around it by tweaking things they 'do' have control over, then go right ahead.
Don't mind the haters. - milezteg, on 10/12/2007, -0/+1Well like the article says I am tuning for vBulletin which is extremely bulky query wise. In fact it is the bulkiest commercial web app I can think of. Hence the article.
- mdxp, on 10/12/2007, -0/+1Unfortunately there is no general solution for this and each particular application and server will need different settings to make MySQL run at its best.
- crbaker, on 10/12/2007, -1/+1You won't see any significant performance improvements if your cache in located on the same box as the DB.
- inactive, on 10/12/2007, -2/+2i'm comparing them to my experience with clueless programmers who write stuff in vb6 back ended by ms access 97 running off workstation pc's and they got less errors then i've seen with mysql systems on the web.
- leodavinci, on 10/12/2007, -4/+4Awesome article, I've been looking for something like this. +dugg
- GrumpySimon, on 10/12/2007, -0/+0A hundred words is not an "article" it's a comment. Anyway, the two best [1], [2] MySQL tuning articles are in Linux Magazine ( need to sign up for a free account to read them, or use bugmenot.com ), and written by Jeremy Zawodny. Unfortunately, they're a good five years old.
[1] http://www.linux-mag.com/2001-06/mysql_01.html
[2] http://www.linux-mag.com/2001-12/mysql_01.html - inactive, on 10/12/2007, -0/+0Indeed. I have a phpBB Board running on a shared hosting account that pulls ~90K Pageviews a day and ~ 1.4 GB traffic a day. We're paying ~ 10$ a month, and it's no special deal, just another anonymous large hoster. It's funny how some people are obsessed with spending a lot of money and big hardware for so little sites.
- aayres, on 10/12/2007, -0/+0Here's an excellent tutorial on optimizing mysql with examples:
http://www.ubbdev.com/forums/ubbthreads.php/ubb/showflat/Number/253430 - zoom1928, on 10/12/2007, -0/+0Exactly. Query caches are just about useless for most busy sites since the entire cache has to be thrown out whenever any table is changed that the query accesses. We found that just by putting about 60% of the data we use in the average session in memcached that our load dropped from a 10 to less than a 1. The average page generation time reduced by nearly a factor of 20.
The most interesting part is that since memcached is so CPU efficient that running it on the same server is even faster than having it on a separate server because of network latency. So we, like Wikipedia does, run memcached on our web servers. Even when hitting the cache for a ton of short-time session data, it doesn't use more than 5% of the CPU time. - john608, on 10/12/2007, -7/+2A perfect compliment to this other story I found on digg:
http://digg.com/software/12_simple_steps_to_make_PHP_lightning_fast_on_IIS
Thanks digg!!! - Dotnetsky, on 10/12/2007, -7/+2Funny, but I see more webpages with MySql connection - related error messages at the top, than any other database. Glad I use Sql Server.
- siggyfawn, on 10/12/2007, -7/+2Worst
Guide
Eva - sandykind, on 10/12/2007, -7/+1Must have been that m-80 you made - now the word 'found' is mixed up with 'posted' in your head
- inactive, on 10/12/2007, -9/+2mysql dies in the arse the second you do anything more complex then select * from table.
it's really fast at selects, crap at anything else. thats why you see all these bogus benchmarks around. - john608, on 10/12/2007, -8/+1I ment posted...DOH!!!
Check out the new & improved 
© Digg Inc. 2009
— Content created and posted by Digg users is