Description:
I have an abnormal complex testcase where the storage engines
Memory and InnoDB clearly outperform MyISAM when a certain
complexity is reached.
The test with MyISAM shows an dramatic non linear increase of
CPU time consumption, but no significant disc I/O, paging or
increase of used virtual memory. InnoDB and MEMORY show a
nearly linear increase of consumed CPU time.
I am sure that the complexity of the testcase is abnormal, but
maybe somebody is able to invent a more real life like testcase
suffering from the same problem.
The extreme annoying facts are that there are too many
surprising effects:
- CPU time (user) in seconds (mysql-test-run activities included)
Complexity level MyISAM MEMORY/InnoDB
10 2.3 2.3
20 10 ~2.5
29 28.8 2.8
30 230 2.9
60 stopped by me ~5.0
- omitting of some records containing a column with NULL
--> no dramatic increase of CPU time
- CHAR(50) instead of VARCHAR(50)
--> no dramatic increase of CPU time
Please have a look into the attached testcase. It contains
some test variants and the execution times I needed.
My environment:
- Intel PC with Linux(SuSE 9.3)
- MySQL compiled from source
Version 5.0 ChangeSet@1.1922, 2005-07-13 18:23:20
How to repeat:
Please use the attached testcase ml037.test.
copy it to mysql-test/t
touch r/ml037.result # Produce a dummy file with
# expected results
./mysql-test-run ml037
and wait
Description: I have an abnormal complex testcase where the storage engines Memory and InnoDB clearly outperform MyISAM when a certain complexity is reached. The test with MyISAM shows an dramatic non linear increase of CPU time consumption, but no significant disc I/O, paging or increase of used virtual memory. InnoDB and MEMORY show a nearly linear increase of consumed CPU time. I am sure that the complexity of the testcase is abnormal, but maybe somebody is able to invent a more real life like testcase suffering from the same problem. The extreme annoying facts are that there are too many surprising effects: - CPU time (user) in seconds (mysql-test-run activities included) Complexity level MyISAM MEMORY/InnoDB 10 2.3 2.3 20 10 ~2.5 29 28.8 2.8 30 230 2.9 60 stopped by me ~5.0 - omitting of some records containing a column with NULL --> no dramatic increase of CPU time - CHAR(50) instead of VARCHAR(50) --> no dramatic increase of CPU time Please have a look into the attached testcase. It contains some test variants and the execution times I needed. My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version 5.0 ChangeSet@1.1922, 2005-07-13 18:23:20 How to repeat: Please use the attached testcase ml037.test. copy it to mysql-test/t touch r/ml037.result # Produce a dummy file with # expected results ./mysql-test-run ml037 and wait