MySQL Forums
Forum List  »  Performance

Re: MySQL 5.5.31 cripplingly slow
Posted by: Rick James
Date: January 18, 2014 04:00PM

> 20% of my RAM is larger than the value I obtained through your new method: what's the downside of setting key_buffer_size too high?

We are going from subtle to more subtle. Here's the discussion on that...
* The key_buffer grows as needed, up to key_buffer_size. So, technically, 20% is OK, even if you don't need that much.
* In contrast, the buffer_pool is initialized to innodb_buffer_pool_size at startup. Hence, it _may_ be beneficial to do the LEAST(1.1*SUM(...), 70%*...) to come up with the best value.
* But, most datasets grow. So 20%. & 70%.
* But, then comes the issue of whether this is a _dedicated_ server. If dedicated, 20% or 70% of all of RAM is usually good. If it is not dedicated, then 20% or 70% of _available_ RAM is wise.
* And if you have both MyISAM and InnoDB, then 20/2 and 70/2 is probably a good starting point.

Confusing? Probably not to you -- you have been walking through this with me.
Overkill? Probably. The typical user is demanding "just give me a number!".

I added the 'algorithm' because a non-trivial number of DBAs were not satisfied with the simple 20/70.

BTW, instead of 70, some say 80, some say 70-80. I feel that, since swapping is really bad, saying a conservative 70 is safer.
But, then, 70 is too big for small machines (under, say, 2GB). So, should I make a more complex formula? I don't think it is worth it.
And, with machines growing well past 16GB, 70 lets a lot of RAM go to waste. Oh, well.

> KEY `delete` (`delete`),
> KEY `pro` (`pro`),
> KEY `deleted` (`deleted`)
> KEY `enginealerts` (`enginealerts`)

It is rarely useful to index a field with only a few distinct values. If `delete`=0 more than, say, 20% of the time, the optimizer will decide that scanning the table will be faster than using the index and bouncing back and forth.

It is sometimes useful to have a "compound" index that includes such a field. In this case (and since you are using MyISAM), INDEX(delete, id) may speed up the query by avoiding the Filesort.

Are cusers and preferences in a 1:1 relationship? If so, why have two separate tables? (There are valid reasons; I just don't see why.)

InnoDB is likely to run faster, since some of the lookups are based on PKs. MyISAM PK lookups are a two-step process: (1) drill down the PK BTree index in the .MYI; (2) reach into the .MYD.

> Rows_sent: 1447 Rows_examined: 5788
> rows: 1352 -- estimation in the EXPLAIN

I interpret that as
* Your output had 1447 rows.
* There are 1447 rows with delete=0 -- this is half the rows (yet it used the index ! ? )
* 5788 ~= 3 * 1447 -- for each of the 1447 rows in craiguser, it needed to lookup one row in each of the other two tables.
* It's very optimal.

> I created them 11 years ago

That explains MyISAM.
I don't see them as being all that bad. My Schema critiques are usually more vicious than what I did to yours.

Options: ReplyQuote


Subject
Views
Written By
Posted
2676
January 15, 2014 07:16AM
1123
January 15, 2014 11:44AM
1185
January 15, 2014 02:12PM
3438
January 17, 2014 05:52AM
1450
January 17, 2014 07:56AM
1215
January 17, 2014 11:23AM
1247
January 17, 2014 11:29AM
1315
January 17, 2014 02:03PM
1092
January 17, 2014 02:43PM
1241
January 17, 2014 05:13PM
1484
January 18, 2014 09:53AM
Re: MySQL 5.5.31 cripplingly slow
1163
January 18, 2014 04:00PM
1141
January 18, 2014 08:55PM


Sorry, you can't reply to this topic. It has been closed.

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.