I'm new to Linode and it's whole setup... I had a Linode 64 that was recently upgraded to Linode 80, and I need help tuning MySQL, Apache, and PHP to handle this low memory usage. I was wonderinf if someone could point me in the right direction. I've found quite a few articles and even a few sample my.cnf configurations, but there's still some latency when first requesting a page from my site. I think it has something to do with mysql using all the memory... and I couldn't find any good articles on optimizing Apache, but if anyonme has some suggestions, please reply!

Thanks in advance

Output of 'ps aux'

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.6  1492  504 ?        S    12:40   0:00 init [2]
root         2  0.0  0.0     0    0 ?        S    12:40   0:00 [keventd]
root         3  0.0  0.0     0    0 ?        SN   12:40   0:00 [ksoftirqd_CPU0]
root         4  0.0  0.0     0    0 ?        S    12:40   0:00 [kswapd]
root         5  0.0  0.0     0    0 ?        S    12:40   0:00 [bdflush]
root         6  0.0  0.0     0    0 ?        S    12:40   0:00 [kupdated]
root         7  0.0  0.0     0    0 ?        S    12:40   0:00 [jfsIO]
root         8  0.0  0.0     0    0 ?        S    12:40   0:00 [jfsCommit]
root         9  0.0  0.0     0    0 ?        S    12:40   0:00 [jfsSync]
root        10  0.0  0.0     0    0 ?        S    12:40   0:00 [xfsbufd]
root        11  0.0  0.0     0    0 ?        S    12:40   0:00 [xfslogd/0]
root        12  0.0  0.0     0    0 ?        S    12:40   0:00 [xfsdatad/0]
root        13  0.0  0.0     0    0 ?        S<   12:40   0:00 [mdrecoveryd]
root        14  0.0  0.0     0    0 ?        S    12:40   0:00 [kjournald]
root       178  0.0  1.3  2084  992 ?        Ss   12:40   0:00 dhclient3 -pf /var/run/dhclient.eth0.pid -lf /var/run/dhclient.eth0.leases eth0
root       215  0.0  2.0  3648 1516 ?        Ss   12:40   0:00 /usr/sbin/sshd
root       226  0.0  0.9  1632  688 ?        Ss   12:40   0:00 /sbin/syslogd
root       229  0.0  0.6  1492  472 ?        Ss   12:40   0:00 /sbin/klogd
root       234  0.0  0.6  1656  460 ?        S    12:40   0:00 /usr/sbin/courierlogger -pid=/var/run/courier/authdaemon/pid -start /usr/lib/courier/
root       235  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       237  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       238  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       239  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       240  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       241  0.0  0.6  1796  504 ?        S    12:40   0:00 /usr/lib/courier/authlib/authdaemond.plain
root       246  0.0  0.7  1760  540 ?        S    12:40   0:00 /usr/sbin/couriertcpd -address=127.0.0.1 -stderrlogger=/usr/sbin/courierlogger -maxpr
root       249  0.0  0.4  1532  312 ?        S    12:40   0:00 /usr/sbin/courierlogger imaplogin
root       259  0.0  0.7  1760  540 ?        S    12:40   0:00 /usr/sbin/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -stderrloggern
root       262  0.0  0.4  1532  312 ?        S    12:40   0:00 /usr/sbin/courierlogger imapd-ssl
root       264  0.0  2.6  6536 2012 ?        Rs   12:40   0:00 sshd: root@pts/0
root       266  0.0  5.5 10012 4216 ?        Ss   12:40   0:00 /usr/local/apache/bin/httpd
root       271  0.0  0.7  1520  532 ?        Ss   12:40   0:00 /usr/sbin/inetd
nobody     275  0.2 10.6 12716 8012 ?        S    12:40   0:01 /usr/local/apache/bin/httpd
nobody     277  0.0 10.3 12716 7768 ?        S    12:40   0:00 /usr/local/apache/bin/httpd
nobody     278  0.3 10.6 12744 8036 ?        S    12:40   0:03 /usr/local/apache/bin/httpd
nobody     279  0.3 10.4 12680 7912 ?        S    12:40   0:02 /usr/local/apache/bin/httpd
nobody     281  0.0 10.2 12608 7748 ?        S    12:40   0:00 /usr/local/apache/bin/httpd
root       285  0.0  1.6  2496 1248 ?        S    12:40   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/u
mysql      315  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      318  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      319  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      320  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      321  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      322  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      325  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      326  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      327  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      328  0.0 18.4 41408 13900 ?       S    12:40   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
root       336  0.0  1.0  1752  816 ?        Ss   12:40   0:00 /usr/sbin/cron
root       342  0.0  2.6  6536 2012 ?        S    12:40   0:00 sshd: root@pts/0
root       344  0.0  0.6  1484  476 tty0     Ss+  12:40   0:00 /sbin/getty 38400 tty0
root       345  0.0  1.5  2480 1204 ?        Ss   12:40   0:00 /bin/sh /command/svscanboot
root       347  0.0  0.4  1504  328 ?        S    12:40   0:00 svscan /service
root       348  0.0  0.3  1336  244 ?        S    12:40   0:00 readproctitle service errors: .......................................................
root       349  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise qmail-send
root       350  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise log
root       351  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise qmail-smtpd
root       352  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise log
root       353  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise qmail-pop3d
root       354  0.0  0.3  1348  284 ?        S    12:40   0:00 supervise log
qmails     355  0.0  0.4  1520  376 ?        S    12:40   0:00 qmail-send
vpopmail   356  0.0  0.6  1540  508 ?        S    12:40   0:00 /usr/local/bin/tcpserver -v -R -l vortekweb.com -x /etc/tcp.smtp.cdb -c 30 -u 1008 -g
qmaill     357  0.0  0.3  1356  288 ?        S    12:40   0:00 multilog t s100000 n20 /var/log/qmail/qmail-send
root       358  0.0  0.3  1372  292 ?        S    12:40   0:00 tcpserver -H -R -v -c100 0 110 qmail-popup mail.vortekweb.com /home/vpopmail/bin/vchk
qmaill     360  0.0  0.3  1356  288 ?        S    12:40   0:00 multilog t s100000 n20 /var/log/qmail/qmail-smtpd
qmaill     361  0.0  0.3  1356  288 ?        S    12:40   0:00 multilog t s100000 n20 /var/log/qmail/qmail-pop3d
root       365  0.0  0.4  1484  320 ?        S    12:40   0:00 qmail-lspawn ./Maildir
qmailr     366  0.0  0.4  1480  316 ?        S    12:40   0:00 qmail-rspawn
qmailq     367  0.0  0.4  1472  316 ?        S    12:40   0:00 qmail-clean
root       369  0.0  1.9  2584 1440 pts/0    Ss   12:41   0:00 -bash
mysql      373  0.0 18.4 41408 13900 ?       S    12:41   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
nobody     374  0.2 10.4 12600 7868 ?        S    12:41   0:02 /usr/local/apache/bin/httpd
mysql      375  0.0 18.4 41408 13900 ?       S    12:41   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
nobody     376  0.0  9.8 12304 7440 ?        S    12:41   0:00 /usr/local/apache/bin/httpd
mysql      377  0.0 18.4 41408 13900 ?       S    12:41   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      378  0.0 18.4 41408 13900 ?       S    12:42   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
nobody     379  0.0 10.2 12492 7728 ?        S    12:42   0:00 /usr/local/apache/bin/httpd
mysql      380  0.0 18.4 41408 13900 ?       S    12:42   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
nobody     381  0.0  6.1 10164 4628 ?        S    12:42   0:00 /usr/local/apache/bin/httpd
mysql      382  0.0 18.4 41408 13900 ?       S    12:42   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      383  0.0 18.4 41408 13900 ?       S    12:43   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
mysql      384  0.0 18.4 41408 13900 ?       S    12:43   0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql
root       395  0.0  1.1  2480  864 pts/0    R+   12:56   0:00 ps aux

