Help with MySQL optimisation

Portal Home > Knowledgebase > Articles Database > Help with MySQL optimisation

Posted by NuPagady, 12-20-2008, 01:51 PM
Help with MySQL optimisation Hello, I'm running a forum with 1.1 GB database. It's hosted on quad core server with one HDD. On peak time website load very slowly. It appears that the bottleneck is the hard drive. I quess MySQL is not properly optimized and instead of using RAM to cache database content, it's putting a lot of pressure for a hard drive. What would be the optimal MySQL configuration in my case? Were I should start looking for information on this? Thank you.
Posted by HD Fanatic, 12-20-2008, 02:21 PM
Post your my.cnf configuration here. __________________ Proud customer of Softlayer
Posted by woods01, 12-20-2008, 02:31 PM
Optimizing MySQL How much memory do you have? Chosing the right configuration file for your memory use is the best way to run. Examples are shown. <= 64M Memory http://hkdtn.net/mysql/my-small.cnf (32M - 64M) http://hkdtn.net/mysql/my-medium.cnf 512M http://hkdtn.net/mysql/my-large.cnf 1G-2G http://hkdtn.net/mysql/my-huge.cnf More then likely you'll see a difference by choosing the config file that suits your memory. These files may already be on your server depending on how you installed SQL. They were in our /etc folder (FreeBSD). __________________ James Paul Woods Operations Manager HostKitty Internet Services
Posted by mrpepik, 12-20-2008, 02:46 PM
Another thing it might be is how many writes and reads are hitting a specific table. If you are running MyISAM be aware that every read and write creates a table lock and everything queues up behind the active query. If this is the case you might want to consider moving to INNODB, which uses row locking. If you don't have slow query logging turned on you might want to do so, that will give you a better view into what queries are slowing your systems down and you can better optimize the query or add indexes to help. You can use the mysql explain function to analyze your queries to help out. To do so login mysql via command line or open a sql command window in phpMySQLAdmin or via the MySQL Administration Utility you can download from mysql. Then enter the query after the word "explain". For instance: mysql> explain select * from mysql.users; You can find the explaination of the explain command here: http://dev.mysql.com/doc/refman/5.0/en/explain.html The output will help you figure out what indexes are being used and if you need to add additional indexs. __________________Colocube, LLC http://www.colocube.com Dedicated Servers, Fully Managed Servers, Premium Bandwidth, Rack and Cage Space email: sales@colocube.com
Posted by stardot, 12-21-2008, 01:55 PM
Without more information (such as specific forum software) and, as previously requested, your my.cnf theres not much I can suggest other than generalized strategies such as MySQL load balancing. It could also be a simple issue of upgrading your hardware. Also, please post your hardware specs and perhaps hard drive benchmark tests for the sake of argument. AFAIK MySQL runs completely in RAM , but depending on how the forum software is coded and how efficient it is at performing database tasks, it would affect performance undoubtedly.
Posted by @Matt, 12-21-2008, 01:58 PM
Try installing the following script and using this to edit your my.cnf file.http://mysqltuner.com/ __________________HostPenguin - Separate Yourself Ã¢ÂÂ A Christian Owned and Operated Hosting Provider!Ã¢ÂÂ Shared, Reseller, Virtual Private Server Hosting and Website Integrationshttp://www.HostPenguin.net - http://Integration.HostPenguin.net - Sales@HostPenguin.net
Posted by The Universes, 12-21-2008, 02:25 PM
Quote: Originally Posted by stardot AFAIK MySQL runs completely in RAM , but depending on how the forum software is coded and how efficient it is at performing database tasks, it would affect performance undoubtedly. It does not completely run in RAM, it has to read and write from the disk, and if you have a lot of temp tables that don't fit in RAM, your going to suffer performance issues. What you can do is make particular tables (sessions, etc) "MEMORY" instead of MyISAM, assuming you have enough RAM. In addition to running MySQL Tuner, also run Tuning Primer:http://www.day32.com/MySQL/tuning-primer.sh Those 2 scripts will reveal any major misconfiguration and performance problems. __________________The Universes - Server/VPS Management and PHP/MySQL Application Development
Posted by Steven, 12-21-2008, 09:21 PM
Mysql cannot be optimized just based on what people tell you. To be optimized correctly it has to be seen in real time, and tweaked to see what works best and what doesn't I have seen some real huge improvement's with minimal vanilla kernels installed. __________________Steven Ciaburri Competent Linux Server Management from Rack911
Posted by stardot, 12-21-2008, 09:51 PM
MySQL + kernel tuning and other customizations are the only way to go if your software is that "un-standard". Thorough testing and Q
Posted by net, 12-21-2008, 09:56 PM
Moved > Technical

Add to Favourites Print this Article