Clayton's Tech Bits

Home

Contact

Resumé / C.V.

Links

Search this site:
Custom Search

Categories:

/ (224)
  Admin/ (86)
    Apache/ (7)
      HTTPS-SSL/ (4)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (3)
    Monitoring/ (2)
      munin/ (2)
    OpenVPN/ (1)
    SSH-Proxy/ (3)
    SSH-SSL/ (6)
    backups/ (16)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (1)
    commandLine/ (11)
    crontab/ (1)
    databases/ (8)
      MSSQL/ (2)
      MySQL/ (5)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (9)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (6)
    iptables/ (2)
    virtualization/ (8)
      VMware/ (1)
      virtualBox/ (7)
  Coding/ (11)
    bash/ (1)
    gdb/ (1)
    git/ (2)
    php/ (4)
    python/ (3)
      Django/ (1)
  Education/ (1)
  Hosting/ (23)
    Amazon/ (14)
      EBS/ (3)
      EC2/ (11)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (20)
    Awesome/ (3)
    CPUfreq/ (1)
    Chinese/ (1)
    Debian/ (5)
      WPA/ (1)
    audio/ (1)
    encryption/ (2)
    fonts/ (1)
    misc/ (4)
    router-bridge/ (2)
  SW/ (39)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (25)
      Drupal/ (8)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (6)
      WebERP/ (2)
      eGroupware/ (1)
    email/ (1)
    fileSharing/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (12)
    IMchat/ (1)
    circumvention/ (2)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (1)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (13)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Archives:

  • 2012/03
  • 2012/01
  • 2011/12
  • 2011/11
  • 2011/10
  • 2011/09
  • 2011/08
  • 2011/07
  • 2011/06
  • 2011/05
  • 2011/04
  • 2011/02
  • 2010/12
  • 2010/11
  • 2010/10
  • 2010/09
  • 2010/08
  • 2010/07
  • 2010/06
  • 2010/05
  • 2010/04
  • 2010/03
  • 2010/02
  • 2010/01
  • 2009/12
  • 2009/11
  • 2009/10
  • 2009/09
  • 2009/08
  • 2009/07
  • 2009/06
  • 2009/05
  • 2009/04
  • 2009/03
  • 2009/02
  • 2009/01
  • 2008/12
  • 2008/11
  • 2008/10
  • 2008/09

    • Subscribe XML RSS Feed

    Sun, 12 Dec 2010

    /Admin/databases/MySQL: MySQL Performance Tuning

    ***Update: with the current state of my Apache and MySQL tuning efforts, the graphs of my server's memory usage have gone from a state of consistently zero free memory and wild swings in swap usage and swapping, to a fairly consistent state of some free memory, with the occasional spike that leads to zero free memory and a LITTLE BIT of swap usage and swapping. (This with 256M of memory.) Things seem much more stable, but only time will tell if I have completely solved the problem of the machine sometimes swapping itself to death.

    This is a continuation of my related post on Apache performance tuning.

    I found a very informative little tool call "mysqltuner" in the Debian archives. Upon running, it gives a nice concise breakdown of what is going on with my MySQL server performance-wise, followed by some suggestions for how to increase performance.

    I found out, for instance, that the default values for interactive_timeout and wait_timeout were set to (what seems like an outrageous) value of 8 hours. I cranked them back to two minutes and mysqltuner stopped complaining.

    mysqltuner also tells me "Total buffers: 58.0M global + 2.7M per thread" which implies that cutting back on max_connections (number of threads) will have quite a limited impact. But my memory is really tight, so let's do that anyway. I went with 10 for max_connections: 5 Apache servers plus padding for phpmyadmin connections, among other things. (mysqltuner will warn me in the future if I am bumping up against this limit.)

    mysqltuner also suggests increasing some memory constraints on certain MySQL functions, but I am going to hold off on that until I see what happens with the current setup for a couple of days.

    [1] also talks about turning off the InnoDB engine with "skip-innodb" in order to save a pile of memory, but one of my databases is using InnoDB tables, and when I try to convert them to myisam it refuses while complaining about "foreign keys". I believe that database is using an InnoDB feature where, if I delete a row in one table dependent rows in other tables are also automatically deleted, so I guess I will live with it for the moment.

    [1] http://www.360doc.com/content/07/1210/14/15540_883954.shtml

    posted at: 21:02 | path: /Admin/databases/MySQL | permanent link to this entry