Category: MySQL

ib_logfile*

ibdata* is a shared table space (It managed all data)
ib_logfile* seems to be a log file.

Data is not updated directory to the table space, but once the update contents are written to the log file.
After that, it seems that the flow is reflected in table space.
Tablespace updates are expensive and are not reflected immediately.
By writing to the log file, the writing performance is improved.

中身を見てみましょう

cd /var/lib/mysql
ls
sud cat ib_logfile0

なんじゃこりゃーー

InnoDB

– Default storage engine from MySQL5.5
– General purpose storage engine with good balance of reliability and performance.
– DML operations process transactions according to the ACID model, such as commit rollback and crash recovery
– Data is aligned on disk so that queries are optimized based on primary key
– Operations that call different storage engine tables can be mixed in one query
– Foreign key constraints are also supported
– InnoDB stores in tablespaces whereas MyISAM always stores data in file
– The good thing about MyISAM is that it can be easily backed up and copied in units of tables, but innoDB is still poorly managed in this respect.
– Table spaces can be stored in files, but can also be written directly to partitions.
– There is unique buffer pool for caching data and indexes in many mamory, which helps high speed data access.

う～ん、。。。

ibdata1には何が入っているのか？

$ sudo cat /var/lib/mysql/ibdata1
ん？

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

checksum以外は、FIL_PAGE_*だな。FILって何の略だろうか？

ibdata1には何が入っているのか？
– データディクショナリ(InnoDBテーブル)
– チェンジバッファ
– ダブルライトバッファ
– UNDOログ

んん？？

Take a look at the MySQL binary log

The binary log has not been output yet.
[vagrant@localhost ~]$ ls /var/lib/mysql/
addb count equity ibdata1 mysql.sock zeus
auto.cnf demo ib_logfile0 laravel57 performance_schema
click dev ib_logfile1 mysql test

Add log-bin and configure to output binary log.
log-binを足します。
[vagrant@localhost ~]$ sudo vi /etc/my.cnf
[vagrant@localhost ~]$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character_set_server=utf8
default-storage-engine=InnoDB
innodb_file_per_table

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
log-bin

[vagrant@localhost ~]$ sudo service mysqld restart
mysqld を停止中: [ OK ]
mysqld を起動中: [ OK ]
[vagrant@localhost ~]$ ls /var/lib/mysql/
addb count equity ibdata1 mysql.sock zeus
auto.cnf demo ib_logfile0 laravel57 performance_schema
click dev ib_logfile1 mysql test

あれ、、、、、、、、、、、、　bin-log出てないやんけ。おかしいな。。

mysql> create database sample;
Query OK, 1 row affected (0.02 sec)

mysql> quit
Bye
[vagrant@localhost ~]$ ls /var/lib/mysql/

log-binの追加する位置が悪いか。。

statement handle

A statement handle is a data object that describes on SQL statement and tracks its execution. You can execute a statement only by allocating a statement handle.

SQLAllocHandle() (with HandleType set to SQL_HANDLE_STMT) allocates a statement handle to describe an SQL statement. Descriptions of SQL statements include information such as statement attributes, SQL statement text, dynamic parameters, cursor information, dynamic arguments and column bindings, result values, and status information. Each statement handle associates a connection with statement that the handle describes.

What is bind?

Bind variables are variables used in SQL statements, which are languages that perform writing and query to a database.

When making similar processing requests repeatedly while changing only a certain number to the database, if you re-set the number of the SQL statement each time, many types of SQL statements will be processed, which is inefficient. In such a case, if the bind variable is used, the database recognizes that “the same SQL statement is repeated processed under another condition”, so that the processing time can be efficiently improved and the processing time can be shortened.

In the case of Oracle Database, bind variables are represented by adding a colon(:) to the beginning of the variable name(e.g :number)

$sql = ‘select name, breed, weight from animals where weight > ? and weight < ?'; $conn = db2_connect($database, $user, $password); $stmt = db2_prepare($conn, $sql); $lower_limit = 1; db2_bind_param($stmt, 1, "lower_limit", DB2_PARAM_IN); db2_bind_param($stmt, 2, "upper_limit", DB2_PARAM_IN); $upper_limit = 15.0; if(db2_execute($stmt)){ while ($row = db2_fetch_array($stmt)){ print "{$row[0]}, {$row[1]}, {$row[2]}\n"; } } [/php]

Database Handles

Database handle are fully encapsulated objects. Transactions from one database handle do not cross over or leak with one another.

$rc = $dbh->do($statement) || die $dbh->errstr;
$rc = $dbh->do($statement, \%attr) ||die $dbh->errstr;

$rv = $dbh->do($statement, \%attr,@bind_values) || ...

my $rows =$dbh->do(q{
	DELETE FROM table
},undef,'DONE') || die $dbh->errstr;

$art_ref =$dbh->selectall_arrayref($statement);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);

$sth = $dbh->prepare($statement) || die $dbh->errstr;
$sth = $dbh->prepare($statement, \%attr) ||die $dbh->errstr;

