Limit в большой базе MySQL

Есть база Mysql, в ней 52 000 000 записей, база весит 3,7 Гб.
Запрос вида SELECT * FROM `names` LIMIT 52860000 , 30 выполняется 163 секунды.
PS. В базу каждую секунду добавляется около 200 записей.
Можно ли как-нибудь оптимизировать время выполнение запросов?

В такой БД есть смысл использовать memcache, а вместо Limit:

Держать в актуальном состоянии информацию о id записывая их через мемкеш.

В результате придумать хитрый механизм для получения минимального ид и максимального ид в диапазоне 30 записей (так как на 30 записей может быть первый ид 52.000.000, и последний 52.000.035 (а где-то в средине 5 записей уделены были, если они конечно удаляются)

И запрос быть должен SELECT * FROM WHERE `id` >= ПОЛУЧЕНЫЙ_ПЕРВЫЙ_ИД and `id` <= ПОЛУЧЕНЫЙ_ВТОРОЙ_ИД

Думаю так быстрее, но не факт, не проверял, на базу 52кк записей думаю найдётся 55-60мб озу для хранения ид в мемкеше.

----------------

Цитата ("Php"):

PS. В базу каждую секунду добавляется около 200 записей.

хм, что же тогда получается..

В сутки добавляется в среднем: 60*60*24*200 = 17.280.000 записей.
И того прирост объема базы в среднем в сутки: (3,7 / 52,000,000) * 17,280,000 = ~1.2Гб

За месяц БД будет более 35Гб..

Что же за проект такой ))

* при таких нагрузках есть смысл распределить на несколько серверов, и их количество постоянно будет расти. И может быть, вообще отказаться от мускл, или серьезно поработать над оптимизацией БД и настройкой мускл.

------
В любом случае нужно хотя бы структуру таблицы показать, может кому интересно будет.

База работает на двухъядерном атлоне 4400 - 3 гб оперативы OS - ubuntu.
Боты Клиенты (5 штук) общаются с сервером по сетке.
Сервер Мой компьютер слегка тормозит. Я ищу способ снизить нагрузку.

Цитата ("kirilev"):

Что же за проект такой ))

База с акаунтами пользователей.

Цитата ("kirilev"):

В любом случае нужно хотя бы структуру таблицы показать, может кому интересно будет.

------------------
| id | login | url |
------------------
id - bigint (11)
login - varchar (256)
url - int (9)
И на id на login стоят индексы, для быстрого поиска - чтобы не добавлять дубликаты.

Конфиг мускля меняли под большие базы?

Все по дефолту. Что там нужно изменить?

Там должны заготовки быть где-то в /usr/share/mysql (хз гдев убунте)
называются mysql-small.cgf, mysql-medium.cfg, mysql-large.cfg и т.д.

Например mysql-huge.cfg выглядит так.

# Example MySQL config file for very large systems.

#

# This is for a large system with memory of 1G-2G where the system runs mainly

# MySQL.

#

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# ссылка

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.


# The following options will be passed to all MySQL clients


#password	= your_password

port		= 3306

socket		= /tmp/mysql.sock


# Here follows entries for some specific programs


# The MySQL server


port		= 3306

socket		= /tmp/mysql.sock

skip-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8


# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

# 

#skip-networking


# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin


# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id	= 1


# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between

# two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#

#    where you replace <host>, <user>, <password> by quoted strings and

#    <port> by the master's port number (3306 by default).

#

#    Example:

#

#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

#    MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR

#

# 2) Set the variables below. However, in case you choose this method, then

#    start replication for the first time (even unsuccessfully, for example

#    if you mistyped the password in master-password and the slave fails to

#    connect), the slave will create a master.info file, and any later

#    change in this file to the variables' values below will be ignored and

#    overridden by the content of the master.info file, unless you shutdown

#    the slave server, delete master.info and restart the slaver server.

#    For that reason, you may want to leave the lines below untouched

#    (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id       = 2

#

# The replication master for this slave - required

