PHP/mySQL съвет как да процедирам

exoexo

Member
Здравейте,

имам идея от PHP/mySQL, но чак специалист не съм. Сам съм си писал не прекалено сложни скриптове и сайтовете. Но се натъквам на един проблем кое да реша и кое е по-добре ...

С няколко думи като начало:

Метод 1. Дълга myQSL заявка от много по-малки обединени с UNION:
(заявка order by rand() limit 5) UNION (заявка order by rand() limit 5) UNION (заявка order by rand() limit 5) ...
като всяка заявка уви трябва да включва толкова неприятното order by rand() (всички сме наясно че rand() е мор за заявката, но няма избор, трябва да се извлекат само 5 случайни резултата. Като цяло голямата заявка връща крайния резултата без да е нужна допълнителна обработка - дирекно извеждаме резултатите.

Метод 2. Отделни по-малки заявки вместо обединената голяма. Всяка обаче връща всички резултати от търсенето. А после PHP shuffle() за да разбъркаме резултата на всяка заявка и вземаме първите 5 резултата и това всичко отива в един масив. Този масив вече е същия резултат като горната голяма заявка и извеждаме резултата.

Дилемата сега е кое е по-добре ... Определено една голяма заявка връща резултата директно от базата, не се налага да обработваме малките резултати с PHP. Метод(1) връща точния брой резултати от търсенето, докато малките, понеже нямат лимит и order се налага да върнат всички резултати за тяхното си търсене. Така малките реално връщат повече, излишно памет и т.н. Отделно после PHP трябва да ги размеси на случаен принцип за да вземем после 5 случайни резултата.

Сложих код да засече времето за изпълнение и засякох със 100 проби:
Метод 1: 0,278 секунди ! Почти 1/3 секунда докато извлече от базата и имаме крайния резултат от търсенето.
Метод 2: 0,082 секунди. Значително в пъти по-бързо извлича и обработва до същия краен резултат от търсенето.

Метод 2 определено е по-добър за потребителите като скорост. Също за SEO. Страницата зарежда осезаемо по-бързо.

Но забелязах че въпреки че е по-бърз Метод 2 се оказва може би повече товарещ ресурсите на машинката. За целта ще пусна и графиките:
attachment.php
- естествено с Метод 2 MySQL заявките да скочат доста.

attachment.php
- но се забелязва и повишението на CPU, заради обработките на малките от php/CPU

attachment.php
- натоварването (load average) на машината се повиши (макар че имах надежди да падне, след като вече нямаме rand() в заявките и всичко се обработва по-бързо. Вижда се, макар че тази графика не е много показателна, защото имах и други неща да върша и товареха излишно по време на Метод 1, та изглежда че Метод 2 не е много повече load спрямо Метод 1, но реално предните периоди средно load (Метод 1) = 0,90 ... а с Метод 2 средно load = 2,75.


Реално с Метод 2 исках да постигна по-бързо зареждане на страниците (което се получи осезаемо) и намаляване натоварването на машината, а да мога да поема повече трафик към сайтовете. Но реално се получи едно добро + едно зло. Скоростта е много по-добра, но пък натоварването се повиши и машината няма да поеме очакваните повече посетители.

И дилемата е дали да се върна на Метод 1, и да жертвам скоростта на страниците за сметка на поемането на повече посетители, или да жертвам трафика за сметка на скоростта ... Бавната скорост ще е негатив за SEO и самите посетители и ще загубя клиенти така, но пък с Метод 2 няма да има как сървъра така или иначе да поеме повече клиенти ... Не мога да сменям машината с по-мощна!

Някой ако е запознат с нещата, да препоръча нещо. Може би дори идеи за нов подход, Метод 3, с който да постигнем всички цели ? При нужда може да публикувам и самите кодове на php / mysql. (ПС: базата има индекси, това съм го оптимизира, заявките и обработката им са проблем)
 

Прикачени файлове

  • mysql_queries-day.png
    mysql_queries-day.png
    31.1 KB · Преглеждания: 175
  • cpu-day.png
    cpu-day.png
    24.7 KB · Преглеждания: 173
  • load-day.png
    load-day.png
    20.2 KB · Преглеждания: 174
От: PHP/mySQL съвет как да процедирам

Варианта е да го направиш с 2 заявки - едната ще избере всичките ID-та от главната таблица, която те интересува, след това PHP ще ги разбърка и вземе само бройката, която ти трябва, и в следващата голяма заявка ще добавиш нещо от типа на WHERE id IN (разбърканите ID-та).
 
От: PHP/mySQL съвет как да процедирам

Такива заявки се правят в доста редки случа, не знам каква е крайната цел, но може би трябва да помислиш дали наистина си заслужава.
 
От: PHP/mySQL съвет как да процедирам

SELECT t.id FROM table t JOIN (SELECT(FLOOR(max(id) * rand())) as maxid FROM table) as tt on t.id >= tt.maxid LIMIT 5

Ето ти един начин, да селектираш случайни резултати от таблица без да използваш ORDER BY RAND()
 
От: PHP/mySQL съвет как да процедирам

Каква е логиката тук. Не може ли да се опростят нещата?
 
От: PHP/mySQL съвет как да процедирам

Сега ще обясня каква точно е целта, понеже виждам че давате примери които също в някаква степен съм обмислил но просто не работят в случая (като WHERE id IN (x,y,z....) и въобще където селектираме по id).

Имаме база с текст - от вида: id, text

Клиента въвежда в полето колкото иска ключови думи. После целта е да се направи търсене в базата и да се изведат по 5 случайни резултата с текст за всяка от ключовите думи - т.е. текста някъде трябва в себе си да съдържа търсената от клиента дума.

Затова търсенето не е по ID а е търсене в текста (text like '%keyword%). И няма как да генерирам случайни ID и да ги изведа, понеже те едва ли ще съдържат някоя от търсените думи. Също не мога да направя една проста заявка за търсене на всички думи наведнъж като:

where text like '%keyword1%' or text like '%keyword2%' or text like '%keyword3%' ... и т.н.

защото в този случай не знам как да огранича до само 5 резултата за всяка дума.


Пример:
Клиента пуска търсене за тези думи:
1. Жаба
2. Локва
3. Въдица


Съответно трябва да се претърси цялата база и за всяка от думите да върнем по точно 5 случайни статии/текста съдържащи тази търсена дума (не 5 съдържащи коя да е от думите)

Примерен резултат:
текст1, текст2, текст3, текст4, текст5 (за Жаба).
текст1, текст2, текст3, текст4, текст5 (за Локва).
текст1, текст2, текст3, текст4, текст5 (за Въдица).
(15 статии).
 
От: PHP/mySQL съвет как да процедирам

Ако не измислиш друго, втория ти вариант е правилния (по принцип).
Иначе може да използваш временна таблица в която да събереш резултатите от всички обединения и която вече ще има истинско и реално ID, а от там нататък , лесно и бързо може да изведеш случайни IDта.
 
От: PHP/mySQL съвет как да процедирам

Като цяло e добре да се замислиш за нещо като пълнотекстово индексиране. Тия заявки с LIKE с wildcard от двете страни са страшно неефективни за разлика от like 'alabala%' и съответния индекс.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
http://lucene.apache.org/solr/

Иначе идеята на пхпбб е приложима.

Другото, което ми хрумна на прима виста
Код:
INSERT INTO temp_table SELECT FROM alabala where text like '%keyword1%' 
DELETE FROM temp_table where text NOT LIKE '%keyword2%' 
DELETE FROM temp_table where text NOT LIKE '%keyword3%'
 
От: PHP/mySQL съвет как да процедирам

Нямам идея като ефективност как ще е, но бих пробвал с процедура и само да подавам с пхп думите. Освен това, там където имаш WHERE клаузи, ако имаш де... слагай индекси... би трябвало да ускорява нещата.

Всъщност, ти така или иначе трябва да вземеш всички резултати... какъв е проблемът? Просто после с пхп избираш произволни 5 и готово.
 
От: PHP/mySQL съвет как да процедирам

Тествах набързо с временна таблица и т.н. варианти ... скоростта не се подобрява (дори понякога драстично отново става по-бавно). Няма положителен ефект и към натоварването. Така че засега оставам с Вариант 2.

Ест всичко е с индекс - първите неща които направих, преди това заявката бе над 2 секунди, с индекси и т.н. падна на 1/3 секунда, сега с Вариант 2 е под 1/10 секунда и мисля най-добро за посетителите.

Натоварването малко скочи, но идеално щастие явно няма ;)
 
От: PHP/mySQL съвет как да процедирам

Тествах набързо с временна таблица и т.н. варианти ... скоростта не се подобрява (дори понякога драстично отново става по-бавно). Няма положителен ефект и към натоварването. Така че засега оставам с Вариант 2.

Ест всичко е с индекс - първите неща които направих, преди това заявката бе над 2 секунди, с индекси и т.н. падна на 1/3 секунда, сега с Вариант 2 е под 1/10 секунда и мисля най-добро за посетителите.

Натоварването малко скочи, но идеално щастие явно няма ;)

Сравнително проста заявка правиш. Виждал съм някои, които показват 5 и 100 резултата и заявката е еднакво бавна - 5-6 секунди.
 
От: PHP/mySQL съвет как да процедирам

Тествах набързо с временна таблица и т.н. варианти ... скоростта не се подобрява (дори понякога драстично отново става по-бавно). Няма положителен ефект и към натоварването. Така че засега оставам с Вариант 2.

Ест всичко е с индекс - първите неща които направих, преди това заявката бе над 2 секунди, с индекси и т.н. падна на 1/3 секунда, сега с Вариант 2 е под 1/10 секунда и мисля най-добро за посетителите.

Натоварването малко скочи, но идеално щастие явно няма ;)
На временната таблица сложи ли индекс?

