Please recommend tuning suggestions for slow MySql??? 2

[BobMCT]

This is a revisited topic. Here's the situation:
We have a production job in perl using perl::dbi and randomly accesses a 18MB database from certain records in a 3 million record sequential file.
Originally this app ran on AIX version 4.2.1 with perl 5.5 and it would run in about an hour. Several months ago we upgraded our OS to AIX version 5.1.01 with perl 5.6/5.8 on the SAME hardware. Now the SAME job takes upwards of 30 HOURS. All our other apps seem to run just as before.

Can someone skilled in MySQL tuning PLEASE offer some suggestions of where/what to look for in an attempt to bring this app back to acceptable run times?

Thanks for ANY recommendations received.

 

[BobMCT]

First of all I would like to thank everyone who provided any sort of response. Please don't overwork yourselves (sarcastic).

At any rate - in case anyone reading is interested - I found the culprit and resolved this issue. Here is what I found:

Previously we were running mysql 3.23.older and when we upgraded our OS and perl version we installed a newer mysql version 3.23.53. Apparently there is a difference in the handling of data types.

The database table is defined with an INDEX which is a char type field. In the "select *" statement the data field used as an argument contained NUMERIC values. In the older versions this didn't seem to matter because there were no error/type mismatch messages and the program ran like lightning. However, in the newest version the type mismatch seems to make a BIG difference. I was able to isolate the slowdown to the select statement and then using the EXPLAIN command from the mysql> command line I could see that the INDEX was NOT being used. Again, there were NO error messages but the select actually searched all the database rows for matches (all 300,000 of them). The ONLY change made to our perl program was to wrap the search field name in double quotes (&quot.

Just this morning a test showed that this reduced a 3 million record run from 30 hours last week to under 7 MINUTES today. What a difference a (&quot makes.

Hope someone else can learn from this.

Bob

 

[Dfly]

Hi, Bob. I'm having a similar problem and not quite following what you are saying.

Our database has over 6,000 items and the 'inventory' table is indexed. The pages seem to retrieve data way too slow.

Would you please show a before and after SELECT statement to demo what you changed?

Ashford

http://www.magicbookshop.com

 

[sleipnir214]

What he's saying is: in a select query you will get better performance if you match the datatype of the search item with the datatype of the column. This allows MySQL to use its table indeces to make the search more efficient.

He also says that this is apparently a new behavior.

Want the best answers? Ask the best questions:

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

TANSTAAFL!

 

[BobMCT]

Sleipnir214 is absolutely correct!

At our perl mongers meeting we discussed this in detail this evening and it appears that either the perl or the perlDBD/DBI was altered to NOT force a data type conversion. As a result when MySQL received the select request it could not match the argument data type to the index so it didn't use it. It therefore read each and every table record, did a type conversion of the key field in an attempt to match the argument. Hence, when the select was modified perl passed the argument as it and MySql recognized the data type to key field match and used the index. The end result was thousands of percentage points increase in performance.

A sample of the select would be as follows:

Original:

select * from tablename where FLD = $FLD;

Changed:

select * from tablename where fld = '$FLD';

Note the single quoted around the argument field name. This ONE syntax change made all the difference.

Hope this helps.

Bob

 

[sleipnir214]

BobMFDC:

The reason I have never come across this error is because I have always been very conscious of matching datatypes in a query. I've been bitten a couple of times by default type mappings.

<aside>
BobMFDC: &quot;MFDC&quot;?
</aside> Want the best answers? Ask the best questions:

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

TANSTAAFL!

 

[Dfly]

I've always used the single quotes in the WHERE clause, except for when the data is numbers.

Ex: WHERE media='audio' and stock > 0;

I'll change those to see if it makes a difference.

My next quest for speed involves normalization of the inventory table, if our webhost (our mySQL server) can handle InnoDB tables.

Ashford

 

[sleipnir214]

Dfly:
I think you misunderstood. You are doing it correctly. You'll take the performance hit if you do use &quot;WHERE media='audio' and stock > '0'&quot; Want the best answers? Ask the best questions:

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

TANSTAAFL!

 

[sleipnir214]

BobMFDC:
I forgot. Sharing an answer that good deserves a star. Here's one from me.
Want the best answers? Ask the best questions:

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

TANSTAAFL!