my.cnf file:

~# cat /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
basedir         = /usr/local/mysql
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
set-variable    = key_buffer=128K
set-variable    = max_allowed_packet=1M
set-variable    = thread_stack=64K
set-variable    = table_cache=4
set-variable    = sort_buffer=64K
set-variable    = net_buffer_length=2K
server-id       = 1

[mysqldump]
quick
set-variable    = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable    = key_buffer=8M
set-variable    = sort_buffer=8M

[myisamchk]
set-variable    = key_buffer=8M
set-variable    = sort_buffer=8M

Apache conf file (partial):

ServerType standalone

ScoreBoardFile /usr/local/apache/logs/httpd.scoreboard

Timeout 300

KeepAlive On

MaxKeepAliveRequests 100

KeepAliveTimeout 15

MinSpareServers 5
MaxSpareServers 10

StartServers 5

MaxClients 150

MaxRequestsPerChild 0

#CacheNegotiatedDocs

UseCanonicalName On

HostnameLookups On

ErrorLog /usr/local/apache/logs/error_log

LogLevel warn

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

CustomLog /usr/local/apache/logs/access_log common

any ideas??? thanks

Let's discuss the theory of relativity.

What are your average page generation times?
What are your average response times?
What is your server load running at?

What would you consider a fast server?

