Sudden decrease in MySQL perfomance

[roardood]

Hi All

My problem is as follows: I am currently running a PHP application that has a MySQL backend (Apache & PHP are on a seperate server to MySQL which is on a dedicated box - P4, 1.6GHz, 512MB RAM, OS: Linux). Part of the application merges data from approximately 900 tables in one DB (roughly 2.9 GB) into a single table in another DB on the same physical partition. We have stripped off all the indexes on the destination table and indexed the fields in the source tables that qualify the query.

What we notice is that in the first half hour of processing approximately 5.4 million records are written, in the next 30 minutes only 250 000 records are written - indicating a massive decrease in peformance.

If the peformance was consistently poor, I would understand that the query is written badly and attempt to correct it. However, I cannot understand why the performance suddenly degrades and by such a large amount.

Can anyone suggest possible solutions? Places to look?

Regards

r

 

[sleipnir214]

It could be index updating as the records are being loaded. You might try dropping all indeces, loading the data, then recreating the indeces.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[roardood]

Hi sleipnir

Thought of that, and have stripped the destination table of all indices (incl PK)- made the query a little faster but it still exhibits the same pattern.

The indices on the source tables should not affect an "INSERT...SELECT" query.....or am I wrong?

 

[sleipnir214]

If anything, indeces on source tables could speed "INSERT...SELECT", depending on the WHERE clause of that query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[roardood]

Hi Sleipnir

The indices on the source tables are currently set on each of the columns referenced in the predicate of the query.

We've tried increasing the env variable for MySQL that covers bulk inserts...its name escapes me at the moment, but doubling it has made precious little difference

 

[ingresman]

Can you produce the execution plan and post it please ?

 

[roardood]

All

Just a case of being stoopid on my part..nothing wrong with MySQL...

In the destination table..I had created a control field which I used to determine which records had been written across so that I could delete them from the source tables...

I then used a script which counted the number of rows that had the control field set

When I stripped off all the indices..I also stripped off this index (big mistake)..and this is why the performance sucked..in the beginning when the total records in the destination table were small ..no problem...after a certain point, the checking of the control field sent process time off the scale....

So y'day afternoon set the index again ..and problem solved..processed > 20 Million records in 50 minutes .

Ta for the help sleipnir...

Ingresman - what is an execution plan?

 

[ingresman]

Hi,

It's the expalin staement, it shows you what indexes are used etc. It might still only work for selects.

My background is ingres which will show you the plan it is doing at end the end tells you how well it did !, cool for tuning.

Anyway glad you got it working anyway