Stai visualizzando una vecchia versione di questo article. Visualizza la versione più recente.

DBT-3 (OSDL Database Test 3) è uno strumento di workload per il kernel Linux sviluppato da OSDL (Open Source Development Labs, inc), e basato su TPC-H del Transaction Performance Processing Council (TPC).

DBT-3, come TPC-H, simula un effettivo sistema di supporto al decision making e modella complesse applicazioni di business analysis che svolgono un lavoro di analisi dei dati per poter prendere le decisioni migliori. Eseguendo il workload simulato da DBT-3, è possibile verificare e misurare le prestazioni del kernel Linux in un effettivo sistema di supporto al decision making.

DBT-3 utilizza lo "scale factor (SF)" come indicatore dello stress sul sistema. Modificando l'SF, è possibile cambiare le dimensioni del database perché raggiunga SF volte le sue vecchie dimensioni.

I test eseguiti da DBT-3 comprendono i tre elencati sotto. Misura i loro tempi di esecuzione, così come le informazioni sullo stato del sistema e le statistiche sul database.

  1. Load test
    • Inserisce nel database i dati che verranno poi usati dai test Power e Throughput. Esegue una bulk insert dei grandi dati CSV corrispondenti allo Scale Factor.
  1. Power test
    • Esegue 22 query complesse.
  1. Throughput test
    • Esegue le stesse 22 query del test Power, ma simultaneamente in più di un processo.

Ai fini di questo task, viene eseguito solo il test Power su un database precedentemente preparato con diversi SF. Il tempo di esecuzione di ogni query viene misurato e registrato in un database. Successivamente i risultati di un unico test con tutte le 22 query verranno disegnati in un istogramma per comparare le differenti configurazioni.

Preparazione dell'ambiente dei benchmark

Permessi per sudo

L'utente che eseguirà i benchmark deve avere i permessi per sudo sulla macchina.

Per pulire le cache di sistema tra un'esecuzione di una query e l'altra, lo script di automazione lancia il seguente comando:

sudo /sbin/sysctl vm.drop_caches=3

Il comando deve essere eseguito con i permessi da superutente. Anche se l'utente passa una password a sudo, questa password scade dopo un timeout. Perché il comando funzioni senza password, occorre aggiungere al file visudo la riga seguente (per modificarlo si può usare il comando "sudo visudo"):

'username' ALL=NOPASSWD:/sbin/sysctl

...dove 'username' è l'utente che esegue il benchmark.

Il software necessario

I benchmark automatizzati di DBT3 necessitano dei seguenti software:

  • Config::Auto un modulo Perlche legge i file di configurazione. Il comando per installarlo è:
    sudo cpan Config::Auto
  • DBD::mysql un modulo Perl per connettersi a MariaDB/MySQL e PostgreSQL. Il comando per installarlo è:
    sudo cpan DBD::mysql

NOTA: E' possibile che appaia un errore che dice che CPAN non è riuscito a trovare mysql_config. In questo caso occorre installare la libreria client di sviluppo mysql. In OpenSuse il comando è:

sudo zypper install libmysqlclient-devel

In alternativa questo modulo può essere installato manualmente seguento queste istruzioni:

  1. Si scarichi DBD-mysql-4.020.tar.gz da http://search.cpan.org/~capttofu/DBD-mysql-4.020/lib/DBD/mysql.pm e lo si decomprima
  1. Si esegua lo script Perl PerlMake.pl sotto la directory decompressa:
    perl Makefile.PL --mysql_config=/path/to/some/mysql_binary_distribution/bin/mysql_config
  1. Si esegua make per compilare DBD::mysql:
    make
  1. Si aggiungano i percorsi necessari per poter eseguire DBD::mysql:
    export PERL5LIB="/path/to/unzipped_DBD_mysql/DBD-mysql-4.020/lib"
    export LD_LIBRARY_PATH="/path/to/unzipped_DBD_mysql/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/:/path/to/some/mysql_binary_distribution/lib/"

DBMS testati

NOTA: DBT3 richiede molto spazio su disco (per esempio i database MySQL 5.5.x + MyISAM con scale factor 30 necessita di circa 50 GB). Inoltre alcune query richiedono l'uso delle tabelle temporanee sotto la directory specificata nel parametro --tmpdir di mysqld. Nei file della configurazione già pronta la directory temporanea punta alla directory di sistema di mysql della distribuzione binaria, ma è meglio accertarsi che la suddetta directory abbia abbastanza spazio libero.

Istruzioni di installazione

NOTA: La directory in cui tutti i file verranno scaricati e installati verrà chiamata $PROJECT_HOME. Ed esempio potrebbe essere ~/benchmark/dbt3.

Scaricare mariadb-tools

  1. Ci si porti nella cartella del progetto
    cd $PROJECT_HOME
  2. Si scarichi la branch più recente da LaunchPad con Bazaar:
    bzr branch lp:mariadb-tools

Il progetto di dbt3 dovrebbe trovarsi nella seguente directory:

$PROJECT_HOME/mariadb-tools/dbt3_benchmark/

Il progetto dbt3_benchmark contiene i seguenti file e directory:

  • config una cartella contenente i file di configurazione di MariaDB, MySQL e PostgreSQL. E' suddivisa in sottodirectory chiamate 'sXX', dove XX è lo scale factor.
  • dbt3_mysql una cartella contenente i file necessari per preparare i database di DBT3 e le query di test da eseguire con MySQL e MariaDB
  • tests una cartella contenente le varie configurazioni dei test. Dentro ci sono le seguenti subdirectory:
    • db_conf qui si trovano i file di configurazione dei database.
    • queries_conf qui ci sono i file di configurazione delle varie query.
    • results_db_conf qui si trova la configurazione del database dei risultati
    • test_conf qui ci sono le configurazioni dei test
    • launcher.pl uno script in Perl che automatizza il test. I dettagli su come invocarlo e sulle sue funzionalità si trovano più avanti in questa pagina.

Preparare il workload e le query

