MySQL. Получить последний ID по параметру.

[Dmffmd]

Доброй ночи.
Помогите оптимизировать запрос к БД.
Есть таблица с тремя полями:
1. id - автоинкремент
2. text - произвольный текст
3. int - цифра от 0 до 100 (индексация)

В таблице ~300 миллионов записей. Первая треть таблицы уже имеет рандомные значения в поле int от нуля до 100. Мне нужно получить последний ID, в котором int > 0

Сейчас юзаю:

SELECT `id` FROM `table` WHERE `int` != 0 ORDER BY `id` DESC LIMIT 1

Но запрос обрабатывается более трёх минут (из-за сортировки). Я прям чувствую что это можно оптимизировать каким нибудь подзапросом, но как?

Повторюсь, как можно быстро вытащить последнюю строку с int > 0?

 

[Gfoblin]

id уникальный? )
тогда нажми PRIMARY в php admin рядом с ним... и запрос будет потом выполнятся моментально )

 

[Gfoblin]

а фу блин... и еще
https://stackoverflow.com/questions/7604893/sql-select-row-from-table-where-id-maxid
SELECT max(id) FROM `table` where `int` != 0

 

[Dmffmd]

На нём висит примари.

 

[Gfoblin]

На нём висит примари.

дочитал... 300 млн записей...
тип поля какой к стате и сколько памяти на сервере?
ну и int какой тип, его бы тоже в индекс - т.к. выборка по нему идёт, но не примари само собой?
но на таком кол-ве многое от памяти зависит!

 

[Gfoblin]

int - TINYINT
Attributes - UNSIGNED
https://site-do.ru/db/sql2.php
1 байт на ячеку... 300 млн записей - это... 300 млн байт - сколько это мегабайт я чето посчитать не могу

 

[ol1ver]

а может сделать проще чем хранить 300млн строк? Что вы хотите сделать?

 

[Dmffmd]

дочитал... 300 млн записей...
тип поля какой к стате и сколько памяти на сервере?
ну и int какой тип, его бы тоже в индекс - т.к. выборка по нему идёт, но не примари само собой?
но на таком кол-ве многое от памяти зависит!

На сервере 32гб озу.
Mysql никак не настраивался. Так что настройки там дефолтные.

Таблица InnoDB
id - bigint (примари)
name - varchar 255
pole - bigint (индекс)

Твой запрос с max(id) всё ещё не сработал.

 

[Gfoblin]

На сервере 32гб озу.
Mysql никак не настраивался. Так что настройки там дефолтные.

Таблица InnoDB
id - longint (примари)
name - varchar 255
number - longint (индекс)

Твой запрос с max(id) всё ещё не сработал.

да индекс в оперативу не помещается банально - поэтому толку от него ноль
глянь как у тебя хард хомячит там )
atop 2
потом D
pole - это то что от 0 до 100?
он жрет в 8 раз больше места чем ему надо )
https://site-do.ru/db/sql2.php

 

[Gfoblin]

10 млн записей - по 1 байту - это 10Гб оперативы как раз если его сделать как я выше написал то хватит, а сейчас ему надо 80гб )
Но че делать с примари я хз...
Он у тебя в выборке участвует гденить вообще?
Диск ssd я так думаю т.к. на hdd сдохло бы вообще всё уже давно

 

[Dmffmd]

а может сделать проще чем хранить 300млн строк? Что вы хотите сделать?

В общем таблица с доменами. в поле с bigint хранится IPv4 в числовом виде.

NodeJS берёт с базы 10кк записей с WHERE pole=0, больше не тянет. Но на 3-4 цикле он упирается в мёртвые сайты, так как 30% доменов не имеют IP.
Сейчас я его руками перезапускаю с дополнительным условием типа: id > 123123123 && pole=0.
Хочу эту задачу перевести на скрипт. Вот и задался вопросом, как можно получить последний ID

 

[Dmffmd]

10 млн записей - по 1 байту - это 10Гб оперативы как раз если его сделать как я выше написал то хватит, а сейчас ему надо 80гб )
Но че делать с примари я хз...
Он у тебя в выборке участвует гденить вообще?
Диск ssd я так думаю т.к. на hdd сдохло бы вообще всё уже давно

