Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса, большая выборка для экспорта в XML / 15 сообщений из 15, страница 1 из 1
18.11.2014, 20:11:39
    #38810079
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
Добрый день, есть скрипт экспорта объявлений в XML файл. Число позиций в таблице 'ad' выросло до 30K. Начались задержки в 6-8 секунд на выборку. Есть мысли что запрос не до конца оптимален.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT ads.*
       users.phone, users.firstName AS first_name, users.email AS user_email,
       agencies.name AS agency_name, agencies.phone AS agency_phone,
       agencies.email AS agency_email, agencies.site AS agency_site, 
       IFNULL(images.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
LEFT JOIN images
	 ON (images.ad_id = ads.id)
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
WHERE ads.publish_status = 4
	 AND ads.object_type IN (1, 2, 3, 4)
GROUP BY unq_ancestor



Вывод EXPLAIN: https://www.dropbox.com/s/ix1i0wrus9v3e7h/explain.jpg?dl=0

Может быть дело во временной таблице 'users' ?

Сейчас 30k выполняется за 6 секунд, если будет 300k записей это будет уже минута, что слишком долго для этого места.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2014, 21:20:24
    #38810138
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
adrocket,

0. какая версия МЫСКЛ? 5.1, 5.5 или 5.6?

1. да, 6 секунд это многовато.
Експолейн выглядит не плохо но есть непонятки.

2. проведите SHOW CREATE TABLE для всех таблиц.

3. проверьте логику ЛЕФТ джоинов.

4. из-за большого количества таблиц и смеси
ЛЕФТ и обычных джоинтов оптимизатор выбрал USER
как начальную таблицу и не использовал индексы
на АДС.статус и обжект_тупе.

5. Похоже что запрос вывалился на диск --- это возможно если
маленький жоинт буфер или по дороге вытягивается
большие поля --- напромер весь ИМАЖЕ -- который потом не нужен.

6. можно попробовать отоптимизировать
этот запрос последовательным приближением,
по порядки с замером времени и ЕКСПЛЕЙНОМ:

6.1

Код: sql
1.
2.
3.
4.
5.
select
  *
from ADS
where ads.publish_status = 4
	 AND ads.object_type IN (1, 2, 3, 4)




6.2 предыдушее плюс ЮЗЕР

6.3 предыдушее плюс agencies

6.4 предыдушее плюс имаже

7. .... а там и посмотрим куда дальше плыть
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2014, 22:18:01
    #38810161
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
Огромное спасибо, не ожидал такой быстрый о четкой ответ увидеть!

Запалил я конечно свою базу конкурентам :)

0.

Код: plaintext
1.
2.
3.
4.
innodb_version          5.6.19
protocol_version        10
version                 5.6.19-0ubuntu0.14.04.1
version_compile_machine x86_64
version_compile_os      debian-linux-gnu