И пак те съветвам да помислиш върху това http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
 
От: PHP/mySQL съвет как да процедирам

Нямам идея като ефективност как ще е, но бих пробвал с процедура и само да подавам с пхп думите. Освен това, там където имаш WHERE клаузи, ако имаш де... слагай индекси... би трябвало да ускорява нещата.

Всъщност, ти така или иначе трябва да вземеш всички резултати... какъв е проблемът? Просто после с пхп избираш произволни 5 и готово.

Аз също бих използвал метод, който извлича 5 произволни резултата, съдържащ определена ключова дума:

$query = " SELECT ..... WHERE text LIKE '%$str%' ORDER BY RAND() LIMIT 5";

Ще ми е интересно какви са резултатите от това, че и аз като теб не съм специалист... :)
 
От: PHP/mySQL съвет как да процедирам

Ето ти още една идея , ако не приемеш съвета на Torbalan Trolski де.
Ако данните които ползваш за да се генерира така наречената временна таблица не се обновяват често - не я трии тая таблица. Нека си стои. Само я допълвай(с cron ли ще е или с нещо друго - ти си решаваш). Имаш ли я тая таблица постоянно , с както трябва направени индекси и с уникално ID на всеки запис, дърпай в PHP само IDтата, избери си 5 случайни IDта и после си изтегли тия 5 записа.
 
