Agrégateur de flux

Hallo MySQL Gemeinde,

Im neuen iX (2012-10) hat es zwei nette Artikel über: MySQL HA-Lösungen (S. 116) und MySQL 5.6 (S. 66).

Viel Spass beim Lesen!

Taxonomy upgrade extras: High AvailabilityclusterMySQL Clustergalerareplikation

Taxonomy upgrade extras: High AvailabilityclusterMySQL Clustergalerareplikation

Hallo MySQL Gemeinde,

Im neuen iX (2012-10) hat es zwei nette Artikel über: MySQL HA-Lösungen (S. 116) und MySQL 5.6 (S. 66).

Viel Spass beim Lesen!

Taxonomy upgrade extras: monitoringgaleraplugincluster

Based on customer feedback we have decided to add a plugin Galera Cluster for MySQL to our MySQL Nagios/Icinga Plugins.

The module checks, if the node is in status Primary and if the expected amount of Galera Cluster nodes is available. If not, a warning or an alarm is returned.

The script is written in Perl and is Nagios Plugin API v3.0 compatible.

You can download it from our download page.

If you have suggestions for improvements, please contact us. Bugs can be reported at our bugs database.

The following modules are contained in the package:

• check_db_mysql.pl
• check_errorlog_mysql.pl
• check_galera_nodes.pl
• check_repl_mysql_cnt_slave_hosts.pl
• check_repl_mysql_hearbeat.pl
• check_repl_mysql_io_thread.pl
• check_repl_mysql_read_exec_pos.pl
• check_repl_mysql_readonly.pl
• check_repl_mysql_seconds_behind_master.pl
• check_repl_mysql_sql_thread.pl
• perf_mysql.pl

Aufgrund einer Kundenrückmeldung haben wir uns entschlossen unsere MySQL Nagios/Icinga Plugins um ein Plugin Galera Cluster für MySQL zu erweitern.

Das Modul prüft, ob der Knoten im Status Primary und ob die erwartete Anzahl von Galera Cluster Knoten vorhanden ist. Wenn nicht, wird eine Warnung oder ein Alarm zurückgemeldet.

Das Skript ist in Perl geschrieben und ist Nagios Plugin API v3.0 konform.

Ihr könnt es Euch von unserer Download Page herunterladen.

Wenn Ihr noch Verbesserungsvorschläge habt, bitte melden. Fehler können in unserer Bugs-Datenbank gemeldet werden.

Folgende Module sind im Paket enthalten:

• check_db_mysql.pl
• check_errorlog_mysql.pl
• check_galera_nodes.pl
• check_repl_mysql_cnt_slave_hosts.pl
• check_repl_mysql_hearbeat.pl
• check_repl_mysql_io_thread.pl
• check_repl_mysql_read_exec_pos.pl
• check_repl_mysql_readonly.pl
• check_repl_mysql_seconds_behind_master.pl
• check_repl_mysql_sql_thread.pl
• perf_mysql.pl

Taxonomy upgrade extras: galeramonitoringcluster

Aufgrund einer Kundenrückmeldung haben wir uns entschlossen unsere MySQL Nagios/Icinga Plugins um ein Plugin Galera Cluster für MySQL zu erweitern.

Das Modul prüft, ob der Knoten im Status Primary und ob die erwartete Anzahl von Galera Cluster Knoten vorhanden ist. Wenn nicht, wird eine Warnung oder ein Alarm zurückgemeldet.

Das Skript ist in Perl geschrieben und ist Nagios Plugin API v3.0 konform.

Ihr könnt es Euch von unserer Download Page herunterladen.

Wenn Ihr noch Verbesserungsvorschläge habt, bitte melden. Fehler können in unserer Bugs-Datenbank gemeldet werden.

Folgende Module sind im Paket enthalten:

• check_db_mysql.pl
• check_errorlog_mysql.pl
• check_galera_nodes.pl
• check_repl_mysql_cnt_slave_hosts.pl
• check_repl_mysql_hearbeat.pl
• check_repl_mysql_io_thread.pl
• check_repl_mysql_read_exec_pos.pl
• check_repl_mysql_readonly.pl
• check_repl_mysql_seconds_behind_master.pl
• check_repl_mysql_sql_thread.pl
• perf_mysql.pl

Taxonomy upgrade extras: galeramonitoringcluster

Taxonomy upgrade extras: galeramonitoringcluster