2.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
CREATE TABLE `ads` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `external_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `external_url` varchar(256) NOT NULL DEFAULT '',
 `status` tinyint(4) NOT NULL,
 `publish_status` tinyint(4) NOT NULL,
 `moderation_comment` varchar(512) NOT NULL DEFAULT '',
 `user_id` mediumint(8) unsigned NOT NULL,
 `boards_to_publish` varchar(255) NOT NULL DEFAULT '',
 `created` datetime NOT NULL,
 `touch` datetime NOT NULL,
 `title` varchar(512) NOT NULL DEFAULT '',
 `offer_type` tinyint(4) NOT NULL,
 `object_type` tinyint(4) NOT NULL,
 `geo_coordinates` varchar(64) NOT NULL DEFAULT '',
 `geo_address` varchar(512) NOT NULL DEFAULT '',
 `geo_country` varchar(255) NOT NULL DEFAULT '',
 `geo_obl` varchar(256) NOT NULL DEFAULT '',
 `geo_raion` varchar(256) NOT NULL DEFAULT '',
 `geo_city_raion` varchar(256) NOT NULL DEFAULT '',
 `geo_place` varchar(256) NOT NULL DEFAULT '',
 `geo_street` varchar(256) NOT NULL DEFAULT '',
 `geo_house` varchar(64) NOT NULL DEFAULT '',
 `geo_metro` varchar(128) NOT NULL DEFAULT '',
 `rent_type` tinyint(4) NOT NULL DEFAULT '0',
 `commission_agent` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `is_mortgage_possible` tinyint(4) NOT NULL DEFAULT '0',
 `is_rent_need_deposit` tinyint(4) NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `is_new_building` tinyint(4) NOT NULL DEFAULT '0',
 `ready_date` date NOT NULL,
 `year_build` smallint(5) unsigned NOT NULL DEFAULT '0',
 `building_type` tinyint(4) NOT NULL DEFAULT '0',
 `commercial_type` tinyint(4) NOT NULL DEFAULT '0',
 `land_type` tinyint(4) NOT NULL DEFAULT '0',
 `garage_type` tinyint(4) NOT NULL DEFAULT '0',
 `wall_material` tinyint(4) NOT NULL DEFAULT '0',
 `house_type` tinyint(4) NOT NULL DEFAULT '0',
 `series_building` varchar(128) NOT NULL DEFAULT '',
 `floor` tinyint(4) NOT NULL DEFAULT '0',
 `floor_total` tinyint(4) NOT NULL DEFAULT '0',
 `room_total` smallint(5) unsigned NOT NULL DEFAULT '0',
 `room_count_for_sale_rent` tinyint(4) NOT NULL DEFAULT '0',
 `square_total` float NOT NULL DEFAULT '0',
 `square_living` float NOT NULL DEFAULT '0',
 `square_kitchen` float NOT NULL DEFAULT '0',
 `square_room` float NOT NULL DEFAULT '0',
 `square_land` float NOT NULL DEFAULT '0',
 `square_living_rooms` varchar(128) NOT NULL DEFAULT '',
 `layout` tinyint(4) NOT NULL DEFAULT '0',
 `ceiling_height` float NOT NULL DEFAULT '0',
 `wc_type` tinyint(4) NOT NULL DEFAULT '0',
 `repair_type` tinyint(4) NOT NULL DEFAULT '0',
 `floor_type` tinyint(4) NOT NULL DEFAULT '0',
 `overlap_type` tinyint(4) NOT NULL DEFAULT '0',
 `window_type` tinyint(4) NOT NULL DEFAULT '0',
 `view_type` tinyint(4) NOT NULL DEFAULT '0',
 `parking_type` tinyint(4) NOT NULL DEFAULT '0',
 `balcony_type` tinyint(4) NOT NULL DEFAULT '0',
 `toilet_type` tinyint(4) NOT NULL DEFAULT '0',
 `is_passenger_lift` tinyint(4) NOT NULL DEFAULT '0',
 `is_service_lift` tinyint(4) NOT NULL DEFAULT '0',
 `is_television` tinyint(4) NOT NULL DEFAULT '0',
 `is_rubbish_chute` tinyint(4) NOT NULL DEFAULT '0',
 `is_furniture` tinyint(4) NOT NULL DEFAULT '0',
 `is_air_conditioning` tinyint(4) NOT NULL DEFAULT '0',
 `is_household_appliances` tinyint(4) NOT NULL DEFAULT '0',
 `is_phone` tinyint(4) NOT NULL DEFAULT '0',
 `is_internet` tinyint(4) NOT NULL DEFAULT '0',
 `is_heating` tinyint(4) NOT NULL DEFAULT '0',
 `is_gas` tinyint(4) NOT NULL DEFAULT '0',
 `is_electricity` tinyint(4) NOT NULL DEFAULT '0',
 `is_water` tinyint(4) NOT NULL DEFAULT '0',
 `is_sewerage` tinyint(4) NOT NULL DEFAULT '0',
 `is_permanent_residence` tinyint(4) NOT NULL DEFAULT '0',
 `is_sauna` tinyint(4) NOT NULL DEFAULT '0',
 `is_garage` tinyint(4) NOT NULL DEFAULT '0',
 `description` text NOT NULL,
 `description_ext` text NOT NULL,
 `description_ext_m` text NOT NULL,
 `description_ext_o` text NOT NULL,
 `description_ext_r` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `external_id` (`external_id`),
 KEY `status` (`status`),
 KEY `publish_status` (`publish_status`),
 KEY `object_type` (`object_type`)
) ENGINE=InnoDB AUTO_INCREMENT=37324 DEFAULT CHARSET=utf8



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
CREATE TABLE `users` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(512) NOT NULL DEFAULT '',
 `phone` varchar(15) NOT NULL,
 `firstName` varchar(100) NOT NULL,
 `surName` varchar(100) NOT NULL DEFAULT '',
 `lastName` varchar(100) NOT NULL DEFAULT '',
 `password` varchar(512) NOT NULL,
 `status` int(11) NOT NULL DEFAULT '1',
 `total` float NOT NULL DEFAULT '0',
 `partner_code` varchar(32) NOT NULL DEFAULT '',
 `partner_id` bigint(20) NOT NULL DEFAULT '0',
 `partner_total` float NOT NULL DEFAULT '0',
 `partner_wallet_type` varchar(64) NOT NULL DEFAULT '',
 `partner_wallet_number` varchar(128) NOT NULL DEFAULT '',
 `sms_daily_counter` int(11) NOT NULL DEFAULT '0',
 `new_user_mail_wizard_state` int(11) NOT NULL DEFAULT '0',
 `trial` int(11) NOT NULL DEFAULT '1',
 `user_type` int(11) NOT NULL DEFAULT '1',
 `created` datetime NOT NULL,
 `last_visit` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `partner_code` (`partner_code`),
 KEY `user_type` (`user_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2099 DEFAULT CHARSET=utf8




Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE TABLE `agencies` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(128) NOT NULL,
 `phone` varchar(128) NOT NULL DEFAULT '',
 `billing_type` int(11) NOT NULL,
 `site` varchar(512) NOT NULL DEFAULT '',
 `address` varchar(4096) NOT NULL DEFAULT '',
 `email` varchar(128) NOT NULL DEFAULT '',
 `about` varchar(5000) NOT NULL DEFAULT '',
 `logo` varchar(512) NOT NULL,
 `total` float NOT NULL,
 `city_phone_code` varchar(16) NOT NULL DEFAULT '',
 `xml_feed_url` varchar(512) NOT NULL DEFAULT '',
 `xml_feed_format` int(11) NOT NULL DEFAULT '0',
 `xml_publish_to` varchar(512) NOT NULL DEFAULT '',
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=utf8




Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE `agencies_users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` mediumint(8) unsigned NOT NULL,
 `agency_id` smallint(5) unsigned NOT NULL,
 `role` tinyint(4) NOT NULL,
 `touch` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `agency_id` (`agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=727 DEFAULT CHARSET=utf8




Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE `images` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(512) NOT NULL,
 `ext` varchar(128) NOT NULL,
 `meta` varchar(128) NOT NULL,
 `gen_name` varchar(512) NOT NULL,
 `size` smallint(5) unsigned NOT NULL,
 `ad_id` int(10) unsigned NOT NULL DEFAULT '0',
 `url` varchar(1024) NOT NULL DEFAULT '',
 `etag` varchar(128) DEFAULT '',
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ad_id` (`ad_id`)
) ENGINE=InnoDB AUTO_INCREMENT=125612 DEFAULT CHARSET=utf8



