メモ > 技術 > データベース: MySQL > チューニングテスト
■チューニングテスト
■テーブル
以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていく
ツイートテーブルとユーザテーブルを連結して一覧表示
キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい
CREATE TABLE `tweets` (
`id` varchar(80) NOT NULL COMMENT 'ID',
`created` datetime NOT NULL COMMENT '登録日時',
`user_id` varchar(80) NOT NULL COMMENT 'ユーザID',
`text` text NOT NULL COMMENT 'ツイート内容',
`retweeted_id` varchar(80) DEFAULT NULL COMMENT 'リツイートID',
`retweeted_user_id` varchar(80) DEFAULT NULL COMMENT 'リツイートユーザID',
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ツイート';
CREATE TABLE `users` (
`id` varchar(80) NOT NULL COMMENT 'ID',
`created` datetime NOT NULL COMMENT '登録日時',
`name` varchar(255) NOT NULL COMMENT '名前',
`screen_name` varchar(80) NOT NULL COMMENT 'スクリーンネーム',
`location` varchar(80) DEFAULT NULL COMMENT '位置情報',
`description` text COMMENT '説明',
`url` varchar(255) DEFAULT NULL COMMENT 'URL',
`image_url` varchar(255) NOT NULL COMMENT '画像URL',
`followers_count` int(11) NOT NULL COMMENT 'フォロワー数',
`friends_count` int(11) NOT NULL COMMENT 'フォロー数',
`listed_count` int(11) NOT NULL COMMENT 'リスト数',
`statuses_count` int(11) NOT NULL COMMENT 'リツイート数',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザ';
■環境
CentOS 7 + nginx 1.12.2 + MySQL 5.7.21
https://refirio.net/twitter/
■検証内容
30万件くらいまでは、何も考えずに作れる
30万件くらいになると、「データ比較」「データ推移」が少し重い。最初の一覧やキーワード検索もほんの少し重いがまだ問題ない
50万件くらいでも十分に早い。Apache→nginxで、PHPもMySQLも新しくしているからか
60万件くらいになると、CPU使用率でuserの使用が確認できるようになってきた。ただし0.6%程度なので大したものではない
80万件くらいになると、最初の一覧でもはっきり遅いと感じる。「データ比較」はかなり重い
120万件
トップページの表示が3秒ほどかかる
データ取得時に記事数をキャッシュするようにし、それをもとにページ数などを求めるように変更
トップページが2秒ほどで表示されるようになった
levis標準機能のエクスポートではエラーになるが、execコマンドでmysqldumpを実行すればダンプできる
200万件
トップページの表示が3〜4秒ほどかかる
キーワード検索すると8〜10秒ほどかかる
データ比較は20秒ほどかかる
データ推移は3秒ほどかかる
CPU使用率は2〜3%くらい
実用的ではない程度には重い
インデックスを張る(3つ目だけで良かったかもしれないが要検証)
ALTER TABLE users ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (created);
トップページの表示が一瞬になった
キーワード検索も一瞬になった
データ比較とデータ推移は1〜2秒で表示されるようになった
CPU使用率も下がった(2〜3%あった使用率が0.7%くらいになった)
SWAPの発生も20〜40KB/sあったものが0.01KB/sくらいになった
全体的に明確な改善が見られる
300万件
トップページの表示は一瞬
キーワード検索も一瞬
データ比較とデータ推移は1〜2秒で表示される
ユーザごとのページは3秒くらいかかる
400万件
(300万件のときと大差なし)
600万件
(300万件のときと大差なし)
1200万件
(300万件のときと大差なし)
1900万件
トップページの表示は一瞬
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される
ユーザごとのページは5秒くらいかかる
2700万件
トップページの表示は一瞬
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される
ユーザごとのページも1〜2秒で表示される
何故か早くなっているが、さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明