Skip to content

Instantly share code, notes, and snippets.

View yoku0825's full-sized avatar

yoku0825 yoku0825

49 followers · 29 following
View GitHub Profile
@yoku0825
yoku0825 / gist:0c1f69e7c7d3fbd73ca588a50994b1c8
Created August 24, 2020 14:33
イメージ名の後に `mysqld --lower-case-table-names=1` を渡せば良さそうです
### オプションなしで起動(デフォルトの lower_case_table_names = 0)
$ sudo docker run -d -P -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" mysql/mysql-server:5.6
$ mysql -h172.17.0.3
mysql> create database d1;
Query OK, 1 row affected (0.01 sec)
mysql> use d1
Database changed
@yoku0825
yoku0825 / cmake-log
Created August 12, 2020 08:56
makeでターゲット絞ると全体をビルドするよりは速くなりました
[yoku0825@150-95-141-50 work]$ time cmake3 -DWITH_BOOST=../mysql-8.0.21/boost ../mysql-8.0.21
-- Running cmake version 3.17.3
-- Found Git: /usr/bin/git (found version "1.8.3.1")
-- This is el6, el7 or el8 as found from 'uname -r' or 'rpm -qf /'
-- We probably need some devtoolset compiler
-- Using /opt/rh/devtoolset-9/root/usr/bin/gcc
-- Using /opt/rh/devtoolset-9/root/usr/bin/g++
-- MySQL 8.0.21
-- The C compiler identification is GNU 9.3.1
-- The CXX compiler identification is GNU 9.3.1
@yoku0825
yoku0825 / gist:913d143d28c19b24d424a79517c1a3fc
Created July 25, 2020 07:49
--help --verboseよりも先に--plugin-load処理するんですね…
$ docker run --entrypoint="/usr/sbin/mysqld" mysql/mysql-server:8.0 --plugin-load=group_replication.so --help --verbose | grep group
The following groups are read: mysqld server mysqld-8.0
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--binlog-group-commit-sync-delay=#
binary log group commit sync queue to fill before
--binlog-group-commit-sync-no-delay-count=#
--binlog-group-commit-sync-delay), the commit procedure
The maximum time that the binary log group commit will
bytes. Rows will be grouped into events smaller than this
@yoku0825
yoku0825 / gist:8b65dd49b63b3658fbf79e058221fe6f
Created June 19, 2020 08:51
lsofで見たmmaped TempTableストレージエンジン
mysqld 6330 mysql 27u REG 0,38 1048584 2240585 /tmp/mysql_temptable.e5TG4F (deleted)
mysqld 6330 mysql 28u REG 0,38 2097160 2240604 /tmp/mysql_temptable.fz9ETI (deleted)
mysqld 6330 mysql 29u REG 0,38 2097160 2240605 /tmp/mysql_temptable.MRogLL (deleted)
mysqld 6330 mysql 30u REG 0,38 4194312 2240606 /tmp/mysql_temptable.DkB4GO (deleted)
mysqld 6330 mysql 31u REG 0,38 4194312 2240607 /tmp/mysql_temptable.OlwnMR (deleted)
mysqld 6330 mysql 32u REG 0,38 8388616 2240608 /tmp/mysql_temptable.F7hg4U (deleted)
mysqld 6330 mysql 33u REG 0,38 8388616 2240609 /tmp/mysql_temptable.a9KQUZ (deleted)
mysqld 6330 mysql 34u REG 0,38 16777224 2240610 /tmp/mysql_temptable.25rpe5 (deleted)
mysqld 6330 mysql 35u REG 0,38 16777224 2240602 /tmp/mysql_temptable.3djHti (deleted)
mysqld 6330 mysql 36u REG 0,38 33554440 2240603 /tmp/mysql_temptable.PKmzMw (deleted)
@yoku0825
yoku0825 / gist:b5e1244fda9a8b8f70cf5dcc9a8f2309
Created June 17, 2020 10:39
EXPLAIN ANALYZE
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary> (actual time=0.001..0.016 rows=62 loops=1)
-> Temporary table with deduplication (actual time=99911.386..99911.423 rows=62 loops=1)
-> Filter: (is_metrics_list_analyze_90.`name` in ('lock_deadlocks','lock_row_lock_time_avg')) (actual time=99790.494..99908.201 rows=1178 loops=1)
-> Table scan on is_metrics_list_analyze_90 (actual time=0.002..40.599 rows=174298 loops=1)
-> Materialize (actual time=99788.102..99874.969 rows=174298 loops=1)
-> Window aggregate with buffering: first_value(daily_is_metrics_list.avg_count) OVER w_all, last_value(daily_is_metrics_list.avg_count) OVER w_all (actual time=98284.457..99478.783 rows=174298 loops=1)
-> Table scan on <temporary> (actual time=0.002..51.313 rows=174298 loops=1)
-> Temporary table (actual time=98283.337..98386.183 rows=174298 loops=1)
@yoku0825
yoku0825 / gist:a3a9884db90068ce8f33b82f7cef1d33
Created June 17, 2020 02:31
シャットダウンがハングした時の Connection_handler_manager::wait_till_no_connection のバックトレース
(gdb) bt full
+bt full
#0 pthread_cond_wait@@GLIBC_2.3.2 () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:185
No locals.
#1 0x00000000007b7705 in native_cond_wait (mutex=0x1de8450 <Connection_handler_manager::LOCK_connection_count>,
cond=0x1de8410 <Connection_handler_manager::COND_connection_count>) at /home/yoku0825/mysql-5.7.30/include/thr_cond.h:147
No locals.
#2 my_cond_wait (mp=0x1de8450 <Connection_handler_manager::LOCK_connection_count>,
cond=0x1de8410 <Connection_handler_manager::COND_connection_count>) at /home/yoku0825/mysql-5.7.30/include/thr_cond.h:202
No locals.
@yoku0825
yoku0825 / gist:a10fcd04e975ce61ef053f69b8fc364a
Created June 14, 2020 14:12
mysql_execute_commandあたりから読むのが良いかもですね
(gdb) bt
+bt
#0 mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:2634
#1 0x0000000000eb8378 in mysql_parse (thd=thd@entry=0x7f8f400101d0, parser_state=parser_state@entry=0x7f8f97f6e4d0)
at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:5306
#2 0x0000000000eba4d5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1776
#3 0x0000000000ebb314 in do_command (thd=thd@entry=0x7f8f400101d0) at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1274
#4 0x0000000000fccc70 in handle_connection (arg=arg@entry=0x751fc50)
at /home/yoku0825/mysql-8.0.20/sql/conn_handler/connection_handler_per_thread.cc:302
@yoku0825
yoku0825 / gist:9683dbe7b5b7b9ebffe99f95ba0f8a54
Created June 8, 2020 14:16
gdb使っちゃえば簡単に再現はできるんだけれども
$ gdb -p 20825
(gdb) up
+up
#1 0x0000000000d4c9ad in Mysqld_socket_listener::listen_for_connection_event (this=0x34bcac0)
at /home/yoku0825/mysql-5.7.30/sql/conn_handler/socket_connection.cc:859
859 int retval= poll(&m_poll_info.m_fds[0], m_socket_map.size(), -1);
(gdb) p connection_count
+p connection_count
No symbol "connection_count" in current context.
(gdb) up
@yoku0825
yoku0825 / pt-query-digest.patch
Created May 13, 2020 08:03
pt-query-digest 3.2.0の /usr/bin/pt-query-digestに直接当てるようのパッチ
--- /usr/bin/pt-query-digest 2020-04-24 02:41:46.000000000 +0900
+++ ./pt-query-digest.ex 2020-05-13 14:43:23.213959417 +0900
@@ -4513,2 +4513,3 @@
No_index_used => ($event->{No_index_used} ? 'Yes' : 'No'),
+ Result_size => $packet->{data_len} || 0,
};
@@ -7692,2 +7693,3 @@
bytes
+ Result_size
);
@yoku0825
yoku0825 / gist:aaca884794a6dbb79485a44e22174865
Created May 1, 2020 10:25
オプティマイザさん的には `select count(0)` らしい
mysql80 110> EXPLAIN SELECT COUNT(*);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql80 110> SHOW WARNINGS;
+-------+------+----------------------------------------------+