От: PHP/mySQL съвет как да процедирам

Мерси за всички съвети. Като цяло нещата се оптимизираха значително спрямо началото, когато 2-3 секунди отнемаше да се заредят резултатите и сега под една десета от сек.

Но сега забелязвам че ми излиза друг проблем - ще се търси нов хостинг така или иначе, понеже след като всички услуги бяха активирани трафика е прекалено много.

Apache Status

Server uptime: 2 days 1 hour 58 minutes 10 seconds
Total accesses: 2521371 - Total Traffic: 1039.8 GB
CPU Usage: u.76 s.96 cu59.12 cs0 - .0338% CPU load
14 requests/sec - 5.9 MB/second - 432.4 kB/request
61 requests currently being processed, 92 idle workers

1 TB трафик за 2 дни ... по 500 GB дневно за месец е в пъти повече от разрешеното от доставчика. Спешно трябва хостинг с повече трафик, защото сегашния с неговите 5 TB ще бъде изразходен до седмица.

Някакви препоръки за хостинг с голям трафик, в САЩ да е локализиран (Централни щати най-добре). Като сървър в момента съм нает сървър с Xeon E3-1270 V2 @ 3.50GHz. По-слаб процесор ще се измъчи, така че нещо аналогично. Kaтo RAM 16GB+ са достатъчни, диска RAID 1 /10 и 250 GB стигат. WHM/cPanel, managed. 20 TB+ трафик ! или без ограничение 100mb/s (макар че се чудя дали ще е достатъчно, но 1ГБ портове неограничени са прекалено скъпи, нешо по-средата може би?)
 
От: PHP/mySQL съвет как да процедирам

Търся php специалист
имам един проблем със автоматичните имейли сайта в момента го превеждам на БГ.и ми изникна този проблемproblem email.jpg
поливината е на маймуница
 

Горе