Re: [Openinteract-help] SPOPS MySQL Question

[Timothy M. A. <ti...@ad...>]

On Wed, 2004-08-04 at 09:55, Nick Floersch wrote:
[SNIP]

> 2) There is some problem with the way the database connection is being setup or the SPOPS SQL execution is  taking place such that the query works by hand entry but not in the Perl Program.
> 
> I know there are a lot of variables and unknowns to you, the reader, but what I am really curious about is if anyone else ever has problems *like* this.

One of these would be the table type you're using. I've had issues with
locking under MySQL in the past with the ISAM and MyISAM table types. I
know that InnoDB was supposed to alleviate this issue, but at the time I
was playing with InnoDB support, it seemed to send the server into
never-never land far more frequently.

> My only way of describing the hang is this: When I execute a general, working query, the CPU history will show 100% usage for maybe 10-30 seconds (depending on the query) and then MySQL will return with the data. In this case, CPU usage goes to 100% for some period of time, and then drops down to %50 and just stays there until I end the query. MySQL itself remains completely useable for other connections (CLI client for example). So MySQL doesn't die. Its almost like there is some loop that just keeps running and running...

Again, I'm just guessing here, but have you tried executing the query
manually when the server is under load? This would be a sure pointer to
row or table locking issues.

Failing that, how about slapping together a quick Perl/DBI script with
the same SQL to see if it's an issue only when running a Perl program.
At the least, this would help to insure that it is, in fact, an SPOPS
issue.

[SNIP]

Personally, I run Postgres and haven't (yet) run into issues where the
SQL causes the server to run away. In quick (read: I tried two or three
times) tests, the generated SQL doesn't seem too different between the
databases. Now, my own Cartesean product-ridden SQL is another story. :)

Hope that helps!

/tma