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.