CGI and mySQL causing massive CPU usage

On relatively small database

[Daemonshyai]

What should be a simple query (listing the first 50 rows in 9000 row table) is using approximately 50% of the CPU.
The query being performed is:
SELECT $searchList FROM $table LIMIT 0, 50

I'm not entirely sure why the CPU is being hogged by my script, so any ideas would help.

Thanks in advance.
D.

[jatar_k]

Welcome to WebmasterWorld Daemonshyai,

That seems strange, how about a full text version of the query with out the vars in it? I doubt that will shed any light but it might.

The worst case scenario, I owuld think, for a query like that would be
SELECT * FROM singletable LIMIT 0, 50

It seems like there is another answer somewhere.

[Daemonshyai]

Thank you very much for the welcome :)

The full query is
SELECT name, fullname, login, id, imageloc FROM profiles LIMIT 0, 50

There are about 30 fields per row, and the fields being queried contain the shortest values (login is fixed at 12 characters, id is fixed at 8, imageloc is fixed at 1)

This problem has been plaguing me for a while -- I have a flat-file database and I want to move it over to mySQL for speed reasons, and my host has already suspended my account once for using an excessive amount of the CPU.

Again, thanks for your help.
D.

[jatar_k]

Since it is the script that is hogging process have you broken down everything the script does?

I have a hard time thinking it is that particular call that is causing the problem. Have you tried benchmarking various parts of the script?

How much processing are you doing with the returned data?

[Daemonshyai]

Literally all that is happening is each line is being fetched, and then each field is displayed in a table one after the other. I wouldn't have believed that something so simple would hog the CPU unless I'd seen it myself.

D.

[sun818]

Just for kicks, have you tried the same query using a smaller flat file? I know when I link "flat files" into my database, the database has to scan through the entire flat file as part of the query. If possible, I suggest importing your flat file into a database for better performance and less CPU overhead.

[claus]

Afaik, sun818 is right, the whole flatfile will probably get read even if you just want the first 50 rows. You could throw a script like this after the flatfile to printout the first 50 lines in stead ($calars reads line-by-line i believe):

open(PROF, '<profiles.txt');
for($i = 1; $i <= 50; $i += 1){$line=<PROF>;print $line;}
close(PROF);

/claus

[Daemonshyai]

Actually, the flat-file was migrated into the SQL database... I don't think I explained myself very well.

I have an older Perl script that I used to perform all of the operations on the flat-file, but it runs horribly slowly (the file is 14MB, with ~10k lines in it). The new script that I wrote deals only with the mySQL database, and doesn't interact with the flat-file any longer.

Sorry for the confusion,
D.

[sun818]

Hi, I think jatar_k is right. Knowing what you've said now, a select query that small is virtually instaneous.

jatar_k or anyone else, do you have any suggestions on how one actually goes about benchmarking a script? I found a few discussions here, but they don't discuss how to isolate the components in a script:

Any Tips to Reduce PERL CPU Usage? [webmasterworld.com]

Question about Excessive Perl Use [webmasterworld.com]

Benchmarking / optimising Perl scripts? [webmasterworld.com]

[timster]

Question:
Does the CPU usage spike up to 50% for just an instant, or does it stay there for a significant amount of time (e.g., a second)?

If it's for a significant time, there's probably a real problem with your script.

If it's just for an instant, the script is probably fine. If it's causing you real headaches, you may need to configure um...something...to decrease how greedy the CGI with processor cycles.