Skip to content

Instantly share code, notes, and snippets.

View yoku0825's full-sized avatar

yoku0825 yoku0825

55 followers · 30 following
View GitHub Profile
@yoku0825
yoku0825 / another_
Last active August 25, 2022 08:15
PK AUTO_INCREMENTを比較的後から安全に追加する
master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD dummy_uuid VARCHAR(36) DEFAULT NULL UNIQUE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (3, UUID());
Query OK, 1 row affected (0.00 sec)
$ ./use_all -e "SELECT * FROM d1.t1"
# master
num dummy_uuid
@yoku0825
yoku0825 / gist:ea57b64d26dc645358f2de87f6ef8518
Created July 6, 2022 14:07
競技用my.cnfとなるとこんなもんですかね？
[mysqld]
binlog_row_image = MINIMAL ### バイナリログのエントリあたりの容量を削減
default_authentication_plugin = mysql_native_password ### 5.7, MariaDBとかから載せ替えるなら
event_scheduler = OFF ### 使ってなければお好みで。メモリリークあり
information_schema_stats_expiry = 0 ### SHOW TABLE STATUSが更新されない件
innodb_buffer_pool_size = ?
skip-innodb_doublewrite ### 永続性そっちのけならアリ
innodb_flush_log_at_trx_commit = 2 ### 実はこれより ALTER INSTANCE DISABLE INNODB REDO_LOGの方が効く。ALTER INSTANCE .. はib_logfileに書き込まれるので対応するオプションはない。あと、正常終了しなかったときに二度と起動しなくなる
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_log_writer_threads = OFF ### もしCPU性能が低いVMとかの場合、これをOFFにするとちょっと速くなることがある
@yoku0825
yoku0825 / how_to_repeat.md
Last active February 22, 2022 08:50
https://twitter.com/come25136_spd/status/1495389997847158785 ひと版ぶん回したあと
----system---- ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- ------memory-usage----- --io/total- ---load-avg---
time |usr sys idl wai hiq siq| read writ| recv send| in out | int csw | used buff cach free| read writ| 1m 5m 15m
21-02 10:01:24| 3 0 96 1 0 0| 0 5998k| 567k 1127k| 0 0 |3088 5277 |6573M 2392k 48.2G 8303M| 0 373 |0.19 0.16 0.19
21-02 10:01:25| 2 0 96 1 0 0| 0 8285k| 425k 847k| 0 0 |2847 4928 |6573M 2392k 48.2G 8299M| 0 329 |0.19 0.16 0.19
@yoku0825
yoku0825 / gist:8a1e2b43e80f8ce1b82f41507b28918e
Created January 17, 2022 09:25
general_logをコネクションIDごとに別ファイルに分けるマン(2100年になると使えなくなる
#!/usr/bin/perl
use strict;
use warnings;
use utf8;
my $file= "./general_query.log";
open(my $fh, "<", $file) or die;
@yoku0825
yoku0825 / E8919BF3A08481
Created December 15, 2021 01:15
mysql80 26> SELECT x'E8919BF3A08481', LENGTH(x'E8919BF3A08481'), CHARACTER_LENGTH(x'E8919BF3A08481');
+-------------------+---------------------------+-------------------------------------+
| x'E8919BF3A08481' | LENGTH(x'E8919BF3A08481') | CHARACTER_LENGTH(x'E8919BF3A08481') |
+-------------------+---------------------------+-------------------------------------+
| 葛󠄁 | 7 | 7 |
+-------------------+---------------------------+-------------------------------------+
1 row in set (0.00 sec)
@yoku0825
yoku0825 / gist:af3aef5572a9240dfc725d182c515ac7
Created November 15, 2021 09:04
innodb_file_per_tableを途中で跨がせる
$ ll /usr/mysql/5.7.36/data/d1/
total 220
-rw-r----- 1 yoku0825 yoku0825 67 Nov 15 17:50 db.opt
-rw-r----- 1 yoku0825 yoku0825 8586 Nov 15 17:57 t1.frm
-rw-r----- 1 yoku0825 yoku0825 98304 Nov 15 17:57 t1.ibd <--- innodb_file_per_table= ON
-rw-r----- 1 yoku0825 yoku0825 8586 Nov 15 17:56 t3.frm
-rw-r----- 1 yoku0825 yoku0825 98304 Nov 15 17:56 t3.ibd
mysql57 11> SET GLOBAL innodb_file_per_table= 0;
@yoku0825
yoku0825 / left_join_on_where.php
Created October 4, 2021 12:15
https://twitter.com/mamy1326/status/1444988083426889728
<?php
function t2_in_1_3($buff)
{
if ($buff["t2"] == 1 || $buff["t2"] == 3)
return true;
return false;
}
$t1= array(1, 2, 3);
@yoku0825
yoku0825 / 8025.txt
Created August 5, 2021 06:42
8.0.25 vs 8.0.26 : SET SESSION innodb_strict_mode = OFF
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+--------------------------------------+
@yoku0825
yoku0825 / gist:1add18be35013e9c2c105c1f1a518fd1
Created July 29, 2021 09:38
innodb_parallel_read_threads=4でparallel_readで何本か止めておいてからcontinue
Breakpoint 1, Parallel_reader::parallel_read() () at /home/yoku0825/mysql-8.0.26/storage/innobase/row/row0pread.cc:1087
1087 void Parallel_reader::parallel_read() {
(gdb) c
+c
Continuing.
[New Thread 0x7f2972ffd700 (LWP 1916)]
[New Thread 0x7f2971ffb700 (LWP 1917)]
[New Thread 0x7f2998ff9700 (LWP 1914)]
[New Thread 0x7f296b7fe700 (LWP 1920)]
[New Thread 0x7f2970ff9700 (LWP 1918)]
@yoku0825
yoku0825 / gist:996c2d1319506a2959c0c5d4948abd0f
Created June 8, 2021 23:24
2038年1月19日3時14分8秒 UTCを過ぎると、次のSQLをパースしたところでmysqldが落ちる
2038-01-19T03:14:16.025479Z 8 [Warning] [MY-010112] [Server] Current time has got past year 2038. Validating current time with 5 iterations before initiating the normal server shutdown process.
2038-01-19T03:14:16.025550Z 8 [Warning] [MY-010114] [Server] Iteration 1: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025561Z 8 [Warning] [MY-010114] [Server] Iteration 2: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025569Z 8 [Warning] [MY-010114] [Server] Iteration 3: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025575Z 8 [Warning] [MY-010114] [Server] Iteration 4: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025582Z 8 [Warning] [MY-010114] [Server] Iteration 5: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025590Z 8 [ERROR] [MY-010115] [Server] This MySQL server doesn't support dates later then 2038
2038-01-19T03:14:17.178509Z 0 [System] [MY-010910] [Server] /usr/sbin/