Hatena::ブログ(Diary)

いちいの日記 このページをアンテナに追加 RSSフィード

2017/01/08(Sunday)

[][] フレッツ小型 ONU + ヤマハ NVR510

ちょっと前の話ですが、ヤマハの NVR510 を買って、フレッツの ONU を小型 SFP モジュールのやつ に交換しました。

f:id:ichii386:20170108183658j:image:w360

NVR510 からは onu というデバイスが見えて、 onu1 って名前のインタフェースができてます。

# show onu-module info
ONU1:
  Ethernet Standard:  1000BASE-PX
  Vendor Name:        NTT
  Vendor ID:          0x000000
  Product Name:
  Serial Number:

差し込むとこんな感じで飛び出し気味。

f:id:ichii386:20170108183732j:image:w360

モジュールの下に2つステータス用の LED がついてます。

f:id:ichii386:20170108183733j:image:w360

とくに環境に変わりはないですが、ひかり電話を後から追加した都合、今まで ONU, HGW (RT-500MI だったかな?), ルータ (RTX1210) と 3 つの機器が 1 つにまとまったので大変スッキリしました。

NVR510 は CPU0/1 の 2 つあって IIJDS-Lite で速度もまあまあ出てるようです。

f:id:ichii386:20170108190317p:image:w360

2015/12/09(Wednesday)

[][][] split the output of mysqldump --all-databases

TWIMC

BEGIN {
    file = "header"
}

/^-- Current Database: / {
    match($0, /`[^`]+`/)
    close(file ".sql")
    file = substr($0, RSTART+1, RLENGTH-2)
}

{
    print > file ".sql"
}

2015/07/18(Saturday)

[] Re: The MySQL 5.7 Optimizer Challenge

This is just a translation of my former, explanation-less post in this Apr.

It still reproduces with 5.7.7-rc. Straightforwardly I should report a bug, but I find this: http://www.tocker.ca/2015/07/15/the-mysql-5-7-optimizer-challenge.html -- I would like a polo :)

procedure

% cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.2 LTS"
% dpkg -l|grep mysql
ii  mysql-apt-config                    0.3.5-1ubuntu14.04               all          Auto configuration for MySQL APT Repo.
ii  mysql-client                        5.7.7-rc-1ubuntu14.04            amd64        MySQL Client meta package depending on latest version
ii  mysql-common                        5.7.7-rc-1ubuntu14.04            amd64        MySQL configuration for client and server
ii  mysql-community-client              5.7.7-rc-1ubuntu14.04            amd64        MySQL Client and client tools
ii  mysql-community-server              5.7.7-rc-1ubuntu14.04            amd64        MySQL Server and server tools
% seq 1000000 > seq.out
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.7-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed

mysql> create table foo1 (id int, col1 int default null, col2 int default null, primary key (id), key (col1)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo2 (id int, col1 int default null, col2 int default null, primary key (id), key (col1)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile '/home/ichii386/seq.out' into table foo1 (id);
Query OK, 1000000 rows affected (7.85 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/home/ichii386/seq.out' into table foo2 (id);
Query OK, 1000000 rows affected (2.42 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0
mysql> explain select sql_no_cache * from foo1 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | foo1  | NULL       | ref  | PRIMARY,col1  | col1 | 5       | const |   10 |     0.50 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select sql_no_cache * from foo1 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | NULL | NULL |
|  2 | NULL | NULL |
|  3 | NULL | NULL |
|  4 | NULL | NULL |
|  5 | NULL | NULL |
|  6 | NULL | NULL |
|  7 | NULL | NULL |
|  8 | NULL | NULL |
|  9 | NULL | NULL |
| 10 | NULL | NULL |
+----+------+------+
10 rows in set (0.12 sec)

mysql> explain select sql_no_cache * from foo2 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | foo2  | NULL       | range | PRIMARY,col1  | PRIMARY | 4       | NULL |   10 |   100.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select sql_no_cache * from foo2 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | NULL | NULL |
|  2 | NULL | NULL |
|  3 | NULL | NULL |
|  4 | NULL | NULL |
|  5 | NULL | NULL |
|  6 | NULL | NULL |
|  7 | NULL | NULL |
|  8 | NULL | NULL |
|  9 | NULL | NULL |
| 10 | NULL | NULL |
+----+------+------+
10 rows in set (0.00 sec)

It use pk with foo2 of MyISAM, but not with foo1 of InnoDB, resulted with 0.12sec.