#master-host     =   <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user     =   <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =   <password>

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port     =  <port>

#

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

#

# binary logging format - mixed recommended 

#binlog_format=mixed


# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/db/mysql

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

#innodb_log_group_home_dir = /var/db/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 384M

#innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 100M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50



quick

max_allowed_packet = 16M



no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates



key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M



interactive-timeout

А вообще если от mysql не уйти то вам надо хорошо попробовать различные типы хранилищ. Хотя бы innodb.
И сервер помощнее..

Цитата ("Php"):

Есть база Mysql, в ней 52 000 000 записей, база весит 3,7 Гб.
Запрос вида SELECT * FROM `names` LIMIT 52860000 , 30 выполняется 163 секунды.
PS. В базу каждую секунду добавляется около 200 записей.
Можно ли как-нибудь оптимизировать время выполнение запросов?

Запросы с параметром LIMIT X,Y при X>>0 выполняются медленно всегда, независимо от конфигураций и оптимизаций mysql. Это связано с тем, что при выполнении такого запроса, сначала делается SELECT * FROM `names`, результат переписывается в промежуточную таблицу, которая затем читается последовательно до записи с номером X, после чего, клиенту выдается Y записей и обработка запроса заканчивается. Чтобы обойти это ограничение, можно добавить в запись таблицы поле BIGINT, сделать его индексным и при добавлении записи (стоит попробовать дабавлять записи через INSERT DELAYED) ставить в это поле порядковый номер записи, затем при выборке писать что-то вроде SELECT .. WHERE это-поле BT 52860000,52860030.

Второй вариант - использовать для таких вещей БД rrd или извернуться и сделать это на memcached или чем-то подобном, но лучше сначала попробовать простые пути, поскольку все обходные маневры, rrd, memcached и т.д. проблем, как правило, не решают, а лишь отодвигают решение "на потом", причем "потом" стоимость (во всех смыслах) решения становится намного больше.

1. В первую очередь нужно избавится от limit, если записи ростут с единицы, и выборка проводится последовательно по записях то применяйте не лимит а where выражение, но это только возможно если счетчик идет непрерывно
2. если записи идут как попало и невозможно применить п. 1, то следует использовать кеширование, напр. в простейшем виде - при запросе от и до обращатся к табл. где будут указаны id выбранных записей, если таковой нет - то создать, конечно с понятных причин первый такой запрос будет выполнятся долго, либо насильно генерировать такие запросы для пагинации
3. есть и более эфективные методы кеширования - напр. кеширующые сервера, прокси-сервера и т.д., хотя для таких обемов MySQL подходит слабо, лучше взять чтото посерйозней, напр. MSSQL/Oracle, Postgree - на худой конец с халявных

в любом случаи, как уже отмечалось Limit - как последняя операция плана выполнения, тоесть в вашем случаи независимо что указано в Limit - быстрее запрос не выполнится

П.С. любая оптимизация запроса начинается с изучения плана выполнения запроса

MMM_Corp все правильно расказал.
И да, explain - наше все.

Цитата ("MMM_Corp"):

в любом случаи, как уже отмечалось Limit - как последняя операция плана выполнения, тоесть в вашем случаи независимо что указано в Limit - быстрее запрос не выполнится

На самом деле очень важно, что указано в LIMIT. Чем меньше указанная в LIMIT позиция, тем быстрее будет выполняться запрос. Это связано с тем, что MySQL, сделав выборку, считывает записи до этой позиции, и чем больше записей нужно считывать, тем больше будет обращений к диску, и тем дольше будет выполняться запрос. Обратите же внимание на размер таблицы в данном случае, не поместится всё в памяти.

Цитата ("Php"):

id - bigint (11)

BIGINT(11)? BIGINT же может содержать значение до 19 цифр... Может быть, тут вполне подойдет просто INT (может содержать значение до 10 цифр)? С INT выполняться будет быстрее, и памяти будет меньше использоваться. А вот когда значение очередного id до пары миллиардов дойдет, тогда и над BIGINT можно будет подумать.

