Results 1 to 19 of 19
  1. #1
    Join Date
    Nov 2010
    Location
    /
    Posts
    228

    Tweaking MySQL (my,cnf)

    Hello,

    I got a server Procesor Intel i7, 8GB RAM, 1.5 TB HDD, RAID 1..

    And i need to tweak MySQL for a better performance for the server !!

    How can i do it ? any help ?

    Thankyou.
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  2. #2
    Join Date
    May 2009
    Location
    Midworld
    Posts
    1,814
    What issues are you facing?

  3. #3
    Join Date
    Jan 2011
    Posts
    451
    Hello,

    Are you using cPanel? I would really suggest you to go through the below given cPanel video on mysql optimization to get a better understanding on the topic.

    URL : http://www.cpanel.net/videos/mysql-optimization/
    " Your work is to discover your work and then with all your heart to give yourself to it. "

    That's the mark of a true professional !

  4. #4
    Join Date
    Jul 2006
    Location
    Australia
    Posts
    3,059
    Do a search here at WHT for mysqltuner it should help.
    cPanel, CloudLinux, Softaculous ℵ Off Site Backups, Redundant DNS

  5. #5
    sady,

    MySQL configurations are unique per machine based on the workload expected and TYPE of workload.

    Do you make heavy use of InnoDB?
    What about MyISAM tables?

    The MySQL Tuner script will -not- give you an optimal configuration.

    If you don't mind posting some general info, I can give you a better configuration (I'm a MySQL DBA by profession).

    Info needed:
    Are most of your tables InnoDB or MyISAM?
    What is the total size of each?
    Is this a dedicated MySQL Server or is this serving other purposes?
    Is this for a Cart/Store? Blogs? Random websites?

  6. #6
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Hi Geoff,

    Look from mysqltuner i got this "[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    "

    So only InnoDB is active for now...

    Each of what ? where i can find this ?

    Its a Random Websites Wordpress, Joomla, DLE, Forums, etc..

    I am working with mysqltuner to fix this.. and the loads of slq sometimes exeed the normal loads..

    Thankyou for your help
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  7. #7
    Quote Originally Posted by sady92 View Post
    Hi Geoff,

    Look from mysqltuner i got this "[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    "

    So only InnoDB is active for now...

    Each of what ? where i can find this ?

    Its a Random Websites Wordpress, Joomla, DLE, Forums, etc..

    I am working with mysqltuner to fix this.. and the loads of slq sometimes exeed the normal loads..

    Thankyou for your help
    Sady,

    Most of those apps default to MyISAM.

    Do you know offhand what version of MySQL you're running?


    Here's a query you can run to find out data sizes:

    SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
    CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES;


    Without knowing your sizes, here's a rough config:


    under the [mysqld] tag, add/alter:
    Code:
    open_files_limit=2048
    table_cache=2048
    net_buffer_length=8k
    thread_cache=100
    tmp_table_size=128M
    key_buffer_size=64M
    sort_buffer_size=512K
    net_buffer_length=8K
    read_buffer_size=256K
    read_rnd_buffer_size=512K
    myisam_sort_buffer_size=8M
    
    query_cache_type=1
    query_cache_limit=2M
    query_cache_size=64M
    
    innodb_buffer_pool_size=1G
    innodb_additional_mem_pool_size=32M
    innodb_flush_method=O_DIRECT
    transaction_isolation=READ-COMMITTED

  8. #8
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Quote Originally Posted by Geoff Winans View Post
    Sady,

    Most of those apps default to MyISAM.

    Do you know offhand what version of MySQL you're running?


    Here's a query you can run to find out data sizes:

    SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
    CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES;


    Without knowing your sizes, here's a rough config:


    under the [mysqld] tag, add/alter:
    Code:
    open_files_limit=2048
    table_cache=2048
    net_buffer_length=8k
    thread_cache=100
    tmp_table_size=128M
    key_buffer_size=64M
    sort_buffer_size=512K
    net_buffer_length=8K
    read_buffer_size=256K
    read_rnd_buffer_size=512K
    myisam_sort_buffer_size=8M
    
    query_cache_type=1
    query_cache_limit=2M
    query_cache_size=64M
    
    innodb_buffer_pool_size=1G
    innodb_additional_mem_pool_size=32M
    innodb_flush_method=O_DIRECT
    transaction_isolation=READ-COMMITTED
    As about the Version, i know only that is 5.1 i tried to get to know the real version but nothing

    i think you want this

    Showing rows 0 - 29 (1,083 total, Query took 0.1362 sec)
    And some kind wired on some querys like..

    Code:
    crunch_live	live_alerts	17592186044411.03 MB
    this is from LiveZilla :O

    Also this is my "my.cnf" what to modify ?

    Code:
    [mysqld]
    set-variable = max_connections=500
    slow_query_log
    local-infile=0
    
    table_cache=64
    query_cache_type=1
    query_cache_size=48M
    query_cache_limit=1M
    tmp_table_size=16M
    max_heap_table_size=16K
    thread_cache_size=4
    key_buffer_size=48M
    Thankyou i really aprecatie it
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  9. #9
    sady,

    try the configuration I posted above.

    It may work quite well.

    MySQL is quite a beast to tame. To really get maximum performance, you need a fast machine with fast disks.

  10. #10
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Should i overwrite or just update the old ones with the new ones ...

    Also i have i7, 8GB ram, SATA HDD Raid 1 ... i think this is good right ?
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  11. #11
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Just did what you suggested to

    From mysqltuner i got this..

    [!!] Temporary tables created on disk: 40% (112 on disk / 280 total)
    and this

    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 16M)
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  12. #12
    Single-drive machines are not optimal for a shared-mysql environment.

    Optimal setup is a 12 Disk RAID10 w/ 32-64G of memory and the fastest multicore processor(s) you can get your hands on.

    A single-drive server is going to get IO bound very, very fast.

    Update the configuration - do not overwrite.

  13. #13
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Quote Originally Posted by Geoff Winans View Post
    Single-drive machines are not optimal for a shared-mysql environment.

    Optimal setup is a 12 Disk RAID10 w/ 32-64G of memory and the fastest multicore processor(s) you can get your hands on.

    A single-drive server is going to get IO bound very, very fast.

    Update the configuration - do not overwrite.
    Yeah, but thos kind of servers cost a loot more than this and the only thing i can do now is optimizing it ..

    Thankyou so much i hope this works
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  14. #14
    Hope it helps.

    If you're still seeing issues, let me know. there is more that can be done, but that involves bumping you over to a Percona build of MySQL and is not a task to be taken lightly.

  15. #15
    Join Date
    Nov 2010
    Location
    /
    Posts
    228
    Thankyou ...

    Hmm yeah but for now i think i am good thnx to you ..
    Dot.AL - .AL Domain Name Registration
    .AL - Register your .AL Domains at. Get a Premium Domain NOW.
    Shard Hosting - cPanel - Backup - 24/7 Monitoring Over 200 Ready Scripts
    Awesome Promotions for you, check our announcements page.

  16. #16
    Join Date
    Jun 2011
    Posts
    379
    Mysqltuner is the best idea to start with mysql optimization because It's will find the way to optimize base on your server usage

  17. #17
    Join Date
    Sep 2008
    Location
    Sweden
    Posts
    1,283
    after i run mysqltuner this warning show:

    -------- 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
    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 (> 81.0M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)


    what shuld i do now?

  18. #18
    Quote Originally Posted by mixmox View Post
    after i run mysqltuner this warning show:

    -------- 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
    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 (> 81.0M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)


    what shuld i do now?
    - Run optimize table manually
    - Re-run the test after 24H
    - Enable slow query log - this is really easy to do in the config, and set it to something like 5 seconds. Then just look at the queries taking the most time to find out what might be lagging. If that is too much, lower it and keep repeating this process.
    - Adjust tmp_table_size as suggested
    - Add LIMITs to as many of your queires as you can
    - Adjust the last 3 variables as suggested

    Still it is best to wait 24H before relying on the data from the test because it could be skewed.

  19. #19
    Join Date
    Sep 2008
    Location
    Sweden
    Posts
    1,283
    mm. this is my log after 48 hours i run this tool again:


    >> MySQLTuner 1.2.0 - 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: 431M (Tables: 5326)
    [--] Data in InnoDB tables: 160K (Tables: 10)
    [--] Data in MEMORY tables: 6M (Tables: 19)
    [!!] Total fragmented tables: 218

    -------- Security Recommendations -------------------------------------------
    [!!] User [email protected]' has no password set.

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 16h 40m 9s (5M q [22.137 qps], 104K conn, TX: 49B, RX: 832M)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 58.0M global + 4.5M per thread (100 max threads)
    [OK] Maximum possible memory usage: 508.0M (34% of installed RAM)
    [OK] Slow queries: 0% (14/5M)
    [OK] Highest usage of available connections: 14% (14/100)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/82.7M
    [OK] Key buffer hit rate: 99.5% (22M cached / 115K reads)
    [OK] Query cache efficiency: 70.2% (2M cached / 4M selects)
    [!!] Query cache prunes per day: 94783
    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 127K sorts)
    [!!] Temporary tables created on disk: 36% (158K on disk / 439K total)
    [OK] Thread cache hit rate: 99% (14 created / 104K connections)
    [!!] Table cache hit rate: 0% (1K open / 141K opened)
    [OK] Open file limit used: 24% (2K/8K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 160.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 1024)

Similar Threads

  1. Help Tweaking MySQL
    By SuperHosterz in forum Hosting Security and Technology
    Replies: 7
    Last Post: 01-14-2008, 02:51 PM
  2. Tweaking mysql for plesk
    By Tomcatf14 in forum Hosting Software and Control Panels
    Replies: 0
    Last Post: 06-14-2006, 07:02 AM
  3. mysql/php tweaking
    By Lem0nHead in forum Hosting Security and Technology
    Replies: 6
    Last Post: 02-24-2005, 03:48 PM
  4. Tweaking my.cnf
    By darkpunk99 in forum Programming Discussion
    Replies: 1
    Last Post: 01-25-2004, 06:00 PM
  5. Tweaking configs (my.cnf)
    By UH-Matt in forum Hosting Security and Technology
    Replies: 5
    Last Post: 03-25-2003, 05:09 AM

Posting Permissions

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