5

I am in the process of speeding up a test suite that's using MySQL for persistence. I have changed the datadir and tmpdir in the my.cnf to use the RAMDisk as shown below.

datadir         = /run/shm/mysql
tmpdir          = /run/shm/mysqltemp

The problem is that every time i run the entire test suite, at some point i ran into foreign key constraint violations when the tests try to delete certain rows as part of the functionality being tested. The weird part is that this does not happen when i do not run MySQL on RAMDisk or when i leave the datadir at the default /var/lib/mysql/ but only change the tmpdir. I would like to run the tests with the datadir in RAM because i noticed that the clean up logic is blazingly fast when i do so.

Could someone help point out what i might be doing wrong here?

ivanorone
  • 450
  • 4
  • 17

1 Answers1

3

Are you sure you have enough RAM to run MySQL on it. Also it's not recommended to run MySQL on RAMDISK.

Speaking of optimization. You should utilize MEMORY type data storage and of course also run it on SSD. The major difference between HDD/SSD is I/O wait time and that's why so many servers now utilize in-memory (cache) DB like REDIS.

Look into it and good luck with optimization.

Po1nt
  • 522
  • 1
  • 4
  • 13
  • 5
    Thanks for your help. I am not running MySQL on RAMDisk in production, it's only for tests and for the mere goal of having a faster test suite. On the MEMORY type data storage, i do not really want to use that because i want the tests to run in a configuration that is as close as possible to production where i use InnoDb. I am also already using SSD. The suite is taking over 11 hours without any of the optimizations i talked about in the question and about 3 hours when i only change the tmpdir to RAMDisk – ivanorone Feb 23 '17 at 08:25
  • Running innoDB on RAMDisk should not be problem but as i said it's not recommended. That corruption of foreign keys/indexes may be caused by running out of space for MySQL. Also could you mention the version of storage system you are using ? – Po1nt Feb 23 '17 at 08:28
  • I am using MySQL version 5.5.54. The RAMDisk has 8Gb of memory and the entire box has 16Gb of memory and this box only runs the test suite. Perhaps this memory is not enough but shouldn't MySQL complain when it runs out of memory though? – ivanorone Feb 23 '17 at 08:32