По теме. Как и писали выше, для таких задачи от LIMIT необходимо избавляться, категорически.

Если id идут по порядку и подряд, то есть нет удаленных записей, то делайте как и написал MMM_Corp.

Если возможны удаленные записи в середине таблицы, то можно создать дополнительную таблицу с двумя полями — position (первичный ключ) и id. При добавлении новой записи в основную таблицу, в эту таблицу добавлять запись с новым id и следующей позицией. Если что-то удаляется, тогда ставить в очередь задач таск перенумеровать позиции. А пока хранить в памяти список удаленных записей, чтобы, пока таблица позиций не пересчиталась, учитывать поправки позиции при очередном запросе.

Если есть лишние полгига памяти, то можете просто хранить все id в кеше в памяти, в огромном массиве.

Цитата ("kirilev"):

Думаю так быстрее, но не факт, не проверял, на базу 52кк записей думаю найдётся 55-60мб озу для хранения ид в мемкеше.

Пожалуйста, умножьте 52кк на 8 (тут же BIGINT используется!). Ну никак 55-60 мегабайт не получится. Нужно где-то на полгига расчитывать. А потом эта таблица еще и расти будет...
И еще, мемкеш (Вы же имели в виду Memcache?) тут не подойдет.

Цитата ("Php"):

База работает на двухъядерном атлоне 4400 - 3 гб оперативы OS - ubuntu.

базе памяти не хватает к тому же.

Цитата ("kirilev"):

В такой БД есть смысл использовать memcache, а вместо Limit:

красное вместо сладкого?

А еще существуют такие страшные слова как партиционирование, шардинг, репликация....

Цитата ("raznomir2"):

А еще существуют такие страшные слова как партиционирование, шардинг, репликация....

Есть еще вариант, если в базе каждая запись большая, то делать выборку по id, причем даже не limit, а точно указывать id.
повесить тригеры на основную таблицу и делать дублирующую реалтаймом в которой будут только id (точно такие же + условия для выборки (если надо))
или переходить на Redis

Попробуйте делать дополнительную таблицу id_list, в которой будет хранится список ID из первой таблице, в том же порядке. При изменении таблицы, менять и таблицу со списком(id_list) ID. Делать выборку с помощью SELECT * FROM table WHERE id IN SELECT id FROM id_list LIMIT x, y.
Как то так. Должно работать быстрее(на практику сам не проверил ).

Цитата ("WebDesignStudio"):

Попробуйте делать дополнительную таблицу id_list, в которой будет хранится список ID из первой таблице, в том же порядке. При изменении таблицы, менять и таблицу со списком(id_list) ID. Делать выборку с помощью SELECT * FROM table WHERE id IN SELECT id FROM id_list LIMIT x, y.
Как то так. Должно работать быстрее(на практику сам не проверил ).

Какой ужас...

Цитата ("raznomir2"):

Какой ужас...

Почему?

Цитата ("WebDesignStudio"):

Попробуйте делать дополнительную таблицу id_list, в которой будет хранится список ID из первой таблице, в том же порядке. При изменении таблицы, менять и таблицу со списком(id_list) ID. Делать выборку с помощью SELECT * FROM table WHERE id IN SELECT id FROM id_list LIMIT x, y.

SQL запрос тут неправильный. Выдача ничем не будет отличаться от SELECT * FROM table LIMIT x, y.

Кстати, а вот выборка из дополнительной таблицы, состоящей из одного поля id, будет действительно быстрее. Но всё равно это не решение. Ну будет искать, скажем, пусть хоть раз в 5-10 быстрее, и что? Эту задачу нужно решать кардинально.

Цитата ("tvv"):

Кстати, а вот выборка из дополнительной таблицы, состоящей из одного поля id, будет действительно быстрее

а я о чем писал? Именно об этом.
Цитата ("tvv"):

Эту задачу нужно решать кардинально.

ну да, есть и другие методы. Это как самый легкий способ.