Im ersten Halbjahr wurden dreimal so viele Straftaten gegen Asylunterkünfte begangen wie 2015

Zahl der sexuell Inaktiven bei den in den 1990er Jahren Geborenen doppelt so hoch wie bei den in den 1960er Jahren Geborenen: "Risikoscheue Generation"

Präsidentschaftskandidatin Clinton sieht Täterschaft von Russland als erwiesen an. IT-Experte fordert mehr Sicherheit für Wahlsystem

Die spanischen Sozialisten lehnen eine große Koalition ab, denn sie werden "nicht das unterstützen, was sie ändern wollen"

Während Lawrence Kudlow und Stephen Moore für eine traditionelle republikanische Wirtschaftspolitik stehen, propagiert Peter Navarro Neo-Merkantilismus

Iranische Männer posten Bilder von sich mit dem Kopftuch einer Frau, die sich unverhüllt der Kamera zeigt

Erfolge bei der Rückeroberung der Stadt aus der Kontrolle von IS-Milizen. Deutlich wird der Blutzoll durch die US-Luftangriffe

SPD-Basis will mit Online-Aktionen die Ablehnung des umstrittenen Abkommens mit Kanada erreichen, Primat der Politik wird als gefährdet gesehen

In Italien befürchtet Innenminister Alfano nach dem Bekanntwerden der Höhe der Einkommen von RAI-Managern Privatisierungsforderungen aus der Bevölkerung

Nach einer Umfrage haben viele Deutsche Angst, mehr als die Hälfte macht Merkels Politik für die jüngsten Anschläge und Amokläufe mit verantwortlich

Die neue Ghostbusters-Adaption ist ein mediokrer Film, aber kulturgeschichtlich nicht ganz uninteressant

In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.

First, I want to thank everybody for attending the July 14 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: At which isolation level do pt-online-schema-change and pt-archive  copy data from a table?

A: Both tools do not change the server’s default transaction isolation level. Use either REPEATABLE READ or set it in my.cnf.

Q: Can I create an index to optimize a query which has group by A and order by B, both from different tables and A column is from the first table in the two table join?

A: Do you mean a query like SELECT ... FROM a, b GROUP BY a.A ORDER BY b.B ? Yes, this is possible:

mysql> explain select A, B, count(*) from a join b on(a.A=b.id) WHERE b.B < 4 GROUP BY a.A, b.B ORDER BY b.B ASC; +----+-------------+-------+-------+---------------+------+---------+-----------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-----------+------+-----------------------------------------------------------+ | 1 | SIMPLE | b | range | PRIMARY,B | B | 5 | NULL | 15 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | a | ref | A | A | 5 | test.b.id | 1 | Using index | +----+-------------+-------+-------+---------------+------+---------+-----------+------+-----------------------------------------------------------+ 2 rows in set (0.00 sec)

Q: Where can I find recommendations on what kind of engine to use for different application types or use cases?

A: Storage engines are always being actively developed, therefore I suggest that you don’t search for generic recommendations. These can be outdated just a few weeks after they are written. Study engines instead. For example, just a few years ago MyISAM was the only engine (among those officially supported) that could work with FULLTEXT indexes and SPATIAL columns. Now InnoDB supports both: FULLTEXT indexes since version 5.6 and GIS features in 5.7. Today I can recommend InnoDB as a general-purpose engine for all installations, and TokuDB for write-heavy workloads when you cannot use high-speed disks.

Alternative storage engines can help to realize specific business needs. For example, CONNECT brings data to your server from many sources, SphinxSE talks to the Sphinx daemon, etc.

Other alternative storage engines increase the speed of certain workloads. Memory, for example, can be a good fit for temporary tables.

Q: Can you please explain how we find the full text of the query when we query the view ‘statements_with_full_table_Scans’?

A: Do you mean view in sys schema? Sys schema views take information from summary_* and digests it in Performance Schema, therefore it does not contain full queries (only digests). Full text of the query can be found in the events_statements_*  tables in the Performance Schema. Note that even the events_statements_history_long  table can be rewritten very quickly, and you may want to save data from it periodically.

