MySQL Performance (Pls help)

Portal Home > Knowledgebase > Articles Database > MySQL Performance (Pls help)

Posted by mumuletz, 05-24-2007, 07:30 AM
I have a site that runs from a MySQL database. The database isn't big, it has only 16.1 MB on 17 tables with 103,978 records, and all are properly indexed. My problem is that my MySQL has a certain problem with this database because I'll have to wait around 5-10 minutes to receive the query results. I've tuned MySQL, I've tuned Apache and in the daily usage i have usually low Load Averages of 0.19 0.22 0.40. My server specs are Intel P4 at 3GHz with HT, 2Gb of RAM (in dual Chanel), 2 Hdd of 250Gb (backup) and 300Gb (main hdd), both with 16 mb cache. I'm running Debian 3.1 with 2.6 kernel and there is no swapping to disk as i had previous on the 2.4 kernel. I will post my.conf below and maybe you'll give me a suggestion. I thought that i might need to start rewriting my code in order to be able to fix things but i need at least another opinion from someone who knows more. Any suggestion ?
Posted by Techbrace, 05-24-2007, 07:41 AM
Use the following mysql settings and let me know how it goes. thread_concurrency=2 ## Number of CPUs x 2 interactive_timeout=20 wait_timeout=20 query_cache_size=64M key_buffer=256M thread_cache_size=256 sort_buffer_size=4M read_buffer_size=2M log_slow_queries=/var/log/mysql_slow_queries.log (you can look into this file for any slow queries)
Posted by mumuletz, 05-24-2007, 08:08 AM
well i see that it hasn't changed anything. The only difference is in the slow query log that shows now more slow queryes but those are only 6. just a few examples below..
Posted by juangake, 05-24-2007, 08:50 AM
How many fields your 'photo_package' table have? What field types are those? Can you limit the fields needed for the query instead of using the wildcard * ? I assume your queries are returning binary results, and that can be a HUGE amount of data. Hope that helps.
Posted by mumuletz, 05-24-2007, 09:01 AM
Showing rows 0 - 29 (51,705 total, Query took 0.0084 sec) - for 12 mb of data
Posted by Scott.Mc, 05-24-2007, 01:29 PM
The simple thing is to use optimized querys, never use SELECT *, only select the rows you require. You executed that query 4 times in the space of just a few minutes which is pretty much why you have more mySQL problems.
Posted by Techbrace, 05-24-2007, 03:15 PM
mumuletz, as you have noticed from the logs, you have got a few slow queries. It can happen either due to bad coding or due to high usage of server resources. Try if you can tweak those query commands (such as by selecting only required fields or retreiving only few % records at a time etc). If you can alter the DB, I would also suggest splitting the table to 2 so that the tables will have lesser records. You will also have to look into the memory usage and upgrade RAM if necessary if you think there is nothing should be done with the mysql queries.
Posted by mumuletz, 05-24-2007, 03:22 PM
well thank you for this answer. Ram has been upgraded and has shown no improvement at all when i went from 1 Gb to 2 Gb. Since system usage if at max 50% when running such a query I will look into rewriting the queries. Splitting is no sollution so far since i have bigger databases that work very fast.
Posted by juangake, 05-25-2007, 06:18 AM
Hello, You're right, bigger databases takes less time to query a SELECT * than your problematic case. One million results from a real-table of half your int fields only took 7 seconds on a production server of mine. I assume you have optimized/repaired your tables AND tried things like "tuning-primer" http://www.day32.com/MySQL/ (script is first link) Regards, Juan
Posted by mumuletz, 05-25-2007, 07:15 AM
yes i did that in the first place. now i reconsider rewriting my queries so that they are more effective.

Add to Favourites Print this Article