MySQL Forums
Forum List  »  Optimizer & Parser

Re: Performance for bind variables & Tuning
Posted by: Rick James
Date: August 15, 2009 02:49PM

Count the disk hits.

When you have a database that is much larger than available cache, you will be doing a lot of disk activity. Since disk hits are orders of magnitude more costly than anything else, minimizing the disk hits is the main goal.

The second big thing to tackle is data shrinkage. MEDIUMINT instead of INT, normalization, etc. Smaller data -> more cachable -> faster.

The third thing... And if you can do this, you get a huge performance gain: Summary tables.

I need more info. Please describe your data and application. And Please provide
* SHOW CREATE TABLE tbl\G -- engine type? indexes? data types?
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes?
* EXPLAIN SELECT ...\G -- slow queries? JOINs? Summarization?
and surround them with [ code ] and [ / code ]

With those, plus your RAM size, I can advise on the significant tunable parameters.

Bind parameters -- performance related to this is insignificant compared to disk hits / index optimization / query optimization / data shrinkage.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance for bind variables & Tuning
2684
August 15, 2009 02:49PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.