Q: Hi is TokuDB for the new document protocol?

A: As Alex Rubin showed in his detailed blog post, the new document protocol just converts NoSQL queries into SQL, and is thus not limited to any storage engine. To use documents and collections, a storage engine must support generated columns (which TokuDB currently does not). So support of X Protocol for TokuDB is limited to relational tables access.

Q: Please comment on “read committed” versus “repeatable read.”
Q: Repeatable read holds the cursor on the result set for the client versus read committed where the cursor is updated after a transaction.

A: READ COMMITTED and REPEATABLE READ are transaction isolation levels, whose details are explained here.
I would not correlate locks set on table rows in different transaction isolation modes with the result set. A transaction with isolation level REPEATABLE READ  instead creates a snapshot of rows that are accessed by the transaction. Let’s consider a table:

mysql> create table ti(id int not null primary key, f1 int) engine=innodb; Query OK, 0 rows affected (0.56 sec) mysql> insert into ti values(1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9); Query OK, 9 rows affected (0.03 sec) Records: 9 Duplicates: 0 Warnings: 0

Then start the transaction and select a few rows from this table:

mysql1> begin; Query OK, 0 rows affected (0.00 sec) mysql1> select * from ti where id < 5; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.04 sec)

Now let’s update another set of rows in another transaction:

mysql2> update ti set f1 = id*2 where id > 5; Query OK, 4 rows affected (0.06 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql2> select * from ti; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 12 | | 7 | 14 | | 8 | 16 | | 9 | 18 | +----+------+ 9 rows in set (0.00 sec)

You see that the first four rows – which we accessed in the first transaction – were not modified, and last four were modified. If InnoDB only saved the cursor (as someone answered above) we would expect to see the same result if we ran SELECT * ...  query in our old transaction, but it actually shows whole table content before modification:

mysql1> select * from ti; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | +----+------+ 9 rows in set (0.00 sec)

So “snapshot”  is a better word than “cursor” for the result set. In the case of READ COMMITTED, the first transaction would see modified rows:

mysql1> drop table ti; Query OK, 0 rows affected (0.11 sec) mysql1> create table ti(id int not null primary key, f1 int) engine=innodb; Query OK, 0 rows affected (0.38 sec) mysql1> insert into ti values(1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9); Query OK, 9 rows affected (0.04 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql1> set transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql1> begin; Query OK, 0 rows affected (0.00 sec) mysql1> select * from ti where id < 5; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec)

Let’s update all rows in the table this time:

mysql2> update ti set f1 = id*2; Query OK, 9 rows affected (0.04 sec) Rows matched: 9 Changed: 9 Warnings: 0

Now the first transaction sees both the modified rows with id >= 5 (not in the initial result set), but also the modified rows with id < 5 (which existed in the initial result set):

mysql1> select * from ti; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | | 6 | 12 | | 7 | 14 | | 8 | 16 | | 9 | 18 | +----+------+ 9 rows in set (0.00 sec)

Der Wissenschaftliche Dienst des Deutschen Bundestags belegt in einer Studie die Folgen des Klimawandels

Nach dem gescheiterten Militärputsch gegen Erdogan eskalieren die Spannungen zwischen der Türkei und den USA

Warum greift der IS in Afghanistan die Hazara an?

Die Abschaltung des Atomkraftwerks rückt auf die Tagesordnung, doch das wird teuer für französische Steuerzahler

Al-Qaida freut sich über den Abschuss eines russischen Hubschraubers

Gareth Porter vermutet anhand der Berater der demokratischen Kandidatin ein militärisches Eingreifen zugunsten eines Regimewechsels in Syrien

Verteidigungsministerin Ursula von der Leyen setzt auf Übungen zwischen Polizei und Militär

Die neue deutsche Rechte muss als eine gesellschaftliche Dynamik begriffen werden, die ins barbarische Extrem treibt