erkules

Ich nutze für einige Installationen bestimmte Pfade für das plugin-dir.
Bei dem Upgrade auf MySQL 5.5 wurde die Konfiguration nicht mehr gezogen.
Beim direkten Starten vom mysqld $CONF war wieder alles in Ordnung.
Es zeigte sich, dass der Fehler im mysqld_safe liegt.
mysqld_safe meint seit 5.5.? die Option plugin-dir parsen zu müssen.
Hierfür wurde die Funktion parse_arguments() erweitert.
#v+
177       --plugin-dir=*) PLUGIN_DIR="$val" ;;
#v-

Folgender Code greift auf die Variable zu (wenn die denn gesetzt wurde):
421 if [ -n "${PLUGIN_DIR}" ]; then
422   plugin_dir="${PLUGIN_DIR}"
423 else
424   # Try to find plugin dir relative to basedir
425   for dir in lib/mysql/plugin lib/plugin
426   do
427     if [ -d "${MY_BASEDIR_VERSION}/${dir}" ]; then
428       plugin_dir="${MY_BASEDIR_VERSION}/${dir}"
429       break
430     fi
431   done
432   # Give up and use compiled-in default
433   if [ -z "${plugin_dir}" ]; then
434     plugin_dir='/usr/local/mysql/lib/plugin'
435   fi
436 fi


Dummerweise wird die Funktion zum Parsen der Config erst später aufgerufen :/
488 parse_arguments `$print_defaults $defaults --loose-verbose mysqld server`
494 parse_arguments `$print_defaults $defaults --loose-verbose mysqld_safe safe_mysqld`
495 parse_arguments PICK-ARGS-FROM-ARGV "$@"

Ergo ist es egal was konfiguriert wurde. Da zum Zeitpunkt des ersten Code-Schnipsels $PLUGIN_DIR immer leer ist.

Bugreport: http://bugs.mysql.com/bug.php?id=63862

Für jene, welche einen schnellen Würgaround brauchen, einfach
 "--datadir=$DATADIR" "--plugin-dir=$plugin_dir" "$USER_OPTION"
durch
"--datadir=$DATADIR"  "$USER_OPTION"
ersetzen.

Viel Spaß
Erkan

Moinsen,

hier die Slides zu meinen MySQL Vorträgen auf der DOAG 2011.
Partitionieren ueber Rechnergrenzen hinweg und MySQL kann auch NoSQL.

Viel Spaß
Erkan

MySQL hat seit einiger Zeit Previews auf neue Funktionalitäten in MySQL zum Anschauen zur Verfügung gestellt.
Folgend schauen wir uns mysql-5.6.4-labs-innodb-memcached an. Die Grundidee ist, dass quasi an MySQL vorbei direkt auf die Storage Engine zugegriffen wird. So wird der Overhead des SQL Parsers/Optimisers, wie auch der des Verbindungsaufbau gespart.

Ist das Paket installiert, muß das memcached Plugin noch installiert werden. Vorab sind die Verwaltungstabellen - welche sich in scripts/innodb_memcached_config.sql befinden - zu installieren. (mysql < scripts/innodb_memcached_config.sql )
Ein Blick in die Datei verrät, dass das memcached Plugin ein eigenes Schema (innodb_memcache) zum Verwalten des Zugriffes von Datenbankabfragen benötigt/erstellt.
Die Tabelle containers:

mysql> show create table containers\G
*************************** 1. row ***************************
       Table: containers
Create Table: CREATE TABLE `containers` (
  `name` varchar(50) NOT NULL,
  `db_schema` varchar(250) NOT NULL,
  `db_table` varchar(250) NOT NULL,
  `key_columns` varchar(250) NOT NULL,
  `value_columns` varchar(250) DEFAULT NULL,
  `flags` varchar(250) NOT NULL DEFAULT '0',
  `cas_column` varchar(250) DEFAULT NULL,
  `expire_time_column` varchar(250) DEFAULT NULL,
  `unique_idx_name_on_key` varchar(250) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from containers;
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table  | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| aaa  | test      | demo_test | c1          | c2            | c3    | c4         | c5                 | PRIMARY                |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)

Das Skript innodb_memcached_config.sql hat nicht nur diese Tabelle erstellt sondern - wie auch bei den folgenen Tabellen - auch noch einen Eintrag eingefügt.
Jede Zeile in der Tabelle containers ist definiert auf welche Tabelle memcached zugreift. Die Spalte name dient hier dazu auch mehrere (Ziel)Tabellen zu definieren. Derzeit wird nur eine Tabelle unterstützt. Mir ist auch nicht klar wie via memcached mehr als eine Tabelle unterstützt werden soll. (Ich habe gehört, dass dies dadurch erreicht werden soll, indem die keys aus name:memcachekey bestehen werden sollen.)