Aufgrund einer Kundenrückmeldung haben wir uns entschlossen unsere MySQL Nagios/Icinga Plugins um ein Plugin Galera Cluster für MySQL zu erweitern.

Das Modul prüft, ob der Knoten im Status Primary und ob die erwartete Anzahl von Galera Cluster Knoten vorhanden ist. Wenn nicht, wird eine Warnung oder ein Alarm zurückgemeldet.

Das Skript ist in Perl geschrieben und ist Nagios Plugin API v3.0 konform.

Ihr könnt es Euch von unserer Download Page herunterladen.

Wenn Ihr noch Verbesserungsvorschläge habt, bitte melden. Fehler können in unserer Bugs-Datenbank gemeldet werden.

Folgende Module sind im Paket enthalten:

• check_db_mysql.pl
• check_errorlog_mysql.pl
• check_galera_nodes.pl
• check_repl_mysql_cnt_slave_hosts.pl
• check_repl_mysql_hearbeat.pl
• check_repl_mysql_io_thread.pl
• check_repl_mysql_read_exec_pos.pl
• check_repl_mysql_readonly.pl
• check_repl_mysql_seconds_behind_master.pl
• check_repl_mysql_sql_thread.pl
• perf_mysql.pl

Taxonomy upgrade extras: trainingMySQL Clustermysql-training

The dates for the next MySQL Cluster (ndb) trainings with the Linux Hotel are scheduled now: March 11 - 12 2013 and September 23 - 24 2013.
You can book your training here.

Taxonomy upgrade extras: schulungMySQL Clustertrainingmysql-schulungmysql-training

Die Daten für die nächsten MySQL Cluster (ndb) Schulungen im Linux Hotel sind jetzt festgelegt: 11./12. März 2013 und 23./24. September 2013.
Sie können Ihre Schulung hier buchen.

Taxonomy upgrade extras: dwhvortragUnsere Folien zum Vortrag Wir bauen uns ein Data Warehouse mit MySQL stehen jetzt zur Verfügung unter Präsentationen.

Taxonomy upgrade extras: galeraclusterMySQL Clusterchannelfail-overreplication

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they are not available. With Galera v1.x that was not a problem because after a crash a SST would have happened and the corrupted InnoDB block are corrected. But now with IST in Galera v2.0 MySQL will start without noticing the corruption (as usual) and only an IST is performed. This leads to a running MySQL database with possibly corrupted InnoDB blocks. And this might cause you later troubles for example if this node is used as a donor. Then the corrupted page is inherited to other nodes (using rsync or Xtrabackup?). And in some bad cased then the whole Cluster could crash at once when hitting the corrupted page. Thanks to Monty W. for bringing this up!
  Recommendation is: Do NOT disable InnoDB double write buffer (innodb_doublewrite) with Galera Cluster >= v2.0 if your care about your data!
  
• The second discussion was about the event sequence in the binary-log (for those who where present: the A-B vs B-A discussion). Codership confirmed that the binary-log sequence on 2 different Galera nodes of the same Galera Cluster should be the same (everything else is considered to be a bug). As a result this leads to 2 different consequences:
  a) The binary-log of node B can be used for a PiTR of node A in case we need it. Finding the right position is a bit tricky and it needs some manual work on this (finding XID with binlog-pos of node B, then finding binlog-pos of node A with XID). But Codership told me they are planning a tool for automatizing this.
  b) The binary-log of node B can be used for a Channel fail-over in case we have 2 different Galera Clusters in 2 different data centers connect to each other through MySQL asynchronous replication... For more on this topic see also MySQL Cluster and channel failover...

Taxonomy upgrade extras: indexprimary keyinnodbtuningperformanceOptimizer

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

*** (1) TRANSACTION: TRANSACTION 22723019234, fetching rows mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1216, 14 row lock(s) update location set expires='2012-08-10 04:50:29' where username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2203904 n bits 136 index `GEN_CLUST_INDEX` of table `location` trx id 22723019234 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 22723019222, fetching rows, thread declared inside InnoDB 225 mysql tables in use 1, locked 1 192 lock struct(s), heap size 30704, 9483 row lock(s) delete from location where expires<'2012-08-10 04:49:30' AND expires!='1969-12-31 19:00:00' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 2203904 n bits 136 index `GEN_CLUST_INDEX` of table `location` trx id 22723019222 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2203951 n bits 136 index `GEN_CLUST_INDEX` of table `location` trx id 22723019222 lock_mode X waiting *** WE ROLL BACK TRANSACTION (1)