Не ssd. Сервак старый

 

[Dmffmd]

max(id) не сработал. выбило ошибку на 600 секунде.

 

[Gfoblin]

max(id) не сработал. выбило ошибку на 600 секунде.

ну там по таймауту он вываливаться будет походу и вы еще не апдейтите как я понял базу )

Терь понятно...
Да задачу над поделить по таблицам )
Например
Таблицы
domain_a
domain_b
domain_с

тогда каждый домен у вас с началом на эту букву надо поместить в эти таблицы...
уже у вас выигрышь пойдет в 26 + 10 цифр, только я не помню можно домены на цифры начинать терь или нет )
Это уже у вас база уменьшится в идеале в 30 раз... как раз будет по 10млн записей - уже оно оживет если сразу их всех не открывать...
Ну и логику над переделать, но не сильно...
Мне одно не понятно как вы туда столько залил и оно не встало? )

 

[Gfoblin]

16 gb оперативы
MyISAM
6,776,520 записей
SELECT max( id )FROM `table`WHERE `data` >25
Showing rows 0 - 0 ( 1 total, Query took 6.5786 sec)

SELECT `id` FROM `table` WHERE `data` > 25 ORDER BY `id` DESC

Showing rows 0 - 29 ( 6,775,730 total, Query took 0.0006 sec)

Блин а ваш запросец быстрее заметнее )

 

[Dmffmd]

Да задачу над поделить по таблицам )
Например
Таблицы
domain_a
domain_b
domain_с
тогда каждый домен у вас с началом на эту букву надо поместить в эти таблицы...

Я от подобной схемы пытался уйти. Сотни таблиц с именами: com net ru... По идее это временная таблица, которая должна просто получить список всех IP а потом спокойно удалиться.
Пока у меня в альтернативных вариантах, создать временную таблицу, куда я буду раз в пару минут записывать ID, который парсится.

Мне одно не понятно как вы туда столько залил и оно не встало? )

Я не ожидал что будут проблемы. Понадобилось получить все IP. Решил собрать все домены и быстро спарсить IPшники. Мало того что по скорости упёрся в update, так ещё и перезапускать приходится вручную.

Есть ещё вариант брать строки с таблицы через LIMIT n, 1000000 и молиться что nodejs не упадёт. На крайняк записывать эту переменную n в отдельную таблицу

 

[Dmffmd]

16 gb оперативы
MyISAM
6,776,520 записей
SELECT max( id )FROM `table`WHERE `data` >25
Showing rows 0 - 0 ( 1 total, Query took 6.5786 sec)

SELECT `id` FROM `table` WHERE `data` > 25 ORDER BY `id` DESC

Showing rows 0 - 29 ( 6,775,730 total, Query took 0.0006 sec)

Блин а ваш запросец быстрее заметнее )

Ну так, я ж профи с работой в mysql

Хотя интуитивно первый запрос должен быть куда быстрее.

 

[Dmffmd]

6,776,520 записей

ru su rf?

 

[Gfoblin]

А конфиг дефолтный говорите... тогда пробуем так...
надеюсь если у вас рут попробуйте дать такую комманду...
SET global key_buffer_size=17073741824;#16gb

это увеличит длину буфера под индекс...
если подохнет вдруг попробуйте меньше из расчета на 1гб.
SET global key_buffer_size=1073741824;#1gb

параметр будет действовать до перезагрузки
если постоянка нужна то ищите и ставите в my.cnf
key_buffer_size=16gb
и рестарт

 

[Gfoblin]

ru su rf?

не эт не домены эт другие записи )
просто под ваш тест подходило идеально )
и это на hdd, те наглядно видно когда индексы работают в идеале у вас так же должно быть, т.к. параметр главный в индексе он сразу знает откуда искать (с конца) и там буквально 1-2 проверки....

к предыдущему посту
/phpmyadmin/server_variables.php
key buffer size - тут можно подкрутить его руками еще.
ну и размер я хз может и больше 16 надо )
Смысл в том, что бы его не читать с харда каждый раз...

 

[Dmffmd]

