Apache/PHP/SQL LAMP optimization on small server

Discussion in 'Server Operation' started by dedibox, Jul 6, 2007.

  1. dedibox

    dedibox New Member

    I'm running Debian on a "dedibox" dedicated server with the following specs:
    CPU: VIA C7 2 GHz
    1024 Mb RAM DDR-2
    Hard disk Seagate 160Go SATA-2

    I'm the sole user of this server. I only have e-mail (5 accounts) and web services using ISPConfig 2.2.14 (just upgraded from an earlier version).

    The web site gets between 4000 and 8000 visitors per day. The web pages are fairly simple content with few images, generated by an excellent CMS called SPIP (www.spip.net) which feeds pages using a highly-efficient cache system.

    My problem is that the server load is high throughout the day, top shows 80-99% CPU usage and lots of swap file use.

    I have gotten mixed results by tweaking the my.cnf and apache2.conf files.

    PHPAccelerator was installed, and this helped a little. But the server is still under heavy load despite the modest number of visitors.

    How do I find the bottlenecks and fix them?

    Excerpt from /etc/apache2/apache2.conf (rest of file is standard ISPConfig/Debian)

    Code:
    ServerRoot "/etc/apache2"
    
    LockFile /var/lock/apache2/accept.lock
    
    PidFile /var/run/apache2.pid
    
    Timeout 15
    
    KeepAlive On
    
    MaxKeepAliveRequests 100
    
    KeepAliveTimeout 3
    
    <IfModule prefork.c>
    StartServers         2
    MinSpareServers      5
    MaxSpareServers     10
    MaxClients          150
    MaxRequestsPerChild  1
    </IfModule>
    
    <IfModule worker.c>
    StartServers         2
    MaxClients         150 
    MinSpareThreads     25
    MaxSpareThreads     75
    ThreadsPerChild     25
    MaxRequestsPerChild  1
    </IfModule>
    
    <IfModule perchild.c>
    NumServers           2
    StartThreads         5
    MinSpareThreads      5
    MaxSpareThreads     10
    MaxThreadsPerChild  20
    MaxRequestsPerChild  1
    AcceptMutex fcntl
    </IfModule>
    
    
    
    /etc/mysql/my.cnf

    Code:
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    user		= mysql
    pid-file	= /var/run/mysqld/mysqld.pid
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    language	= /usr/share/mysql/english
    skip-external-locking
    skip-innodb
    
    old_passwords	= 1
    
    max_allowed_packet	= 8M
    #thread_stack		= 1024K
    
    query_cache_limit	= 1048576
    query_cache_size        = 16777216
    query_cache_type = 1
    
    log-bin			= /var/log/mysql/mysql-bin.log
    max_binlog_size         = 104857600
    
    skip-bdb
    
    connect_timeout = 10
    interactive_timeout = 25
    join_buffer_size = 4M
    
    max_connections = 800
    max_user_connections = 800
    max_connect_errors = 999999
    
    myisam_sort_buffer_size = 32M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1024
    thread_cache_size = 286
    tmp_table_size = 32M
    wait_timeout = 1800
    long_query_time = 5
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M
    
    [mysql]
    
    [isamchk]
    key_buffer = 32M
    key_buffer_size = 32M
    
     
  2. Ben

    Ben ISPConfig Developer ISPConfig Developer

    More helpfull would be here, which processes cause the load you experience.
    Can you post an output of top here in times of highest load?

    Then you should activate to log slow queries to wether there are heavy queries.

    You could also take a look with phpMyAdmin about the mysql runtime information to also see where you have problems / optimisation potential in your application.

    If you do not need the binlog I would deactivate it and therefore do a db dump from time to time, cause also the binlog influences the performance.
     
  3. dedibox

    dedibox New Member

    Thanks for your reply! I'm a Debian newbie, so please indulge my questions.

    How do I output the highest load results from top into a text file?

    How do I activate slow queries log, and how do I identify heavy queries?

    This I can figure out how to do, thanks.

    What is binlog and how do I deactivate it?
     
  4. Ben

    Ben ISPConfig Developer ISPConfig Developer

    To output top, just open a ssh session with e.g. putty, runt top mark everything and paste it here ;)

    Regarding the binlog, that logs all transactions / changes to the tables, e.g. inserts etc. to replay changes in case of a tablecrash....
    Deactive it by commenting these lines out:
    To activate the slow query log, put the following lines in your my.cnf but as I saw, you already got the line long_query_time. The value here is the time that must be reached so that the query is saved to the logilfe.
    Then just take a look from time to time into that file.
     
  5. dedibox

    dedibox New Member

    top

    I turned off binlog and turned on the slow queries log. I'll report back asap on the contents of the slow queries log.

    Using /etc/tuning-primer.sh I get

     
    Last edited: Jul 6, 2007

Share This Page