Matthew Martz wrote:

...

...

The databases appear to be about 450MB-600MB.

Not bad, but what operation were you performing on the database at the time? complex queries with joins and such will drive memory/processor usage up.

I was selecting a database from the drop down menu.

try some actual mysql commands -for this you will have to bite the bullet and learn some mysql, so you can do test queries from command line, and determine if it's phpmyadmin or mysql that hammering your machine. Once you know that you can focus on tuning mysql properly, or your machine, or PHP.

login: mysql -u root -p (it'll ask for your password)

use databasename; (databasename should be substituted with your dbname)

select * from table_name; (tablename is name of a table, like recruits, or sysadmins)

select * from sysadmins where mysql_knowledge = "hardcore"; (example query)

make the queries tougher by asking developers to give you some good examples.

...

...

On a side note I had a requirement from the dev group here to set php to a maximum memory usage of 1GB due to the large files that are being parsed

Holy CRAP that's huge. Is this a real need, or is this just the dev group wanting to not fix a problem? This seems like it could very quickly get out of hand if more than a user or two decide to kick off similar processes.

I have no idea where the file comes from but it is a 600-800MB delimited txt document. They use a web page to parse the file and create an Excel spread sheet.

I don't know what the txt file contains or why they need it converted to xls.

In any case I am told to make the system capable of meeting the dev teams needs.

Here is where you are in a pinch, basically as a sysadmin, there are alot of flavors of this trade, for example my sysadmin jobs has 3 or 4 distinct areas: sysadmin-dev, sysadmin-customerservice, sysadmin-linux, sysadmin-network. in your case you are doing sysadmin-dev, a developer flavor of a sysadmin -so developers are your saviours with a boss sitting there behind the scenes saying "just make it work". So let's say this load issue is not typical, how much memory is in your server? What version of centos are you using, what version of mysql, and are you using the ReiserFS filesystem or Ext3?

We use Reiser (even though he's on trial, his FS isn't). It's a better choice for mysql servers, you can read up on why, and skip the debates folks have for now.

What RAID are you using? Having an inefficient RAID setup might slow things from an I/O standpoint. But as to how much memory is being used, -knowing how much RAM you have, and how much swap is a good start. Also the version of mysql, is it the centos one, or the mysql one? Or is it apachefriends or something else?

I can say that I have some hardcore mysql servers, and I use 8GB RAM and dual core opterons, reiserfs, and make very few changes to the system internals after a CentosServerCD install other than the usual hardcore security stuff.

Use the developers as allies, use other servers for comparisons, and tools like mysqlbench when in a pinch. Since you have phpmyadmin on there, we know you have Apache, PHP, and mysql, all on the same machine. Try running mysql queries without phpmyadmin, developers shouldn't need phpmyadmin if they know what they are doing in the first place, especially with tools like MysqlQueryBrowser (a windows client) and other clients. I like phpmyadmin, but it might be setting you up for drama when you dont need it.

Good luck.