Ai fini del benchmark da DBT3-1.9, si necessita soltanto di DBGEN e QGEN. DBGEN è lo strumento che genera il workload per i test e QGEN è lo strumento che genera le query da usare nel test.

  1. Recarsi su http://sourceforge.net/projects/osdldbt/files/dbt3/
  1. Scaricare l'archivio di DBT3 1.9 nella cartella del progetto $PROJECT_HOME
  1. Decomprimere l'archivio nella cartella del progetto
    cd $PROJECT_HOME
    tar -zxf dbt3-1.9.tar.gz
  1. Copiare il file tpcd.h nella cartella dbt3. Questo passaggio comprende le etichette che sono necessarie a MySQL/MariaDB nel costruire le query.
    cp $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/tpcd.h $PROJECT_HOME/dbt3-1.9/src/dbgen/
  1. Copiare il file Makefile sotto $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/ nella cartella dbt3
  • NOTA: Questo passaggio deve essere eseguito solo se si desidera sovrascrivere il comportamento predefinito di QGEN. Dopo aver copiato questo Makefile e compilato il progetto, QGEN verrà impostato per generare le query per MariaDB/MySQL. Se si salta questo passaggio, per default QGEN genererà le query per PostgreSQL.
    cp $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/Makefile $PROJECT_HOME/dbt3-1.9/src/dbgen/
  1. Recarsi in $PROJECT_HOME/dbt3-1.9/src/dbgen e si compili il progetto
    cd $PROJECT_HOME/dbt3-1.9/src/dbgen
    make
  1. Impostare la variabile DSS_QUERY con la cartella contenente le query modello per MariaDB/MySQL o per PostgreSQL
    1. Se si desidera creare le query più adatte al dialetto di MariaDB/MySQL, si esegua il seguente comando:
      export DSS_QUERY=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/mysql_queries/
    2. Se si desidera usare i modelli predefiniti di PostgreSQL, si esegua il seguente comando:
      export DSS_QUERY=$PROJECT_HOME/dbt3-1.9/queries/pgsql/
  1. Creare una directory che conterrà le query così generate, in
    mkdir $PROJECT_HOME/gen_query
  1. Generare le query

NOTA: Gli esempi usano uno scale factor di 30. Se si desidera un SF diverso, si imposti il parametro -s

  • cd $PROJECT_HOME/dbt3-1.9/src/dbgen
    ./qgen -s 30 1 > $PROJECT_HOME/gen_query/s30-m/1.sql
    ./qgen -s 30 2 > $PROJECT_HOME/gen_query/s30-m/2.sql
    ./qgen -s 30 3 > $PROJECT_HOME/gen_query/s30-m/3.sql
    ./qgen -s 30 4 > $PROJECT_HOME/gen_query/s30-m/4.sql
    ./qgen -s 30 5 > $PROJECT_HOME/gen_query/s30-m/5.sql
    ./qgen -s 30 6 > $PROJECT_HOME/gen_query/s30-m/6.sql
    ./qgen -s 30 7 > $PROJECT_HOME/gen_query/s30-m/7.sql
    ./qgen -s 30 8 > $PROJECT_HOME/gen_query/s30-m/8.sql
    ./qgen -s 30 9 > $PROJECT_HOME/gen_query/s30-m/9.sql
    ./qgen -s 30 10 > $PROJECT_HOME/gen_query/s30-m/10.sql
    ./qgen -s 30 11 > $PROJECT_HOME/gen_query/s30-m/11.sql
    ./qgen -s 30 12 > $PROJECT_HOME/gen_query/s30-m/12.sql
    ./qgen -s 30 13 > $PROJECT_HOME/gen_query/s30-m/13.sql
    ./qgen -s 30 14 > $PROJECT_HOME/gen_query/s30-m/14.sql
    ./qgen -s 30 15 > $PROJECT_HOME/gen_query/s30-m/15.sql
    ./qgen -s 30 16 > $PROJECT_HOME/gen_query/s30-m/16.sql
    ./qgen -s 30 17 > $PROJECT_HOME/gen_query/s30-m/17.sql
    ./qgen -s 30 18 > $PROJECT_HOME/gen_query/s30-m/18.sql
    ./qgen -s 30 19 > $PROJECT_HOME/gen_query/s30-m/19.sql
    ./qgen -s 30 20 > $PROJECT_HOME/gen_query/s30-m/20.sql
    ./qgen -s 30 21 > $PROJECT_HOME/gen_query/s30-m/21.sql
    ./qgen -s 30 22 > $PROJECT_HOME/gen_query/s30-m/22.sql
  1. Generare le query explain:
    ./qgen -s 30 -x 1 > $PROJECT_HOME/gen_query/s30-m/1_explain.sql
    ./qgen -s 30 -x 2 > $PROJECT_HOME/gen_query/s30-m/2_explain.sql
    ./qgen -s 30 -x 3 > $PROJECT_HOME/gen_query/s30-m/3_explain.sql
    ./qgen -s 30 -x 4 > $PROJECT_HOME/gen_query/s30-m/4_explain.sql
    ./qgen -s 30 -x 5 > $PROJECT_HOME/gen_query/s30-m/5_explain.sql
    ./qgen -s 30 -x 6 > $PROJECT_HOME/gen_query/s30-m/6_explain.sql
    ./qgen -s 30 -x 7 > $PROJECT_HOME/gen_query/s30-m/7_explain.sql
    ./qgen -s 30 -x 8 > $PROJECT_HOME/gen_query/s30-m/8_explain.sql
    ./qgen -s 30 -x 9 > $PROJECT_HOME/gen_query/s30-m/9_explain.sql
    ./qgen -s 30 -x 10 > $PROJECT_HOME/gen_query/s30-m/10_explain.sql
    ./qgen -s 30 -x 11 > $PROJECT_HOME/gen_query/s30-m/11_explain.sql
    ./qgen -s 30 -x 12 > $PROJECT_HOME/gen_query/s30-m/12_explain.sql
    ./qgen -s 30 -x 13 > $PROJECT_HOME/gen_query/s30-m/13_explain.sql
    ./qgen -s 30 -x 14 > $PROJECT_HOME/gen_query/s30-m/14_explain.sql
    ./qgen -s 30 -x 15 > $PROJECT_HOME/gen_query/s30-m/15_explain.sql
    ./qgen -s 30 -x 16 > $PROJECT_HOME/gen_query/s30-m/16_explain.sql
    ./qgen -s 30 -x 17 > $PROJECT_HOME/gen_query/s30-m/17_explain.sql
    ./qgen -s 30 -x 18 > $PROJECT_HOME/gen_query/s30-m/18_explain.sql
    ./qgen -s 30 -x 19 > $PROJECT_HOME/gen_query/s30-m/19_explain.sql
    ./qgen -s 30 -x 20 > $PROJECT_HOME/gen_query/s30-m/20_explain.sql
    ./qgen -s 30 -x 21 > $PROJECT_HOME/gen_query/s30-m/21_explain.sql
    ./qgen -s 30 -x 22 > $PROJECT_HOME/gen_query/s30-m/22_explain.sql

Ora, le query generate per i test su MariaDB/MySQL sono pronte e si trovano nella directory $PROJECT_HOME/gen_query/s30-m/ (-m sta per MariaDB/MySQL).

