А!!! Спасите!!!! :D

По 3000 MySQL-запросов в минуту...

Кто у нас тут специалист по БД?

Нужна самая скоростая система по обработке простейших запросов, типа инкрементировать число в заданном поле и записать другое число в другое по заданному числовому (сре сотен записей) и строковому (среди ~30 тыс. записей) ключу. (Для АвиаТОПа - хотя он и не даёт основную загрузку сейчас, но вынесение его в такую отдельную систему позволит основательно разгрузить системы)

Файловые системы и DBM не предлагать, т.к. доступ к файлу отнимает больше ресурсов, чем к MySQL...

Скорость работы с файлами в общем-то зависит только о того, как эту работу организовать...
Вы удивеитесь, но iXBT до сих пор на флатах висит.

someuser, 16.04.2004 17:37:37 :

Скорость работы с файлами в общем-то зависит только о того, как эту работу организовать...

 

Всё равно поиск файла в ОС слишком накладная операция, даже при использовании структур в духе БД, типа как в NTFS.

>Вы удивеитесь, но iXBT до сих пор на флатах висит.

Не удивлюсь, т.к. знаю это
Такие тормоза, глюки и малые возможности форума при таких малых объёмах баз - это только на plain/text'е изобразить можно

Как раз позавчера очень много там сидел в поисках инфы по фотику - я очень давно так не матерился

Что подразумевается под "скоростая система по обработке простейших запросов"?

Хранить БД в файлах может быть даже медленнее, зависит от их количества и структуры.

А так - общие меры:
1) использовать stored procedures - они выполняются локально на ДБ сервере.
2) индексация.
3) избегать медленных вещей в SQL-запросах. Это может касаться многого. Важно построение запроса. По типу - не использовать никаких виртуальных таблиц и view.
4) Выделить БД достаточно памяти, чтобы ее самый используемый сегмент уместился в память. Посмотреть, сколько берут памяти индексы.
5) быстрый диск тоже плюс, stripe еще лучше (можно с зеркалом).
6) Логи - на другой быстрый диск. это позволит им работать впаралель.

Забыл, надо еще посмотреть в Execution Plan и Client statistics в MS-SQL (Performance tuning или profiling, в зависимости какая ДБ) затраты времени. Иногда можно увидеть неожиданный тормоз где-нибудь.

Все правильно посоветовано, только, как я понял, таблицы простейшие. Поэтому, stored procedure действительно помогут. Я так понял, это все на РНР - я бы все-таки, для простейших SQL statements выполнил бы prepare и хранил бы connection все время открытым. Но здесь надо bind-ы использовать, чтобы преобразование данных избежать. Надо посмотреть на MySQL, но он, кажеться, не допускает кластеризацию таблиц и индексов. Мы в Informix ложили таблицы на отдельный устройства, если было важно быстродействие.

Рои, а есть у РНР возможность работы с share memory?

SEA, 16.04.2004 20:11:12 :

Что подразумевается под "скоростая система по обработке простейших запросов"?

 

Ну, я же расписал вначале - "типа инкрементировать число в заданном поле и записать другое число в другое по заданному числовому (среди сотен записей) и строковому (среди ~30 тыс. записей) ключу"

На самом деле, внимательно понаблюдав за MySQL понял, что всё же, АвиаТОП его почти не грузит. Не смотря на то, что обращений к нему больше всего.

Всё же, основной потребитель - форум. И тут уже только железо наращивать

>Хранить БД в файлах может быть даже медленнее, зависит от их количества и структуры.

Да, естественно. Но тут может быть иногда выигрыш с того, что БД загружается меньше

>1) использовать stored procedures - они выполняются локально на ДБ сервере.

Хм. В MySQL?

>2) индексация.

Естественно.

>3) избегать медленных вещей в SQL-запросах. Это может касаться многого. >Важно построение запроса. По типу - не использовать никаких виртуальных таблиц и view.

Там нет никаких хитростей. Тупо:

