Optimization MySQL for Server

Discussion in 'Server Operation' started by longvnit, Nov 6, 2009.

  1. longvnit

    longvnit New Member

    hi.
    My server have CPU dual core 1.6 Ghz and 1GB RAM
    I installed only Centos 5.3 And MySQL 5, Apache & PHP latest version.
    I run only 1 website which have ~ 2000 visitor / day.

    When I start your server i used command: free -m , i saw your server used < 200 MB RAM , but after server uptime in 3 days , i saw your server used > 800 MB RAM

    I think I have a problem with MySQL ,

    This is my my.conf

    PHP:
     [mysqld]
        
    datadir=/var/lib/mysql
        socket
    =/var/lib/mysql/mysql.sock
        skip
    -locking
        
    #skip-networking
        
    safe-show-database
        query_cache_limit
    =1M
        query_cache_size
    =32M ## 32MB for every 1GB of RAM
        
    query_cache_type=1
        max_user_connections
    =200
        max_connections
    =500
        interactive_timeout
    =10
        wait_timeout
    =20
        connect_timeout
    =20
        thread_cache_size
    =128
        key_buffer
    =64M ## 64MB for every 1GB of RAM
        
    join_buffer=1M
        max_connect_errors
    =20
        max_allowed_packet
    =16M
        table_cache
    =1024
        record_buffer
    =1M
        sort_buffer_size
    =1M ## 1MB for every 1GB of RAM
        
    read_buffer_size=1M ## 1MB for every 1GB of RAM
        
    read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
        
    thread_concurrency=## Number of CPUs x 2
        
    myisam_sort_buffer_size=64M
        server
    -id=1
        log_slow_queries
    =/var/log/mysql-slow-queries.log
        long_query_time
    =2
        collation
    -server=latin1_general_ci
        old
    -passwords

        
    [mysql.server]
        
    user=mysql
        basedir
    =/var/lib

        
    [safe_mysqld]
        
    err-log=/var/log/mysqld.log
        pid
    -file=/var/lib/mysql/mysql.pid
        open_files_limit
    =8192

        
    [mysqldump]
        
    quick
        max_allowed_packet
    =16M

        
    [mysql]
        
    no-auto-rehash
        
    #safe-updates

        
    [isamchk]
        
    key_buffer=32M
        sort_buffer
    =32M
        read_buffer
    =16M
        write_buffer
    =16M

        
    [myisamchk]
        
    key_buffer=32M
        sort_buffer
    =32M
        read_buffer
    =16M
        write_buffer
    =16M
        
    [mysqlhotcopy]
        
    interactive-timeout
    #top -c
    PHP:
    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     1960 mysql     16   0  226m  37m 4344 S  0.0  2.5   0
    :05.59 /usr/libexec/mysqld
    Please help me ! Thanks
     
    Last edited: Nov 6, 2009
  2. Ben

    Ben New Member Moderator HowtoForge Supporter ISPConfig Developer

    Well in my opinion a really cool tool that helps you with optimizing mysql is the mysql tuning primter script: http://www.day32.com/MySQL/ (first entry on the page).
    It indicates which switches have a non optimal value and why. But it gives you at least (if not trusting the suggested values) a hint at which config switches you should take a look at, and read what the mysql doc (dev.mysql.com) says.

    hth
     

Share This Page