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)