my $sth =$dbh->@prepare("SELECT * from mytable where name like ?");
$sth->bind_param(1, 'J%');

$sth = $dbh->prepare_cached($statement) || die $dbh->errstr;
$sth = $dbh->prepare_cached($statement, \%attr) || die $dbh->errstr;

すげー、相当頑張らねーと。。。

Window function

The window function performs calculations across a set of table rows that are somehow related to the current row. This is similar to the form of calculation performed by aggregate functions. However, unlike regular aggregation functions, the use of window functions does not group rows into a single output row. Each row maintains its own identity.

Windows functions perform aggregation processing(ranking, etc) on acquired(SELECT) data. It can be said that it is an essential function in the recent trend of big data and OLAP.

SELECT
  Start_station_name,
  Duration,
  SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
 FROM
  trip_histories
 WHERE
  Start_time < '2019-01-30'
 LIMIT 10;

SELECT
 Start_station_name,
 Duration,
 SUM(Duration) OVER (PARTITION BY Start_station_name) AS running_total,
 COUNT(Duration) OVER (PARTITION BY Start_station_name) AS running_count,
 AVG(Duration) OVER (PARTITION BY Start_station_name) AS running_avg
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

SELECT
 ROW_NUMBER() OVER (order by Start_time DESC) AS row_num,
 Start_station_name,
 Start_time,
 Duration
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

なるほどー、これは数こなさないとな～

What is CTE?

What is CTE?
– CTE is a so-called “recursive query”
– The WOTH clause has become available
　→ Implemented on other RDMS(PostgreSQL, Oracle etc)
– Hierarchical queries can be written very easily
　→ In the past, it had to be nested using subueries.

WITH RECURSIVE Hatoyama AS
(
 select id, last_name, first_name from family
   where last_name= "鳩山" and first_name="和夫"
 union all
   select child.id, child.last_name, child.first_name
   from family as child, Hatoyama
    where Hatoyama.id = child.parent_id
)
select * from Hatoyama;

WITH Yoshida as
(
	select id, last_name, first_name from family
	  where last_name="吉田" and parent id is null
	union all
	  select child.id, child.last_name, child.first_name
	  from family as child, Yoshida
	  where yoshida.id= child.parent_id
)
select * from Yoshida;

select文が階層化しないようにできるわけね。

mysqlのアップグレード

[vagrant@localhost ~]$ sudo yum remove -y mysql-community-release-el7-5.noarch
読み込んだプラグイン:fastestmirror
削除処理の設定をしています
引数に一致しません: mysql-community-release-el7-5.noarch
Determining fastest mirrors
* base: ftp.yz.yamagata-u.ac.jp
* extras: ftp.yz.yamagata-u.ac.jp
* remi-safe: mirror.xeonbd.com
* updates: mirror.vodien.com
base | 3.7 kB 00:00
extras | 3.4 kB 00:00
extras/primary_db | 29 kB 00:00
jenkins | 2.9 kB 00:00
jenkins/primary_db | 127 kB 00:01
mariadb | 2.9 kB 00:00
mysql-connectors-community | 2.5 kB 00:00
mysql-tools-community | 2.5 kB 00:00
mysql56-community | 2.5 kB 00:00
nginx | 2.9 kB 00:00
nodesource | 2.5 kB 00:00
remi-safe | 3.0 kB 00:00
remi-safe/primary_db | 1.2 MB 00:02
updates | 3.4 kB 00:00
updates/primary_db | 3.7 MB 00:08

なんかものすごく怖いな。。

[vagrant@localhost ~]$ sudo yum localinstall -y http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
読み込んだプラグイン:fastestmirror
ローカルパッケージ処理の設定をしています
mysql57-community-release-el7-7.noarch.rpm | 8.8 kB 00:00
/var/tmp/yum-root-w9oAJm/mysql57-community-release-el7-7.noarch.rpm を調べています: mysql57-community-release-el7-7.noarch
/var/tmp/yum-root-w9oAJm/mysql57-community-release-el7-7.noarch.rpm をインストール済みとして設定しています
Loading mirror speeds from cached hostfile
* base: ftp.yz.yamagata-u.ac.jp
* extras: ftp.yz.yamagata-u.ac.jp
* remi-safe: mirror.xeonbd.com
* updates: mirror.vodien.com
依存性の解決をしています
–> トランザクションの確認を実行しています。
—> Package mysql57-community-release.noarch 0:el7-7 will be インストール
–> 衝突を処理しています: mysql57-community-release-el7-7.noarch は mysql-community-release と衝突しています
No package matched to upgrade: mysql57-community-release
–> 依存性解決を終了しました。
エラー: mysql57-community-release conflicts with mysql-community-release-el6-5.noarch
問題を回避するために –skip-broken を用いることができません
これらを試行できます: rpm -Va –nofiles –nodigest

なんだ。。

[vagrant@localhost ~]$ sudo yum swap -y — remove mysql-community-release — install http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
読み込んだプラグイン:fastestmirror
コマンド「swap」が見つかりません。「/usr/bin/yum –help」を実行してください。

あああああああ、ちょっとあんまりやりたくないなー