MySQL Resources

[AndyH]

Yeah that is weird and I have no idea what could be doing that.

[Dan Grossman]

Right now it's my #1 concern. My desktop is doing queries in 0.5 seconds that sometimes take 2.5 seconds on the server. That's such a huge gap that if I can bridge it, a lot of my other performance worries go away.

It might actually be a good time to buy an entirely new server and just move the sites. Most of my sites, the important ones, are on a different server, so there's not much hassle.

[Todd W]

Dan- Since it sounds like you know a lot about what you are doing in the query aspect and have messed around with the mysql conf file yourself already... Might I suggest hiring a mysql wiz to tweak your my.cnf for you?

You will need to provide all the db info such as your mysql optimization screen, etc.

An image hosting site I ran a while ago was doing ~5million image hits per-day and I was tracking each with mysql and php when I was doing well under 1 million my server overloaded.(Dual Xeon, 2gb, etc) I had a guy tweak my mysql configuration file as WELL AS my httpd config file, after that no problems up to the 5million when I sold the site.

There's only so much you can do with correct db structure and query syntax the rest you will need to make sure your server is configured properly to handle the new number of requests.

[Mal]

Is the MySQL on a separate server? Or is it sharing it with Apache etc? If it's sharing it, then that'll be the reason for the different performance. What CPU is the MySQL server using?

I've worked on some large databases, and in my experience, all but the most basic levels of normalisation should be chucked out the window in such circumstances. Having separate tables is good - especially if you have a lot of inserts/updates, a large table with a large index will need to update that index for every insert and possibly update, which will mean it will die far sooner than the separate tables.

[Dan Grossman]

Is the MySQL on a separate server? Or is it sharing it with Apache etc? If it's sharing it, then that'll be the reason for the different performance. What CPU is the MySQL server using?

My laptop with a slower processor and half the memory is doing these queries faster than my server, with httpd shut down. MySQL has the entire server to itself, and no queries running but my own test.

http://forums.mysql.com/read.php?24,...8510#msg-78510

[Grant29]

Dan,

I've currently got a site with tables with 400,000+ rows. Some tables have millions of rows and queries rarely occur more than 1/2 second. It seems like something definately needs to be tweaked. I struggled for a while with my app, but got lots of help on the mysql discussion list. (http://lists.mysql.com/). Join the MySQL general discussion list. I'm still subscribed to it and I read the list when I have free time to pick up extra pointers.

I basically had to retweak my queries and mysql configuration many times to get the best performance. I'm no expert but try:

[NOTE: Some of these have already be tried by you, or suggested by others]

1. List your machine specs. (processor speed and RAM).
2. Post your mysql version and OS version (Linux, windows, ?).
3. Post your my.cnf file (or my.ini file on windows)
4. Post your create table syntax
5. How many rows are in the table? How big is the table in MB?
6. Try OPTIMIZING the table.
7. Adding too many indexes can be bad. I would try not to INDEX a VARCHAR or TEXT column. Integers take up less space and are quicker to sort. If you have to index a VARCHAR or TEXT, you can try only INDEXing X characters. INDEX(column_name(20)) will use a smaller index)
8. Chris's mention of normalization can help a lot. Basically you are trying to remove all duplicate data when you normalize.
9. Use the EXPLAIN syntax to see how the SELECT query is using the INDEXes to see if it's optimizing like you think.
10. Unless you need row-level locking, I'd use the MyISAM table format. If you are using this, I'd check the key_buffer_size, query_cache_size, and tmp_table_size settings in the my.cnf file.
11. Is this a dedicated webserver, or are you running multiple sites off the same mysql database? It could be that it's got other queries running and slowing down your tests. During a slow query, I sometimes SSH into the box, log into mysql and do a SHOW PROCESSLIST; and it will print out all the queries running and the time they are taking to run.

Grant

[Todd W]

I got a lot from the following articles:


This briefly describes the normalization form 1-4
http://databases.about.com/od/specif...malization.htm

NF2 Sample/Help
http://databases.about.com/od/specif...ucts/a/2nf.htm

NF3 Sample/Help
http://databases.about.com/od/admini.../bldef_3nf.htm

This goes from 1NF --> 6NF (Well, kind of 6) with examples.
http://www.answers.com/topic/database-normalization