They want us to have this fixed. And they did not like the answer that the application has to cope with deadlocks [ 1 ].

But one thing looks suspicious here: The GEN_CLUSTER_INDEX! This basically means there was NO explicit Primary Key on the InnoDB table and InnoDB was creating its own internal Primary Key.

After some discussion we started to examine the whole situation. For this we transformed the UPDATE and the DELETE statement into SELECT's:

UPDATE EXPLAIN SELECT * FROM location WHERE username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191' ; +----+-------------+----------+------+---------------+----------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+----------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | location | ref | username | username | 66 | const | 9 | Using index condition; Using where | +----+-------------+----------+------+---------------+----------+---------+-------+------+------------------------------------+

The first strange thing is, that the MySQL optimizer expects 9 rows (on a long key of 66 bytes) which theoretically should be a Primary Key access! This sound non optimal. And as shorter and faster transactions are as less probable are deadlocks.

So we tried to look at the transaction with SHOW ENGINE INNODB STATUS: START TRANSACTION; SELECT * FROM location WHERE username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191' FOR UPDATE ; ---TRANSACTION 14033 4 lock struct(s), heap size 1248, 11 row lock(s) MySQL thread id 5, OS thread handle 0x7f3647b9e700, query id 526 localhost root cleaning up

We can see that the same query uses 4 lock structs and locks in total 11 rows. This is similar to what we have seen in the deadlock.

DELETE EXPLAIN SELECT * FROM location WHERE expires < '2012-08-10 04:49:30' AND expires != '1969-12-31 19:00:00' ; +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | location | ALL | NULL | NULL | NULL | NULL | 10754 | Using where | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+

Uiii! The DELETE does not use an index at all but does a full table scan. Which is not so optimal performance wise...

START TRANSACTION; SELECT * FROM location WHERE expires < '2012-08-10 04:49:30' AND expires != '1969-12-31 19:00:00' FOR UPDATE ; ---TRANSACTION 14034 168 lock struct(s), heap size 31160, 11007 row lock(s) MySQL thread id 6, OS thread handle 0x7f3647b9e700, query id 663 localhost root cleaning up

And we can see a huge amount of locked rows... The table contains 10840 rows in total. Those numbers differ a bit from the deadlock but it is OK because they do not represent the same point in time.

So we started looking at the table structure. The table which was provided by the customer looks as follows:

