Results 1 to 10 of 10
  1. #1
    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.

  2. #2
    Join Date
    Jan 2005
    Posts
    2,203
    Post your my.cnf configuration here.





    __________________
    Proud customer of Softlayer

  3. #3
    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

  4. #4
    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

  5. #5
    stardot 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.

  6. #6
    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

  7. #7
    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

  8. #8
    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

  9. #9
    stardot Guest
    MySQL + kernel tuning and other customizations are the only way to go if your software is that "un-standard".
    Thorough testing and Q

  10. #10
    Join Date
    Mar 2003
    Location
    /root
    Posts
    23,990
    Moved > Technical

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •