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
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.
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?
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.
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