db_schema/db_table verweisen auf das konkrete Schema/Tabelle.Die Spalten key_columns, value_columns, flags, cas_column, expire_time_column sind dem memcached Protokoll geschuldet. Auf dieses gehe ich - unter anderem wegen meiner Unwissenheit bezüglich des Protokolls - nicht ein. Die Spalte unique_idx_name_on_key enthält den Namen des Indexes (welcher UNIQUE sein muß) auf key_columns (hier c1).
So ist ersichtlich, dass Tabellen, welche via dem memcached Plugin zugreifbar sein sollen, dem obigem Format entsprechen müssen. Memcached ist ein simplers Key (key_colums) Value (value_columns) Store. Das memcached Plugin erlaubt unter value_columns mehrere Spalten anzugeben. Da das memcached Protokoll aber nur Key Value kann, bietet das Plugin an einen Separator zu definieren. So wird via memcached ein String (Value) übergeben welches das Plugin nach dem Trenner teilt und in die passenden Spalten schreibt. Zu definieren ist der Trenner in der Tabelle config_optioins:

mysql> show create table config_options\G
*************************** 1. row ***************************
       Table: config_options
Create Table: CREATE TABLE `config_options` (
  `name` varchar(50) NOT NULL,
  `value` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from config_options;
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+

Es ist davon auszugehen, dass hier noch weitere Konfigurationsmöglichkeiten kommen werden. Eine weitere Tabelle ist die cache_policies:

mysql> show create table cache_policies\G
*************************** 1. row ***************************
       Table: cache_policies
Create Table: CREATE TABLE `cache_policies` (
  `policy_name` varchar(40) NOT NULL,
  `get_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `set_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `delete_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `flush_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  PRIMARY KEY (`policy_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Während in der Tabelle containers noch angelegt ist auf mehrere Tabellen zuzugreifen, ist dies hier nicht vorgeshen, die Einstellungen pro Tabelle vorzunehmen.
Daher ist das als Konfiguration für alle Tabellen zu interpretieren.
Die genaue Konfiguration wie auch eine auführliche Einleitung ist hier zu finden.
Ärgerlich ist, dass Änderungen in den cache_policies erst nach einem Restart des Servers ziehen.

Doch bisher haben wir die Installation des Plugin versäumt:

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * from  INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='daemon_memcached'\G
*************************** 1. row ***************************
           PLUGIN_NAME: daemon_memcached
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: DAEMON
   PLUGIN_TYPE_VERSION: 50604.0
        PLUGIN_LIBRARY: libmemcached.so
PLUGIN_LIBRARY_VERSION: 1.3
         PLUGIN_AUTHOR: Jimmy Yang
    PLUGIN_DESCRIPTION: Memcached Daemon
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

Folgende Variablen sind zu setzen:

mysql> show global variables like 'daemon_memcached%';
+----------------------------------+------------------+
| Variable_name                    | Value            |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog   | OFF              |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path |                  |
| daemon_memcached_option          |                  |
| daemon_memcached_r_batch_size    | 1048576          |
| daemon_memcached_w_batch_size    | 32               |
+----------------------------------+------------------+

Es handelt sich hierbei um read-only Variablen. So sind diese auch nicht zur Laufzeit zu ändern. Interessant ist die Option daemon_memcached_w_batch_size. Zum Steigern der Performance wird nur alles 32 Änderungen committed. Will man nun (in der Verbindung) auch die noch nicht committetten Anweisungen sehen, ist der Transaktions Level anzupassen:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Fazit:

Also mir erschließt sich der Mehrwert der Memcached Erweiterung nicht. Handlersocket z.B. erlaubt den Zugriff auf jede und mehrere Tabellen der Applikation. Das memcached Plugin verlangt eine Tabelle nach einem festgelegtem Format. Die Usability ist bescheiden. Und wenn man meint durch folgendes den mysqld nicht restarten zu müssen, crasht mysqld eben :)

mysql> uninstall PLUGIN daemon_memcached;
Query OK, 0 rows affected (2.00 sec)

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
Query OK, 0 rows affected (0.00 sec)

Viel Spaß
Erkan

Beim Rumspielen mit MySQL 5.6 - von der es Previewversionen gibt - fiel auf, dass es neue Tabellen im I_S gibt:

5.5.13:

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+

5.6.2:

mysql> SHOW TABLES LIKE 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     |
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     |
| INNODB_METRICS                         |
| INNODB_SYS_INDEXES                     |
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      |
| INNODB_SYS_TABLES                      |
+----------------------------------------+

Betrachten wir uns die INNODB_SYS_FOREIGN% Tabellen. Diese erlauben es für InnoDB Tabellen einfach an die Foreign Keys (FK) heran zu kommen. In der Datenbank existiert ein FK kind -> papa.

mysql> select * from INNODB_SYS_FOREIGN;
+------------------+-----------+-----------+--------+------+
| ID               | FOR_NAME  | REF_NAME  | N_COLS | TYPE |
+------------------+-----------+-----------+--------+------+
| test/kind_ibfk_1 | test/kind | test/papa |      1 |    1 |
+------------------+-----------+-----------+--------+------+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_FOREIGN_COLS;
+------------------+--------------+--------------+-----+
| ID               | FOR_COL_NAME | REF_COL_NAME | POS |
+------------------+--------------+--------------+-----+
| test/kind_ibfk_1 | id2          | id           |   0 |
+------------------+--------------+--------------+-----+
1 row in set (0.00 sec)

Über INNODB_SYS_FOREIGN erhalten wir die verknüpften Tabellen. TYPE = 1 meint hier ON DELETE CASCADE. Imho nicht wirklich lesbar, vielleicht hätte man sich bei TYPE für den Typ SET entscheiden sollen :)

Die Tabelle INNODB_SYS_FOREIGN_COLS verrät uns die Spalten des FK.
Beide verknüpft:  

mysql> select concat(FOR_NAME,":",FOR_COL_NAME," -> ",REF_NAME,":",REF_COL_NAME)  as FK from INNODB_SYS_FOREIGN_COLS JOIN INNODB_SYS_FOREIGN using(ID);
+-------------------------------+
| FK                            |
+-------------------------------+
| test/kind:id2 -> test/papa:id |
+-------------------------------+

Folgend sehen wir wie dies für MySQL 5.[1,5,6].x auch ohne die neuen Tabellen (annähernd) erledigt werden kann.

mysql> select * from TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | kind_ibfk_1     | test         | kind       | FOREIGN KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
 
mysql> select * from KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='kind_ibfk_1'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: kind_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: kind
                  COLUMN_NAME: id2
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test
        REFERENCED_TABLE_NAME: papa
       REFERENCED_COLUMN_NAME: id

mysql> SELECT concat(A.TABLE_SCHEMA,'/',A.TABLE_NAME,':',A.COLUMN_NAME,' -> ',A.REFERENCED_TABLE_SCHEMA,'/',A.REFERENCED_TABLE_NAME,':',A.REFERENCED_COLUMN_NAME) FK from KEY_COLUMN_USAGE A JOIN TABLE_CONSTRAINTS USING(CONSTRAINT_NAME) WHERE TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY';
+-------------------------------+
| FK                            |
+-------------------------------+
| test/kind:id2 -> test/papa:id |
+-------------------------------+

Wenn sich jemand merkt, wofür die jeweiligen Werte der Type Spalte in 5.6.2 stehen, sind im Zugriff auf INNODB_SYS_FROREIG% mehr Informationen zu holen. Letzterer Ansatz hat nicht nur denVorteil auf MySQL 5.1.x und 5.5.x zu laufen, sondern auch FKs auf Tabellen zu entdecken, welche nicht InnoDB sind.



Viel Spaß
Erkan

Moin, morgen beginnt die DOAG 2011. Auf der werden einige Vorträge gehalten werden.
Zwei (1,2) von mir. 
Jene, welche sich dort aufhalten, werden gebeten aufzuschlagen.

Viel Spaß
Erkan :)

Wer sich gegenwärtig auf MySQL@Launchpad zugreifen will wird mit folgendem Schild konfrontiert:

 
Auch downloads.mysql.com ist (seit mind.) diesem Wochenende nicht verfügbar. Hierhin wird man geschickt, alsbald man  z.B.  die - heftig umworbenen - 5.6.x Features anschauen will.
Es soll Änderungen in der Infrastruktur von MySQL gegeben haben. So dass diese nun nicht mehr direkt zugänglich sind.  Aber mir ist hier keine Ankündigung bekannt.

[Update]
Beides ist wieder verfügbar.
Das nächste mal werde ich früher bloggen.
Versprochen! :)

Ich hatte schon länger vor mir mal Galera anzuschauen. Folgender Blog hatte mich daran erinnert es endlich zu machen.
Was ist Galera?
Galera verspricht synchrone Replikation und ein Multi-Master-Setup. So werden die Daten nicht nur sicher repliziert, nein Galera ist es auch egal in welche der Nodes (auch gleichzeitig) geschrieben wird.
Um dies zu bewerkstelligen wurde MySQL gepatcht. Wobei wir auch schon beim ersten Problem sind. Wer sich Galera (Version 0.8) anschauen will bekommt eine MySQL 5.1.53. Des weiteren repliziert Galera nur InnoDB/XtraDB-Tabellen. Da obiger Blog schon mit einem Howto kommt. Erspare ich mir diese Erklärung. Zudem ist die Installation des Demo-Tar-Balls straight forward.
Galera erlaubt unter anderem eine (nahezu) synchrone Replikation. Die erste Frage die sich stellen mag, ist ob das nicht etwas langsam ist? Mit MySQL 5.5.x gibt es die semisynchrone Replikation, welche zumindest in die Nähe der synchronen Replikation kommt. Zwar erlaubt Galera auch so schöne Sachen wie auf alle Nodes gleichzeitig zu schreiben, aber dies wird in einem späteren Blogpost fokusiert werden.
Getestet wurden MySQL 5.5.13 und Galera 0.8 (ships with MySQL 5.1.53).
Die Konfiguration richtetet sich nach der von Galera mitegelieferten Konfig. Einzig innodb_buffer_pool_size wurde auf 4GB gesetzt.

innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=4G
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

Für einen schnellen Test wurden je 100.000 Rows mit einer Concurrency von 1,8,16,32,64,128 und 256 in eine einfache Tabelle geschrieben.


Ein einfacher aber imho schon beeindruckender Test. So ist bietet sich Galera schon als performanterer Ersatz für semisynchrone Replikation an. Unterstützung für MySQL 5.5.x ist in der Mache und es ist schwer zu Hoffen, daß Galera in mind. einem der MySQL-Branches aufgenommen wird.

Viel Spaß
Erkan

Moinsen, ich nutze noch stark die 5.1.xer Schiene von MySQL. Zudem verwende ich keine Pakete der Distributionen. Beim Verarbeiten der Binärpakete von MySQL. Schrieben meine Installskripte - bei der 5.1.57 - plötzlich ins Errorlog:

WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED!
The "HELP" command might not work properly.

Genaueres ist hier nachzulesen. Da wurde die fill_help_tables.sql - von 5.1.56 auf 5.1.57 - um genauere Links erweitert. Nur dass diese dann zu lang für `url` char(128) NOT NULL in der Tabelle mysql.help_topic waren.

Es handelt sich "nur" um die Helpfiles ... aber trotzdem :)

Viel Spaß
Erkan

MariaDB soll laut folgender Page bei temporay tables schneller sein als MySQL. Da:

Um dies zu testen wurden folgende Versionen installiert:

• MySQL 5.5.12
• MySQL 5.1.57
• MariaDB 5.2.6
• MariaDB 5.1.55
  

Es wurden zwei Tabellen mit jeweils 10000 Rows erstellt:

mysql> desc sort_id;                                  
+-------+---------+------+-----+---------+-------+    
| Field | Type    | Null | Key | Default | Extra |    
+-------+---------+------+-----+---------+-------+    
| id    | int(11) | YES  |     | NULL    |       |    
+-------+---------+------+-----+---------+-------+    
1 row in set (0.00 sec)                               
                                                      
mysql> desc sort_1;                                   
+-------+---------+------+-----+---------+-------+    
| Field | Type    | Null | Key | Default | Extra |    
+-------+---------+------+-----+---------+-------+    
| id    | int(11) | YES  |     | NULL    |       |    
| tea   | text    | YES  |     | NULL    |       |    
+-------+---------+------+-----+---------+-------+    
2 rows in set (0.00 sec)

Auf allen Servern wurde zudem tmp_table_size auf 1024 gesetzt um Temptables schnellstmöglich zu erzwingen.
Daraufhin wurde mit mysqlslap die Query SELECT * FROM sort_id group by id 20x3 mal ausgeführt. Dies geschah auch für die Tabelle sort_1.
Hier erst mal der EXPLAIN zu den Queries:

mysql> explain SELECT * from sbtest.sort_id group by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sort_id
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10000
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain SELECT * from sbtest.sort_1 group by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sort_1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10000
        Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

Die (aufgehübschte) Ausgabe sah für SELECT * from sort_1 group  by id; wiefolgt aus:

Benchmark [[5.5.12]]
        Average number of seconds to run all queries: 1.803 seconds
        Minimum number of seconds to run all queries: 1.791 seconds
        Maximum number of seconds to run all queries: 1.826 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark [[MariaDB 5.2.6]]
        Average number of seconds to run all queries: 5.547 seconds
        Minimum number of seconds to run all queries: 5.542 seconds
        Maximum number of seconds to run all queries: 5.556 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark [[5.1.57]]
        Average number of seconds to run all queries: 1.605 seconds
        Minimum number of seconds to run all queries: 1.603 seconds
        Maximum number of seconds to run all queries: 1.606 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark [[MariaDB 5.1.55]]
        Average number of seconds to run all queries: 5.283 seconds
        Minimum number of seconds to run all queries: 5.214 seconds
        Maximum number of seconds to run all queries: 5.344 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20



Die (aufgehübschte) Ausgabe sah für SELECT * from sort_id group  by id; wiefolgt aus:



Benchmark [[5.5.12]]
        Average number of seconds to run all queries: 1.146 seconds
        Minimum number of seconds to run all queries: 1.135 seconds
        Maximum number of seconds to run all queries: 1.170 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark  [[MariaDB 5.2.6]]
        Average number of seconds to run all queries: 4.359 seconds
        Minimum number of seconds to run all queries: 4.352 seconds
        Maximum number of seconds to run all queries: 4.366 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark [[5.1.57]]
        Average number of seconds to run all queries: 1.049 seconds
        Minimum number of seconds to run all queries: 1.032 seconds
        Maximum number of seconds to run all queries: 1.068 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20

Benchmark [[MariaDB 5.1.55]]
        Average number of seconds to run all queries: 4.261 seconds
        Minimum number of seconds to run all queries: 4.202 seconds
        Maximum number of seconds to run all queries: 4.341 seconds
        Number of clients running queries: 1
        Average number of queries per client: 20







Nutzt MariaDB wieder die MyISAM-Engine für die temporay tables ist laut Serg Golubchik die Performance wieder vergleichbar mit MySQL. Derzeit muss festgehalten werden, dass die Aussage MariaDB sei bei tmp-tables performanter nicht verifiziert werden kann.


Viel Spaß
Erkan

Ich selbst nutze unter anderem PowerDNS als DNS-Server. Im letzten Monat betrachtete ich mir dessen MySQL-Backend, da ich über zwei Kanäle darüber informiert wurde, dass PowerDNS mit dem Backend nicht skaliert.

Die DNS-Records werden in PowerDNS in zwei Tabellen abgelegt. Eine für die Domains:

create table domains (
id INT auto_increment,
name VARCHAR(255) NOT NULL,
master VARCHAR(128) DEFAULT NULL,
last_check INT DEFAULT NULL,
type VARCHAR(6) NOT NULL,
notified_serial INT DEFAULT NULL,
account VARCHAR(40) DEFAULT NULL,
primary key (id)
) Engine=InnoDB;

Und eine weitere für die Records:

CREATE TABLE records (
id int(11) NOT NULL auto_increment,
domain_id int(11) NOT NULL,
name varchar(255) NOT NULL,
type varchar(10) NOT NULL,
content varchar(255) NOT NULL,
ttl int(11) NOT NULL,
prio int(11) default NULL,
change_date int(11) default NULL,
PRIMARY KEY (id),
KEY name_index(name),
KEY nametype_index(name,type),
KEY domainid_index(domain_id)
);

Zudem existiert noch ein FK-Constraint, aber auch der tut hier nichts zur Sache.
Die Tabelle domains wurde mit 6.000.000 Datensätzen bestückt. Die Tabelle records wurde mit 46.195.356 Datensätzen bestückt. Ich denke damit wird schon ein größerer DNS-Server simuliert :D

Zwar schien es, als sollte man sich mal Gedanken über die Normalisierung ansich machen, doch das war nicht mein Skope. Es galt mit etwas Mikrotuning schon Erfolge zu erzielen. PowerDNS pdns-3.0-rc2 und folgende MySQL-Version kam zum Einsatz:

[pdns]> SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 5.2.5-MariaDB-log |
+-------------------+
1 row in set (0.00 sec)


Die Datenbank meinte die Tabellen würden folgenden Platzverbrauch haben.



> SELECT TABLE_NAME,INDEX_LENGTH,DATA_LENGTH from information_schema.TABLES where TABLE_NAME IN('records','domains');
+------------+--------------+-------------+
| TABLE_NAME | INDEX_LENGTH | DATA_LENGTH |
+------------+--------------+-------------+
| domains    |    475004928 |   431898624 |
| records    |  11372855296 |  5813305344 |
+------------+--------------+-------------+



Bei PowerDNS wurden alle Caches abgeschaltet (es galt die Datenbank zu testen!) und 494969 disjunkte DNS-Abfragen gestellt. Diese waren in 48.9 Sekunden durchgelaufen, was ca. 10114 qps entspricht. (Für jeden Test wurde die Datenbank restartet und der zweite Lauf genommen.)

Da Abfragen gegen die Tabelle records gehen,  nur diese Tabelle "optimiert"- Einige Änderungen sind analog in der Tabelle domains möglich.

Als erste Optimierung wurde ein redundanter Index entfernt.



drop  index `rec_name_index`  on records;



Hiernach wurden 10822 qps gemessen. Dies ist wohl nicht die Welt. Beim Platzverbrauch sieht es schon besser aus:


+------------+--------------+-------------+
| TABLE_NAME | INDEX_LENGTH | DATA_LENGTH |
+------------+--------------+-------------+
| domains    |    475004928 |   431898624 |
| records    |   6116343808 |  5813305344 |
+------------+--------------+-------------+


Die Spalte type speichert die Recordtypen. Aus dem VARCHAR wird im Index ein CHAR. Da die Menge der Werte begrenzt ist bietet sich hier ein ENUM an. Welches den Vorteil hat ein INT zu sein und zum anderen sicherstellt, dass nicht andere als die definierten Werte in die Tabelle kommen.

ALTER TABLE records   MODIFY  `type` enum('A','AAAA','SOA','NS','MX','CNAME','PTR','TXT');


Zugegeben, dies ist nur ein Subset der nötigen Recordtypen. Am Ergebnis wird dies nichts ändern. Nach dieser Änderung haben wir nun 10918 qps. Angenehmer ist die weitere Reduktion der Datengröße. Diesmal auch nicht nur bei den Indexdaten.




+------------+--------------+-------------+
| TABLE_NAME | INDEX_LENGTH | DATA_LENGTH |
+------------+--------------+-------------+
| domains    |    475004928 |   431898624 |
| records    |   5816451072 |  5696913408 |
+------------+--------------+-------------+



All diese Tests liefen mit  distributor-threads=32. Das ist eine Konfigurationseinstellung (im PowerDNS) für die Anzahl der Verbindungen, die PowerDNS zur Datenbank öffnet. Der Default liegt bei 3. Mit distributor-threads=3 erreichte ich lediglich 5656 qps.
Zu guter Letzt ändern wir noch den Index nametype_index. Die wenigsten FQDN nutzen die im RFC ermöglichten 255 Zeichen aus. Sprich hier lohnt sich ein prefix-Index. (Der alte wurde gedropt)



CREATE INDEX `nametype_index` on records(name(100),type);


Nun waren wir bei 10923 qps angelangt und was sagt der Platzverbrauch?

+------------+--------------+-------------+
| TABLE_NAME | INDEX_LENGTH | DATA_LENGTH |
+------------+--------------+-------------+
| domains    |    475004928 |   431898624 |
| records    |   3547332608 |  5696913408 |
+------------+--------------+-------------+

Sweet! Halten wir fest die Index_legth ist von 11372855296 Bytes auf 3547332608 Bytes reduziert worden. Damit wurden hier etwas über 7GB gespart \o/
An diesem Punkt angelangt wurden die records noch in PBXT geändert. Hierbei wurden 12375 qps erreicht:) Wobei der Platzverbrauch immens anstieg:

| records    |   5684629504 | 12380356432 |

Später wurde ich in #powerdns darauf hingewiesen, dass das verwendete Benchmarktool (dnsperf) auch mit einer längeren Queue ausgeführt werden kann. So wurden mit ./dnsperf -d /var/tmp/pdns.list -q 2000 -s localhost schnell 22994 qps erreicht.
Das ist selbstredend nur ein Anfang. Aber zeigt es doch, dass in vielen Projekten noch Steigerungspotential steckt. Von nicht skalieren kann aber nicht gesprochen werden. :)


Viel Spaß
Erkan