Context: Loading a copy of a legacy DB for a CRUD app at build by Spring Boot. Primary use case is developers testing the CRUD functionality as it is developed.
Problem: The loading operation is still over 2 minutes (1.57GB total DB size) but it seems like it should be much faster on a MBP with flash memory. I need to get it faster if at all possible but I don't know: 1. What property/config I am missing? 2. If my expectations are unrealistic?
Tried: 1. Lots of different combinations of mysqldump options. No effect. 2. Resizing the number of dump files, up to an including 1 dump file for each of the 557 tables. Shaves 2-5 seconds off at best.
Current Settings
mariaDb.getConfiguration().addArg("--innodb_doublewrite=0");
mariaDb.getConfiguration().addArg("--innodb_autoinc_lock_mode=2");
mariaDb.getConfiguration().addArg("--innodb_flush_log_at_trx_commit=2");
mariaDb.getConfiguration().addArg("--max_allowed_packet=536870912");
mariaDb.getConfiguration().addArg("--innodb-buffer-pool-size=536870912");
Details: I have the following dump files...
each of which was generated with mysqldump using the following options...
"--no-create-info", "--no-autocommit", "--single-transaction"
...and starts like...
-- MySQL dump 10.13 Distrib 5.7.16, for osx10.11 (x86_64)
--
-- Host: 127.0.0.1 Database: productdb
-- ------------------------------------------------------
-- Server version 5.7.18
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `productdata`
--
LOCK TABLES `productdata` WRITE;
/*!40000 ALTER TABLE `productdata` DISABLE KEYS */;
set autocommit=0;
INSERT INTO `productdata` VALUES (0,1954,'32814500',NULL),(0,2113,'Default',NULL),(10005,1000,'$19.99',NULL), more more more
INSERT INTO `productdata` VALUES (0,1954,'32814500',NULL),(0,2113,'Default',NULL),(10005,1000,'$19.99',NULL), more more more
It should be noted that this is a legacy system, so most of these dump files are <5 tables, and 1 of them has over 500 tables. So, for instance dump1 (shown) is a single 162 MB table with 4 columns. It has 161 multi-insert statements, each of which is 42,136 inserts "wide" in the sql file. Is it possible that parsing the SQL is the limiting factor here?
They are ALL InnoDB tables and I am using the following Java 8 code as part of Spring Boot to load the DB at build...
@Bean
@DependsOn(DB_SERVICE)
DataSource dataSource(MariaDB4jSpringService mariaDB4jSpringService) {
long startTime = System.nanoTime();
//Create our database with default root user and no password
try {
DB db = mariaDB4jSpringService.getDB();
db.source("productdb_schema.sql");
db.run("SET GLOBAL max_allowed_packet=1073741824;");
db.run("SET GLOBAL innodb_flush_log_at_trx_commit=2;");
ClassLoader cl = this.getClass().getClassLoader();
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(cl);
List<Resource> sqlFiles = Arrays.asList(resolver.getResources("classpath*:/LegacyDumps/*.sql"));
sqlFiles.parallelStream().forEach(sqlFile -> {
try {
db.source("LegacyDumps/" + sqlFile.getFilename(), dataSourceUser, dataSourcePassword, databaseName);
}
catch (ManagedProcessException e) {
e.printStackTrace();
}
});
}
catch (Exception e) {
e.printStackTrace();
}
finally {
long endTime = System.nanoTime();
long duration = (endTime - startTime);
System.out.println("Loading Product DB took " + duration / 1000000000 + " seconds");
}
DBConfigurationBuilder config = mariaDB4jSpringService.getConfiguration();
return DataSourceBuilder.create().username(dataSourceUser).password(dataSourcePassword).url(config.getURL(databaseName)).driverClassName(datasourceDriver).build();
}
