2010-09-14

Speeding up database-bound tests with MySQL, redux

Kinda long between the posts here; hopefully what I do write is worth waiting for. :)

Way back in February I wrote about Speeding up database-bound tests with MySQL; a nice chap named Adam Monsen used the idea to speed up the build for something called Mifos.

I've since thought of something that's worth adding:

When you restart your computer with this setup, not only does your data go away (well, d'oh, you're storin' it in RAM, whaddaya expect?), but MySQL gets really confused about whether or not your table is there.

If you know why this happens and want the short version, skip the exposition.

For the rest of us, let's see this in action:

First we set the scene:

espenhw@demokritos:~$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 121
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database confusion;
Query OK, 1 row affected (0.01 sec)

mysql> use confusion;
Database changed
mysql> create table confusion_levels (id bigint primary key, level varchar(255)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into confusion_levels values (1, 'TOTAL');
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_confusion |
+---------------------+
| confusion_levels    |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from confusion_levels;
+----+-------+
| id | level |
+----+-------+
|  1 | TOTAL |
+----+-------+
1 row in set (0.00 sec)

All good. Now lets confuse MySQL:

espenhw@demokritos:~$ sudo service mysql stop
mysql stop/waiting
espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/innodb
total 20520
-rw-rw---- 1 mysql mysql 10485760 2010-09-14 00:41 ibdata1
-rw-rw---- 1 mysql mysql  5242880 2010-09-14 00:41 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2010-09-13 08:16 ib_logfile1
espenhw@demokritos:~$ sudo umount /var/lib/mysql/innodb
espenhw@demokritos:~$ sudo mount /var/lib/mysql/innodb
espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/innodb
total 0
# All gone!
espenhw@demokritos:~$ sudo service mysql start
mysql start/running, process 5388

Let's see what MySQL says:

mysql> show databases;
+--------------------------+
| Database                 |
+--------------------------+
[...redacted...]
| confusion                |
[...redacted...]
+--------------------------+

mmysql> use confusion;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_confusion |
+---------------------+
| confusion_levels    |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from confusion_levels;
ERROR 1146 (42S02): Table 'confusion.confusion_levels' doesn't exist

Ehm, what? But it does exist, you said so yourself!

Before you get bogged down in existential angst, there is a reasonable explanation.

MySQL actually stores information about a database in separate files on disk. Metainformation (such as table structure) lives in files in a directory named for the database.

Vide:

espenhw@demokritos:~$ sudo ls -l /var/lib/mysql/confusion
total 16
-rw-rw---- 1 mysql mysql 8588 2010-09-14 00:50 confusion_levels.frm
-rw-rw---- 1 mysql mysql   65 2010-09-14 00:50 db.opt

Digression: This is actually why MySQL is case-sensitive when it comes to database and table names, on case-sensitive file systems. So if you wondered why MySQL was more forgiving on Windows... Well, now you know.

Data, on the other hand, lives in separate files. For a MyISAM-backed table, the files are named tablename.MYD and tablename.MYI (for data and indexes, respectively); InnoDB, on the other hand, holds data and indexes for all tables across the MySQL instance in the same files (actually, you can change this, but hardly anyone ever does).

This clues us in to the solution to the above existential question: The table structure is still present, but the data is gone.


"Alright Espen, clever boy," I hear you say, "but what do I DO about it?"

Well, you do what you should be doing anyway: Ensure that your tests start running with a clean slate. That means creating any tables it needs from scratch, i.e. either dropping the tables before recreating or simply drop/create on the whole database.

For Hibernate, that should be as simple as setting the hibernate.hbm2ddl.auto property to create-drop (disclaimer: I haven't actually tested that); if you're running hand-crafted DDL to create your tables you probably know what to do...

13 comments:

Qasim Khan said...

Writing assignments is a difficult task to undertake and can often be overwhelming. That is why students need someone to help them with their tasks and complete them on time. If you are looking for a quality homework help services that will ensure that you get the best results, then you have come to the right place.

Shawn Abi said...

From troublemaker to goth, to techno, some (previously) underground music scenes embraced it as a fundamental garment. Subsequently, the rogue one orson krennic jacket mens for sale turned into a staple of metropolitan culture and a thing that represented the wearer's loyalty to a specific local area - whether it was worn by the insubordinate, the detached, or the moderate.

dry bulk trucking said...

emoving dirt to make room for Earthwork service texas in houstonsubsurface infrastructure.

Combined Pumps said...

and requirements of our customers, Combined hydrocarbon pumps AberdeenPumps is able to offer a complete pumping solution. Using a range of our

Loan providers near me said...

with the objective of becoming the bestWorking Capital in Dubai financial institution in Dubai by providing loans with the fewest hassles and transparent returns for any minor errors

Dubai gaming tournament said...

Having fun playing in them is guaranteed, too. We have a tournament for you on four04esports.com that covers everything from CS:GO to Esports Dubai To compete against the top players on the planet, sign up right away.

Learn nft from metacollar easy nft learning classes said...

To be a Fan Specialist and Professional whokdk ceiling fan dubai improves Indoor Air Quality in residence by providing Premium Products for contributing to society.

hastencatering said...

Diving into the realm of efficiency: Speeding up database-bound tests with MySQL, redux – a tech journey for streamlined development. Catering services in Houston Texas, where precision meets speed, our catering services ensure your events are as swift and satisfying as the optimizations made in your database tests.






Anna Thomas said...


Inspired by your February post on accelerating database-bound tests with MySQL—it's fantastic to see tangible results like Adam Monsen leveraging the concept to enhance the build speed for Mifos. As fit-out contractors in Dubai, we appreciate the ripple effect of innovative ideas across diverse fields, fostering efficiency and progress. Cheers to collaborative breakthroughs!

Charbonnel said...

"Fantastic to see the innovative approach to accelerate database-bound tests in action! Kudos to Adam Monsen for implementing this in Mifos. By the way, indulging in charbonnel et walker pistachio truffles would be the perfect celebration for such efficient optimizations!"






new evolution inter deco said...

Impressive to see the collaborative impact of sharing ideas! Kudos to Adam Monsen for leveraging the concept to enhance the build efficiency of Mifos back in February. For innovative solutions in optimizing spaces, check out the services of the leading fit-out company in Abu Dhabi.

Future Style said...

It's inspiring to see ideas spark innovation and improve processes! Much like Benedetta bags elevate any ensemble, Adam Monsen's utilization of your concept in accelerating builds underscores the transformative power of collaboration and resourcefulness in the tech community. πŸ’ΌπŸš€

Palazzolo Watches said...

It's inspiring to see how ideas shared can positively impact projects like Mifos, leading to tangible improvements in efficiency and productivity. Collaboration and innovation truly drive progress in the tech community. And as you continue to innovate, let Mushrooms Supplements support your cognitive function, enhancing problem-solving skills and creativity along the way.