機能追加 #662
サーバー負荷調査
| ステータス: | 新規 | 開始日: | 2016/02/12 | |
|---|---|---|---|---|
| 優先度: | 通常 | 期日: | ||
| 担当者: | 山本 義治 | 進捗 %: | 0% | |
| カテゴリ: | - | 作業時間の記録: | - | |
| 対象バージョン: | - |
説明
毎日12時前後にnagiosアラートがくる
↓
muninのスレッド数が上がっている
履歴
#1 山本 義治 がほぼ9年前に更新
スレッド一覧をログ表示するcronスクリプト設置
/home/admin/thread.sh
#!/bin/sh
echo '-----'
date
cat /proc/[0-9]*/status | grep -e 'Name' -e 'Threads'
cat /proc/[0-9]*/status | grep -e 'Threads' | cut -f 2 | awk '{total += $1} END {print sprintf("total: %d", total)}'
$ crontab -e
* 12 * * * /home/admin/thread.sh >> /home/admin/thread.log
#2 山本 義治 がほぼ9年前に更新
httpプロセスがmax値に達している
[root@www14012uf ~]# ps ax | grep httpd | wc -l
67
・maxclient65→128
・ログにレスポンスタイム出力
[root@www14012uf conf]# diff httpd.conf httpd.conf.bk4
114c114
< MaxClients 128
---
> MaxClients 65
506c506
< LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %D" combined
---
> LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
[root@www14012uf conf]# /etc/init.d/httpd restart
#3 山本 義治 がほぼ9年前に更新
49.212.219.91¶
[admin@www9077ug ~]$ top
top - 12:47:44 up 453 days, 22:51, 1 user, load average: 25.27, 24.58, 15.72
Tasks: 145 total, 1 running, 144 sleeping, 0 stopped, 0 zombie
Cpu(s): 50.0%us, 49.8%sy, 0.0%ni, 0.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3922688k total, 3432928k used, 489760k free, 1392k buffers
Swap: 2097144k total, 951504k used, 1145640k free, 1634760k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1425 mysql 20 0 4202m 172m 3276 S 396.1 4.5 751366:22 mysqld
mysql> show full processlist;
+----------+-------------+-------------------+----------+---------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-------------+-------------------+----------+---------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | system user | | NULL | Connect | 39221185 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 4907 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 73194725 | root | localhost | blogroll | Query | 154 | Locked | update feeds set crawl_date = now() where id = '38' |
| 73196995 | root | 192.168.1.2:43424 | blogroll | Query | 144 | Locked | select name from feeds where id = '351' |
| 73196996 | root | 192.168.1.2:43426 | blogroll | Query | 86 | Locked | select * from entries where title like '%アジア%' and adult_flg != 1 and view_flg = 1 and entry_date > '2016-01-24 12:30:01' order by entry_date desc limit 10 |
| 73197019 | root | 192.168.1.1:37976 | blogroll | Query | 664 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197024 | root | 192.168.1.1:37983 | blogroll | Query | 658 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,社会保険労務士,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-17' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197033 | root | 192.168.1.1:37995 | blogroll | Query | 644 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,モービル,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 73197035 | root | 192.168.1.1:37997 | blogroll | Query | 643 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197036 | root | 192.168.1.1:37998 | blogroll | Query | 641 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197037 | root | 192.168.1.1:37999 | blogroll | Query | 640 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,飛び出し坊や,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 73197038 | root | 192.168.1.1:38000 | blogroll | Query | 640 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197040 | root | 192.168.1.1:38002 | blogroll | Query | 637 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197042 | root | 192.168.1.1:38004 | blogroll | Query | 635 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197045 | root | 192.168.1.1:38008 | blogroll | Query | 635 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-21') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 73197053 | root | 192.168.1.1:38016 | blogroll | Query | 613 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,モービル,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 73197056 | root | 192.168.1.1:38019 | blogroll | Query | 609 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,飛び出し坊や,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 73197070 | root | 192.168.1.1:38037 | blogroll | Query | 582 | Copying to tmp table | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,モービル,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20
テーブルロックされている
#6 山本 義治 がほぼ9年前に更新
httpdアクセスログに対して500 Internal Server Error
cakephpをデバッグモードで起動して再度アクセス
Missing Database Connection Error: A Database connection using "Mysql" was missing or unable to connect. The database server returned this error: SQLSTATE[08004] [1040] Too many connections Notice: If you want to customize this error message, create app/View/Errors/missing_connection.ctp.
mysqlへのコネクションがmaxを超えている
http://qiita.com/kenjiszk/items/c3d46ac837845281e62b
mysql> show variables like "%max_connections%";----------------------+
| Variable_name | Value |----------------------+
| max_connections | 151 |----------------------+
mysql> show processlist;
149 rows in set (0.02 sec)
top
top - 03:04:51 up 454 days, 13:08, 1 user, load average: 3.46, 2.68, 2.47 Tasks: 163 total, 1 running, 162 sleeping, 0 stopped, 0 zombie Cpu(s): 58.4%us, 10.5%sy, 0.0%ni, 14.8%id, 16.4%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 3922688k total, 3799316k used, 123372k free, 1012k buffers Swap: 2097144k total, 1173412k used, 923732k free, 1841332k cached
メモリ、CPUともにMAXに達しているのでコネクション数はこれ以上増やせそうにない
↓
webサーバーのhttpdコネクション数を減らす
StartServers 8
MinSpareServers 5
MaxSpareServers 10
ServerLimit 128
MaxClients 128
MaxRequestsPerChild 1000
↓
StartServers 8
MinSpareServers 5
MaxSpareServers 10
ServerLimit 64
MaxClients 64
MaxRequestsPerChild 1000
- 4. row *******************
Id: 31
User: root
Host: 192.168.1.1:44410
db: blogroll
Command: Query
Time: 270
State: Copying to tmp table
Info: SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT like '%,ダーツ,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 - 5. row *******************
Id: 37
User: root
Host: 192.168.1.1:44423
db: blogroll
Command: Query
Time: 260
State: Copying to tmp table
Info: SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-22') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 - 6. row *******************
selectのレスポンスが遅すぎる。。
mysql> explain SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-22') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20;---------------+----------+--------+----------------------------------------------+-----------+---------+----------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------+----------+--------+----------------------------------------------+-----------+---------+----------------------------+--------+-----------------------------+
| 1 | SIMPLE | Entry | ref | feed_id,adult_flg,original_entry_id,view_flg | adult_flg | 2 | const | 342546 | Using where; Using filesort |
| 1 | SIMPLE | Feed | eq_ref | PRIMARY,view_flg,top_view_flg | PRIMARY | 4 | blogroll.Entry.feed_id | 1 | Using where |
| 1 | SIMPLE | Category | eq_ref | PRIMARY | PRIMARY | 4 | blogroll.Entry.category_id | 1 | Using index |---------------+----------+--------+----------------------------------------------+-----------+---------+----------------------------+--------+-----------------------------+
3 rows in set (0.27 sec)
rowsが大きすぎる。
http://tech.aainc.co.jp/archives/4634
indexチューニング必要
https://www.inter-office.co.jp/contents/157/
myisamとinnodbのleft joinの性能の違いはほぼない
mysql> show variables like '%inno%';---------------------------------------------------------------+
| Variable_name | Value |---------------------------------------------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |---------------------------------------------------------------+
mysql> show engine innodb status\G
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 73, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
34861882 OS file reads, 5545 OS file writes, 3912 OS fsyncs
1 pending preads, 0 pending pwrites
11813.35 reads/s, 22471 avg bytes/read, 1.02 writes/s, 0.80 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 138, seg size 140,
19 inserts, 19 merged recs, 19 merges
Hash table size 17393, node heap has 5 buffer(s)
15611.81 hash searches/s, 30890.69 non-hash searches/s
---
LOG
---
Log sequence number 6 2715680898
Log flushed up to 6 2715680898
Last checkpoint at 6 2715680898
0 pending log writes, 0 pending chkp writes
1857 log i/o's done, 0.32 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 26269840; in additional pool allocated 1048576
Dictionary memory allocated 801640
Buffer pool size 512
Free buffers 0
Database pages 506
Modified db pages 0
Pending reads 73
Pending writes: LRU 0, flush list 0, single page 0
Pages read 47704888, created 23, written 2772
16203.68 reads/s, 0.00 creates/s, 0.49 writes/s
Buffer pool hit rate 917 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 65 queries in queue
74 read views open inside InnoDB
Main thread process no. 22611, id 140665501198080, state: sleeping
Number of rows inserted 668, updated 60, deleted 0, read 239048364
0.00 inserts/s, 0.02 updates/s, 0.00 deletes/s, 72401.80 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
http://blog.flatlabs.net/20100727_212649/
160225 04:19:02 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160225 04:20:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160225 4:20:04 InnoDB: Initializing buffer pool, size = 512.0M
160225 4:20:04 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
160225 4:20:04 [ERROR] Plugin 'InnoDB' init function returned error.
160225 4:20:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160225 4:20:05 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
160225 4:20:05 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000152' at position 3882182, relay log './mysqld-relay-bin.014700' position: 251
160225 4:20:05 [Note] Slave I/O thread: connected to master 'repl@49.212.214.23:3306',replication started in log 'mysql-bin.000152' at position 3882182
160225 4:20:05 [Note] Event Scheduler: Loaded 0 events
160225 4:20:05 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
slaveとして起動している!?
にもかかわらずcronでslaveに対してupdateとしている
[root@www9077ug admin]# crontab -e
0 * * * * /var/www/blogroll/cakephp/app/cron/rss_check.sh
my.cnf
#server-id = 2
skip-slave-start
160225 4:47:20 InnoDB: Initializing buffer pool, size = 512.0M
160225 4:47:20 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
160225 4:47:20 [ERROR] Plugin 'InnoDB' init function returned error.
160225 4:47:20 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
innodb失敗
[root@www9077ug admin]# /etc/init.d/mysqld stop
[root@www9077ug admin]# rm /var/lib/mysql/ib_logfile0
[root@www9077ug admin]# /etc/init.d/mysqld restart
160225 04:48:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160225 4:48:43 InnoDB: Initializing buffer pool, size = 512.0M
160225 4:48:43 InnoDB: Completed initialization of buffer pool
160225 4:48:43 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
160225 4:48:44 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
160225 4:48:45 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 294125, file name ./mysql-bin.000019
160225 4:48:46 InnoDB: Started; log sequence number 6 2715695116
160225 4:48:46 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
160225 4:48:46 [Note] Event Scheduler: Loaded 0 events
160225 4:48:46 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
無事masterのinnodbとして起動
mysql> show variables like '%inno%'
> ;------------------------+
| Variable_name | Value |
-------------------------------------------------------------------------------------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 536870912 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 134217728 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |---------------------------------------------------------------+
innodb_buffer_pool_size, innodb_log_file_sizeともに更新されていること確認
entryページは初回10秒程度、2回目以降は数秒に改善
トップページは相変わらず遅い。。
mysql> show full processlist;
+-----+------+-------------------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-------------------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 48 | root | 192.168.1.2:34085 | blogroll | Query | 95 | Sorting result | select * from entries where category_id = '12' and adult_flg != 1 order by entry_date desc limit 10 |
| 51 | root | 192.168.1.2:34100 | blogroll | Query | 608 | Sending data | select
fl.feed_id,
fl.referrer_feed_id,
fl.part_id,
fs.show_rule,
fs.show_span,
fs.show_match,
fs.keyword,
ds.view_num,
et.id,
et.title,
et.description,
et.contents,
et.link_url,
et.image_url,
et.image_flg,
et.entry_date,
fd.name
from
feed_lists as fl
left join feed_sets as fs on fl.part_id = fs.part_id
left join designs as ds on fl.part_id = ds.part_id
left join entries as et on fl.referrer_feed_id = et.feed_id
left join feeds as fd on fl.referrer_feed_id = fd.id
where
fl.block_flg != 1
and fs.show_feed = 1
and et.entry_date > '2015-02-25'
order by et.entry_date desc |
| 52 | root | 192.168.1.2:34101 | blogroll | Query | 132 | Sorting result | select * from entries where title like '%アジア%' and adult_flg != 1 and view_flg = 1 and entry_date > '2016-01-25 04:50:01' order by entry_date desc limit 10 |
| 93 | root | 192.168.1.1:57403 | blogroll | Query | 534 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,生駒里奈,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 99 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 106 | root | 192.168.1.1:57445 | blogroll | Query | 523 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,å¿«æ„Ÿ,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 111 | root | 192.168.1.1:57475 | blogroll | Query | 513 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where CONCAT(',',entries.tags,',') like '%,むみんぼうのやり,%' and feeds.top_view_flg = 1 and entries.adult_flg = 0 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 139 | root | 192.168.1.1:57561 | blogroll | Query | 467 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-22') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 146 | root | 192.168.1.1:57577 | blogroll | Query | 456 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,薄手,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 152 | root | 192.168.1.1:57600 | blogroll | Query | 441 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,マックス,%' or CONCAT(',',entries.tags,',') like '%,ベイ,%' or CONCAT(',',entries.tags,',') like '%,ゴーゴー,%' or CONCAT(',',entries.tags,',') like '%,め,%' or CONCAT(',',entries.tags,',') like '%,しこ,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 0 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 160 | root | 192.168.1.1:57622 | blogroll | Query | 434 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,百瀬,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 161 | root | 192.168.1.1:57628 | blogroll | Query | 431 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,ネジザウルス,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 169 | root | 192.168.1.1:57662 | blogroll | Query | 424 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,ゲームテック,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 173 | root | 192.168.1.1:57678 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 174 | root | 192.168.1.1:57680 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 175 | root | 192.168.1.1:57683 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 177 | root | 192.168.1.1:57690 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 178 | root | 192.168.1.1:57692 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 179 | root | 192.168.1.1:57693 | blogroll | Query | 419 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,貴,%' or CONCAT(',',entries.tags,',') like '%,姉,%' or CONCAT(',',entries.tags,',') like '%,ケツ,%' or CONCAT(',',entries.tags,',') like '%,勉強,%' or CONCAT(',',entries.tags,',') like '%,浪人,%' or CONCAT(',',entries.tags,',') like '%,生活,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 1 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 184 | root | 192.168.1.1:57716 | blogroll | Query | 414 | Sorting result | select
entries.id as entry_id,
entries.title as entry_title,
entries.link_url as link_url,
entries.image_url as image_url,
entries.image_flg as image_flg,
entries.entry_date as entry_date,
entries.facebook_like_count,
entries.twitter_retweet_count,
entries.hatena_bookmark_count,
entries.view_count,
feeds.id as feed_id,
feeds.name as feed_title
from entries left join feeds on entries.feed_id = feeds.id
left join categories on feeds.category_id = categories.id
where (CONCAT(',',entries.tags,',') like '%,ファイアーエムブレム,%' or CONCAT(',',entries.tags,',') like '%,if,%' or CONCAT(',',entries.tags,',') like '%,悲報,%' or CONCAT(',',entries.tags,',') like '%,キャラ,%' or CONCAT(',',entries.tags,',') like '%,モード,%' or CONCAT(',',entries.tags,',') like '%,海外,%' or CONCAT(',',entries.tags,',') like '%,削除,%' )and feeds.top_view_flg = 1 and entries.adult_flg = 0 and (entries.original_entry_id is null or entries.original_entry_id = 0) and entries.view_flg = 1 and feeds.view_flg = 1
order by entries.entry_date desc
limit 200 |
| 186 | root | 192.168.1.1:57736 | blogroll | Query | 412 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,身体,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 196 | root | 192.168.1.1:57766 | blogroll | Query | 397 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,私立,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 199 | root | 192.168.1.1:57783 | blogroll | Query | 394 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,エレクトリカル,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-18' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 200 | root | 192.168.1.1:57786 | blogroll | Query | 392 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`entry_date` >= '2016-02-22') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`original_entry_id` is null or `Entry`.`original_entry_id` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1) AND (`Feed`.`top_view_flg` = 1)) ORDER BY `Entry`.`rank` desc, `Entry`.`entry_date` desc LIMIT 20 |
| 205 | root | 192.168.1.1:57803 | blogroll | Query | 376 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,輪,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-18' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 218 | root | 192.168.1.1:57838 | blogroll | Query | 356 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,Ads,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 219 | root | 192.168.1.1:57840 | blogroll | Query | 355 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,猿,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 221 | root | 192.168.1.1:57844 | blogroll | Query | 353 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,華麗,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2015-02-25' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 223 | root | 192.168.1.1:57851 | blogroll | Query | 237 | Sending data | SELECT `Entry`.`id`, `Entry`.`feed_id`, `Entry`.`category_id`, `Entry`.`title`, `Entry`.`description`, `Entry`.`contents`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`image_flg`, `Entry`.`entry_date`, `Entry`.`created`, `Entry`.`updated`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Entry`.`tags`, `Entry`.`adult_flg`, `Entry`.`2ch_url`, `Entry`.`2ch_content`, `Entry`.`2ch_entry_date`, `Entry`.`tweet_bot_flg`, `Entry`.`original_entry_id`, `Entry`.`view_flg`, `Entry`.`item_id_list`, `Entry`.`rank` FROM `blogroll`.`entries` AS `Entry` WHERE `Entry`.`feed_id` = (139) |
| 225 | root | 192.168.1.1:57855 | blogroll | Query | 348 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,食卓,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 234 | root | 192.168.1.1:57885 | blogroll | Query | 333 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,組み合わせ,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2015-02-25' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 249 | root | 192.168.1.1:57936 | blogroll | Query | 306 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,知ってる,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 266 | root | 192.168.1.1:58096 | blogroll | Query | 270 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,ドスケベ,%') AND (`Entry`.`adult_flg` = 1) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 267 | root | 192.168.1.1:58099 | blogroll | Query | 264 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,えな,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 269 | root | 192.168.1.1:58103 | blogroll | Query | 263 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,超人,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 272 | root | 192.168.1.1:58112 | blogroll | Query | 260 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,溺水,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`entry_date` desc LIMIT 20 |
| 277 | root | 192.168.1.1:58122 | blogroll | Query | 244 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,Kリーグ,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-25' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 281 | root | 192.168.1.1:58133 | blogroll | Query | 238 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,交渉人,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-25' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20 |
| 283 | root | 192.168.1.1:58137 | blogroll | Query | 238 | Sorting result | SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT(',',`Entry`.`tags`,',') like '%,コスプレ,%') AND (`Entry`.`adult_flg` = 1) AND (`Entr#7 山本 義治 がほぼ9年前に更新
top - 15:50:30 up 455 days, 1:54, 1 user, load average: 6.32, 6.15, 5.72
Tasks: 156 total, 1 running, 155 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.2%us, 1.7%sy, 0.0%ni, 47.2%id, 45.9%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3922688k total, 3771344k used, 151344k free, 2160k buffers
Swap: 2097144k total, 797992k used, 1299152k free, 1443596k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24016 mysql 20 0 2899m 694m 3280 S 17.3 18.1 62:20.79 mysqld
5052 root 20 0 727m 52m 2972 S 3.0 1.4 5824:58 PM2 v0.14.7: Go
10172 munin 20 0 112m 16m 2132 S 1.3 0.4 0:00.25 munin-update
31273 root 20 0 1080m 84m 2712 S 0.7 2.2 1350:52 PM2: Keymetrics
58 root 20 0 0 0 0 D 0.3 0.0 40:58.69 kswapd0
881 root 20 0 0 0 0 S 0.3 0.0 705:58.87 flush-252:0
10174 root 20 0 46264 3360 1124 S 0.3 0.1 0:00.11 munin-nodeフルテキスト検索で負荷かかっている
like文はinnodbと相性わるい?
http://nob-log.info/2012/03/22/install-mroonga/
ここを参考にmroonga導入
[root@www9077ug admin]# yum install -y groonga-tokenizer-mecab
導入前
mysql> explain SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT like '%,チャールストーマス,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-18' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20;---------------+----------+--------+----------------------------+-----------+---------+----------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------+----------+--------+----------------------------+-----------+---------+----------------------------+--------+-----------------------------+
| 1 | SIMPLE | Entry | ref | feed_id,adult_flg,view_flg | adult_flg | 2 | const | 258040 | Using where; Using filesort |
| 1 | SIMPLE | Feed | eq_ref | PRIMARY,view_flg | PRIMARY | 4 | blogroll.Entry.feed_id | 1 | Using where |
| 1 | SIMPLE | Category | eq_ref | PRIMARY | PRIMARY | 4 | blogroll.Entry.category_id | 1 | Using index |---------------+----------+--------+----------------------------+-----------+---------+----------------------------+--------+-----------------------------+
3 rows in set (0.54 sec)
like文+entry_date指定
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((CONCAT like '%,チャールストーマス,%') AND (`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-18' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20;
Empty set (4 min 27.82 sec)
like文なし＋entry_date指定
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) AND `Entry`.`entry_date` >= '2016-02-18' ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20;
like文なし＋entry_date指定なし
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY (`Entry`.`facebook_like_count` + `Entry`.`twitter_retweet_count` + `Entry`.`hatena_bookmark_count` + `Entry`.`view_count`) desc LIMIT 20;
orderなし
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) LIMIT 20;
20 rows in set (0.00 sec)
→爆速
単独indexのみorder
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) ORDER BY `Entry`.`twitter_retweet_count` desc LIMIT 20;
→爆速
orderは複合indexで解決!?
mysql> desc entries;-----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |-----------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| feed_id | int(11) | YES | MUL | NULL | |
| category_id | int(11) | YES | MUL | NULL | |
| title | varchar(255) | YES | | NULL | |
| description | mediumtext | YES | | NULL | |
| contents | mediumtext | YES | | NULL | |
| link_url | varchar(255) | YES | UNI | NULL | |
| image_url | varchar(255) | YES | | NULL | |
| image_flg | tinyint(4) | YES | MUL | NULL | |
| entry_date | datetime | YES | | NULL | |
| created | datetime | YES | | NULL | |
| updated | datetime | YES | | NULL | |
| facebook_like_count | int(11) | YES | MUL | NULL | |
| twitter_retweet_count | int(11) | YES | MUL | NULL | |
| hatena_bookmark_count | int(11) | YES | MUL | NULL | |
| view_count | int(11) | YES | MUL | 0 | |
| tags | mediumtext | YES | | NULL | |
| adult_flg | tinyint(4) | YES | MUL | NULL | |
| 2ch_url | varchar(255) | YES | | NULL | |
| 2ch_content | mediumtext | YES | | NULL | |
| 2ch_entry_date | datetime | YES | | NULL | |
| tweet_bot_flg | tinyint(4) | YES | MUL | 0 | |
| original_entry_id | int(11) | YES | MUL | NULL | |
| view_flg | tinyint(4) | YES | MUL | 1 | |
| item_id_list | varchar(255) | YES | | NULL | |
| rank | tinyint(4) | YES | MUL | 1 | |-----------------------------------+------+-----+---------+----------------+
26 rows in set (0.03 sec)
mysql> show index from entries;-------------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |-------------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| entries | 0 | PRIMARY | 1 | id | A | 665037 | NULL | NULL | | BTREE | |
| entries | 0 | link_url | 1 | link_url | A | 665037 | NULL | NULL | YES | BTREE | |
| entries | 1 | feed_id | 1 | feed_id | A | 291 | NULL | NULL | YES | BTREE | |
| entries | 1 | category_id | 1 | category_id | A | 23 | NULL | NULL | YES | BTREE | |
| entries | 1 | image_flg | 1 | image_flg | A | 20 | NULL | NULL | YES | BTREE | |
| entries | 1 | facebook_like_count | 1 | facebook_like_count | A | 23 | NULL | NULL | YES | BTREE | |
| entries | 1 | twitter_retweet_count | 1 | twitter_retweet_count | A | 737 | NULL | NULL | YES | BTREE | |
| entries | 1 | hatena_bookmark_count | 1 | hatena_bookmark_count | A | 5363 | NULL | NULL | YES | BTREE | |
| entries | 1 | adult_flg | 1 | adult_flg | A | 18 | NULL | NULL | YES | BTREE | |
| entries | 1 | tweet_bot_flg | 1 | tweet_bot_flg | A | 20 | NULL | NULL | YES | BTREE | |
| entries | 1 | original_entry_id | 1 | original_entry_id | A | 23 | NULL | NULL | YES | BTREE | |
| entries | 1 | view_flg | 1 | view_flg | A | 20 | NULL | NULL | YES | BTREE | |
| entries | 1 | view_count | 1 | view_count | A | 533 | NULL | NULL | YES | BTREE | |
| entries | 1 | rank | 1 | rank | A | 20 | NULL | NULL | YES | BTREE | |-------------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> alter table entries add index (facebook_like_count,twitter_retweet_count,hatena_bookmark_count);
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) order by `Entry`.`facebook_like_count`,`Entry`.`twitter_retweet_count`,`Entry`.`hatena_bookmark_count` desc LIMIT 20;
結果変わらない。。
複合index一旦削除
mysql> alter table entries drop index facebook_like_count_2;
#8 山本 義治 がほぼ9年前に更新
fulltext検索はmyisamのみ（5.6系〜innodbのfulltext対応）
myisamに戻す
mysql> alter table categories engine = myisam;
mysql> alter table feeds engine = myisam;
mysql> alter table entries engine = myisam;
mysql> explain SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) order by `Entry`.`facebook_like_count` desc LIMIT 20;---------------+----------+--------+----------------------------+---------------------+---------+----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------+----------+--------+----------------------------+---------------------+---------+----------------------------+------+-------------+
| 1 | SIMPLE | Entry | index | feed_id,adult_flg,view_flg | facebook_like_count | 5 | NULL | 25 | Using where |
| 1 | SIMPLE | Feed | eq_ref | PRIMARY,view_flg | PRIMARY | 4 | blogroll.Entry.feed_id | 1 | Using where |
| 1 | SIMPLE | Category | eq_ref | PRIMARY | PRIMARY | 4 | blogroll.Entry.category_id | 1 | Using index |---------------+----------+--------+----------------------------+---------------------+---------+----------------------------+------+-------------+
3 rows in set (0.02 sec)
mysql> SELECT `Entry`.`id`, `Entry`.`title`, `Entry`.`link_url`, `Entry`.`image_url`, `Entry`.`entry_date`, `Entry`.`tags`, `Entry`.`facebook_like_count`, `Entry`.`twitter_retweet_count`, `Entry`.`hatena_bookmark_count`, `Entry`.`view_count`, `Feed`.`name` FROM `blogroll`.`entries` AS `Entry` LEFT JOIN `blogroll`.`feeds` AS `Feed` ON (`Entry`.`feed_id` = `Feed`.`id`) LEFT JOIN `blogroll`.`categories` AS `Category` ON (`Entry`.`category_id` = `Category`.`id`) WHERE ((`Entry`.`adult_flg` = 0) AND (`Entry`.`view_flg` = 1) AND (`Feed`.`view_flg` = 1)) order by `Entry`.`facebook_like_count` desc LIMIT 20;
20 rows in set (0.06 sec)
→爆速
fulltext検索とsort系はmyisamが圧倒的に速い
varnish起動
[root@www14012uf admin]# /etc/init.d/varnish start