Hi All Happy new 2008!!! On 1 of my servers i have a phpBB2 forum with many users. Server hardware: AMD Opteron 148 2,2 Ghz, 2 GB RAM, 2x160 GB sata HDDs in RAID1 (swap partition is 512 MB also working in RAID1) Server software: debian etch 32bit, ispconfig 2.2.18, php4+suphp my.conf: 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 key_buffer = 128M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 table_cache = 1024 query_cache_limit = 1M query_cache_size = 16M long_query_time = 2 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M join_buffer_size = 1M open_files_limit = 3196 tmp_table_size and = 128M max_heap_table_size = 64M read_buffer_size = 1M low_priority_updates = 1 !includedir /etc/mysql/conf.d/ httpd -V: Code: Server version: Apache/2.2.3 Server built: Sep 19 2007 19:49:48 Server's Module Magic Number: 20051115:3 Server loaded: APR 1.2.7, APR-Util 1.2.7 Compiled using: APR 1.2.7, APR-Util 1.2.7 Architecture: 32-bit Server MPM: Prefork threaded: no forked: yes (variable process count) Server compiled with.... -D APACHE_MPM_DIR="server/mpm/prefork" -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D DYNAMIC_MODULE_LIMIT=128 -D HTTPD_ROOT="" -D SUEXEC_BIN="/usr/lib/apache2/suexec" -D DEFAULT_PIDLOG="/var/run/apache2.pid" -D DEFAULT_SCOREBOARD="logs/apache_runtime_status" -D DEFAULT_LOCKFILE="/var/run/apache2/accept.lock" -D DEFAULT_ERRORLOG="logs/error_log" -D AP_TYPES_CONFIG_FILE="/etc/apache2/mime.types" -D SERVER_CONFIG_FILE="/etc/apache2/apache2.conf"
apache2.conf: Code: ServerRoot "/etc/apache2" LockFile /var/lock/apache2/accept.lock PidFile /var/run/apache2.pid Timeout 300 KeepAlive On MaxKeepAliveRequests 100 KeepAliveTimeout 3 <IfModule mpm_prefork_module> StartServers 10 MinSpareServers 10 MaxSpareServers 20 MaxClients 200 MaxRequestsPerChild 4000 </IfModule> <IfModule mpm_worker_module> StartServers 2 MaxClients 150 MinSpareThreads 25 MaxSpareThreads 75 ThreadsPerChild 25 MaxRequestsPerChild 0 </IfModule> User www-data Group www-data AccessFileName .htaccess <Files ~ "^\.ht"> Order allow,deny Deny from all </Files> TypesConfig /etc/mime.types DefaultType text/plain HostnameLookups Off ErrorLog /var/log/apache2/error.log LogLevel warn Include /etc/apache2/mods-enabled/*.load Include /etc/apache2/mods-enabled/*.conf Include /etc/apache2/httpd.conf Include /etc/apache2/ports.conf Include /etc/apache2/conf.d/ LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined LogFormat "%h %l %u %t \"%r\" %>s %b" common LogFormat "%{Referer}i -> %U" referer LogFormat "%{User-agent}i" agent ServerTokens Full ServerSignature On <IfModule alias_module> Alias /icons/ "/usr/share/apache2/icons/" <Directory "/usr/share/apache2/icons"> Options Indexes MultiViews AllowOverride None Order allow,deny Allow from all </Directory> </IfModule> <IfModule mod_autoindex.c> IndexOptions FancyIndexing VersionSort HTMLTable NameWidth=* AddIconByEncoding (CMP,/icons/compressed.gif) x-compress x-gzip AddIconByType (TXT,/icons/text.gif) text/* AddIconByType (IMG,/icons/image2.gif) image/* AddIconByType (SND,/icons/sound2.gif) audio/* AddIconByType (VID,/icons/movie.gif) video/* AddIcon /icons/binary.gif .bin .exe AddIcon /icons/binhex.gif .hqx AddIcon /icons/tar.gif .tar AddIcon /icons/world2.gif .wrl .wrl.gz .vrml .vrm .iv AddIcon /icons/compressed.gif .Z .z .tgz .gz .zip AddIcon /icons/a.gif .ps .ai .eps AddIcon /icons/layout.gif .html .shtml .htm .pdf AddIcon /icons/text.gif .txt AddIcon /icons/c.gif .c AddIcon /icons/p.gif .pl .py AddIcon /icons/f.gif .for AddIcon /icons/dvi.gif .dvi AddIcon /icons/uuencoded.gif .uu AddIcon /icons/script.gif .conf .sh .shar .csh .ksh .tcl AddIcon /icons/tex.gif .tex AddIcon /icons/bomb.gif core AddIcon /icons/back.gif .. AddIcon /icons/hand.right.gif README AddIcon /icons/folder.gif ^^DIRECTORY^^ AddIcon /icons/blank.gif ^^BLANKICON^^ DefaultIcon /icons/unknown.gif ReadmeName README.html HeaderName HEADER.html IndexIgnore .??* *~ *# RCS CVS *,v *,t </IfModule> <IfModule mod_mime.c> AddType application/x-compress .Z AddType application/x-gzip .gz .tgz AddLanguage ca .ca AddLanguage cs .cz .cs AddLanguage da .dk AddLanguage de .de AddLanguage el .el AddLanguage en .en AddLanguage eo .eo AddLanguage es .es AddLanguage et .et AddLanguage fr .fr AddLanguage he .he AddLanguage hr .hr AddLanguage it .it AddLanguage ja .ja AddLanguage ko .ko AddLanguage ltz .ltz AddLanguage nl .nl AddLanguage nn .nn AddLanguage no .no AddLanguage pl .po AddLanguage pt .pt AddLanguage pt-BR .pt-br AddLanguage ru .ru AddLanguage sv .sv AddLanguage zh-CN .zh-cn AddLanguage zh-TW .zh-tw </IfModule> <IfModule mod_negotiation.c> LanguagePriority en ca cs da de el eo es et fr he hr it ja ko ltz nl nn no pl pt pt-BR ru sv zh-CN zh-TW ForceLanguagePriority Prefer Fallback </IfModule> <IfModule mod_mime.c> AddCharset us-ascii .ascii .us-ascii AddCharset ISO-8859-1 .iso8859-1 .latin1 AddCharset ISO-8859-2 .iso8859-2 .latin2 .cen AddCharset ISO-8859-3 .iso8859-3 .latin3 AddCharset ISO-8859-4 .iso8859-4 .latin4 AddCharset ISO-8859-5 .iso8859-5 .cyr .iso-ru AddCharset ISO-8859-6 .iso8859-6 .arb .arabic AddCharset ISO-8859-7 .iso8859-7 .grk .greek AddCharset ISO-8859-8 .iso8859-8 .heb .hebrew AddCharset ISO-8859-9 .iso8859-9 .latin5 .trk AddCharset ISO-8859-10 .iso8859-10 .latin6 AddCharset ISO-8859-13 .iso8859-13 AddCharset ISO-8859-14 .iso8859-14 .latin8 AddCharset ISO-8859-15 .iso8859-15 .latin9 AddCharset ISO-8859-16 .iso8859-16 .latin10 AddCharset ISO-2022-JP .iso2022-jp .jis AddCharset ISO-2022-KR .iso2022-kr .kis AddCharset ISO-2022-CN .iso2022-cn .cis AddCharset Big5 .Big5 .big5 .b5 AddCharset cn-Big5 .cn-big5 # For russian, more than one charset is used (depends on client, mostly): AddCharset WINDOWS-1251 .cp-1251 .win-1251 AddCharset CP866 .cp866 AddCharset KOI8 .koi8 AddCharset KOI8-E .koi8-e AddCharset KOI8-r .koi8-r .koi8-ru AddCharset KOI8-U .koi8-u AddCharset KOI8-ru .koi8-uk .ua AddCharset ISO-10646-UCS-2 .ucs2 AddCharset ISO-10646-UCS-4 .ucs4 AddCharset UTF-7 .utf7 AddCharset UTF-8 .utf8 AddCharset UTF-16 .utf16 AddCharset UTF-16BE .utf16be AddCharset UTF-16LE .utf16le AddCharset UTF-32 .utf32 AddCharset UTF-32BE .utf32be AddCharset UTF-32LE .utf32le AddCharset euc-cn .euc-cn AddCharset euc-gb .euc-gb AddCharset euc-jp .euc-jp AddCharset euc-kr .euc-kr #Not sure how euc-tw got in - IANA doesn't list it??? AddCharset EUC-TW .euc-tw AddCharset gb2312 .gb2312 .gb AddCharset iso-10646-ucs-2 .ucs-2 .iso-10646-ucs-2 AddCharset iso-10646-ucs-4 .ucs-4 .iso-10646-ucs-4 AddCharset shift_jis .shift_jis .sjis AddHandler type-map var </IfModule> <IfModule mod_setenvif.c> # # The following directives modify normal HTTP response behavior to # handle known problems with browser implementations. # BrowserMatch "Mozilla/2" nokeepalive BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0 BrowserMatch "RealPlayer 4\.0" force-response-1.0 BrowserMatch "Java/1\.0" force-response-1.0 BrowserMatch "JDK/1\.0" force-response-1.0 # # The following directive disables redirects on non-GET requests for # a directory that does not include the trailing slash. This fixes a # problem with Microsoft WebFolders which does not appropriately handle # redirects for folders with DAV methods. # Same deal with Apple's DAV filesystem and Gnome VFS support for DAV. # BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully BrowserMatch "MS FrontPage" redirect-carefully BrowserMatch "^WebDrive" redirect-carefully BrowserMatch "^WebDAVFS/1.[012]" redirect-carefully BrowserMatch "^gnome-vfs/1.0" redirect-carefully BrowserMatch "^XML Spy" redirect-carefully BrowserMatch "^Dreamweaver-WebDAV-SCM1" redirect-carefully </IfModule> Include /etc/apache2/sites-enabled/ <Directory /var/www/sharedip> Options +Includes -Indexes AllowOverride None AllowOverride Indexes AuthConfig Limit FileInfo Order allow,deny Allow from all <Files ~ "^\.ht"> Deny from all </Files> </Directory> ###############ispconfig_log############### LogFormat "%v||||%b||||%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined_ispconfig CustomLog "|/root/ispconfig/cronolog --symlink=/var/log/httpd/ispconfig_access_log /var/log/httpd/ispconfig_access_log_%Y_%m_%d" combined_ispconfig <Directory /var/www/*/web> Options +Includes -Indexes AllowOverride None AllowOverride Indexes AuthConfig Limit FileInfo Order allow,deny Allow from all <Files ~ "^\.ht"> Deny from all </Files> </Directory> <Directory /var/www/*/user/*/web> Options +Includes -Indexes AllowOverride None AllowOverride Indexes AuthConfig Limit FileInfo Order allow,deny Allow from all <Files ~ "^\.ht"> Deny from all </Files> </Directory> <Directory /var/www/*/cgi-bin> Options ExecCGI -Indexes AllowOverride None AllowOverride Indexes AuthConfig Limit FileInfo Order allow,deny Allow from all <Files ~ "^\.ht"> Deny from all </Files> </Directory> Include /etc/apache2/vhosts/Vhosts_ispconfig.conf
top (in the time where its nearly "no load", sometimes at night it goes to 15.00): Code: top - 13:12:12 up 18 days, 20:56, 1 user, load average: 4.70, 3.95, 4.70 Tasks: 126 total, 12 running, 113 sleeping, 0 stopped, 1 zombie Cpu(s): 83.1%us, 15.6%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.7%hi, 0.7%si, 0.0%st Mem: 2061076k total, 2002768k used, 58308k free, 14244k buffers Swap: 505912k total, 48k used, 505864k free, 1702352k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8902 mysql 15 0 279m 102m 5208 S 29.6 5.1 1262:24 mysqld 20497 web1_adm 16 0 36660 20m 6952 R 13.6 1.0 0:00.41 php4-cgi 20498 web1_adm 17 0 30176 14m 6956 R 7.0 0.7 0:00.21 php4-cgi 20491 web1_adm 17 0 46748 30m 6972 S 2.0 1.5 0:01.31 php4-cgi 20476 web1_adm 21 0 46280 29m 6956 R 1.7 1.5 0:01.20 php4-cgi 20496 web1_adm 20 0 22616 7016 5304 R 0.7 0.3 0:00.02 php4-cgi 961 root 10 -5 0 0 0 S 0.3 0.0 5:25.83 md2_raid1 19292 www-data 15 0 35036 5900 1712 S 0.3 0.3 0:00.32 apache2 19818 www-data 15 0 35212 5956 1712 S 0.3 0.3 0:00.21 apache2 20248 www-data 16 0 35352 6100 1712 S 0.3 0.3 0:00.11 apache2 1 root 15 0 1852 576 488 S 0.0 0.0 0:01.00 init 2 root 34 19 0 0 0 R 0.0 0.0 0:07.34 ksoftirqd/0 3 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 events/0 4 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper 5 root 19 -5 0 0 0 S 0.0 0.0 0:00.00 kthread 8 root 10 -5 0 0 0 S 0.0 0.0 0:01.49 kblockd/0 9 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid 133 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod 170 root 15 0 0 0 0 S 0.0 0.0 0:00.00 pdflush 171 root 15 0 0 0 0 S 0.0 0.0 0:22.84 pdflush 172 root 10 -5 0 0 0 S 0.0 0.0 1:43.13 kswapd0 173 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0 402 root 17 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused 668 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0 673 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 ata_aux 674 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0 675 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_1 720 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_2 721 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_3 953 root 18 -5 0 0 0 S 0.0 0.0 0:00.01 md1_raid1 957 root 18 -5 0 0 0 S 0.0 0.0 0:00.02 md0_raid1 1029 root 10 -5 0 0 0 S 0.0 0.0 2:06.84 kjournald Please share your suggestions for more optimization of the server Thanks! Regards
Looks like your MySQL is taking all the power. I've had the same, and after running the "MySQL Performance Tuning Primer Script" (and doing what it's suggesting) managed to tweak it better. The script is coded by Matt Montgomery, who is part of a team that manages +7,000 linux servers! The script can be found here: http://day32.com/MySQL/ Small note. After changing things in the my.cnf you will need to wait 48 hrs to see the "real" effect!
Because of a permission denied error, i needed to execute the command: chmod u+x tuning-primer.sh before i could access the script.
