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

my.cnf setting for 512 ram

Discussion in 'Server Configuration and Hosting' started by clove28, Apr 1, 2015.

  1. clove28

    clove28 Active Member

    Hi guys,

    I've read different configuration of my.cnf. But I really don't know is the best for 512 ram server. Please help me to set it up properly. I'm receiving error "Zend_Db_Adapter_Mysqli_Exception: User admin already has more than 'max_user_connections' active connections - library/Zend/Db/Adapter/Mysqli.php:333

    Here's my current my.cnf

    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    symbolic-links=0
    max_connections=500
    max_user_connections=30
    wait_timeout=600
    interactive_timeout=50
    long_query_time=5
    
    #GENERAL#
    performance_schema             = off
    
    # MyISAM #
    myisam-recover                 = FORCE,BACKUP
    
    #log-queries-not-using-indexes
    #log-slow-queries=/var/log/mysql/log-slow-queries.log
    
    key_buffer = 16M
    myisam_sort_buffer_size = 32M
    join_buffer_size=1M
    read_buffer_size=1M
    sort_buffer_size=2M
    table_cache=1024
    thread_cache_size=286
    interactive_timeout=25
    connect_timeout=5
    max_allowed_packet=64M
    max_connect_errors=1000
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    tmp_table_size=16M
    
    #innodb_use_native_aio = 0
    
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
     
  2. rainmotorsports

    rainmotorsports Well-Known Member

    Turning performance schema off was the best thing right there. It sucks a huge amount of ram, talked about my experiences with it here http://www.rainmotorsports.com/blog/29/configuring-mysql-5-6-for-low-memory-usage/

    The biggest issue I see is that 30 limit on max user connections. I really dont remember what my tests had me at, but I want to say under a large load I saw no more than 100 connections. Xenforo as an application accessing the database is 1 user, so limiting user connection to 30 is limiting xenforo, not workers or real users. I would up that.

    The innodb buffer pool is one of the more important settings for innodb performance, though default (not set) is probably fine. I wouldn't worry too much about guides that are tweaking for myisam as much as innodb. As I found most guides about lowering ram usage were not for innodb setups. But you can get a server to host a hundred or more concurrent users on 512mb of ram no problem.
     
    maszd and clove28 like this.
  3. clove28

    clove28 Active Member

    Hi Rain,

    Thank you for your reply. I also adjusted the max user connections to 100. I'd like to put innodb buffer pool but I don't know how. Do I just need to put it in there or set it up somewhere else?
     
  4. RoldanLT

    RoldanLT Well-Known Member

  5. clove28

    clove28 Active Member

  6. RoldanLT

    RoldanLT Well-Known Member

  7. clove28

    clove28 Active Member

    It failed to start when I followed this configuration from percona..

    Code:
    # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    
    
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/mysql.pid
    performance_schema             = off
    
    # MyISAM #
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    skip-name-resolve
    sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
    sysdate-is-now                 = 1
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log-bin                        = /var/lib/mysql/mysql-bin
    expire-logs-days               = 14
    sync-binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 32M
    max-heap-table-size            = 32M
    query-cache-type               = 0
    query-cache-size               = 0
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 4096
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 64M
    innodb-flush-log-at-trx-commit = 1
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 700M
    
    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    
     
  8. RoldanLT

    RoldanLT Well-Known Member

  9. clove28

    clove28 Active Member

  10. RoldanLT

    RoldanLT Well-Known Member

    Yeah, most likely that's the reason of the error.
    or you can post the whole mysql error log here.
     
  11. clove28

    clove28 Active Member

    I didn't change the value, but followed the link you provided. The restart is working now. Thanks a lot! One quick question, how will I know that the new my.cnf is working properly? :)
     

Share This Page