Un'organizzazione ulteriore delle directory è lasciata all'utente.

  1. Creare una directory per il workload da generare
    mkdir $PROJECT_HOME/gen_data/s30
  1. Impostare la variabile DSS_PATH con la cartella che conterrà i dati delle tabelle. Il workload dei test si troverà lì.
    export DSS_PATH=$PROJECT_HOME/gen_data/s30/
  1. Generare i dati delle tabelle
  • NOTA: L'esempio usa uno scale factor = 30. Per modificarlo, ci si serva del parametro -s.
    ./dbgen -vfF -s 30
  • Ora i dati generati si trovano nella cartella impostata in $DSS_PATH = $PROJECT_HOME/gen_data/

Ai fini di questo benchmark, questi passaggi sono stati eseguiti con uno scale factor di 30 e si trovano su facebook-maria1 ai seguenti percorsi:

  • /benchmark/dbt3/gen_data/s30 i dati con uno scale factor di 30
  • /benchmark/dbt3/gen_query/s30-m query generate per MariaDB/MySQL con scale factor 30
  • /benchmark/dbt3/gen_query/s30-p query generate per PostgreSQL con scale factor 30

Si veda Esempio di tempi di preparazione di DBT3 per sapere quanto tempo è necessario per preparare i database per i test.

Scaricare MySQL 5.5.x

  1. Si scarichi il file tar.gz nella cartella del progetto $PROJECT_HOME/bin/
  1. Si scompatti l'archivio con il comando seguente:
    gunzip < mysql-5.5.x-linux2.6-x86_64.tar.gz |tar xf -

Ora il server si dovrebbe avviare con questo comando:

$PROJECT_HOME/bin/mysql-5.5.x-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &

Scaricare MySQL 5.6.x

  1. Si scarichi il file tar.gz nella cartella del progetto $PROJECT_HOME/bin/
  1. Si scompatti l'archivio con il comando seguente:
    gunzip < mysql-5.6.x-m5-linux2.6-x86_64.tar.gz |tar xf -

Ora il server si dovrebbe avviare con questo comando:

$PROJECT_HOME/bin/mysql-5.6.x-m5-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &

Scaricare e compilare MariaDB 5.3.x / MariaDB 5.5.x

NOTA: Questi passaggi si applicano anche a MariaDB 5.5.x, cambiando soltanto il numero della versione

  1. Si scarichi con Bazaar il progetto mariadb 5.3
    bzr branch lp:maria/5.3
    mv 5.3/ mariadb-5.3
  1. Si compili MariaDB
    cd mariadb-5.3/
    ./BUILD/compile-amd64-max
  1. Si crei un file tar.gz
    ./scripts/make_binary_distribution
  1. Si sposti il file tar.gz così generato e lo si scompatti in $PROJECT_HOME/bin, dove verrà usato dallo script di automazione
    mv mariadb-5.3.x-beta-linux-x86_64.tar.gz $PROJECT_HOME/bin/
    cd $PROJECT_HOME/bin/
    tar -xf mariadb-5.3.x-beta-linux-x86_64.tar.gz

Ora il server si dovrebbe avviare con il seguente comando:

$PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64/bin/mysqld_safe --datadir=some/data/dir &

Preparare i database per i benchmark

NOTA: Queste istruzioni sono identiche per MariaDB, MySQL 5.5.x e MySQL 5.6.x, modificando solo le directory dei database, qui chiamate $DB_HOME (per esempio, per MySQL 5.5.x, $DB_HOME è $PROJECT_HOME/bin/mysql-5.5.x-linux2.6-x86_64). Inoltre si possono preparare i database di test dello Storage Engine InnoDB. Le istruzioni per preparare PostgreSQL si trovano nella sezione per scaricare, compilare e preparare PostgreSQL, più avanti in questa pagina.

  1. Si apra il file $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_innodb.sql e si modifichino i valori nelle chiamate ai comandi SQL che assomigliano al seguente:
    LOAD DATA INFILE '/some/path/to/gen_data/nation.tbl' into table nation fields terminated by '|';
  • Sembrano tutti uguali, ma operano su tabelle diverse.
  • Si sostituisca "/some/path/to/gen_data/" con la directory corretta, in cui si trovano i dati generati. Il nuovo comando dovrebbe assomigliare al seguente:
    LOAD DATA INFILE '~/benchmark/dbt3/gen_data/s30/nation.tbl' into table nation fields terminated by '|';
  1. Si crei un database MySQL vuoto in una cartella che verrà usata per il benchmark
    cd $DB_HOME
    ./scripts/mysql_install_db --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s30/load_mysql_myisam_my.cnf --basedir=$DB_HOME --datadir=$PROJECT_HOME/db_data/myisam-s30/
  • NOTA: Per InnoDB si modifichino i defaults-file in load_mysql_innodb_my.cnf.
  1. Si avvii il processo mysqld ./bin/mysqld_safe --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s30/load_mysql_myisam_my.cnf --tmpdir=$PROJECT_HOME/temp/ --socket=$PROJECT_HOME/temp/mysql.sock --datadir=$PROJECT_HOME/db_data/myisam-s30/ &
  • NOTA: Per InnoDB si modifichino i defaults-file in load_mysql_innodb_my.cnf. Inoltre ci si accerti di avere abbastanza spazio nella directory impostata nel parametro --tmpdir, perché il caricamento del database richiede molto spazio temporaneo.
  1. Si carichino i dati nel database eseguendo il file make-dbt3-db_pre_create_PK.sql (per InnoDB) o make-dbt3-db_post_create_PK.sql (per MyISAM)./bin/mysql -u root -S $PROJECT_HOME/temp/mysql.sock < $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_post_create_PK.sql
  • NOTA: Per velocizzare la creazione, si raccomanda di usare make-dbt3-db_pre_create_PK.sql per caricare i database InnoDB e make-dbt3-db_post_create_PK.sql per i database MyISAM.
  1. Arrestare il server dei database:
    ./bin/mysqladmin --user=root --socket=$PROJECT_HOME/temp/mysql.sock shutdown 0

Ora si ha un database caricato con SF 30. La datadir è $PROJECT_HOME/db_data/myisam-s30/

Gli stessi passaggi possono essere ripetuti con SF differenti e per differenti Storage Engine.

