Results 1 to 16 of 16
  1. #1

    cPanel MySQL Optimize

    Hello
    I want virtual server Mysql Optimization ! Please Help me ...

    Code:
     >>  MySQLTuner 1.1.2 - Major Hayden <[email protected]>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 8957)
    [--] Data in MEMORY tables: 0B (Tables: 29)
    [!!] Total fragmented tables: 514
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9s (1K q [205.333 qps], 22 conn, TX: 42M, RX: 143K)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 56.0M global + 3.9M per thread (400 max threads)
    [OK] Maximum possible memory usage: 1.6G (53% of installed RAM)
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 1% (5/400)
    [!!] Key buffer size / total MyISAM indexes: 16.0M/198.6M
    [!!] Key buffer hit rate: 86.9% (9K cached / 1K reads)
    [OK] Query cache efficiency: 61.9% (1K cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 3% (1 temp sorts / 33 sorts)
    [!!] Joins performed without indexes: 1
    [!!] Temporary tables created on disk: 30% (7 on disk / 23 total)
    [OK] Thread cache hit rate: 77% (5 created / 22 connections)
    [OK] Table cache hit rate: 66% (80 open / 120 opened)
    [OK] Open file limit used: 3% (158/4K)
    [OK] Table locks acquired immediately: 99% (812 immediate / 817 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        key_buffer_size (> 198.6M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)

  2. #2
    MySQL optimization is not as simple, as you may think.
    I would advise in the first place to see your slow_queries log (in MySQL log dir)
    and ensure you have indexes for your tables (you will probably see the table names in your log)
    CloudVPS
    Affordable VPS Servers - Middle East
    High performance | Green technologies

  3. #3
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    Quote Originally Posted by NovinServer View Post
    Hello
    I want virtual server Mysql Optimization ! Please Help me ...

    Code:
     >>  MySQLTuner 1.1.2 - Major Hayden <[email protected]>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 8957)
    [--] Data in MEMORY tables: 0B (Tables: 29)
    [!!] Total fragmented tables: 514
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9s (1K q [205.333 qps], 22 conn, TX: 42M, RX: 143K)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 56.0M global + 3.9M per thread (400 max threads)
    [OK] Maximum possible memory usage: 1.6G (53% of installed RAM)
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 1% (5/400)
    [!!] Key buffer size / total MyISAM indexes: 16.0M/198.6M
    [!!] Key buffer hit rate: 86.9% (9K cached / 1K reads)
    [OK] Query cache efficiency: 61.9% (1K cached / 1K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 3% (1 temp sorts / 33 sorts)
    [!!] Joins performed without indexes: 1
    [!!] Temporary tables created on disk: 30% (7 on disk / 23 total)
    [OK] Thread cache hit rate: 77% (5 created / 22 connections)
    [OK] Table cache hit rate: 66% (80 open / 120 opened)
    [OK] Open file limit used: 3% (158/4K)
    [OK] Table locks acquired immediately: 99% (812 immediate / 817 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        key_buffer_size (> 198.6M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
    First, back up your MySql databases. Then optimize all your database tables by running:
    Code:
    root [#] mysqlcheck -Aao --auto-repair
    If you have a password for root access to MySql, you have to add "-uroot -pPASSWORDHERE" to the command line, before the "-Aao".

    Then re-run ./mysqltuner.pl. If you get the same results, edit your my.cnf file to increase the settings as recommended. Don't go crazy adding a lot to the values; I usually increase the values by 8 or 16. Its better to creep up on them than risk data corruption (did I remind you to back up your databases first? Yeah, I did).

    You may find that your my.cnf file appears empty when you first edit it. You will add the entries if that is the case. I also note that you have innoDB enabled yet mysqltuner.pl is not listing any data in innoDB tables; you can save some memory by adding 'skip-innodb' to my.cnf as I have done below. This is optional, and you should make sure you are not using innoDB tables.

    If the results are the same when you re-run mysqltuner.pl, and you edit my.cnf (using "nano /etc/my.cnf" from the command line), the top portion should include these lines:

    Code:
    [mysqld]
    key_buffer_size = 208M
    join_buffer_size = 2M
    tmp_table_size = 48M
    max_heap_table_size = 48M
    skip-innodb
    Don't change any other lines, if they appear. After editing the file, and saving with CTRL-O and exiting with CTRL-X, restart MySql:

    Code:
    root [#] /etc/init.d/mysqld restart
    Then wait 48 hours before tweaking again.

  4. #4
    Join Date
    Jul 2010
    Location
    Singapore
    Posts
    775
    hi fshagan,

    looking at your config after using mysqltuner.pl, is it always a must to have the value of tmp_table_size and max_heap_table_size the same ?
    tmp_table_size = 48M
    max_heap_table_size = 48M
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  5. #5
    Join Date
    Mar 2011
    Location
    Graz, Austria
    Posts
    298
    you can save some memory by adding 'skip-innodb' to my.cnf as I have done below
    Only on OLDER (under 5.1.38) MySQL versions, newer require this to disable InnoDB:
    ignore_builtin_innodb

  6. #6
    Join Date
    Jul 2010
    Location
    Singapore
    Posts
    775
    Quote Originally Posted by EDIS View Post
    Only on OLDER (under 5.1.38) MySQL versions, newer require this to disable InnoDB:
    ignore_builtin_innodb
    Hi EDIS,

    do you set the same value for tmp_table_size and max_heap_table_size?
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  7. #7
    The MySQL optimization depends on your application. You can get a good performance by optimizing queries. I recommend you to check the slow queries.

  8. #8
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    Quote Originally Posted by Benny Kusman View Post
    hi fshagan,

    looking at your config after using mysqltuner.pl, is it always a must to have the value of tmp_table_size and max_heap_table_size the same ?
    tmp_table_size = 48M
    max_heap_table_size = 48M
    That's the general recommendation. I don't know why it is recommended, or what happens if you choose different sizes.

    I tend to be conservative with tweaking MySql as data corruption can result if its done wrong. So keeping the values the same is a rule I follow blindly. Unless someone comes up with a reason to make them different, that is.

  9. #9
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    Quote Originally Posted by EDIS View Post
    Only on OLDER (under 5.1.38) MySQL versions, newer require this to disable InnoDB:
    ignore_builtin_innodb
    Thanks, I see that "skip-innodb" is deprecated (although it still works, its always better to use the most current version).

  10. #10
    Join Date
    Jul 2010
    Location
    Singapore
    Posts
    775
    Quote Originally Posted by fshagan View Post
    That's the general recommendation. I don't know why it is recommended, or what happens if you choose different sizes.

    I tend to be conservative with tweaking MySql as data corruption can result if its done wrong. So keeping the values the same is a rule I follow blindly. Unless someone comes up with a reason to make them different, that is.
    hi,

    may i know where is the reference to put the value the same ?

    tq
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  11. #11
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    Quote Originally Posted by Benny Kusman View Post
    hi,

    may i know where is the reference to put the value the same ?

    tq
    mysqltuner.pl recommends it in the output of the analysis:

    When making adjustments, make tmp_table_size/max_heap_table_size equal
    If you want a more technical explanation, you can resort to Google searches where you'll find links like this one describing the technical differences between the two variables.

  12. #12
    Join Date
    Feb 2004
    Location
    Sacramento CA
    Posts
    3,342

  13. #13
    Join Date
    Jul 2010
    Location
    Singapore
    Posts
    775
    ok thanks fshagan.
    i tried setting to 42MB and hopes things goes well for 1.4MB ram
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  14. #14
    What about the query cache? Do many people use that?
    Adam McMaster

    Valcato Hosting Over nine years of great service
    Shared, VPS and dedicated servers.

  15. #15
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    Quote Originally Posted by Benny Kusman View Post
    ok thanks fshagan.
    i tried setting to 42MB and hopes things goes well for 1.4MB ram
    Be careful if you're not running something like mysqltuner.pl to evaluate the configuration. It is relatively easy to botch things up and end up with data corruption.

    To get mysqltuner.pl, use "wget mysqltuner.pl" from the shell, then "chmod 755 mysqltuner.pl". Run it with "./mysqltuner.pl" and look at the recommendations and warnings it gives.

    After adjusting values in /etc/my.cnf (or wherever your config file is), you have to restart the mysqld daemon ("/etc/init.d/mysqld restart") and run it for 24 - 48 hours before adjusting again.

  16. #16
    Join Date
    Jul 2010
    Location
    Singapore
    Posts
    775
    Quote Originally Posted by fshagan View Post
    Be careful if you're not running something like mysqltuner.pl to evaluate the configuration. It is relatively easy to botch things up and end up with data corruption.

    To get mysqltuner.pl, use "wget mysqltuner.pl" from the shell, then "chmod 755 mysqltuner.pl". Run it with "./mysqltuner.pl" and look at the recommendations and warnings it gives.

    After adjusting values in /etc/my.cnf (or wherever your config file is), you have to restart the mysqld daemon ("/etc/init.d/mysqld restart") and run it for 24 - 48 hours before adjusting again.
    hi fshagan,

    yeap, im using mysqltuner.pl

    after setting the tmp_table_size to 42mb, it still recommends me to increase > 42mb.
    shall i follow ?

    im confused what is the best number to put.
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

Similar Threads

  1. How to optimize MySQL?
    By SlAiD in forum Hosting Security and Technology
    Replies: 38
    Last Post: 04-18-2011, 12:57 AM
  2. Help me optimize mySQL
    By grahamrb in forum Hosting Security and Technology
    Replies: 6
    Last Post: 02-20-2008, 05:13 PM
  3. Optimize MySQL
    By micronz in forum Dedicated Server
    Replies: 3
    Last Post: 03-25-2005, 01:40 AM
  4. Optimize MySQL?
    By bdmtrfngr in forum Hosting Security and Technology
    Replies: 3
    Last Post: 01-08-2005, 10:21 PM

Posting Permissions

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