Optimal configuration of Apache and MySQL

Hello,
I am owner of high-traffic website (over 100 000 UIPs/day).
What is optimal (best) configuration of apache and mysql for my server?
I have installed Virtualmin with default configuration and site is loading too slowly (it takes about 3 minutes to full load of site). Connection time is long too (Sometimes clients got message: Connection Timeout).
Apache creates about 500 proccesses (80% is in state of Sleeping).
Mysqld uses up to 80% of processor load.

My server HW configuration:
AMD x2 5000+ 64bit; 4 Gb of RAM

Installed OS: CentOS 5.3 Final (x64)

Thank you for all replies.

you can run tests on http://loadimpact.com to find out if per haps you are also running out of network resources.
80% for mysql seems rather high. The php script could also be responsible

you could play with entries in the /etc/my.cnf like

  • set-variable = max_connections=100 set-variable = key_buffer=512
  • How big is the database? Is there and what apps/scripts is (on) your site?

    Database has just about 100 Mb (but there are also tables with over 1 000 000 records)
    Script is optimized as much as it can be (sql queries are shrinked, etc…)

    System Stats from Virtualmin:

    CPU load averages: 311.68 (1 min) 129.83 (5 mins) 113.43 (15 mins)

    Real memory: 4.04 GB total, 2.91 GB used

    Running processes: 657

    // Edit: I have also problem with mysqld, it is randomly shutting down and clients are getting this error message: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (11)

    What does this error mean and what can I do to fix it?

    The database isn’t big enough to cause problems. Memory usage seems a bit high.
    the script connects to the mysql.socket instead through tcp/ip since the socket is faster.
    if it can’t connect then it may not exist. restarting mysql server should (re)create it.

    you can create a sym to /tmp :

  • cd /tmp ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
  • and chmod 777

    if the domain is running in mod_fcgid then you can set the child processes to 2 instead of the default 5 or even to none to spare memory.

    the collectinfo.pl proces has caused high cpu load with several users and you can change the default setting of 5 minutes to an hour or even less often.
    This is a cron job wich can be changed under Webmin-System-Scheduled Cron Jobs and find the job in the right pane, click it and change the schedule.
    Or go to Virtualmin-System Settings-Module Config-choose Status Collection in the dropdown.

    I would also inspect the script (your website) and look under Webmin-System-Running Processes
    You can inspect the processes, click on the ID for the mysql, then click Trace processes and Files and connections to see oddities

    I would also investigate the hardware to make sure the harddrives are still up to par, memory etc.

    Does not help much, server load has fall down, but as you can see there: http://www.e2ads.com or there: http://www.rapidcpm.info, sites are still loading slowly and connection is still taking too much time.

    Here is system info (after changes you have recommended me):

    Kernel and CPU: Linux 2.6.18-128.1.10.el5xen on x86_64

    CPU load averages: 6.91 (1 min) 12.68 (5 mins) 31.91 (15 mins)

    Real memory: 4.04 GB total, 1.82 GB used

    Running processes: 511

    Any other idea, how to make it smoothly working?

    the cpu load has dropped dramatically, but its still high.
    the sites actually load pretty quickly for me (i’m in NL and your site is in CZ)

    are those sites on a Xen instance? There is a newer kernel, i think 2.6.29
    I think Joe should chime in, he has experience with Xen.
    I advice to create a support ticket http://www.virtualmin.com/support.html with a link to this thread

    for what it’s worth: I ran a stress test against the info domain and I wasn’t able to stress your server even a bit. Below 2 seconds load time with 50 concurrent users.

    Also now being discussed in: https://www.virtualmin.com/node/9998