Scaricare, compilare e preparare PostgreSQL

  1. Ci si rechi su http://www.postgresql.org/ftp/source/v9.1rc1/
  1. Si scarichi il file tramite il link postgresql-9.1rc1.tar.gz
  1. Si scompatti l'archivio nella cartella del progetto
    gunzip < postgresql-9.1rc1.tar.gz |tar xf -
  1. Per installare PostgreSQL si eseguano i seguenti comandi nella shell:
    mkdir $PROJECT_HOME/PostgreSQL_bin
    cd $PROJECT_HOME/postgresql-9.1rc1 
    ./configure --prefix=$PROJECT_HOME/bin/PostgreSQL_bin 
    make
    make install
  • NOTA: Lo script di configurazione potrebbe non essere in grado di trovare le seguenti librerie: readline e zlib. In questo caso si esegua la configurazione senza di esse, aggiungendo i seguenti parametri alla riga di comando: --without-readline --without-zlib
  1. Si prepari il database dei test:
    mkdir $PROJECT_HOME/db_data/postgre_s30
    cd $PROJECT_HOME/bin/PostgreSQL_bin
    ./bin/initdb -D $PROJECT_HOME/db_data/postgre_s30
  1. Si avvii il server:
    ./bin/postgres -D $PROJECT_HOME/db_data/postgre_s30 -p 54322 &
  1. Si carichi il dataload nel DB
    ./bin/createdb -O {UTENTE} dbt3 -p 54322
    ./bin/psql -p 54322 -d dbt3 -f $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_pg.sql
  • NOTA: Qui, al posto di {UTENTE}, occorre inserire il proprietario del database.
  1. Si arresti il server:
./bin/pg_ctl -D $PROJECT_HOME/db_data/postgre_s30/ -p 54322 stop

Le istruzioni per preparare il carico di lavoro per il benchmark su facebook-maria1 sono quasi pronte per MariaDB, MySQL e PostgreSQL. Ecco le directory per i vari DBMS, Storage Engine e scale factor che sono pronte in facebook-maria1:

  • ~/benchmark/dbt3/db_data/myisam_s30 dir dei dati per MariaDB/MySQL + MyISAM con scale factor 30
  • ~/benchmark/dbt3/db_data/innodb_mariadb_s30 dir dei dati per MariaDB + InnoDB con scale factor 30 (TODO)
  • ~/benchmark/dbt3/db_data/innodb_mysql_s30 dir dei dati per MySQL + InnoDB con scale factor 30 (TODO)
  • ~/benchmark/dbt3/db_data/postgre_s30 dir dei dati per PostgreSQL con scale factor 30 (TODO)

Preparare il database dei risultati

I risultati del benchmark verranno scritti in un database separato, che verrà eseguito da MariaDB 5.3.x.

NOTA: Il database dei risultati sarà soggetto a modifiche nelle versioni future del progetto DBT3.

Il database viene creato dal file $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-results-db.sql. In esso è possibile trovare tutti i dettagli riguardo alle tabelle e alle colonne del database.

Per preparare il database, si seguano queste istruzioni:

  1. Ci si porti nella directory di installazione di MariaDB 5.3.x
    cd $PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64
  1. Si installino le tabelle del database di sistema nella directory dei risultati (per esempio $PROJECT_HOME/db_data/dbt3_results_db)
    ./scripts/mysql_install_db --datadir=$PROJECT_HOME/db_data/dbt3_results_db
  1. Si avvii il mysqld del db dei risultati
    ./bin/mysqld_safe --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/mariadb_my.cnf --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock  --datadir=$PROJECT_HOME/db_data/dbt3_results_db/ &
  1. Si installi il database
    ./bin/mysql -u root -P 12340 -S $PROJECT_HOME/temp/mysql_results.sock < $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-results-db.sql
  1. Si arresti il server del db dei risultati:
    ./bin/mysqladmin --user=root --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock shutdown 0

Script di automazione

Configurare ed eseguire un benchmark

Per poter eseguire un benchmark, occorre avere:

Per ognuna di esse, si troveranno ulteriori dettagli nelle sezioni seguenti.

Ogni benchmark si configura tramite un insieme di file. Quelli di esempio (predefiniti) si trovano sotto la directory 'mariadb-tools/dbt3_benchmark/tests'. Ogni file di configurazione utilizza la sintassi 'ini' e viene letto dallo script di automazione in Perl con il modulo CPAN Config::Auto

Parole chiave della configurazione

Ogni file di configurazione potrebbe contenere delle parole chiave che verranno poi sostituite dallo script con dei valori particolari. Esse possono essere usate per comodità, allo scopo di rendere i file più riutilizzabili all'interno dell'ambiente del benchmark. Queste parole chiave sono:

  • $PROJECT_HOME è la directory in cui si trova il progetto 'mariadb-tools', oppure un percorso base per l'intero progetto (esempio: "DBMS_HOME = $PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64"). Viene sostituita con il valore del parametro di avvio 'project-home', passato a launcher.pl,
  • $DATADIR_HOME è la directory in cui si trovano le directory dei dati del benchmark (esempio: "$DATADIR_HOME/myisam-s30"). Viene sostituita con il valore del parametro di avvio 'datadir-home', passato a launcher.pl.
  • $QUERIES_HOME è la directory in cui si trovano le query (esempio: "$QUERIES_HOME/s30-m" sono le query per MariaDB/MySQL con scale factor 30). Viene sostituita con il valore del parametro di avvio 'queries-home', passato a launcher.pl.
  • $SCALE_FACTOR è lo scale factor prescelto. Solitamente è parte della directory dei dati (esempio: "$DATADIR_HOME/myisam-s$SCALE_FACTOR"), di quella delle query (esempio: "$QUERIES_HOME/s$SCALE_FACTOR-m") e delle cartella di configurazione del database (esempio: $PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s$SCALE_FACTOR). Viene sostituita con il valore del parametro di avvio 'scale-factor', passato a launcher.pl.

Si noti che se anche uno solo dei file usa una di queste parole chiave, il parametro corrispondente di launcher.pl diviene obbligatorio.

Configurazione di alto livello

Un file di configurazione di alto livello definisce i percorsi dei di configurazione Test, DBMS, Queries e Results database.

Questi sono i file predefiniti nella directory mariadb-tools/dbt3_benchmark/tests/ e contengono le seguenti impostazioni:

ParameterDescription
RESULTS_DB_CONFIGIl file di configurazione per il DB dei risultati
TEST_CONFIGIl file di configurazione per i test
QUERIES_CONFIGIl file di configurazione per le query
DB_CONFIGIl file di configurazione per per il server del DBMS

Questo file ha il seguente formato:

[common]
RESULTS_DB_CONFIG  = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/results_db_conf/results_db.conf
TEST_CONFIG        = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/test_conf/test_myisam.conf

[mariadb_5_3]
QUERIES_CONFIG 	= $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries.conf
DB_CONFIG 	= $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam.conf

[mysql_5_5]
QUERIES_CONFIG 	= $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries_mysql.conf
DB_CONFIG 	= $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mysql_5_5_myisam.conf
...

NOTA: Le impostazioni RESULTS_DB_CONFIG e TEST_CONFIG devono trovarsi nella sezione [common]. Esse servono all'intero test (debbene alcune impostazioni di TEST_CONFIG possano essere sovrascritte nel file QUERIES_CONFIG). Tutte le impostazioni che combinano QUERIES_CONFIG e DB_CONFIG dovrebbero essere in una sezione separata (esempio: [mariadb_5_3]).