1. <?
2.     $link = @mysql_connect("localhost", "....", "....");
3.  
4.     $result = mysql_query ("SELECT * FROM avia_top_pages WHERE url='$url'");
5.     $line = mysql_fetch_array($result);
6.  
7.     if(!$line)
8.         mysql_query ("INSERT INTO avia_top_pages (url,count,start,last) VALUES ('$url',1,UNIX_TIMESTAMP(),UNIX_TIMESTAMP());");
9.     else
10.         mysql_query ("UPDATE avia_top_pages SET start=start, count=count+1, last=UNIX_TIMESTAMP() WHERE url='$url';");
11.  
12.     mysql_free_result($result);
13.  
14. //...
15.  
16.         $result = mysql_query ("SELECT * FROM avia_top_counts WHERE id='$id'");
17.         $line = mysql_fetch_array($result);
18.  
19.         if(!$line)
20.         {
21. //...
22.                 mysql_query ("INSERT INTO avia_top_counts (id,count,start,ips) VALUES ('$id',0,UNIX_TIMESTAMP(),'');");
23.         }
24.  
25. //...
26.  
27.             mysql_query ("UPDATE avia_top_counts SET count=count+1 WHERE id='$id';");
28.  
29. //...
30.  
31.         mysql_query ("UPDATE avia_top_counts SET ips='$ips' WHERE id='$id';");
32.    
33.     mysql_close($link);
34. ?>

Интексы по `url` и `id`, конечно же есть

>4) Выделить БД достаточно памяти, чтобы ее самый используемый сегмент уместился в память.

Вот с этим самая большая проблема

>5) быстрый диск тоже плюс, stripe еще лучше (можно с зеркалом).

Аналогично. Т.е. диск итак SCSI 10000, но страйп там ну очень не рекомендуется. 1U-корпус

>6) Логи - на другой быстрый диск. это позволит им работать впаралель.

Да логи и не ведутся с такими темпами.

>Забыл, надо еще посмотреть в Execution Plan и Client statistics в MS-SQL

MySQL

Mishka, 17.04.2004 20:13:19 :

Я так понял, это все на РНР - я бы все-таки, для простейших SQL statements выполнил бы prepare и хранил бы connection все время открытым.

 

Ты имеешь в виду persistent connection?

Думаю, это не даст заметного выигрыша. PHP подсоединяется к MySQL ну соврешенно молниеносно Не измерял, но, думаю, это микросекунды.
Кроме того, что-то слышал негативное про pconnections на PHP. И, вообще, не зря в MySQL прямо таки ключик есть - игнорировать pconnections

>Но здесь надо bind-ы использовать, чтобы преобразование данных избежать. >Надо посмотреть на MySQL, но он, кажеться, не допускает кластеризацию таблиц и индексов. Мы в Informix ложили таблицы на отдельный устройства, если было важно быстродействие.

Тёмный лес для меня

>Рои, а есть у РНР возможность работы с share memory?

Угу, есть. Но ни разу не пробовал.

Balancer, 17.04.2004 20:05:41 :

>1) использовать stored procedures - они выполняются локально на ДБ сервере.

Хм. В MySQL?

 

MySQL поддерживате их, хотя и с некоторыми ограничениями MySQL ::.

А выигрыш получиться, если свести несколько вещей в одну процедуру или функцию. Так как процедура храниться в базе в уже оттранслированном виде, то на это не тратиться время. Это и имелось ввиду, когда говорилось, что SP выполняется локально на сервере БД.

Mishka, 17.04.2004 23:24:01 :

MySQL поддерживате их, хотя и с некоторыми ограничениями

 

Ага, точно. Как раз с 4.1 начиная.

>Так как процедура храниться в базе в уже оттранслированном виде, то на это не тратиться время.

Угу, уже понял. попробую

Ах... MySQL

По тексту запросов сразу можно сказать, что можно сделать поэффективнее.
Три обращения объединяем в 1, и получаем почти 3х кратную экономию в передаче, инициализации, обработке и финализации запросов, и логгинга тоже.

Самый эффективный путь - испосльзовать Replace Into , НО применим если используется поиск по primary key или unique field. Ты можешь сделать url как unique или primarily key или foreign key. Синтаксис:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expression | DEFAULT},...),(...),...

Если такая запись уже есть (с тем же unique или key) то она будет обновлена, иначе - сработает просто как insert. Возвращаемое значение: 1 или 2 - новый инсерт или замещен старый. Вообщето, замещения как такового не происходит, а происходит инсерт с одновременным удалением старого. Поэтому отсюда главный недостаток - надо передавать все значения для строки (или уаказать где использовать DEFAULT). Это не совсем эффективно в случае, если надо изменить только 1 поле.
Если это не устраивает или юник или key сделать невозможно тогда так.

Из 3х запросов делаем один комбинированный. Синтаксис в примере для MS SQL (tSQL), но MySQL это все "знает":