А конфиг дефолтный говорите... тогда пробуем так...
надеюсь если у вас рут попробуйте дать такую комманду...
SET global key_buffer_size=17073741824;#16gb

это увеличит длину буфера под индекс...
если подохнет вдруг попробуйте меньше из расчета на 1гб.
SET global key_buffer_size=1073741824;#1gb

параметр будет действовать до перезагрузки
если постоянка нужна то ищите и ставите в my.cnf
key_buffer_size=16gb
и рестарт

А разве это не настройки MyISAM. У меня innodb. Я совершенно не разбираюсь в этих настройках. Вроде бы нашёл то что надо, но оно не редактируется. Буду пробовать с командами.

п.с. root есть, mysql висит на винде.

 

[Gfoblin]

А разве это не настройки MyISAM. У меня innodb. Я совершенно не разбираюсь в этих настройках. Вроде бы нашёл то что надо, но оно не редактируется. Буду пробовать с командами.

п.с. root есть, mysql висит на винде.

вот может и это надо почитать мануал если честно...

 

[Gfoblin]

Ну вот а говорите не шарите )
Судя по всему то что надо, а я ошибся...
https://dev.1c-bitrix.ru/support/forum/forum6/topic76755/

Откуда у вас MyISAM-таблицы? Если это таблицы с данными, то нельзя ли их конвертировать в InnoDB?
И даже если нельзя, то key_buffer_size можно уменьшить до пары мегабайт, а освободившиеся 14 МБ добавить в innodb_buffer_pool_size.

и да еще многое чего может зависить временные таблицы например
max_heap_table_size = 32M
tmp_table_size = 32M

Как понять чего не хватает mysql самой много чего написано...
Через сервер статус посмотрите еще какой параметр красным светится и можно покопаться в инете как его оптимизировать, может и потащит 300кк записей )

 

[Gfoblin]

p.s. нашел у себя innodb buffer pool size 3gb стояло )

 

[Dmffmd]

p.s. нашел у себя innodb buffer pool size 3gb стояло )

Спасибо тебе за помощь. Настройки конфига значительно ускорили работу скрипта. Только теперь мне не хватает ОЗУ
Запрос из первого поста теперь выполняется за 20 сек, вместо 300.

П.с. Конфиги взял отсюда: https://dba.stackexchange.com/questions/50941/the-best-mysql-settings-for-32gb-ram-on-a-dedicated-server

 

[ol1ver]

Я так понял тянете домен, затем повторно он не может участвовать в раздаче? дак присваивайте ему после отдачи used=1
SELECT id FROM table_name WHERE used=0
$id = // получаем id после запроса

UPDATE table_name
SET used=1
WHERE id= $id

ну и правильно сказали, разбейте на таблицы домены, зачем сразу всё грузить

 

[Lord_Alfred]

уже у вас выигрышь пойдет в 26 + 10 цифр, только я не помню можно домены на цифры начинать терь или нет )

Можно.


Тут скорее нужно не придумывать как оптимизировать текущую кашу, а продумать другую архитектуру + правильно построить индексы. То есть лучше свести задачу к переносу текущей структуры во что-то другое, а потом уже работать с ней.

 

[Gfoblin]

Спасибо тебе за помощь. Настройки конфига значительно ускорили работу скрипта. Только теперь мне не хватает ОЗУ
Запрос из первого поста теперь выполняется за 20 сек, вместо 300.

П.с. Конфиги взял отсюда: https://dba.stackexchange.com/questions/50941/the-best-mysql-settings-for-32gb-ram-on-a-dedicated-server

• Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use query_cache_size=0, query_cache_type=0.

Вот да наверное лучше вам это дело отключить, один фиг не поможет кеширование...
Сколько в итоге под innodb buffer pool size выделили может надо еще чуть чуть и будет еще быстрее?

К стате советую 2 форума по mysql меня там выручают очень часто, можете описать проблему может подскажут.
http://www.cyberforum.ru/mysql/ - лучший имхо
http://www.sql.ru/forum/mysql - тоже пишу иногда

 

[falke2]

10 млн записей - по 1 байту - это 10Гб

не пугай) 10Мб

 

[Gfoblin]

не пугай) 10Мб

точняк )