Una configurazione viene passata allo script di configurazione con il parametro --test=/path/to/some_test_configuration.conf (si veda #parametri-di-avvio-dello-script)

DBMS server configuration

These configuration files contain settings that describe the benchmarked DBMS. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/db_conf.

Here is the list of parameters that could be set into this configuration file:

ParameterDescription
DBMS_HOMEWhere the instalation folder of MariaDB / MySQL / PostgreSQL is located.

NOTE: The automation script uses "./bin/mysqld_safe" to start the mysqld process. So the versions of MariaDB and MySQL should be a "binary distribution" ones.
DBMS_USERThe database user that will be used.
CONFIG_FILEThe config file that mysqld or postgres will use when starting
SOCKETThe socket that will be used to start the server
PORTThe port that the server will be started on
HOSTThe host where the server is located
DATADIRWhere the datadir for mysqld or postgres is located
TMPDIRWhere the temp tables will be created while sorting and grouping.
DBNAMEThe database (schema) name where the benchmark tables are located.
KEYWORDThis text will be stored into the results database as a keyword. Also will be used as a name for a subfolder with results and statistics.
DBMSDatabase Management System that will be used. Possible values: "MySQL", "MariaDB" and "PostgreSQL"
STORAGE_ENGINEThe storage engine that was used (MyISAM, InnoDB, etc.)
STARTUP_PARAMSAny startup parameters that will be used while starting the mysqld process or postgres process. Same format as given on the command line.
GRAPH_HEADINGThe heading of the graphic for that particular test.
MYSQL_SYSTEM_DIRSee "MYSQL_SYSTEM_DIR note", below.
READ_ONLYIf set to 1, mysqld process will be started with '--read-only' startup parameter
PRE_RUN_SQLSQL commands that are run prior each query run
POST_RUN_SQLSQL commands that are run after each query run
PRE_TEST_SQLSQL commands that are run prior the whole test with that database settings
POST_TEST_SQLSQL commands that are run after the whole test with that database settings

MYSQL_SYSTEM_DIR note:

This option is added for convenience when you want to save time and disk space for generating databases for different DBMS (and different versions) and use a single data directory for all of them. When running different versions of MariaDB/MySQL over a single datadir, one should run mysql-upgrade in order to fix the system tables. So in one data directory, you could prepare the following directories for different MariaDB/MySQL system directories:

  • mysql_mysql_5_5 a copy of the system directory 'mysql' upgraded by MySQL 5.5.x
  • mysql_mariadb_5_3 a copy of the system directory 'mysql' upgraded by MariaDB 5.3.x
  • mysql_mariadb_5_5 a copy of the system directory 'mysql' upgraded by MariaDB 5.5.x

If MYSQL_SYSTEM_DIR is set to one of these directories, the automation script will unlink the current system directory 'mysql' and make a new symbolic link with that name to the one in the setting.

Here is an example command that will be executed:

unlink /path/to/datadir/mysql
ln -s /path/to/value/in/MYSQL_SYSTEM_DIR/mysql_mariadb_5_3 /path/to/datadir/mysql


NOTE: This approach is suitable for MyISAM tests.

The configuration file looks like this:

[db_settings]
DBMS_HOME	= $PROJECT_HOME/bin/mariadb-5.3.2-beta-linux-x86_64
DBMS_USER	= root
...

Note that the section [db_settings] is required for the file to be properly parsed by the automation script.

Test configuration

These configuration files contain settings describing the test. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/test_conf.

Here is the list of parameters that could be set into this configuration file:

ParameterDescription
QUERIES_AT_ONCEIf set to 1, then all the queries are executed sequentially without restarting the server or clearing the caches between queries.
CLEAR_CACHESIf set to 1, the disk caches will be cleared before each query test.
WARMUPPerform a warm-up runs before running the query.
EXPLAINRun an Explain command prior the run of the query. The explain results will be stored in a file under the results output directory.
RUNPerform the actual test
ANALYZE_EXPLAINA result extraction mechanism where only the best execution plan (results from EXPLAIN select) will be measured. It is designed to be used when benchmarking InnoDB storage engine where execution plan is changing between server restarts (see #results-extraction-mechanisms).
MIN_MAX_OUT_OF_NA result extraction mechanism where the minimal and maximal values out of N (set by the parameter NUM_TESTS) tests are taken as a result. This could be used when InnoDB storage engine is tested (see #results-extraction-mechanisms).
SIMPLE_AVERAGEA result extraction mechanism where the final result is the average time taken for the tests. The number of tests is per query is set by the NUM_TESTS parameter. Note that if even one test has timed out, the result is 'time-out'. This is used when testing MyISAM storage engine since there the execution plan is constant (see #results-extraction-mechanisms).
NUM_TESTSHow many tests should be performed for each query. When ANALYZE_EXPLAIN is set, this value could be set to 0, meaning that the tests will continue until enough results are extracted (see setting CLUSTER_SIZE). This parameter is very important when MIN_MAX_OUT_OF_N or SIMPLE_AVERAGE is selected.
MAX_SKIPPED_TESTSWhen ANALYZE_EXPLAIN is set and an execution plan that is slower is selected, the execution of the query is skipped and the server is restarted in order to change the execution plan. If the server is restarted more than MAX_SKIPPED_TESTS, there are obviously no more different execution plans and the script continues to the next query benchmark.
WARMUPS_COUNTHow many warmup runs will be performed prior the actual benchmark run.
CLUSTER_SIZEHow big a cluster with results for a query should be in order to extract the final result. It is used when ANALYZE_EXPLAIN is selected as a result extraction method.
MAX_QUERY_TIMEThe maximum time that one query will be tested. Currently it is applicable only when ANALYZE_EXPLAIN is selected.
TIMEOUTThe maximum time that one query could run. Currently timeout is applicable only for MySQL and MariaDB.
OS_STATS_INTERVALWhat is the time interval between extraction of OS statistics for CPU, memory, etc.
PRE_RUN_OSOS commands that should be executed prior each query run
POST_RUN_OSOS commands that should be executed after each query run
PRE_TEST_OSOS commands that should be executed prior the whole test
POST_TEST_OSOS commands that should be executed after the whole test is complete

The configuration file looks like this:

QUERIES_AT_ONCE = 0
CLEAR_CACHES	= 1
WARMUP		= 0
...

Queries configuration

These configuration files contain the list of all the queries that will be benchmarked against each database. Some settings from DBMS server configuration and Test configuration could be overridden into the Queries configuration files. The folder that contains such configurations is mariadb-tools/dbt3_benchmark/tests/queries_conf.

Here is the list of parameters that could be set into this configuration file:

ParameterDescription
QUERIES_HOMEWhere the queries are located on disk. This value is concatenated to the QUERY setting and this makes the path to the particular query. NOTE: This setting should be set under the section [queries_settings].
CONFIG_FILEThis overrides the startup setting CONFIG_FILE from DMBS server configuration file and sets the database configuration file that is used. It could be used if some configuration file without any optimizations should be set for this particular queries configuration file.

NOTE: This setting should be set under the section [queries_settings].
QUERYThe name of the query located into QUERIES_HOME folder. E.g. "1.sql"
EXPLAIN_QUERYThe name of the explain query into QUERIES_HOME folder. E.g. "1_explain.sql"
TMPDIRThis overrides the setting TMPDIR from the DMBS server configuration.
STARTUP_PARAMSThis overrides the setting STARTUP_PARAMS from the DMBS server configuration. Using this setting one could change the particular startup parameters (like optimizations and buffers) for the DB server.
PRE_RUN_SQLThis overrides the setting PRE_RUN_SQL from the DMBS server configuration.
POST_RUN_SQLThis overrides the setting POST_RUN_SQL from the DMBS server configuration.
RUNThis overrides the setting RUN from the test configuration.
EXPLAINThis overrides the setting EXPLAIN from the test configuration.
TIMEOUTThis overrides the setting TIMEOUT from the test configuration.
NUM_TESTSThis overrides the setting NUM_TESTS from the test configuration.
MAX_SKIPPED_TESTSThis overrides the setting MAX_SKIPPED_TESTS from the test configuration.
WARMUPThis overrides the setting WARMUP from the test configuration.
WARMUPS_COUNTThis overrides the setting WARMUPS_COUNT from the test configuration.
MAX_QUERY_TIMEThis overrides the setting MAX_QUERY_TIME from the test configuration.
CLUSTER_SIZEThis overrides the setting CLUSTER_SIZE from the test configuration.
PRE_RUN_OSThis overrides the setting PRE_RUN_OS from the test configuration.
POST_RUN_OSThis overrides the setting POST_RUN_OS from the test configuration.
OS_STATS_INTERVALThis overrides the setting OS_STATS_INTERVAL from the test configuration.

The queries configuration file could look like this:

[queries_settings]
QUERIES_HOME = /path/to/queries

[query1]
QUERY=1.sql
EXPLAIN_QUERY=1_explain.sql
STARTUP_PARAMS=

[query2]
QUERY=2.sql
EXPLAIN_QUERY=2_explain.sql
STARTUP_PARAMS=--optimizer_switch='mrr=on' --mrr_buffer_size=8M --some_startup_parmas
...

...where "QUERIES_HOME = /path/to/queries" could be replaced with "QUERIES_HOME = $QUERIES_HOME/s$SCALE_FACTOR-m" for example and thus $QUERIES_HOME and $SCALE_FACTOR will be replaced by the script startup parameters passed to launcher.pl (see #script-startup-parameters)

NOTE: The section [queries_settings] is required for the configuration file to be parsed correctly. Also each query settings should be set under an uniquely named configuration section (e.g. [query1] or [1.sql])

Results database configuration

These configuration files contain settings about the database where the results will be stored. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/results_db_conf.

Here is the list of parameters that could be set into this configuration file:

ParameterDescription
DBMS_HOMEWhere the database directory is located. E.g. "$PROJECT_HOME/mariadb-5.3.x-beta-linux-x86_64". This should be a binary distribution of MariaDB or MySQL.
DBMS_USERThe user that will be used by the DBMS
DATADIRWhere the data directory is located for the results database
CONFIG_FILEWhat the configuration file used by the database is.
SOCKETThe socket that will be used by the results database. This should be different socket than the one provided for the testing databases.
PORTThe port that the results database will use. This should be different port than the one provided for the testing databases.
STARTUP_PARAMSAny startup parameters that should be set to start the server.
DBNAMEThe database name to use.
HOSTThe host where the results database is.

The results database configuration could look like this:

DBMS_HOME	= $PROJECT_HOME/mariadb-5.3.x-beta-linux-x86_64
DBMS_USER	= root
...

Script startup parameters

launcher.pl could accept startup parameters called in the manner "--some-param". Note that these startup parameters are case-sensitive. The ones that are with upper-case are used when overriding a setting in some of the configuration files.

Here is a list of the startup parameters:

ParameterDescription
testThe top-level benchmark configuration file that will be run. This is a required startup parameter.
results-output-dirWhere the results of the benchmark will be stored. A timestamp is automatically attached to the directory name so that it keeps track of time and date of the benchmark. This is a required parameter.
dry-runIf set, no benchmark will be performed. Instead only messages will be displayed for the actions that were intended to be done.
project-homeRequired if any configuration file uses the variable '$PROJECT_HOME'. If all configuration files use absolute paths, not used.
datadir-homeThe value in this parameter will replace any occurrences of the string '$DATADIR_HOME' into the configuration files. If there are no such occurances, it is not a required parameter.
queries-homeThe value in this parameter will replace any occurrences of the string '$QUERIES_HOME' into the configuration files. If there are no such occurances, it is not a required parameter.
scale-factorThe value in this parameter will replace any occurrences of the string '$SCALE_FACTOR' into the configuration files. If there are no such occurances, it is not a required parameter.
CLEAR_CACHESIf set. this overrides the default setting set into the test configuration file.
QUERIES_AT_ONCEIf set. this overrides the default setting set into the test configuration file.
RUNIf set. this overrides the default setting set into the test configuration file.
EXPLAINIf set. this overrides the default setting set into the test configuration file.
TIMEOUTIf set. this overrides the default setting set into the test configuration file.
NUM_TESTSIf set. this overrides the default setting set into the test configuration file.
MAX_SKIPPED_TESTSIf set. this overrides the default setting set into the test configuration file.
WARMUPIf set. this overrides the default setting set into the test configuration file.
WARMUPS_COUNTIf set. this overrides the default setting set into the test configuration file.
MAX_QUERY_TIMEIf set. this overrides the default setting set into the test configuration file.
CLUSTER_SIZEIf set. this overrides the default setting set into the test configuration file.
PRE_RUN_OSIf set. this overrides the default setting set into the test configuration file.
POST_RUN_OSIf set. this overrides the default setting set into the test configuration file.
OS_STATS_INTERVALIf set. this overrides the default setting set into the test configuration file.

Results extraction mechanisms

There are three possible result extraction mechanisms. They are set by the parameters set into the test configuration file:

  • ANALYZE_EXPLAIN
  • MIN_MAX_OUT_OF_N
  • SIMPLE_AVERAGE

Only one of these should be set to true (1).

ANALYZE_EXPLAIN is used for benchmarking InnoDB storage engine where the execution plan could change for the same query when the server is restarted. It is designed to run the query only with the fastest execution plan. This means that the server is restarted if the current execution plan is proven slower than the other. As a final result is taken the result for the query plan that turns out to be fastest and there are at least CLUSTER_SIZE tests with it for that query. By setting the configuration parameter NUM_TESTS you can set a maximum test runs that when reached will get the best cluster's average time (even if it is less than CLUSTER_SIZE). Also when a timeout for that query (MAX_QUERY_TIME) is reached, the scoring mechanism will return the best available cluster result.

MIN_MAX_OUT_OF_N is also used for benchmarking InnoDB storage engine. As a result are stored the values for the fastest and the slowest run. It is assumed that when the execution plan has changed it has different execution plan and we are interested only in the min and max time.

SIMPLE_AVERAGE is used for benchmarking storage engines that do not change the execution plan between restarts like MyISAM. The final result is the average execution time from all the test runs for the query.

Results graphics

After each query test run, the result is stored into a file named results.dat located into {RESULTS_OUTPUT_DIR}. This file is designed to be easy to be read by the plotting program Gnuplot 4.4. It is divided into blocks, separated by several new lines. Each block starts with a comment line containing details for the current block of results.

Queries that have timed out have a value of 100000 so that they run out of the graphics and are cut off. Other queries have their real times (in seconds) starting from 0. The graphics is cut off on the y-axis on the longest time for completed test + 20%. For example if the longest time is 100 seconds, the graphics is cut-off to 120 seconds. Thus the timed out queries will be truncated by this limitation and will seem as really timed out.

During the test run, a gnuplot script file is generated with the necessary parameters for the graphics to be generated automatically. After each query test run is complete, the graphic is regenerated, so that the user can see the current results before the whole benchmark is complete. This file is called gnuplot_script.txt and is located into {RESULTS_OUTPUT_DIR}. The user can edit it to fine-tune the parameters or headings after the test is complete so that one could get the look and feel he/she wants for the final result.

Script output

Benchmark output

In the directory set by the parameter {RESULTS_OUTPUT_DIR} (example: /benchmark/dbt3/results/myisam_test_2011-12-08_191427/) there are the following files/directories:

  • A directory for each test, named as the parameter {KEYWORD} from the test configuration (example: mariadb-5-3-2)
  • cpu_info.txt the output of "/bin/cat /proc/cpuinfo" OS command
  • uname.txt the output of "uname -a" OS command
  • results.dat the results of each query execution in one file. This file will be used as a datafile for the gnuplot script. It also contains the ratio between the current test and the first one.
  • gnuplot_script.txt the Gnuplot script that renders the graphics.
  • graphics.jpeg the output graphics
  • A benchmark configuration file (example: myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf) copied from mariadb-tools/dbt3_benchmark/tests/
  • A results database configuration file (example: results_db.conf) copied from mariadb-tools/dbt3_benchmark/tests/results_db_conf/
  • A test configuration file (example: test_myisam.conf) copied from mariadb-tools/dbt3_benchmark/tests/test_conf/

Test output

In the subdirectory for the particular test, set by the parameter {KEYWORD} (example: /benchmark/dbt3/results/myisam_test_2011-12-08_191427/mariadb-5-3-2/), there are the following files:

  • pre_test_os_resutls.txt - the output of the OS commands (if any) executed before the first query run for that test
  • pre_test_sql_resutls.txt - the output of the SQL commands (if any) executed before the first query run for that test
  • post_test_os_resutls.txt - the output of the OS commands (if any) executed after the last query run for that test
  • post_test_sql_resutls.txt - the output of the SQL commands (if any) executed after the last query run for that test
  • all_explains.txt - a file containing all the explain queries, their startup parameters for the benchmark and the explain result
  • The config file (my.cnf) that was passed to mysqld or postgres (example: mariadb_myisam_my.cnf) copied from mariadb-tools/dbt3_benchmark/config/s$SCALE_FACTOR/
  • The queries configuration file (example: ''queries-mariadb.conf'') copied from mariadb-tools/dbt3_benchmark/tests/queries_conf/
  • The database configuration file (example: ''db_mariadb_5_3_myisam.conf'') copied from mariadb-tools/dbt3_benchmark/tests/db_conf/

Query output

For each query execution there are several files that are outputted by the automation script. They are all saved under the subdirectory set by the parameters {KEYWORD}:

  • Explain result - a file named '{query_name}_{number_of_query_run}_results.txt' (example: '1_explain.sql_1_results.txt' first test for 1_explain.sql)
  • Pre-run OS commands - OS commands, executed before the actual query run. Output is a file named 'pre_run_os_q_{query_name}_no_{number_of_query_run}_results.txt' (example: 'pre_run_os_q_1.sql_no_2_results.txt' second test for query 1.sql)
  • Pre-run SQL commands - SQL commands executed before the actual query run. Output is a file named 'pre_run_sql_q_{query_name}_no_{number_of_query_run}_results.txt'.
  • Post-run OS commands - OS commands, executed after the actual query run. Output is a file named 'post_run_os_q_{query_name}_no_{number_of_query_run}_results.txt'.
  • Post-run SQL commands - SQL commands executed after the actual query run. Output is a file named 'post_run_sql_q_{query_name}_no_{number_of_query_run}_results.txt'.
  • CPU utilization statistics: '{query_name}_no_{number_of_query_run}_sar_u.txt'
  • I/O and transfer rate statistics: '{query_name}_no_{number_of_query_run}_sar_b.txt'
  • Memory utilization statistics: '{query_name}_no_{number_of_query_run}_sar_r.txt'

Hooks

The automation script provides hooks that allow the user to add both SQL and OS commands prior and after each test. Here is a list of all possible hooks:

  • Pre-test SQL hook: it is set with the parameter PRE_TEST_SQL. Contains SQL commands that are run once for the whole test configuration before the first run. (Example: "use dbt3; select version(); show variables; show engines; show table status;")
  • Post-test SQL hook: it is set with the parameter POST_TEST_SQL. Contains SQL commands that are run once for the whole test configuration after the last run.
  • Pre-test OS hook: it is set with the parameter PRE_TEST_OS. Contains OS commands that are run once for the whole test configuration before the first run.
  • Post-test OS hook: it is set with the parameter POST_TEST_OS. Contains OS commands that are run once for the whole test configuration after the last run.
  • Pre-run SQL hook: it is set with the parameter PRE_RUN_SQL. Contains SQL commands that are run prior each query run. (Example: "flush status; set global userstat=on;")
  • Post-run SQL hook: it is set with the parameter POST_RUN_SQL. Contains SQL commands that are run after each query run. (Example: "show status; select * from information_schema.TABLE_STATISTICS;")
  • Pre-run OS hook: it is set with the parameter PRE_RUN_OS. Contains OS commands that are run once prior each query run.
  • Post-run OS hook: it is set with the parameter POST_RUN_OS. Contains OS commands that are run once after each query run.

The results of these commands is stored into the {RESULTS_OUTPUT_DIR}/{KEYWORD} folder (see #script-output)

Activities

Here are the main activities that this script does:

  1. Parse the configuration files and check the input parameters - if any of the required parameters is missing, the script will stop resulting an error.
  2. Collect hardware information - collecting information about the hardware of the machine that the benchmark is run. Currently it collects cpuinfo and uname. Results of these commands are stored into the results output directory set as an input parameter
  3. Loop through the passed test configurations

    For each passed in test configuration the script does the following:
    1. Start the results database server. The results of the test are stored into that database.
    2. Clears the caches on the server

      Clearing the caches is done with the following command:
      sudo /sbin/sysctl vm.drop_caches=3
      • NOTE: In order to clear the caches, the user is required to have sudo rights and the following line should be added to the sudoers file (edit it with "sudo vusudo" command):
        {your_username} ALL=NOPASSWD:/sbin/sysctl
    3. Start the database server
    4. Perform pre-test SQL commands. The results are stored under results_output_dir/{KEYWORD} folder and are called pre_test_sql_results.txt. {KEYWORD} is a unique keyword for the current database configuration.
    5. Perform pre-test OS commands. The results are stored under results_output_dir/{KEYWORD} folder and are called pre_test_os_results.txt.
      • NOTE: If in the test configuration the setting QUERIES_AT_ONCE is set to 0, then the server is restarted between each query run. Thus the steps 3.2, 3.3, 3.4 and 3.5 are executed only once right before step 3.6.2.
    6. Read all query configurations and execute the following for each of them:
      1. Check the test configuration parameters. If something is wrong with some required parameter, the program will exit resulting an error.
      2. Get the server version if that's the first run of the query
      3. Perform pre-run OS commands in shell. The results of these queries are stored into a file named pre_run_os_q_{QUERY}_no_{RUN_NO}_results.txt under results_output_dir/{KEYWORD} where {QUERY} is the query name (ex: 1.sql), {RUN_NO} is the sequential run number and {KEYWORD} is a unique keyword for the particular test configuration.
      4. Perform pre-run SQL queries. The results of these queries are stored into a file named pre_run_sql_q_{QUERY}_no_{RUN_NO}_results.txt under results_output_dir/{KEYWORD} where {QUERY} is the query name (ex: 1.sql), {RUN_NO} is the sequential run number and {KEYWORD} is a unique keyword for the particular test configuration.
      5. Perform warm-up runs if set into the test configuration file
      6. Perform actual test run and measure time.
        • During this step, a new child process is created in order to measure the statistics of the OS. Currently the statistics being collected are:
          • CPU utilization statistics. The command for this is:
            sar -u 0 2>null
          • I/O and transfer rate statistics. The command for this is:
            sar -b 0 2>null
          • Memory utilization statistics. The command for this is:
            sar -r 0 2>null
        • These statistics are measured every N seconds, where N is set with the OS_STATS_INTERVAL test configuration parameter.
        • The test run for MariaDB and MySQL has an implemented mechanism for cut-off when timeout exceeds. It is controlled with the TIMEOUT test parameter. Currently for PostgreSQL there is no such functionality and should be implemented in future versions.
      7. Execute the "explain" statement for that query.
        • NOTE: Running EXPLAIN queries with MySQL prior version 5.6.3 could result in long running queries since MySQL has to execute the whole query when there are nested selects in it. For MariaDB and PostgreSQL there is no such problem. The long-running explain queries are for queries #7, 8, 9, 13 and 15. For that reason in MySQL prior version 5.6.3 for these queries no EXPLAIN selects should be executed.
      8. Perform post-run SQL queries
      9. Perform post-run OS commands in shell
      10. Log the results into the results database
      11. A graphics with the current results is generated using Gnuplot
    7. Shutdown the database server.
    8. Perform post-test SQL commands. The results are stored under results_output_dir/{KEYWORD} folder and are called post_test_sql_results.txt.
    9. Perform post-test OS commands. The results are stored under results_output_dir/{KEYWORD} folder and are called post_test_os_results.txt.
    10. Stop the results database server

Script calling examples

  • Example call for MyISAM test for scale factor 30 and timeout 10 minutes:
    perl launcher.pl \
    --project-home=/path/to/project/home/ \
    --results-output-dir=/path/to/project/home/results/myisam_test \
    --datadir=/path/to/project/home/db_data/ \
    --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf \
    --queries-home=/path/to/project/home/gen_query/ \
    --scale-factor=30 \
    --TIMEOUT=600

...where /path/to/project/home is where the mariadb-tools project is located. This will replace all occurrences of the string "$PROJECT_HOME" in the configuration files (example: "TMPDIR = $PROJECT_HOME/temp/" will become "TMPDIR = /path/to/project/home/temp/").

--TIMEOUT overrides the timeout setting into the test configuration file to 10 minutes.

  • Example for InnoDB test for scale factor 30 with 2 hours timeout per query and 3 runs for each query:
    perl launcher.pl \
    --project-home=/path/to/project/home/ \
    --results-output-dir=/path/to/project/home/results/innodb_test \
    --datadir=/path/to/project/home/db_data/ \
    --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/innodb_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf \
    --queries-home=/path/to/project/home/gen_query/ \
    --scale-factor=30 \
    --TIMEOUT=7200 \
    --NUM_TESTS=3
  • If a newer version of MariaDB 5.5 is available:
    • copy or edit the DMBS server configuration file mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_5_myisam.conf and change the parameter DBMS_HOME to the new binary distribution. You can also edit KEYWORD and GRAPH_HEADING
  • If you want to add additional test in the MyISAM benchmark for MariaDB 5.3, but with another defaults-file (my.cnf):
    • copy or edit the DMBS server configuration file mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam.conf and change the parameter CONFIG_FILE to the new my.cnf
    • copy or edit the test configuration file mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf and add the new configuration settings:
      [mariadb_5_3_new_configuration]
      QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries-mariadb.conf
      DB_CONFIG 	= $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam_new_configuration.conf
  • If you want to add additional startup parameters for query 6 for MariaDB for example:
    • copy or edit the file mariadb-tools/dbt3_benchmark/tests/queries_conf/queries-mariadb.conf and add a parameter "STARTUP_PARAMS=--optimizer_switch='mrr=on' --mrr_buffer_size=96M" for example for the section for query 6.
    • copy or edit the test configuration file mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf to include the new queries configuration file

Results

MyISAM test

DBT3 benchmark for the following configuration:

Results page: DBT3 benchmark results MyISAM

InnoDB test

DBT3 benchmark for the following configuration:

Results page: DBT3 benchmark results InnoDB

PostgreSQL test

DBT3 benchmark for the following configuration:

Results page: (TODO)

Commenti

Sto caricando i commenti......