Results 1 to 1 of 1
  1. #1

    Giving MT's Grid a try, 128mb GridContainer, optimizing my.cnf

    Alright, so I've decided to give MT a try and am still worried about the way they handle MySQL and it not being "enough" even for a moderately viewed WP site. Now, end-game there will be ~7 websites on this Grid, with 6 of the being small local businesses with MAYBE 10 viewers per month.

    Also, all tables will be innodb, so the only myisam tables will be the informational mysql tables.

    I understand mysqltuner doesn't give reliable results within seconds of restarting mysql, but last night within 5 minutes of restarting on a site that literally no-one but cralwers know about I was getting:

    [!!] Connections aborted: 7%
    Your applications are not closing MySQL connections properly
    I was extremely confused by what exactly that meant, but I figured something had to be wrong with the existing my.cnf and decided to go poking into it and trying to optimize it. Here is what I've came up with:

    mysqltuner - yes I know it hasn't been running for 24 hours

    [--] Assuming 128 MB of physical memory
    [!!] Assuming 0 MB of swap space (use --forceswap to specify)
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.26-rc-5.1.26rc-log
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in InnoDB tables: 9M (Tables: 118)
    [!!] Total fragmented tables: 3
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user 'USER'@'DBIP' for table 'user'
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 14h 26m 51s (32K q [0.631 qps], 786 conn, TX: 294M, RX: 3M)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 50.0M global + 4.6M per thread (120 max threads)
    [!!] Maximum possible memory usage: 605.0M (472% of installed RAM)
    [OK] Slow queries: 0% (0/32K)
    [OK] Highest usage of available connections: 13% (16/120)
    [!!] Cannot calculate MyISAM index size - re-run script as root user
    [OK] Query cache efficiency: 65.9% (11K cached / 17K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 189 sorts)
    [!!] Temporary tables created on disk: 38% (2K on disk / 6K total)
    [OK] Thread cache hit rate: 92% (57 created / 786 connections)
    [OK] Table cache hit rate: 95% (156 open / 163 opened)
    [OK] Open file limit used: 0% (54/8K)
    [OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)
    [!!] InnoDB data size / buffer pool: 9.1M/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        tmp_table_size (> 4M)
        max_heap_table_size (> 4M)
        innodb_buffer_pool_size (>= 9M)
    and my.cnf

    # Base Container
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    #read this speeds up first-connects
    init_connect='SET collation_connection = utf8_general_ci'
    init_connect='SET NAMES utf8'
    character-set-server = utf8 
    collation-server = utf8_general_ci
    #end first connects optimization
    default_storage_engine         = InnoDB
    max_heap_table_size = 8M
    open_files_limit = 8000
    #Custom Variables
    innodb_thread_concurrency      = 0
    low_priority_updates           = 1
    innodb_flush_method            = O_DIRECT
    # * Basic Settings
    user		= mysql
    pid-file	= /var/run/mysqld/
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    language	= /usr/share/mysql/english
    bind-address =
    # For compatibility 
    old_passwords	= 1
    max_connections=120 #changed from 60 to 120
    # due to mysql internally considering connections from [email protected]' as a separate user from [email protected]',  max_user_connections is largely irrelevant in a clustered situation
    wait_timeout=60 #change from 60 to 30
    max_allowed_packet	= 16M
    thread_stack		= 128K
    # query cache
    query_cache_limit	= 1048576
    query_cache_size        = 12M
    query_cache_type        = 1
    # slow query log
    log-slow-queries	= /var/log/mysql/mysql-slow.log
    long_query_time         = 1
    max_allowed_packet	= 16M
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    So that's where we stand and while I know they don't handle shared hosting like traditional shared hosting, does anyone have any rough numbers on just how many concurrent visitors a site can get with a 128mb mysql container?
    Last edited by kpedana; 10-22-2013 at 01:40 PM.

Similar Threads

  1. optimizing my.cnf?
    By Maikon in forum Managed Hosting and Services
    Replies: 5
    Last Post: 02-04-2011, 12:38 AM
  2. [HELP] Optimizing my.cnf
    By kyokorin in forum Hosting Security and Technology
    Replies: 1
    Last Post: 10-26-2010, 01:20 PM
  3. need help optimizing my.cnf file
    By Cyber404 in forum VPS Hosting
    Replies: 4
    Last Post: 06-05-2010, 01:24 AM
  4. Optimizing my.cnf for InnoDB on VPS?
    By Badmovies in forum VPS Hosting
    Replies: 0
    Last Post: 12-01-2006, 11:45 PM
  5. Optimizing my-huge.cnf
    By Jakiao in forum Hosting Security and Technology
    Replies: 1
    Last Post: 03-27-2005, 09:45 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