6.1 - 6.4 - в аттаче

Похоже что 'users' не взирая на 'images' и остальные..

БЛИН, фигня какая-то запрос начал выполняться за 2.258 сек, может зря я эту портянку настрочил и гоню на mysql?

my.cnf
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
#bind-address            = 0.0.0.0

key_buffer              = 8M
max_allowed_packet      = 8M
thread_stack            = 128K
thread_cache_size       = 1
myisam-recover         = BACKUP
max_connections        = 100
max_user_connections   = 75

#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 256K
query_cache_size        = 2M

log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 8M

!includedir /etc/mysql/conf.d/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2014, 03:43:09
    #38810252
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
adrocket,


8.
Пожалуйста добавьте для всех запросов 6.1-6.4 время
выполнения без ЕХПЛАИН. Поставьте sql_no_cache
и замерьте время 3 раза для каждого варианта, типа

Код: sql
1.
2.
3.
4.
select SQL_NO_CACHE
  *
from ADS
where ads.publish_status = 4



9. Установите и погоняйте:

> wget raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
> perl ./mysqltuner.pl
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2014, 09:38:51
    #38810345
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
javajdbc4. из-за большого количества таблиц и смеси
ЛЕФТ и обычных джоинтов оптимизатор выбрал USER
как начальную таблицу и не использовал индексы
на АДС.статус и обжект_тупе.Так индексы-то отдельные. Поэтому и не использовал.
Я бы предложил сделать индекс ads (publish_status, object_type)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2014, 12:30:52
    #38810614
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
Так, по порядку.

javajdbc

Хочу обратить внимание что все запросы даже без object_type.

Код: plsql
1.
2.
3.
SELECT SQL_NO_CACHE *
FROM ads
WHERE ads.publish_status = 4


Код: plaintext
1.
2.
0.0012 seconds
0.0032 seconds
0.0013 seconds

