[CentOS] phpMyAdmin high Memory usage

Tue Mar 20 01:36:32 UTC 2007
Karl R. Balsmeier <karl at klxsystems.net>

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.