Unresponsive Server

[matt fawcett]

Hi, my company has a VPS that currently has just a few very small low traffic php sites on it. The server has 512MB of guarenteed ram. One of the projects that I have just written is cms that allows the upload and processing of a large CSV file (> 60000 records each with around 30 columns).

Because the file is quite large, and takes a long time to run I don't wat the user to be greeted by just a loading page for the length of time that it takes to load so once the CSV has been uploaded to the server I use the php 'exec' function to proccess the csv in the background which validates the row, inserts new and updates existing rows in a mysql database and sends out emails and sms messages following certain criteria.

The whole proccess takes around 15minutes depending on the data. I have implemented an ajax page that basically makes a call to the database every 10 seconds to get how far the import is and display on the page. All of this works fine on my local development machine (a newish Imac) but when running it on our VPS, when you request a page when the backgroud proccess is running it just loads and it takes ages to get the page. Obviously this messes up my ajax status as well because it never gets the information back.

I have a feeling that this problem may be more related to mysql than apache or php. When importing, the system is only using around half the ram. My database tables are InnoDB because I use transactions and the tables have been normalised and denormalised to make them as efficiant as possible and I have added Indexes where needed.

My mysql settings are
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M

and from the apache settings
KeepAlive On

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 15

##
## Server-Pool Size Regulation (MPM specific)
##

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>

User www-data
Group www-data

Does anyone know how I can solve this problem. The import doesn't need to run fast because it is in the background. The main priority is to deal with other requests first.