1. if exists (SELECT * FROM avia_top_pages WHERE url='$url')
2.         INSERT INTO avia_top_pages (url,count,start,last)
3.           VALUES ('$url',1,UNIX_TIMESTAMP(),UNIX_TIMESTAMP())
4.     else
5.         UPDATE avia_top_pages SET start=start, count=count+1,
6.           last=UNIX_TIMESTAMP() WHERE url='$url'

Оба варианта заметно быстрее твоего существующего, а из этих 2х - Replace into наверное чуть побыстрее (незначительно). Ты можешь посмотреть время исполнения.

Одно исправление
SELECT *
FROM avia_top pages
WHERE url = '$url'
надо заменить на
SELECT 1
FROM avia_top pages
WHERE url = '$url'
- не хорошо создавать курсор по полной программе ,когда надо проверить существование.

Mishka
Совершенно верно, я скопировал не подумав.

Есть здесь еще один вариант, наверное самый эффективный. Почему наверное - потому что не знаю, так ли это и для MySQL.
Я выяснил, что MySQL тоже поддерживает триггеры. Тогда работа еще упрощается:

сначала используем только UPDATE, даже не проверяя, есть ли там такой ключ:
UPDATE avia_top_pages SET start=start, count=count+1, last=UNIX_TIMESTAMP() WHERE url='$url'
А в обработчик ошибок ставим INSERT (url будет доступен тут же, в обработчике).

P.S.
Роман, а как в теге [code] сделать тип 'php' вместо текст?
Как у тебя - Created with Colorer, type 'php'

Пардон, все-таки stored procedure начинаются вроде в 5.0, а то бы выглядело бы вот так:

1. CREATE PROCEDURE Replace( IN TimeStamp char( 20 ), IN Url CHAR( 255 ), OUT rc INT )
2. BEGIN
3.         DECLARE res INT DEFAULT 0;
4.         SELECT 1 INTO res FROM avia_top WHERE url = Url LIMIT 1;
5.         IF res = 1
6.         THEN
7.                 INSERT INTO avia_top_pages (url,count,start,last)
8.                 VALUES ( Url, 1, TimeStamp, TimeStamp );
9.                 SET rc = 1;
10.         ELSE
11.                 UPDATE avia_top_pages SET start=start, count=count+1,
12.                         last=TimeStamp WHERE url=Url;
13.                 SET rc = 2;
14.         END IF;
15. END

А вот вызов был бы такой:

1. $result = mysql_query ("SELECT @Replace( UNIX_TIMESTAMP(), '$url', $rc )" );

Но вроде функции можно было определять раньше - можно попробовать:

1. CREATE FUNCTION Replace( IN TimeStamp char( 20 ), IN Url CHAR( 255 ) )
2. RETURNS INT
3. BEGIN
4.         DECLARE res INT DEFAULT 0;
5.         SELECT 1 INTO res FROM avia_top WHERE url = Url LIMIT 1;
6.         IF res = 1
7.         THEN
8.                 INSERT INTO avia_top_pages (url,count,start,last)
9.                 VALUES ( Url, 1, TimeStamp, TimeStamp );
10.                 RETURN 1;
11.         ELSE
12.                 UPDATE avia_top_pages SET start=start, count=count+1,
13.                         last=TimeStamp WHERE url=Url;
14.                 RETURN 2;
15.         END IF;
16. END

Тогда вызов выглядит так:

1. $result = mysql_query ("SELECT Replace( UNIX_TIMESTAMP(), '$url' )" );

[COLOR=red]Внимание, код не проверял![COLOR=blue]

SEA - а что такое REPLACE - вроде не нашел по стандарту и в доках по MySQL?

1.8.5.4 Stored Procedures and Triggers
Stored procedures are implemented in MySQL version 5.0. See Chapter 19 [Stored Procedures],
page 837.
Triggers are scheduled for implementation in MySQL version 5.1. A trigger is effectively a
type of stored procedure, one that is invoked when a particular event occurs. For example,
you could set up a stored procedure that is triggered each time a record is deleted from
a transactional table and that stored procedure automatically deletes the corresponding
customer from a customer table when all their transactions are deleted.

Mishka:

1.8.5.4 Stored Procedures and Triggers

 

Простите, я не понял. Вы хотели показать версии?
Роман, а какая версия на авиабазе?