You need to have a target to know when you have arrived at it.

ahh, good points

Currently, the first time you access the page, it takes about 5 seconds average to START loading the pages, and sometimes it can take as much as 2-3 seconds to finish the entire page (using simple PHP microtime timers). This is a PHP script running about 6-8 simple MySQL SELECT queries with left joins. I don't have much traffic now, but suppose I had 10,000 unique visitors/day I don't want load times to drop or people thinking "man this site is SLOW."

Server load normally stays below 1 all the time, even when running some complex MySQL scripts through perl (command line) it doesn't even read 2.

I conder a server fast when I can type www.website.com, hit enter, and in mere seconds I have the page in front of me. With mine, sometimes it takes some time to go from entering the URL to actually seeing the page (5-10 seconds). example: IE shows "web site found... waiting for reply" longer than most sites. I guess an example "fast" site would be google. I have always considered the speed of google as fast, and yes, I do understand the hardware google is running sure as hell surpasses the performance of the cheapest linode, but these are the results I am after.

I have a stats page which seems to be running faster than it used to, it's actually Psychostats for a counter-strike source server. Try these pages out and tell me how you react to the speed:

http://csstats.phraxion.com

The MySQL optimization I found (the one caker wrote, I believe) helped this tremendously, but currently running on this server I have no free physical ram space, all the services currently running are going in and out of swap. I guess that's my ultimate goal is to minimize memory usage of all my services, but still maintain top-notch performance (for the low cost of the Linode 80 I cm currently using)

I've tried the DNS timers from www.dnsstuff.com and they all score me A+ each time with very fast response times. I'm using Zoneedit right now as opposed to running BIND or similar.

Wouldn't an option be to cache the query results?

Still kinda new to the whole PHP/MySQL thing... if this would be beneficial could someone point me in the right direction for some good documentation on optimizing PHP/MySQL scripts?

I wish I could give you some concrete advice, but I don't use php/mysql. In the java appserver world, caching query results is pretty standard and automated. I have heard though that caching results with php/mysql is possible and somewhat easy, and greatly improves access times. I'd suggest you google "php mysql cache query results" or something like that.

By the way I clicked on your link and the access time seemed fine to me. I could tell you about the government-funded web sites I've had to use with horrific performence, minutes to retrieve single pages, and they are very well funded and run by highly reputed outsourced adminitrators. Don't be too hard on yourself!

I have no idea how to improve your database speed, but I tried browsing your site and the speed seemed fine to me. Maybe 1 - 2 seconds to load the front page (which was a long list of player names) and < 1 second to load the page representing any individual player. Considering that the content all looked to be dynamically generated, I'd say the performance looks quite good.

Thanks for the input! I've been playing around with some MySQL memory settings to get it where it is now and I think I'm satisifed for now. Also, I've found some query caching info, and I'm looking into Smarty template engine now for its caching features to speed it up even more.