SQL – Speed Testing

Even though the PostgreSQL server is often considered to be the fastest RDBMS in the world after Oracle Database, the MariaDB (fork of MySQL) server remains one of the fastest and most popular RDBMSs, especially when it comes to simple SQL queries. Thus, when discussing SQL optimizations in this book, we will mostly use MariaDB.

To benchmark our MariaDB server, we will be using the mysqlslap utility included with MySQL servers since version 5.1.4. In order to run the tests, we will start by loading the Sakila test database. On the container's command line, enter the following commands:

# wget -O sakila-db.tar.gz \ 
> https://downloads.mysql.com/docs/sakila-db.tar.gz 
# tar -xzvf sakila-db.tar.gz 
# mysql -uroot < sakila-db/sakila-schema.sql 
# mysql -uroot < sakila-db/sakila-data.sql 

Once the database is loaded, you can launch the first benchmarking test:

# mysqlslap --user=root --host=localhost --concurrency=20 --number-of-queries=1000 --create-schema=sakila --query="SELECT * FROM film;" --delimiter=";" --verbose --iterations=2 --debug-info  

You should then obtain a result similar to this:

Benchmarking the MariaDB server with the mysqlslap tool

You can then run a second benchmark test, but with a different level of concurrency in order to compare the results:

# mysqlslap --user=root --host=localhost --concurrency=50 --number-of-queries=1000 --create-schema=sakila --query="SELECT * FROM film;" --delimiter=";" --verbose --iterations=2 --debug-info 

Here are the results of the second test:

Benchmarking the MariaDB server with the mysqlslap tool using higher concurrency

The results of my tests show me that, with a full table scan query on a table with approximately 1,000 entries, performance degrades drastically when 50 or more concurrent queries are sent to the server.

We will see how these types of tests and many other more advanced ones will be particularly useful when discussing SQL query optimizations in the chapters dedicated to this topic.