CREATE TABLE `location` ( `username` varchar(64) NOT NULL DEFAULT '', `domain` varchar(128) NOT NULL DEFAULT '', `contact` varchar(255) NOT NULL DEFAULT '', `received` varchar(255) DEFAULT NULL, `path` varchar(255) DEFAULT NULL, `expires` datetime NOT NULL DEFAULT '2020-01-01 00:00:00', `q` float(10,2) NOT NULL DEFAULT '1.00', `callid` varchar(255) NOT NULL DEFAULT 'Default-Call-ID', `cseq` int(11) NOT NULL DEFAULT '42', `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `replicate` int(10) unsigned NOT NULL DEFAULT '0', `state` tinyint(1) unsigned NOT NULL DEFAULT '0', `flags` int(11) NOT NULL DEFAULT '0', `cflags` int(11) NOT NULL DEFAULT '0', `user_agent` varchar(100) NOT NULL DEFAULT '', `socket` varchar(128) DEFAULT NULL, `methods` int(11) DEFAULT NULL, `id` int(10) NOT NULL DEFAULT '0', KEY `username` (`username`,`domain`,`contact`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

First I want to fix the problem of the full table scan:

ALTER TABLE location ADD INDEX (expires);

Then the DELETE looks much better:

+----+-------------+----------+-------+---------------+---------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+------+-----------------------+ | 1 | SIMPLE | location | range | expires | expires | 5 | NULL | 2 | Using index condition | +----+-------------+----------+-------+---------------+---------+---------+------+------+-----------------------+ ---TRANSACTION 14074 2 lock struct(s), heap size 1248, 1 row lock(s) MySQL thread id 6, OS thread handle 0x7f3647b9e700, query id 671 localhost root cleaning up

But I do not know how realistic my actual data are. This can change with an other data-set!

Now I want to see if there is any difference with the KEY declared as a Primary Key:

ALTER TABLE location DROP INDEX username, ADD PRIMARY KEY (username, domain, contact);

Long indexes are bad for InnoDB. See blog post which will hopefully appear soon!

+----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | location | ref | PRIMARY | PRIMARY | 66 | const | 9 | Using where | +----+-------------+----------+------+---------------+---------+---------+-------+------+-------------+ ---TRANSACTION 14145 3 lock struct(s), heap size 1248, 10 row lock(s) MySQL thread id 6, OS thread handle 0x7f3647b9e700, query id 684 localhost root cleaning up

Execution plan looks the same. OK. 1 row less is locked. This means 10% less probability of deadlocks?

The effect was not as big as expected. So rolling back last change (making at least a unique key out of it).

As already mentioned, short Primary Keys are good for InnoDB. And as we will show in a blog post soon VARCHAR are bad performance wise. So we try to use the non used? field id:

ALTER TABLE location DROP PRIMARY KEY, ADD UNIQUE KEY (username, domain, contact); ALTER TABLE location MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; EXPLAIN SELECT * FROM location WHERE id = 2984 ; +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | location | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ START TRANSACTION; SELECT * FROM location WHERE id = 2984 FOR UPDATE ; ---TRANSACTION 14336 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 6, OS thread handle 0x7f3647b9e700, query id 701 localhost root cleaning up

And see there: The Query Execution Plan looks much better and locks are much smaller.

As a result

• Use proper indexing (expires).
• Follow the rules: Creating Primary Keys on relational database tables. See also article: Disadvantages of explicitly NOT using InnoDB Primary Keys?.
• Follow the rules: Create short synthetic Primary Keys and avoid long natural Primary Keys (especially with InnoDB): Clustered and Secondary Indexes and Optimizing InnoDB Queries.
• Make your application aware of deadlocks (and other kinds of aborted transactions) and reissue transaction if it fails with a deadlock. [ 1 ]

I hope we can add here the results on the impact of deadlock occurrence soon.

Taxonomy upgrade extras: doagreplicationgaleramysql

On Tuesday, September 4, 2012 the next DOAG SIG MySQL meeting will take place in Hamburg (Germany) with the topic Replication.

Possible presentations are: MySQL Replication, Galera Cluster, Replication with Oracle GoldenGate, Replication with Zimory Scale and Tungsten Replicator.

The event will presumably be located at the Hotel Böttcherhof at Wöhlerstrasse 2.

Taxonomy upgrade extras: doagreplikation

Am Dienstag, 4. September 2012 find in Hamburg das nächste DOAG SIG MySQL Meeting zum Thema Replikation statt.

Mögliche Vorträge sind: MySQL Replication, Galera Cluster, Replikation mit Oracle GoldenGate, Replikation mit Zimory Scale und Tungsten Replicator.

Das Event findet voraussichtlich im Hotel Böttcherhof an der Wöhlerstrasse 2 statt.

FromDual is having a talk at Frankfurter Datenbanktage 2013, March 14/15

FromDual hält einen Vortrag an den Frankfurter Datenbanktagen 2013 am 14. und 15. März.

Es ist ein bekanntes Problem, dass das Ändern der Storage Engine von MyISAM nach InnoDB Probleme verursachen kann [ 1 ], wenn Abfragen der folgenden Art auftreten:

SELECT COUNT(*) from table;

Glücklicherweise kommt dieser Typ von Abfragen selten vor. Und wenn, kann die Abfrage meist einfach weggelassen oder darum herum gearbeitet werden, indem man die Anzahl Zeilen in der Tabelle schätzt. Zum Beispiel mit:

SHOW TABLE STATUS LIKE 'test';

Aber in einigen seltenen Fällen braucht der Kunde diese Werte aus bestimmten Gründen wirklich exakt. Um die Ressourcen des Servers mit dieser Abfrage, welche in manchen Fällen sehr oft abgesetzt werden kann, nicht zu erschöpfen, nutzen wir die Materialized Views und/oder Shadow Tabellen-Technik [ 2 ].

Das folgende Beispiel zeigt auf, wie dies umgesetzt werden kann.

Unsere ursprüngliche Situation

Wir haben eine Tabelle mit Angeboten, welche durch ein Host-System befüllt wird:

CREATE TABLE offer ( id int unsigned NOT NULL AUTO_INCREMENT , `type` CHAR(3) NOT NULL DEFAULT 'AAA' , data varchar(64) DEFAULT NULL , PRIMARY KEY (`id`) , INDEX (type) ) ENGINE=InnoDB; INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

Die Abfrage, welche wir absetzen wollen, schaut wie folgt aus:

SELECT COUNT(*) FROM offer;

Diese Abfrage wird bei InnoDB sehr teuer, wenn Zillionen von Zeilen in der Tabelle sind.

Die Lösung des Problems

Um das Problem zu lösen, legen wir eine Zähler-Tabelle an, in welcher wir die Zeilen zählen, welche auf der Angebots-Tabelle eingefügt, geändert oder gelöscht werden.

CREATE TABLE counter ( `type` char(3) NOT NULL DEFAULT 'AAA' , `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 , `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (type) ) ENGINE=InnoDB;

Um unsere Zähler-Tabelle zu füllen, brauchen wir einen initialen Stand:

INSERT INTO counter SELECT type, COUNT(*), NULL FROM offer GROUP BY type; SELECT * FROM counter; SELECT COUNT(*) FROM counter; Unterhalt der Zähler-Tabelle

Um die Zähler-Tabelle aktuell zu halten, benötigen wir die 3 folgenden Trigger:

DROP TRIGGER IF EXISTS insert_offer_trigger; delimiter // CREATE TRIGGER insert_offer_trigger AFTER INSERT ON offer FOR EACH ROW BEGIN INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END; // delimiter ; DROP TRIGGER IF EXISTS update_offer_trigger; delimiter // CREATE TRIGGER update_offer_trigger AFTER UPDATE ON offer FOR EACH ROW BEGIN IF NEW.type = OLD.type THEN UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type; ELSE UPDATE counter SET count = count - 1, ts = CURRENT_TIMESTAMP() WHERE type = OLD.type; INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END IF; END; // delimiter ; DROP TRIGGER IF EXISTS delete_offer_trigger; delimiter // CREATE TRIGGER delete_offer_trigger AFTER DELETE ON offer FOR EACH ROW BEGIN UPDATE counter SET count = count - 1 WHERE type = OLD.type; END; // delimiter ;

Jetzt können wir einige Fälle testen und die Resultate beider Tabellen vergleichen:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); -- Single offer change UPDATE offer SET data = 'Single offer change' WHERE id = 2; -- Multi offer change UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA'; -- Single offer delete DELETE FROM offer WHERE id = 1; -- REPLACE (= DELETE / INSERT) REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace'); -- New type INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla'); -- Change of type UPDATE offer SET type = 'ZZZ' where id = 2; -- Change of type to new type UPDATE offer SET type = 'YYY' where id = 3; -- INSERT on DUPLICATE KEY UPDATE INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY'; INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE'; SELECT * FROM offer; SELECT COUNT(*) FROM offer; SELECT * FROM counter; SELECT SUM(count) FROM counter;

Diese Lösung hat den Vorteil, dass wir für eine Abfrage nach der Anzahl Zeilen für einen bestimmten Bestellungs-Typ ebenfalls eine sehr schnelle Antwort erhalten. Diese Abfrage wäre auch für MyISAM Tabellen eine teure Operation...

SELECT `count` FROM counter WHERE `type` = 'DDD'; Taxonomy upgrade extras: innodbmaterialized viewsmaterialised viewsmyisamselectshadow tablecount

Es ist ein bekanntes Problem, dass das Ändern der Storage Engine von MyISAM nach InnoDB Probleme verursachen kann [ 1 ], wenn Abfragen der folgenden Art auftreten:

SELECT COUNT(*) from table;

Glücklicherweise kommt dieser Typ von Abfragen selten vor. Und wenn, kann die Abfrage meist einfach weggelassen oder darum herum gearbeitet werden, indem man die Anzahl Zeilen in der Tabelle schätzt. Zum Beispiel mit:

SHOW TABLE STATUS LIKE 'test';

Aber in einigen seltenen Fällen braucht der Kunde diese Werte aus bestimmten Gründen wirklich exakt. Um die Ressourcen des Servers mit dieser Abfrage, welche in manchen Fällen sehr oft abgesetzt werden kann, nicht zu erschöpfen, nutzen wir die Materialized Views und/oder Shadow Tabellen-Technik [ 2 ].

Das folgende Beispiel zeigt auf, wie dies umgesetzt werden kann.

Unsere ursprüngliche Situation

Wir haben eine Tabelle mit Angeboten, welche durch ein Host-System befüllt wird:

CREATE TABLE offer ( id int unsigned NOT NULL AUTO_INCREMENT , `type` CHAR(3) NOT NULL DEFAULT 'AAA' , data varchar(64) DEFAULT NULL , PRIMARY KEY (`id`) , INDEX (type) ) ENGINE=InnoDB; INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

Die Abfrage, welche wir absetzen wollen, schaut wie folgt aus:

SELECT COUNT(*) FROM offer;

Diese Abfrage wird bei InnoDB sehr teuer, wenn Zillionen von Zeilen in der Tabelle sind.

Die Lösung des Problems

Um das Problem zu lösen, legen wir eine Zähler-Tabelle an, in welcher wir die Zeilen zählen, welche auf der Angebots-Tabelle eingefügt, geändert oder gelöscht werden.

CREATE TABLE counter ( `type` char(3) NOT NULL DEFAULT 'AAA' , `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 , `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (type) ) ENGINE=InnoDB;

Um unsere Zähler-Tabelle zu füllen, brauchen wir einen initialen Stand:

INSERT INTO counter SELECT type, COUNT(*), NULL FROM offer GROUP BY type; SELECT * FROM counter; SELECT COUNT(*) FROM counter; Unterhalt der Zähler-Tabelle

Um die Zähler-Tabelle aktuell zu halten, benötigen wir die 3 folgenden Trigger:

DROP TRIGGER IF EXISTS insert_offer_trigger; delimiter // CREATE TRIGGER insert_offer_trigger AFTER INSERT ON offer FOR EACH ROW BEGIN INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END; // delimiter ; DROP TRIGGER IF EXISTS update_offer_trigger; delimiter // CREATE TRIGGER update_offer_trigger AFTER UPDATE ON offer FOR EACH ROW BEGIN IF NEW.type = OLD.type THEN UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type; ELSE UPDATE counter SET count = count - 1, ts = CURRENT_TIMESTAMP() WHERE type = OLD.type; INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END IF; END; // delimiter ; DROP TRIGGER IF EXISTS delete_offer_trigger; delimiter // CREATE TRIGGER delete_offer_trigger AFTER DELETE ON offer FOR EACH ROW BEGIN UPDATE counter SET count = count - 1 WHERE type = OLD.type; END; // delimiter ;

Jetzt können wir einige Fälle testen und die Resultate beider Tabellen vergleichen:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); -- Single offer change UPDATE offer SET data = 'Single offer change' WHERE id = 2; -- Multi offer change UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA'; -- Single offer delete DELETE FROM offer WHERE id = 1; -- REPLACE (= DELETE / INSERT) REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace'); -- New type INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla'); -- Change of type UPDATE offer SET type = 'ZZZ' where id = 2; -- Change of type to new type UPDATE offer SET type = 'YYY' where id = 3; -- INSERT on DUPLICATE KEY UPDATE INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY'; INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE'; SELECT * FROM offer; SELECT COUNT(*) FROM offer; SELECT * FROM counter; SELECT SUM(count) FROM counter;

Diese Lösung hat den Vorteil, dass wir für eine Abfrage nach der Anzahl Zeilen für einen bestimmten Bestellungs-Typ ebenfalls eine sehr schnelle Antwort erhalten. Diese Abfrage wäre auch für MyISAM Tabellen eine teure Operation...

SELECT `count` FROM counter WHERE `type` = 'DDD'; Taxonomy upgrade extras: innodbmaterialized viewsmaterialised viewsmyisamselectshadow tablecount

Taxonomy upgrade extras: innodbmaterialized viewsmaterialised viewsmyisamselectshadow tablecount

Es ist ein bekanntes Problem, dass das Ändern der Storage Engine von MyISAM nach InnoDB Probleme verursachen kann [ 1 ], wenn Abfragen der folgenden Art auftreten:

SELECT COUNT(*) from table;

Glücklicherweise kommt dieser Typ von Abfragen selten vor. Und wenn, kann die Abfrage meist einfach weggelassen oder darum herum gearbeitet werden, indem man die Anzahl Zeilen in der Tabelle schätzt. Zum Beispiel mit:

SHOW TABLE STATUS LIKE 'test';

Aber in einigen seltenen Fällen braucht der Kunde diese Werte aus bestimmten Gründen wirklich exakt. Um die Ressourcen des Servers mit dieser Abfrage, welche in manchen Fällen sehr oft abgesetzt werden kann, nicht zu erschöpfen, nutzen wir die Materialized Views und/oder Shadow Tabellen-Technik [ 2 ].

Das folgende Beispiel zeigt auf, wie dies umgesetzt werden kann.

Unsere ursprüngliche Situation

Wir haben eine Tabelle mit Angeboten, welche durch ein Host-System befüllt wird:

CREATE TABLE offer ( id int unsigned NOT NULL AUTO_INCREMENT , `type` CHAR(3) NOT NULL DEFAULT 'AAA' , data varchar(64) DEFAULT NULL , PRIMARY KEY (`id`) , INDEX (type) ) ENGINE=InnoDB; INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

Die Abfrage, welche wir absetzen wollen, schaut wie folgt aus:

SELECT COUNT(*) FROM offer;

Diese Abfrage wird bei InnoDB sehr teuer, wenn Zillionen von Zeilen in der Tabelle sind.

Die Lösung des Problems

Um das Problem zu lösen, legen wir eine Zähler-Tabelle an, in welcher wir die Zeilen zählen, welche auf der Angebots-Tabelle eingefügt, geändert oder gelöscht werden.

CREATE TABLE counter ( `type` char(3) NOT NULL DEFAULT 'AAA' , `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 , `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (type) ) ENGINE=InnoDB;

Um unsere Zähler-Tabelle zu füllen, brauchen wir einen initialen Stand:

INSERT INTO counter SELECT type, COUNT(*), NULL FROM offer GROUP BY type; SELECT * FROM counter; SELECT COUNT(*) FROM counter; Unterhalt der Zähler-Tabelle

Um die Zähler-Tabelle aktuell zu halten, benötigen wir die 3 folgenden Trigger:

DROP TRIGGER IF EXISTS insert_offer_trigger; delimiter // CREATE TRIGGER insert_offer_trigger AFTER INSERT ON offer FOR EACH ROW BEGIN INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END; // delimiter ; DROP TRIGGER IF EXISTS update_offer_trigger; delimiter // CREATE TRIGGER update_offer_trigger AFTER UPDATE ON offer FOR EACH ROW BEGIN IF NEW.type = OLD.type THEN UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type; ELSE UPDATE counter SET count = count - 1, ts = CURRENT_TIMESTAMP() WHERE type = OLD.type; INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END IF; END; // delimiter ; DROP TRIGGER IF EXISTS delete_offer_trigger; delimiter // CREATE TRIGGER delete_offer_trigger AFTER DELETE ON offer FOR EACH ROW BEGIN UPDATE counter SET count = count - 1 WHERE type = OLD.type; END; // delimiter ;

Jetzt können wir einige Fälle testen und die Resultate beider Tabellen vergleichen:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); -- Single offer change UPDATE offer SET data = 'Single offer change' WHERE id = 2; -- Multi offer change UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA'; -- Single offer delete DELETE FROM offer WHERE id = 1; -- REPLACE (= DELETE / INSERT) REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace'); -- New type INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla'); -- Change of type UPDATE offer SET type = 'ZZZ' where id = 2; -- Change of type to new type UPDATE offer SET type = 'YYY' where id = 3; -- INSERT on DUPLICATE KEY UPDATE INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY'; INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE'; SELECT * FROM offer; SELECT COUNT(*) FROM offer; SELECT * FROM counter; SELECT SUM(count) FROM counter;

Diese Lösung hat den Vorteil, dass wir für eine Abfrage nach der Anzahl Zeilen für einen bestimmten Bestellungs-Typ ebenfalls eine sehr schnelle Antwort erhalten. Diese Abfrage wäre auch für MyISAM Tabellen eine teure Operation...

SELECT `count` FROM counter WHERE `type` = 'DDD';

Taxonomy upgrade extras: innodbmaterialized viewsmaterialised viewsmyisamselectshadow tablecount

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems [ 1 ] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

SHOW TABLE STATUS LIKE 'test';

But in some rare cases customer really needs these values for some reasons. To not exhaust the resources of the server with this query which can be fired quite often in some cases we make use of the materialized views/shadow table technique [ 2 ].

The following example illustrates how to do this.

Our original situation

We have an offer table which is feed by a host system:

CREATE TABLE offer ( id int unsigned NOT NULL AUTO_INCREMENT , `type` CHAR(3) NOT NULL DEFAULT 'AAA' , data varchar(64) DEFAULT NULL , PRIMARY KEY (`id`) , INDEX (type) ) ENGINE=InnoDB; INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ABC', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'ZZZ', 'Blablabla');

The query we want to perform looks like this:

SELECT COUNT(*) FROM offer;

This query becomes expensive when you have zillions of rows in your table.v

The work around

To work around the problem we create a counter table where we count the rows which are inserted, updated or deleted on the offer table.

CREATE TABLE counter ( `type` char(3) NOT NULL DEFAULT 'AAA' , `count` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 , `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (type) ) ENGINE=InnoDB;

