MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

Following my tests with DATETIME vs vs TIMESTAMP vs INT performance and benchmarking with MyISAM storage engine, I’ve wondered about the performance impact using InnoDB, which is usually more peaky with I/O.
Test environment was pretty much the same:
So I went to do some benchmarks to see what we can conclude. The tests were run with:

• MySQL 5.4.0-beta (although specific 5.4/Google SMP patch configuration options left as defaults);
• Intel Quad core x 2800 MHz
• Solaris 10
• For each test, the tables were recreated to be positively sure there was no caching at all (also, but not every iteration, I did a filesystem cache flush) and the test itself was repeated a couple of times.
• I should also note that the InnoDB logs and datafiles and the exported data resided on the same storage device.

 Here are the configuration details specific to InnoDB:

CODE:

1. transaction-isolation = READ-COMMITTED
2. innodb_data_home_dir = /export/home/ntavares/mysql
3. innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
4. innodb_buffer_pool_size = 1200M
5. innodb_additional_mem_pool_size = 20M
6. innodb_flush_log_at_trx_commit = 2
7. innodb_log_file_size = 128M
8. innodb_log_buffer_size = 20M
9. innodb_autoinc_lock_mode = 2
10. innodb_file_per_table

The schema used was basically the same:

MySQL:

1. CREATE TABLE `test_datetime` (
2. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3. `DATETIME` FIELDTTYPE NOT NULL,
4. PRIMARY KEY (`id`)
5. ) ENGINE=INNODB;

Being FIELDTYPE either DATETIME, TIMESTAMP and INT. I’ve skipped the insert script since I had the exported data from the previous test. Hence, I’m assuming that LOAD DATA INFILE reflects directly the same behaviours as if I was doing a lot of external INSERTs.

CODE:

1. mysql> load data infile ’/export/home/ntavares/test_datetime.sql’ into table test_datetime;
2. Query OK, 10000000 rows affected (3 min 23.27 sec)
3. Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0
5. mysql> load data infile ’/export/home/ntavares/test_datetime.sql’ into table test_timestamp;
6. Query OK, 10000000 rows affected (3 min 2.09 sec)
7. Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0
9. mysql> load data infile ’/export/home/ntavares/test_int2.sql’ into table test_int;
10. Query OK, 10000000 rows affected (2 min 48.40 sec)
11. Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

I like this test because it exposes a typical sensation of a MyISAM-to-InnoDB migration. Notice the relatively huge values when compared with the ones from MyISAM. It’s also interesting to note how this turns to be much more I/O aggressive – although the buffer pool is big enough to hold all tables, but there are logs to be written… -, probably aggravated by having the original data on the same storage device (causing lots of disk head seeks, as if they weren’t sufficient eheh). The datafiles were aprox. 124MB for the INT and TIMESTAMP data types, and 265MB for DATETIME.
Let’s see how it performs on table scans. I’ll include both INT operations with and without UNIX_TIMESTAMP(), although we are already aware of the uneffectiveness of converting it on the server:

CODE:

1. mysql> select SQL_NO_CACHE count(id) from test_datetime where datetime > ’1970-01-01 01:30:00′ AND datetime < ’1970-01-01 01:35:00′;
2. +———–+
3. | count(id) |
4. +———–+
5. |    211991 |
6. +———–+
7. 1 row in set (6.20 sec)
9. mysql> select SQL_NO_CACHE count(id) from test_timestamp where datetime > ’1970-01-01 01:30:00′ AND datetime < ’1970-01-01 01:35:00′;
10. +———–+
11. | count(id) |
12. +———–+
13. |    211991 |
14. +———–+
15. 1 row in set (11.74 sec)
17. mysql> select SQL_NO_CACHE count(id) from test_int where datetime > UNIX_TIMESTAMP(’1970-01-01 01:30:00′) AND datetime < UNIX_TIMESTAMP(’1970-01-01 01:35:00′);
18. +———–+
19. | count(id) |
20. +———–+
21. |    211991 |
22. +———–+
23. 1 row in set (18.16 sec)
25. mysql> select SQL_NO_CACHE count(id) from test_int where datetime > 1800 AND datetime < 2100;
26. +———–+
27. | count(id) |
28. +———–+
29. |    211991 |
30. +———–+
31. 1 row in set (3.83 sec)

OK, this looks consistent with what I’ve demonstrated in the earlier test and seemed very acceptable to me, considering that we had no index. So I went to verify the theory of pushing INTs instead of DATETIMEs would be faster, and I could pretty almost beat DATETIME in MyISAM speed using AUTOCOMMIT=0 and committing every second (I used a SIG_ALRM timer trick in the script). I took the chance to explore the theory also with MyISAM, since I didn’t on the previous test:

CODE:

1. ——- avg          min          max
2. InnoDB  14062         6218        14066
3. MyISAM  14405        14398        14662

Special note to the minimum value for InnoDB which I blame the poor storage load capacity of this machine (~45MB/sec) and the innodb_max_dirty_pages_pct value which was default. Actually, I could match every big drop in performance with the Innodb_buffer_pool_pages_dirty variable decreasing. In fact, this machine has been suffering of the symptoms explained in the adaptive checkpointing comparison at MySQL Performance Blog and that’s why I installed 5.4 for testing.
Next path would be exploring the performance effects on I/O bound workload, but I don’t trust this machine for it, and I’ll better leave it for another one. Also interesting in this scenario, if I find the time, will be verifying the performance effects of patches in Percona HighPerf release, but this is a whole new set of tests that I’ll leave to another opportunity.

Refer: http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-innodb/