Код: plsql
1.
2.
3.
4.
5.
SELECT SQL_NO_CACHE ads.*, users.*
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
WHERE ads.publish_status = 4


Код: plaintext
1.
2.
0.0024 seconds.
0.0017 seconds
0.0019 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
WHERE ads.publish_status = 4


Код: plaintext
1.
2.
0.0025 seconds
0.0024 seconds
0.0027 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
	   IFNULL(images.ad_id, UUID()) as unq_ancestor,
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor


Код: plaintext
1.
2.
7.4967 seconds
8.3472 seconds
6.7859 seconds

Опа, снова 7 секунд.. для этого последнего запроса снова прикрепляю вывод с EXPLAIN.

Тюнер:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.19-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 84K (Tables: 12)
[--] Data in InnoDB tables: 102M (Tables: 89)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 26d 21h 7m 8s (26M q [11.261 qps], 2M conn, TX: 329B, RX: 5B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 170.0M global + 1.0M per thread (100 max threads)
[OK] Maximum possible memory usage: 270.0M (13% of installed RAM)
[OK] Slow queries: 0% (43/26M)
[OK] Highest usage of available connections: 14% (14/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K
[OK] Key buffer hit rate: 100.0% (855K cached / 6 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 5% (30K temp sorts / 566K sorts)
[!!] Joins performed without indexes: 32311
[OK] Temporary tables created on disk: 5% (146K on disk / 2M total)
[OK] Thread cache hit rate: 86% (317K created / 2M connections)
[!!] Table cache hit rate: 0% (279 open / 67K opened)
[OK] Open file limit used: 1% (70/5K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB buffer pool / data size: 128.0M/102.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64:  http://bit.ly/1mi7c4C 
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 2000)

Далее я добавил в my.cnf
Код: plaintext
1.
2.
query_cache_type    = 1
join_buffer_size    = 256K
table_open_cache    = 2000

Как быстро стал выполняться последний запрос (так же):
Код: plaintext
1.
2.
8.0584 seconds
6.3425 seconds
6.4612 seconds

Какие изменения произошли в выводе тюнера. (наверное еще прошло слишком мало времени меньше часа)

Код: plaintext
1.
2.
3.
4.
[OK] Query cache efficiency: 25.0% (254 cached / 1K selects)
[!!] Sorts requiring temporary tables: 89% (227 temp sorts / 253 sorts)
Variables to adjust:
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)

"Joins performed without indexes" - Скорее всего относится к другим запросам.

Снова добавил:
Код: plaintext
1.
sort_buffer_size    = 256K
read_rnd_buffer_size    = 256K

Как быстро стал выполняться последний запрос (так же):
Код: plaintext
1.
2.
6.7029 seconds
7.3590 seconds
7.6332 seconds

Думаю что дело тут так же не во фрагментации таблиц.. Пока мыслей больше нет..

miksoft
Ключ добавил но запросы без `object_type`..
Код: plsql
1.
KEY `publish_status_object_type` (`publish_status`,`object_type`)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2014, 12:45:18
    #38810638
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
"JOIN users"
Код: plaintext
1.
2.
4.5921 second
3.1965 seconds
4.7697 seconds

"LEFT JOIN users"
Код: plaintext
1.
2.
4.6068 seconds
4.9824 seconds
3.9361 seconds


Если "JOIN users" -> "LEFT JOIN users", то EXPLAIN:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2014, 17:39:35
    #38811041
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
ок, проблема где-то здесь, будем искать.
Возмите за основу вот этот СКЛ (7-8 секунд)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id, --------- (4), добавить когда убираете (1) и (2)
	    IFNULL(images.ad_id, UUID()) as unq_ancestor,    ---- (1)
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name -- (2)
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor --- (3)



Проблема может быть в UUID((), или в ГРОУП БУ или
в самом факте подключения имажей или в ГРОУП_КОНКАТ --
последнее маловероятно, но для полноты списка.

Определите скорость (3 раза СКЛ_НО_КАШЕ) таких СКЛ:
(неважно что некоторые логически неверные)

10. СКЛ минус (3)
11. СКЛ минус (2)
12. СКЛ минус (1) плюс (4) , ГРОУП БУ перевести на имаже.ид
13. СКЛ минус (1),(2), (3) плус (4)
14. СКЛ -- заменить UUID() на RAND()

EXPLAIN проверяйте на всякий случай, но скорее всего он будет одним и темже
для всех СКЛ-ов 10-14

15. У вас большой запас по RAM -- тюрнер говорит что используется
(мах возможного) всего 13%. Если на сервере только МЫСКЛ,
то можно добавлять буферов. Например

Variables to adjust:
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)

поставьте 1М и 1М

16. [OK] Highest usage of available connections: 14% (14/100)

Можно скинуть вниз мах_конекш до , скажем, 40-ка.

Лучше эти подкрутки сделать ПОСЛЕ оптимизации основного
СКЛ-а
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2014, 19:46:16
    #38812421
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
Судя по поведению ниже, дело на 80% в GROUP BY и на 20% в GROUP_CONCAT. Причем, если хотя бы кто-то из них присутствует то эффект значителен...

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor


Код: plaintext
1.
2.
2.1628 seconds
1.8497 seconds
1.8589 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4


Код: plaintext
1.
2.
1.2300 seconds
1.2527 seconds
1.2593 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor


Код: plaintext
1.
2.
2.0149 seconds
2.0128 seconds
1.9645 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
       images.id,
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY images.id


Код: plaintext
1.
2.
1.7581 seconds
1.7163 seconds
1.6948 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
       images.id
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4


Код: plaintext
1.
2.
0.0023 seconds
0.0033 seconds
0.0026 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT SQL_NO_CACHE ads.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, RAND()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM ads
JOIN users
	 ON (users.id = ads.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads.id)
WHERE ads.publish_status = 4
GROUP BY unq_ancestor


Код: plaintext
1.
2.
1.6764 seconds
1.6823 seconds
1.6610 seconds
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2014, 20:14:10
    #38812436
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
adrocket,

ок, я продолжаю подозревать вываливание на диск.
лечится двумя способами :
-- переписать СКЛ
-- увеличить выделение памяти
Первый варинат намного предпочтительнее, хотя
второй тоже надо рассмотреть ибо сейчас задействовано только 13%.

21. для доказательсва (или опровержения) выпадения
на диск есть быстрая проверка. Возмите такой СКЛ:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT SQL_NO_CACHE ads2.*, users.*, agencies.*,
           images.id,
	    IFNULL(images.ad_id, UUID()) as unq_ancestor, 
	   GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
FROM 
       (select * from ads where ads.publish_status = 4 limit 5) ads2
JOIN users
	 ON (users.id = ads2.user_id)
LEFT JOIN agencies
	 ON (agencies.id IN (SELECT agency_id FROM agencies_users WHERE user_id = ads2.user_id))
LEFT JOIN images
	 ON (images.ad_id = ads2.id)
GROUP BY unq_ancestor



Вместо все таблицы ADS можно взять сначала 5 записей
замерьте время (3 раза СКЛ_НО_КАШЕ). потом возмите
ЛИМИТ 20, потом ЛИМИТ 50, потом 200, 500, 2000, 5000, 20000.
Если есть резкий скачёк, значит есть качественое изменение.
Лог-лог график может помочь визуально найти скачек
(если он есть!).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2014, 20:19:27
    #38812440
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
22. интересно что RAND() оказалось таки реально быстрее чем UUID():
сброс с 2.1 до 1.6 сек.

23. 1.6 сек --- это только от ГРОУП БУ.
Когда есть ГРОУП_КОНКАТ и нету ГРОУП БУ
то МуСКЛ делает групировку по всем остальным полям.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2014, 20:31:04
    #38812448
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
теперь как попробовать переписать.
Начнем с простейшего, для все СКЛ ниже выдайте
3 раза скорость СКЛ_НО_КАШЕ:


Код: sql
1.
2.
3.
4.
5.
select 
       i.ad_id,
       count(1) cnt
from images i
group by i.ad_id



Код: sql
1.
2.
3.
4.
5.
select 
       i.ad_id,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
from images i
group by i.ad_id



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
from 
      ads a,
      JOIN images i on a.id=i.ad_id
group by i.ad_id



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
from 
      ads a,
      JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by i.ad_id



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
from 
      ads a,
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by a.id



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       IFNULL(i.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(images.gen_name SEPARATOR ';') AS image_name
from 
      ads a,
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by unq_ancestor
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.11.2014, 10:44:42
    #38812814
adrocket
adrocket
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
Скачка вроде нет.. Насчет RAM, не хотелось бы увеличивать ее использование, так как помимо mysql есть еще Apache, Redis и конечно одна, постоянно работающая JVM. Но ради эксперимента я попробую это сделать.

Код: plaintext
1.
2.
0.0041
0.0052
0.0044

Код: plaintext
1.
2.
3.
0.0061
0.0061
0.0058

Код: plaintext
1.
2.
0.0081
0.0085
0.0084

Код: plaintext
1.
2.
0.0267
0.0259
0.0266

Код: plaintext
1.
2.
0.0642
0.0556
0.0572

Код: plaintext
1.
2.
0.2740
0.2153
0.2159

Код: plaintext
1.
2.
0.4515
0.4250
0.4205

Код: plaintext
1.
2.
1.5728
1.4660
1.4847



Код: plsql
1.
2.
3.
4.
5.
select SQL_NO_CACHE
       i.ad_id,
       count(1) cnt
from images i
group by i.ad_id


Код: plaintext
1.
2.
0.0012 seconds
0.0006 seconds
0.0006 seconds

Код: plsql
1.
2.
3.
4.
5.
[src]select SQL_NO_CACHE
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from images i
group by i.ad_id

[/SRC]
Код: plaintext
1.
2.
0.0018 seconds
0.0016 seconds
0.0017 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      JOIN images i on a.id=i.ad_id
group by i.ad_id


Код: plaintext
1.
2.
0.0030 seconds
0.0003 seconds
0.0003 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by i.ad_id


Код: plaintext
1.
2.
1.0132 seconds
0.0003 seconds
0.0004 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       i.ad_id,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by a.id


Код: plaintext
1.
2.
0.0021 seconds
0.0004 seconds
0.0003 seconds

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select
       a.*, 
       IFNULL(i.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by unq_ancestor


Код: plaintext
1.
2.
1.1988 seconds
1.1484 seconds
1.1562 seconds


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
[OK] Maximum possible memory usage: 270.0M (13% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 7% (7/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K
[OK] Key buffer hit rate: 100.0% (3M cached / 2 reads)
[OK] Query cache efficiency: 20.7% (142K cached / 686K selects)
[!!] Query cache prunes per day: 241503
[!!] Sorts requiring temporary tables: 52% (8K temp sorts / 16K sorts)
[OK] Temporary tables created on disk: 9% (9K on disk / 100K total)
[OK] Thread cache hit rate: 85% (12K created / 89K connections)
[!!] Table cache hit rate: 12% (233 open / 1K opened)
[OK] Open file limit used: 1% (70/5K)
[OK] Table locks acquired immediately: 100% (589K immediate / 589K locks)
[OK] InnoDB buffer pool / data size: 128.0M/104.8M
[OK] InnoDB log waits: 0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.11.2014, 18:05:15
    #38813597
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
adrocket,

да, сильного перегиба графика нет. на лог-лог графике
есть небольшой перегиб на 50 -- врядли можно что-то сказать.
Ну да ладно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.11.2014, 18:21:57
    #38813616
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса, большая выборка для экспорта в XML
adrocket,

Я сам забыл в некоторые СКЛ поставить СКЛ_НО_КАШЕ,
в дальнейшем всегда ставьте, иначе получаются результаты типа:

1.0132 seconds
0.0003 seconds
0.0004 seconds

Теперь по сушеству.
Запрос вылетает на с милисекунд на секундны если
(а) есть связка
(б) групировка по имаге аттрибуту
(ц) непонятка с a.publish_status = 4 -- этот филтр ухудшил ситуацию

Теперь рассмотрим логику.
Группировку по

...IFNULL(i.ad_id, UUID()) as unq_ancestor...
group by unq_ancestor

я бы заменил на

...IFNULL(i.ad_id, UUID()) as unq_ancestor...
group by a.id

Т.е. если имаже не найден, то УУИД и адс.ид будут
одинаково уникальны, т.е. как мне кажется,
замена развнозначаная.

Посмотрите вот такой СКЛ (СКЛ_НО_КАШЕ, 3 раза и ЕКСПЛАИН!)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select SQL_NO_CACHE
       a.*, 
       i.ad_id,
       IFNULL(i.ad_id, UUID()) as unq_ancestor,
       GROUP_CONCAT(i.gen_name SEPARATOR ';') AS image_name
from 
      ads a
      LEFT JOIN images i on a.id=i.ad_id
WHERE
     a.publish_status = 4
group by a.id



Если скорость будет приемлемая (10-50 мс),
то аккуратно подключайте другие таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса, большая выборка для экспорта в XML / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 314ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1833903]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 37ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 196ms
total:  314ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]