You are here

How fast can MySQL Process Data

MySQL Performance Blog - Wed, 09/04/2008 - 7:12pm

Reading Barons post about Kickfire Appliance and of course talking to them directly I learned a lot in their product is about beating data processing limitations of current systems.

This raises valid question how fast can MySQL process (filter) data using it current architecture ?
I decided to test the most simple case - what if we take the in memory table with very narrow row and run simple query which needs to do simple filtering - how many rows per second it will be able to do?

PLAIN TEXT SQL:
  1. CREATE TABLE `m` (
  2.   `i` int(11) NOT NULL
  3. ) ENGINE=MEMORY DEFAULT CHARSET=latin1
  4.  
  5. mysql> SELECT count(*) FROM m;
  6. +----------+
  7. | count(*) |
  8. +----------+
  9. |  1047684 |
  10. +----------+
  11. 1 row IN SET (0.00 sec)
  12.  
  13. mysql> SELECT count(*) FROM m WHERE i>0;
  14. +----------+
  15. | count(*) |
  16. +----------+
  17. |   349229 |
  18. +----------+
  19. 1 row IN SET (0.15 sec)

So we get 0.15 sec to scan about 1.000.000 rows which gives us peak filtering speed of about 7M rows/sec on this Intel(R) Xeon(R) CPU 5130 @ 2.00GHz CPU. This number is per core. In theory this box which has 4 cores should be able to do up to 4 times more, though in practice scaling factor is less of course.

Interesting enough if we get bigger table (so smaller portion of table will fit in CPU cache) the filtering speed stays about the same:

PLAIN TEXT SQL:
  1. mysql> SELECT count(*) FROM m3;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 10476840 |
  6. +----------+
  7. 1 row IN SET (0.00 sec)
  8.  
  9. mysql> SELECT count(*) FROM m3 WHERE i>0;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. |  3492290 |
  14. +----------+
  15. 1 row IN SET (1.49 sec)

To check completely in-cache scenario I created a table with just 10000 rows and wrote little stored procedure to make timing easier:

PLAIN TEXT SQL:
  1. mysql> DELIMITER //
  2. mysql> CREATE PROCEDURE test_read(pl INT)
  3.     -> BEGIN
  4.     -> DECLARE t INT;
  5.     -> SET @x = 0;
  6.     -> REPEAT SET @x = @x + 1;
  7.     -> SELECT COUNT(*) FROM m1 WHERE i>0 INTO t;
  8.     -> UNTIL @x> pl
  9.     -> END REPEAT;
  10.     -> END;
  11.     -> //
  12. Query OK, 0 rows affected (0.00 sec)
  13.  
  14. mysql> DELIMITER ;
  15.  
  16. mysql> call test_read(1000);
  17. Query OK, 0 rows affected (1.37 sec)

So we can get 10Mil rows filtered in 1.37 sec giving us again a bit over 7M rows/sec.

This CPU is 2Ghz so we get some 280 CPU Cycles per filtered row, which is not that bad considering abstraction of storage engine which requires "row by row" processing which means function calls for each row.

Lets see if we do some row function on those 10.000.000 rows (to keep it simple)
(In reality I did multiple runs to get accurate results, but I show only one here)

PLAIN TEXT SQL:
  1. mysql> SELECT sum(i) FROM m3;
  2. +---------+
  3. | sum(i)  |
  4. +---------+
  5. | 3492290 |
  6. +---------+
  7. 1 row IN SET (1.86 sec)
  8.  
  9. mysql> SELECT avg(i) FROM m3;
  10. +--------+
  11. | avg(i) |
  12. +--------+
  13. | 0.3333 |
  14. +--------+
  15. 1 row IN SET (1.97 sec)
  16.  
  17. mysql> SELECT avg(i+sqrt(i+1)+abs(i)) FROM m3;
  18. +-------------------------+
  19. | avg(i+sqrt(i+1)+abs(i)) |
  20. +-------------------------+
  21. |         1.8047401583918 |
  22. +-------------------------+
  23. 1 row IN SET (2.56 sec)

So as you see as we add some math the row scan speed is significantly affected.

I also decided to see how longer rows affect performance and created the following table:

PLAIN TEXT SQL:
  1. CREATE TABLE `m4` (
  2.   `c` char(128) NOT NULL
  3. ) ENGINE=MEMORY DEFAULT CHARSET=latin1
  4. mysql> SHOW TABLE STATUS LIKE "m4" \G
  5. *************************** 1. row ***************************
  6.            Name: m4
  7.          Engine: MEMORY
  8.         Version: 10
  9.      Row_format: Fixed
  10.            Rows: 5000000
  11.  Avg_row_length: 129
  12.     Data_length: 685609952
  13. Max_data_length: 948528873
  14.    Index_length: 0
  15.       Data_free: 0
  16.  AUTO_INCREMENT: NULL
  17.     Create_time: NULL
  18.     Update_time: NULL
  19.      Check_time: NULL
  20.       Collation: latin1_swedish_ci
  21.        Checksum: NULL
  22.  Create_options:
  23.         Comment:
  24. 1 row IN SET (0.00 sec)
  25.  
  26. mysql> SELECT count(*) FROM m4 WHERE c>"a";
  27. +----------+
  28. | count(*) |
  29. +----------+
  30. |        0 |
  31. +----------+
  32. 1 row IN SET (1.16 sec)

So with a bit longer rows we instantly get 4.3M rows per second. And now if we look at the memory amount consumed by table we can see the filtering speed is about 600MB/sec which is surely small fraction of what memory bus capacity of this system can deliver.

Entry posted by peter | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks