Performance Tuning Apache and MySQL

[JIKOmetrix]

Hi,

I have a VPS that needs to be performance tuned. It regularly has high loads. I have tried to tune Apache and MySQL. Here is some background:

1. There is one web site on the VPS.
2. It is running a Joomla web site with caching turned on. This helped.
3. It is also running an OpenX advertising server.
4. They get about 180,000 unique visitors a month or about 9,000 per day.
5. I have munin installed in WHM.
6. Apache averages about 20-30 access per second during peek hours
7. Apache runs about 20-30 processes during peek hours
8. MySQL averages about 20-30 queries per second during peek hours
9. MySQL averages 2 thread but can spike to 9 during peek hours
10. Netstat shows an average of 10-20 connections established during peek hours, but spikes to over 100 at times.
11. Average load is about 1.3 but regularly spikes to over 10 during during peek hours.
12. Memory usage is low though, only 500MB out of 2GB being used with just a 750MB commit.

I'm wondering if there is something I can do in the configuration to get better performance (lower loads) or are we hitting the performance limits of the VPS (CPU limits, Rationing)? The hardware we are on is is dual Quad core Intel Xeon 2.8GHz with RAID-10 drives.

Any suggestions would be great. I'm also considering moving the OpenX ad server to the hosted option from OpenX.

Thanks,
Mike

[Spiral]

Given the numbers you posted, I would actually be a little bit leery about
hosting this on a traditional VPS server. I don't think that is going to be
your most ideal hosting platform for this.

You might be able to tune some items like go to FCGI, add a cache like
X-cache, better match Apache settings to physical limits, and tune MySQL a
bit better but you are probably going to run yourself a bit into a brick wall
from the memory and network constraints there.

If I saw your current settings and performance at those settings, I might
be able to tell you a bit better what you can do and just how far you can
push it but given what you said, I suspect physical barriers to stop you
before you get things up to where you would like them to be.

[JIKOmetrix]

Hi,

Thanks. I manage this VPS for a client and have considering telling them they need to move to dedicated hardware. Here are some additional configuration settings:

Apache:
MaxClients 150
MaxRequestsPerChild 1000
KeepAlive On
KeepAliveTimeout 2
Timeout 300

MySQL info from 'mysql-tuning-primer.sh'

MySQL Version 5.0.90-community-log x86_64

Uptime = 1 days 21 hrs 44 min 57 sec
Avg. qps = 17
Total Questions = 2905816
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
MySQL :: MySQL 5.0 Reference Manual :: 5.1.3 Server System Variables
Visit MySQL :: MySQL Enterprise Advisors
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10 sec.
You have 62 out of 2905837 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See MySQL :: MySQL 5.0 Reference Manual :: 6.5 Point-in-Time (Incremental) Recovery Using the Binary Log

WORKER THREADS
Current thread_cache_size = 48
Current threads_cached = 44
Current threads_per_sec = 0
Historic threads_per_sec = 0

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 45
The number of used connections is 45% of the configured maximum.

INNODB STATUS
Current InnoDB index space = 160 K
Current InnoDB data space = 464 K
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 13 M

MEMORY USAGE
Max Memory Ever Allocated : 417 M
Configured Max Per-thread Buffers : 650 M
Configured Max Global Buffers : 125 M
Configured Max Memory Limit : 775 M
Physical Memory : 2.00 G

KEY BUFFER
Current MyISAM index space = 126 M
Current key_buffer_size = 100 M
Key cache miss rate is 1 : 1622
Key buffer free ratio = 71 %

QUERY CACHE
Query cache is enabled
Current query_cache_size = 10 M
Current query_cache_used = 2 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 29.18 %
Current query_cache_min_res_unit = 4 K

SORT OPERATIONS
Current sort_buffer_size = 768 K
Current read_rnd_buffer_size = 768 K

JOINS
Current join_buffer_size = 4.00 M
You have had 8407 queries where a join could not use an index properly
join_buffer_size >= 4 M

OPEN FILES LIMIT
Current open_files_limit = 8302 files

TABLE CACHE
Current table_cache value = 4096 tables
You have a total of 372 tables
You have 837 open tables.

TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 35734 temp tables, 44% were created on disk

TABLE SCANS
Current read_buffer_size = 768 K
Current table scan ratio = 62 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 387

Thanks,
Mike