more RAM, less performance

MySQL & MariaDB Other MySQL® Questions
1

Greetings:

Just one very obtuse question: Can anyone think of a situation where providing more memory to a system running Linux (i386 / 32-bit, kernel 2.6.20-1) causes MySQL (specifically 5.0.27) to perform slower than it did with less memory using the same settings (same my.cnf file)?

I know windows XP and lesser flavors of Windows Server have problems when approaching 4GB, but I was a bit concerned when the MySQL environment began to exhibit unexpected behavior. Simply maximizing memory (2GB to 4GB) and changing no server settings I would expect performance to be roughly identical. Instead, MySQL appeared to not allocate as many bytes of RAM as previously and queries where taking longer, running at 100% CPU usage-- probably writing tmp tables to disk. Its just weird, isn’t it? Again, all I did was add more memory. I changed no server settings.

Happy ending, at any rate. I was able to spend a few hours tweaking settings and a couple of the queires in question to re-gain acceptable performance.

2

This is unusual - may be something else was changed.
If you take out memory and repeat the test does the problem comes back ?

It is also possible ballance between “high memory” and “low memory” changed so you got more buffer bounces while doing IO. But seems unlikely

Use 64bit platforms )

3

I’ve been busy, but things like these never really leave the back of the mind. After tinkering the only thing I can think of that could case symptoms similar to these under the same circumstances is something to do with the table_cache (many more tables had been added since the the sever came online) or a colation problem (someone changed a charset or colation in one of the tables).

Anyway, we are all 64-bit now, thank God.

4

If your OS reports that MySQL takes 100% CPU then the problem has nothing to do with disk activity.
Since if you have a lot of writes to disk then you will get a lot of waits where the CPU will have to wait for the disks.

What instead causes high CPU load is for example:

  1. Large ORDER BY that don’t use index
  2. Large GROUP BY that don’t use index
  3. Large joins that don’t use index (but the tables are small enough to fit into memory so that it doesn’t have to read from disk).
  4. A query that uses a function on a column and compares to a constant. For example:

… WHERE MD5(column1) = ‘xyz’;

Which forces mysql to calculate the MD5 for each record in the table before it compares it to the string xyz.

And continuing on your theory about collation, you could if you join on two string columns, and you define them as different collation, end up in the scenario where mysql needs to perform implicit type casts to be able to compare them. Which means a lot of CPU.