Results 1 to 10 of 10
Thread: Help with MySQL optimisation
-
12-20-2008, 01:51 PM #1WHT Addict
- Join Date
- Jul 2005
- Posts
- 122
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.
-
12-20-2008, 02:21 PM #2Web Hosting Master
- Join Date
- Jan 2005
- Posts
- 2,203
Post your my.cnf configuration here.
__________________
Proud customer of Softlayer
-
12-20-2008, 02:31 PM #3relax, im a professional
- Join Date
- Dec 2007
- Posts
- 1,278
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
-
12-20-2008, 02:46 PM #4Junior Guru Wannabe
- Join Date
- Apr 2009
- Posts
- 57
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
-
12-21-2008, 01:55 PM #5stardot Guest
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.
-
12-21-2008, 01:58 PM #6Web Hosting Master
- Join Date
- Jan 2003
- Location
- U.S.A.
- Posts
- 3,928
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
-
12-21-2008, 02:25 PM #7Web Hosting Master
- Join Date
- Jun 2008
- Posts
- 1,471
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 certain 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
-
12-21-2008, 09:21 PM #8Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
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
-
12-21-2008, 09:51 PM #9stardot Guest
MySQL + kernel tuning and other customizations are the only way to go if your software is that "un-standard".
Thorough testing and Q
-
12-21-2008, 09:56 PM #10The Linux Specialist
- Join Date
- Mar 2003
- Location
- /root
- Posts
- 23,990
Moved > Technical