To fill this counter table we need an initial snapshot:

INSERT INTO counter SELECT type, COUNT(*), NULL FROM offer GROUP BY type; SELECT * FROM counter; SELECT COUNT(*) FROM counter; Update the counter table

To keep the counter table up-to-date we need the following 3 triggers:

DROP TRIGGER IF EXISTS insert_offer_trigger; delimiter // CREATE TRIGGER insert_offer_trigger AFTER INSERT ON offer FOR EACH ROW BEGIN INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END; // delimiter ; DROP TRIGGER IF EXISTS update_offer_trigger; delimiter // CREATE TRIGGER update_offer_trigger AFTER UPDATE ON offer FOR EACH ROW BEGIN IF NEW.type = OLD.type THEN UPDATE counter SET ts = CURRENT_TIMESTAMP() WHERE type = NEW.type; ELSE UPDATE counter SET count = count - 1, ts = CURRENT_TIMESTAMP() WHERE type = OLD.type; INSERT INTO counter VALUES (NEW.type, 1, NULL) ON DUPLICATE KEY UPDATE count = count + 1, ts = CURRENT_TIMESTAMP(); END IF; END; // delimiter ; DROP TRIGGER IF EXISTS delete_offer_trigger; delimiter // CREATE TRIGGER delete_offer_trigger AFTER DELETE ON offer FOR EACH ROW BEGIN UPDATE counter SET count = count - 1 WHERE type = OLD.type; END; // delimiter ;

