1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysql optimization

Discussion in 'Server Configuration and Hosting' started by Adam Howard, May 9, 2014.

  1. Adam Howard

    Adam Howard Well-Known Member

    When it comes to mysql I usually stick to the policy of; if it's not broken, why fix it?! But out of curiosity (or stupidity), I've decided to play around with it a bit. Could use some basic feedback. Server is dedicated, but nothing overly done.

    Intel Core2Duo E8300 (2 x 2.83GHz)
    4GB DDR2
    80GB SATA

    This is the default my.conf (currently in use)




    !includedir /etc/mysql/conf.d/
    This is the one I generated (thanks to Percona)


    # CLIENT #
    port  3306
    = /var/lib/mysql/mysql.sock


    # GENERAL #
    user  mysql
    default-storage-engine  InnoDB
    = /var/lib/mysql/mysql.sock
    -file  = /var/lib/mysql/mysql.pid

    # MyISAM #
    key-buffer-size  32M
    -recover  FORCE,BACKUP

    # SAFETY #
    max-allowed-packet  16M
    -connect-errors  1000000

    datadir  = /var/lib/mysql/

    log-bin  = /var/lib/mysql/mysql-bin
    -logs-days  14
    -binlog  1

    tmp-table-size  32M
    -heap-table-size  32M
    -cache-type  0
    -cache-size  0
    -connections  500
    -cache-size  50
    -files-limit  65535
    -definition-cache  4096
    -open-cache  4096

    # INNODB #
    innodb-flush-method  O_DIRECT
    -log-files-in-group  2
    -log-file-size  128M
    -flush-log-at-trx-commit 2
    -file-per-table  1
    -buffer-pool-size  2G

    # LOGGING #
    log-error  = /var/lib/mysql/mysql-error.log
    -queries-not-using-indexes  1
    -query-log  1
    -query-log-file  = /var/lib/mysql/mysql-slow.log
    Thoughts and feedback is always welcome :)
    Last edited: May 9, 2014
  2. Floren

    Floren Well-Known Member

    This is not the way to optimize MySQL. Posting a configuration with some values will not help anyone, you have to do it yourself. Did you looked at peak time what is going on for last few days? I'll give the method George (eva2000) uses: For 2 days, he logs everything on server and then he analyses the obtained data. Then he makes changes to MySQL config files and logs again everything for another 2 days. This process is repeated until everything is optimized.

    You will have to repeat this process every few months as MySQL is completely dynamic, what you set now as values will not be good anymore in 6 months.
    SneakyDave likes this.
  3. Adam Howard

    Adam Howard Well-Known Member

    Your post is not helpful or productive.

    It's almost bate`ish (trollish) in my opinion considering many people have done as I have done (search) and have been given some feedback on their configuration. And yet here you are replying, 'no help for you, you're on your own'.

    If you wish not to contribute.... Don't.
    Last edited: May 9, 2014
    Amaury likes this.
  4. Floren

    Floren Well-Known Member

    I'm trying to give you the proper guidelines how to optimize MySQL. If it would that easy to tune MySQL, Percona and other expensive consultants will not exist. Take it the way you like it.
    realaqu and SneakyDave like this.
  5. Sheratan

    Sheratan Well-Known Member

    Optimizing mysql is not a one day job. Like floren said, you had to see the log, siege it, analyses the performance and impact to you database, then see the log again and change again. Do it over and over again to find the best configuration.

    Last time, I need to spend a week just to find the right configuration for 512MB RAM VPS

    But for some basic optimization, my don't you show us your mysqltuner result?
    Adam Howard and SneakyDave like this.
  6. Adam Howard

    Adam Howard Well-Known Member

    @Sheratan Thank you for your reply and for offering to help and providing some information I can actually use and apply. :)

    Also thank you as well as reminding me about mysqltuner, it's been a while (more than a year) since I've used that script (totally forgot it existed) or attempted to do more depth tweaking of mySQL.

    Thank you :)

    This is what I got from that script
    >>  MySQLTuner 1.3.0 Major Hayden <major@mhtx.net>
    Bug reportsfeature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OKLogged in using credentials from debian maintenance account.
    OKCurrently running supported MySQL version 5.6.17-1~dotdeb.1
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Data in MyISAM tables152M (Tables6)
    Data in InnoDB tables824M (Tables378)
    Data in PERFORMANCE_SCHEMA tables0B (Tables52)
    Data in MEMORY tables496K (Tables9)
    Total fragmented tables44

    -------- Security Recommendations  -------------------------------------------
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    Up for: 18h 24m 48s (470K q [7.093 qps], 18K connTX20BRX338M)
    Reads Writes86% / 14%
    Total buffers169.0M global + 1.1M per thread (200 max threads)
    OKMaximum possible memory usage394.0M (10of installed RAM)
    OKSlow queries0% (14/470K)
    OKHighest usage of available connections3% (7/200)
    OKKey buffer size total MyISAM indexes8.0M/113.0M
    [OKKey buffer hit rate98.5% (172K cached 2K reads)
    Query cache is disabled
    [OKSorts requiring temporary tables0% (4 temp sorts 26K sorts)
    Temporary tables created on disk35% (4K on disk 12K total)
    OKThread cache hit rate99% (7 created 18K connections)
    Table cache hit rate13% (405 open 3K opened)
    OKOpen file limit used1% (11/1K)
    OKTable locks acquired immediately99% (809K immediate 809K locks)
    InnoDB  buffer pool data size128.0M/824.2M
    [OKInnoDB log waits0
    -------- Recommendations -----------------------------------------------------
    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
      Increase table_cache gradually to avoid file descriptor limits
      Read this before increasing table_cache over 64
    Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 407)
    innodb_buffer_pool_size (>= 824M)
    Thank you again in advance :)

    Attached Files:

    • 1.png
      File size:
      141.9 KB
    • 2.png
      File size:
      23.5 KB
    Last edited: May 9, 2014
  7. Sheratan

    Sheratan Well-Known Member

    How much your free memory and swap usage in peak time?
  8. Adam Howard

    Adam Howard Well-Known Member

    Naturally, I would much prefer to avoid using any swap. ;) This is one of the reasons that make me think maybe I should do some tweaking. :)

    My new dedicated server has been online for only a day, but since I am starting to hit the swap; I figured I needed to do something before reaching a peek usage time. Current swap used is 12m (virtually nothing, but doubt it will stay nothing for long)

  9. Sheratan

    Sheratan Well-Known Member

    So it's a brand new server, 18h uptime, and no information about peak usage.

    I see you are using 2GB for InnoDB buffer and 500 max concurrent, yet mysqltuner result said you are using 128M and 200 max concurrent. Do you use that percona generated conf file?
    SneakyDave likes this.
  10. Floren

    Floren Well-Known Member

    @Adam Howard, that does not mean necessarily MySQL is the source of your swapping.
    Use the normal top command and sort the results by swap usage. Or use smem for a detailed output.
    While viewing top, Shift + O > press P > press Enter > press C to show the executed commands.
    SneakyDave likes this.
  11. Adam Howard

    Adam Howard Well-Known Member

    No, I have not yet applied the percona generated config. I generated it at the time of this posting (only a short while ago) and was waiting for late at night to apply it.... Just in case something did go wrong and fewer people would notice any downtime (it's 11:40 pm now, so I'll likely being doing so).

    I was generally hoping for just a little feedback between what I'm using now vs what percona provided and maybe get an understanding of the values that they seem to be missing between them.
  12. Adam Howard

    Adam Howard Well-Known Member

    With respects, your command do not work.

    Help for Interactive Commands procps-ng version 3.3.3
    Window 1
    :DefCumulative mode Off.  SystemDelay 3.0 secsSecure mode Off.

    Z,B  Global: 'Z' change color mappings'B' disable/enable bold
    ,t,m  Toggle Summaries'l' load avg't' task/cpu stats'm' mem info
    ,I  Toggle SMP view'1' single/separate states'I' Irix/Solaris mode
    ,F  Manage Fieldsadd/removechange orderselect sort field

    ,&,<,> . Locate'L'/'&' find/againMove sort column'<'/'>' left/right
    ,H,V  Toggle'R' norm/rev sort'H' show threads'V' forest view
    ,i,S  Toggle'c' cmd name/line'i' idle tasks'S' cumulative time
    ,y  Toggle highlights'x' sort field'y' running tasks
    ,b  Toggle'z' color/mono'b' bold/reverse (only if 'x' or 'y')
    u,U  Show'u' effective user'U' realsavedfile or effective user
    or #  . Set maximum tasks displayed
    C,...  . Toggle scroll coordinates msg for: up,down,left,right,home,end

    ,r  Manipulate tasks'k' kill'r' renice
    or s  Set update interval
      W  Write configuration file
      q  Quit
    commands shown with '.' require a visible task display window )
    Press 'h' or '?' for help with Windows,
    any other key to continue

    Attached Files:

  13. Floren

    Floren Well-Known Member

  14. Sheratan

    Sheratan Well-Known Member

    This is a quick suggestion: Try to use that generated configuration, but before that, change:

    max-connections from 500 to 200 (500 max concurrent with 2GB buffer pool is "something" :D in this case - no information about peak)

    Watch out with mysql pid and socket.

    Restart your mysql, and then log your ram usage, swap usage, mysql connection (it's easy check that in phpMyAdmin > status > max. concurrent connections) and cpu load during peak time for three days (and no mysql restart), better to manually log that every 6 hours.

    After that, run mysqltuner again and post the result and the log in here. (That means go back to this thread 3 days from now :p)
    Adam Howard likes this.
  15. Adam Howard

    Adam Howard Well-Known Member

    I had just applied the one generated, but am using the 500 connections... Did it before seeing this post and was doing my update based upon your previous inquire. So 500 is to much I assume, even with 2GB?
  16. Sheratan

    Sheratan Well-Known Member

    Can't say. Depends of your peak usage, how big is you database, how many database you had, how many RAM will your PHP use, etc etc.

    But if you are in doubt, you can use 300 connection.
    Adam Howard likes this.
  17. Tracy Perry

    Tracy Perry Well-Known Member

    Actually @Floren is 100% correct. What may be great today may not be great in 2 weeks. The demand on mysql will change dependent upon many factors and to keep it optimized is a non-stop process.
    SneakyDave likes this.
  18. Tracy Perry

    Tracy Perry Well-Known Member

    F to pull up the menu, scroll down to swap and press lowercase s. The rest should be the same (using C for executed commands).
    Fields Management for window 1:Def, whose current sort field is SWAP
       Navigate with Up/Dn, Right selects for move then <Enter> or Left commits,
       'd' or <Space> toggles display, 's' sets sort.  Use 'q' or <Esc> to end!
    * PID     = Process Id       
    * USER    = Effective User Name
    * PR      = Priority         
    * NI      = Nice Value       
    * VIRT    = Virtual Image (KiB)
    * RES     = Resident Size (KiB)
    * SHR     = Shared Memory (KiB)
    * S       = Process Status   
    * %CPU    = CPU Usage        
    * %MEM    = Memory Usage (RES)
    * TIME+   = CPU Time, hundredths
    * COMMAND = Command Name/Line
    * PPID    = Parent Process pid
      UID     = Effective User Id
    * RUID    = Real User Id     
    * RUSER   = Real User Name   
      SUID    = Saved User Id    
      SUSER   = Saved User Name  
      GID     = Group Id         
      GROUP   = Group Name       
      PGRP    = Process Group Id 
      TTY     = Controlling Tty  
      TPGID   = Tty Process Grp Id
      SID     = Session Id       
      nTH     = Number of Threads
      P       = Last Used Cpu (SMP)
      TIME    = CPU Time         
    * SWAP    = Swapped Size (KiB)
      CODE    = Code Size (KiB)  
      DATA    = Data+Stack (KiB) 
      nMaj    = Major Page Faults
      nMin    = Minor Page Faults
      nDRT    = Dirty Pages Count
      WCHAN   = Sleeping in Function
      Flags   = Task Flags <sched.h>
      CGROUPS = Control Groups   
      SUPGIDS = Supp Groups IDs  
      SUPGRPS = Supp Groups Names
      TGID    = Thread Group Id  
    Last edited: May 9, 2014
  19. Adam Howard

    Adam Howard Well-Known Member

    Which is great if you know what you're looking for or what to do, which at the moment.. I do not. As stated in the 1st post, I usually leave such things 'as is' and do not bother to tweak anything.

    And whole point of this thread was to ask for some advise and maybe guidance and someone telling me, 'no help for you, you're on your own' ... Is neither helpful or productive (it's actually quite condescending).

    So right now, I am very thankful and greatful that @Sheratan came into this thread and he's earned not only my thanks, but also my respect. :)

    Thank you very much, Sheratan for your common curtsy, feedback, advise, and general willingness to help another member here within XenForo.(y)
  20. Tracy Perry

    Tracy Perry Well-Known Member

    That will work as a baseline to start with... but you still will want to tweak it some over time - but for a fully optimized configuration it's ongoing. I honestly don't worry about tweaking mine. I get it where it works well and let it go from there. I'll check it every month or so and see if there might be something I need to tweak but there hasn't been so far.
    MattW likes this.

Share This Page