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?
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.