Now we can test some cases and compare the results of both tables:

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla'); -- Single offer change UPDATE offer SET data = 'Single offer change' WHERE id = 2; -- Multi offer change UPDATE offer SET data = 'Multi offer change' WHERE type = 'AAA'; -- Single offer delete DELETE FROM offer WHERE id = 1; -- REPLACE (= DELETE / INSERT) REPLACE INTO offer VALUES (3, 'ZZZ', 'Single row replace'); -- New type INSERT INTO offer VALUES (NULL, 'DDD', 'Blablabla'); -- Change of type UPDATE offer SET type = 'ZZZ' where id = 2; -- Change of type to new type UPDATE offer SET type = 'YYY' where id = 3; -- INSERT on DUPLICATE KEY UPDATE INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'INSERT ON DUPLICATE KEY'; INSERT INTO offer VALUES (7, 'DDD', 'ON DUPLICATE KEY UPDATE') ON DUPLICATE KEY UPDATE type = 'DDD', data = 'UPDATE ON DUPLICATE KEY UPDATE'; SELECT * FROM offer; SELECT COUNT(*) FROM offer; SELECT * FROM counter; SELECT SUM(count) FROM counter;

This solution has the advantage that we get also a very fast response on the number of rows for a specific order type. Which would be also expensive for MyISAM tables...

Taxonomy upgrade extras: mysqltalkconferencefroscon

Also this year we will have a special track for MySQL, Galera, Percona und MariaDB at the FrOSCon in St. Augustin in Germany. The conference is scheduled for August 25 and 26 2012.

Together with the PostgreSQL people we are organizing a sub-conference for Open Source RDBMS there. Now we are looking for interesting talks about MySQL and related techniques like Galera, Percona, MariaDB. The only restriction for the talks is: They must be about an Open Source topic.

We encourage you to send your proposals.

After registering you can Submit a new event. Choose the Databases track. It makes easier to assign the proposal.

Regarding the talks: Please do NOT add talks about NON Open Source solutions. It can be about some new technical things or about some user experience with MySQL technology.

Keep in mind the audience is going to be technical driven. Think about the audience as colleges and not as decision makers.

Please help spreading the word for the Conference by blogging and twittering about it (#froscon)!

And now let us go...

Oli