По поводу REPLACE:
14.1.6 REPLACE Syntax

написал было про replace into, но это не может быть использовано, поскольку надо:
count = count+1

К сожалению, replace into не имеет доступ к старому рекорду, и поэтому здесь неприемлем.
Вобщем, прикинув затраты времени на разные вещи, я пришел к выводу, самый быстрый вариатн - это stored prosedure. Код привел Mishka, я только бы убрал возврат результата (новая запись или замена), поскольку он вроде не нужен.

SEA, 19.04.2004 23:29:00 :

Роман, а какая версия на авиабазе?

 

4.1.1alpha

Сегодня попробую прикрутить эти самые stored-процедуры

Как успехи по поводу сохранненых процедур?

Роман, кстати, я догадываюсь, как один человек может легко создать 30 запросов в секунду: Если мышка с колесиком, то кликнув на dropdown выбора форума (внизу справа под форумом), остается крутить колесико взад-вперед, что и будет генерировать переходы. Потому что для перехода достаточно выбрать новый форум и не надо кликать маленькую кнопочку ОК рядом.
Думаю, надо сделать чтобы всегда для перехода должна была быть кликнута та самая кнопочка ОК.

SEA, 21.04.2004 21:50:49 :

Как успехи по поводу сохранненых процедур?

 

Я нашёл, наконец, самое слабое место - поиск. Надо переводить на FULLTEXT-индексы и искать по ним. Потренировался на Поиск по форуму

Беда в том, что mysql виснет при попытке проиндексировать уже имеющуюся базу. Пришлось создать новую, с индексом, и переносить туда записи по одной. 140тыс. на данный момент перенесено (но процесс жутко медоенный. Впрочем, торопиться некуда)

>Роман, кстати, я догадываюсь, как один человек может легко создать 30 запросов в секунду: Если мышка с колесиком, то кликнув на dropdown выбора форума (внизу справа под форумом), остается крутить колесико взад-вперед, что и будет генерировать переходы.

Хм. У меня никак не выходит До перехода на страницу выпадающее меню в IE не крутится, а в Опере оно не крутится вообще

В любом случае это слишком гипотетическая ситуация. Это постараться нужно Кроме того, 30 выдачей страниц форумов, движок тянет легко.

>Думаю, надо сделать чтобы всегда для перехода должна была быть кликнута та самая кнопочка ОК

Лично мне так неудобно!
Не люблю лишних кликов

Хм. У меня никак не выходит До перехода на страницу выпадающее меню в IE не крутится, а в Опере оно не крутится вообще

 

Я поленился написать подробнее. Итак:
Надо кликнуть дважды. В результате это меню закроется, но будет выбрано (в фокусе). И тогда вращение колесика как раз и начинает перебирать все пункты меню.
У меня так выходит всегда (если кликнуть 2 раза).

Лично мне так неудобно!
Не люблю лишних кликов

 

Я тоже. Но это дает возможность кому-нибудь загружать сервер форума легким движением руки.

удалил повтор.
Кстати, а возможно полностью удалить сообщение?

Script Execution time: 112,5988 и это не предел. Один раз было более 900. Что грузит сервер.

SEA, 21.04.2004 22:36:20 :

Кстати, а возможно полностью удалить сообщение?

 

Не-а. Такая возможность заблокирована Только просить модератора, лучше ткнувшись в ссылку "Report".

Mishka, 23.04.2004 01:15:42 :

Script Execution time: 112,5988 и это не предел. Один раз было более 900. Что грузит сервер.

 

Офигеть. Это я вчера к ночи запустил переиндексацию в FULLINDEX. Вообще, фигня какая-то творится.

При попытке создать FULLTEXT-индекс для имеющейся базы в лоб, минут 15 ваяет временные файлы, после чего благополучно виснет. По крайней мере, за пару часов работы после этого ни одного обновления ни одного файла в базе.

Пошёл с другой стороны. Создал таблицу отдельно, с готовым индексом, пустую, и начал переносить туда PHP-скриптом из первой таблицы данные построчно. Вначале всё шустро шло, до 200-тысячных индексов за часы пролетает, а вот потом - за всю прошедшую ночь перенесено всего 2000 строк.

При этом памяти отжирается в свопе 500Мб и машина уходит в своп. Вот и дикие тормоза...

Что делать - Х.З.
Понятно, что память надо наращивать, гига бы на это дело, наверное, хватило бы. Но непонятно как-то... :-/