Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > PHP: Общие вопросы > Статистика

Автор: m1kle 23.9.2008, 16:25

Год назад я написал программу своей первой статистики, цель была - собрать информациую откуда и сколько люедй приходят ко мне на сайты. По началу всё было просто отлично, но теперь всё это весит 60 Мб и загружается десять секунд. Нужно оптимизировать, я, попытался это сделать, но в силу своих знаний у меня это не очень получилось. В таблице есть пять полей (IP(VARCHAR(15), SITE(VARCHAR(300), TIME(DATETIME), COMP(VARCHAR(200), WHERE(VARCHAR(100)). Есть ли какие-нибудь способы, приёмы, как это дело оптимизировать?

Автор: bobik02 23.9.2008, 16:38

Как один из вариантов: Перелить данные за прошлые  квартал/год/месяц в "бекапную" таблицу ...

Автор: m1kle 23.9.2008, 16:50

bobik02, Хорошая идея, только как сделать бекапную таблицу, и в чем её отличия от обычной? PS: Но ведь популярность проектов растет и новой таблицы хватит только на полгода

Автор: bobik02 23.9.2008, 17:16

А  какие запросы  у Вас там  выполняются ?

Автор: MoLeX 23.9.2008, 17:21

скрипт в студию для начала (может что подправить можно)

Автор: m1kle 23.9.2008, 17:39

В скрптах примитивизм полный. Код $ip=$_SERVER['REMOTE_ADDR']; $site=@ $_SERVER['HTTP_REFERER']; $comp=$_SERVER['HTTP_USER_AGENT']; $time=date("Y-m-j, H:i:s"); $where=$_SERVER['SCRIPT_FILENAME']; if(!$site) $site="n"; $q_ins_enter=mysql_query("insert into `enters` (`ip`,`site`,`time`,`comp`,`where`) values ('$ip','$site','$time','$comp','$where')") or die(mysql_error());

Автор: bobik02 23.9.2008, 18:24

Ну добавления это понятно, а вот с выводом(отчётами) как  код ?

Автор: password 23.9.2008, 18:30

Пару дней назад писал. У меня тоже ограничения по размеру бд. Так придумал для себя, может конечно сильно грамозко, но разбил на 3 базы: 1: CREATE TABLE IF NOT EXISTS `stat_osn` (               `site` varchar(80) NOT NULL,               `kols` int(15) NOT NULL             ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - основная' 2: CREATE TABLE IF NOT EXISTS `stat_ip` (               `ip` varchar(50) NOT NULL             ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - уникальные ip' 3: CREATE TABLE IF NOT EXISTS `stat_mon` (               `site` varchar(80) NOT NULL,               `ip` varchar(60),               `date_in` datetime default '0000-00-00 00:00:00'             ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - за последний месяц подробная' В 1 записываю для данного сайта kols+1; Во 2-ю записываю и по ней же сравниваю иникальные ip; В 3-ю записываю подробный отчет со всеми вхождениями, и храню только определенное время например - месяц:  $dd1 = date("Y-m-d H:i:s", mktime(0,0,0,date("m")-1,date("d"),  date("Y")));  $SQL2="delete from `stat_mon` where `date_in`<'$dd1'";  $r2 = mysql_query($SQL2); А дальше информер рисую с двумя цифрами, уникальных ip и количество посещений из 1 таблицы.

Автор: m1kle 23.9.2008, 18:36

$q_stat=mysql_query("select `stat`, `where`, `year` from `stat` WHERE `status` = '1' and `category` LIKE '%$categ%' order by `id` desc LIMIT $col2, 20") or die(mysql_error());

Автор: teroni 23.9.2008, 20:42

Индексы все нужные в таблице созданы?

Автор: m1kle 23.9.2008, 20:45

Какие индексы?

Автор: teroni 23.9.2008, 20:55

http://www.mysql.ru/docs/man/MySQL_indexes.html Для огромных таблиц - нужная вещь.. Позволяет ускорить время выполнения запросов.

Автор: m1kle 23.9.2008, 21:46

teroni, прочитаю завтра, может размер некоторых полей уменьшить, или есть более экономные типы, например свой тип поля для IP адреса?

Автор: mr.fox 24.9.2008, 00:36

Цитата(m1kle @  23.9.2008,  21:46 ) например свой тип поля для IP адреса?  Int + ip2long  Цитата(m1kle @  23.9.2008,  18:36 ) LIKE '%$categ%'  желательно от like избавиться. Он занимает много времени. Цитата(bobik02 @  23.9.2008,  16:38 ) Как один из вариантов: Перелить данные за прошлые  квартал/год/месяц в "бекапную" таблицу ...  солидарен. На мой взгляд лучшее решение. Как только основная таблица начинает тормозить, переносить часть/все ее данные в бэкапную.  А вообще конечно это большой поток информации. У меня на сайте начало тормозить после того как таблица выросла далеко за 1гб.

Автор: m1kle 26.9.2008, 13:40

teroni, там очень плохо написано, я совсем не понял, для чего нужны индексы, и как они работают. Может ты объяснишь? Есть вопрос, как быстро превратить таблицу в бекапную средствами HeidiSQL? И ещё вопрос: может просто не надо записывать посещения сайта ботами?

Автор: yurik_l 26.9.2008, 14:08

m1kle, о индексах: http://kurepin.ru/php/index/

Автор: m1kle 1.10.2008, 17:53

Кое-как разобрался с индексами. select * from `enters` WHERE `time` LIKE '%".mysql_real_escape_string($tim)."%' $bot order by `".mysql_real_escape_string($o)."` ".mysql_real_escape_string($d)." Использую вот такой запрос. Данные за год он считал до введения индекса за 8 секунд, после введения индекса - также. Непонимаю, почему. *Думаю стоит удалить все данные о ботах, которые заходили на сайты. *Еще появилась идея не записывать в базу время, только даты. Вопрос: Как сделать тип данной IP?

Автор: Валерия 1.10.2008, 17:57

select * from `enters` limit 30 долго выполняеться такой запрос?

Автор: m1kle 1.10.2008, 18:07

Валерия, 0,172 сек. Так для любой базы, даже маленькой.

Автор: Валерия 1.10.2008, 19:15

Что за $bot? Вообще что тебе нужно сделать со всеми этими записями? Вывести? Тогда делай постранично.

Автор: m1kle 1.10.2008, 19:18

$bot - это если пользователь выбрал не показывать роботов, которые посетили сайт, ему их не показывают. Мне нужно вывести, чтобы все были на одной странице, а вообще мне надо сократить время загрузки, 8 секунд это не дело.

Автор: Валерия 1.10.2008, 19:25

Ну смотри, если ты говоришь что у тебя база 60 мб, то ты на страницу так много выводишь о_О? По крайней мере делай не  select * а перечисляй только те поля что выводишь, уже меньше мускул будет в пхп инфы передавать. Только не говори что нужно все поля и выводить)))

Автор: m1kle 1.10.2008, 19:30

Да, нужно все поля выводить. Я думаю, можно съэкономить на удалении ботов(80% базы), и сделать дату без времени. Еще перевести IP в тип IP.

Автор: Валерия 1.10.2008, 19:46

Мне просто интересно сколько у тебя html страничка сгенерированная грузиться с такой кучей инфы) Сделай делит там где встречаеться в useragent слово bot (кажись все поисковики дают юзерагент типа google-bot yandex-bot, но не уверена)

Автор: m1kle 1.10.2008, 19:59

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

Автор: m1kle 3.10.2008, 14:46

Разобрался, удалил всех ботов. Количество записей сократилось в два раза. Теперь нужно не записывать новых ботов. Вопрос, можно как-то через MySQL прописать, что поле не может принимать определенные значения. Например поле `beta` не может принимать значения, которые включают в себя "%alpha%" Также есть вопрос, как сделать поле типа IP, с помощью MySQL? Появился еще вопрос: как ограничит Базу данных до 50 записей? *После предпринятых мер, время загрузки сократилось на треть.

Автор: m1kle 9.11.2008, 20:36

Ап. Тема забытая, а ответы на вопросы не получены.

Автор: bars80080 9.11.2008, 21:20

Цитата(m1kle @  3.10.2008,  13:46 ) Теперь нужно не записывать новых ботов. Вопрос, можно как-то через MySQL прописать, что поле не может принимать определенные значения. select и если есть, то не записывать Цитата(m1kle @  3.10.2008,  13:46 ) Также есть вопрос, как сделать поле типа IP, с помощью MySQL нет такого типа поля, как и нет типа поля районные центры Хабаровского края. сделай varchar(16), а при поиске простое сравнение Добавлено через 56 секунд Цитата(m1kle @  3.10.2008,  13:46 ) Появился еще вопрос: как ограничит Базу данных до 50 записей? select count(*) если больше 50 или больше, не записывать п.с. в базе таблицы, а уже в таблицах - записи

Автор: georgiy11 9.11.2008, 21:26

Вот неплохая  статья ИМХО по оптимизации БД http://stroimsayt.com/drugoe/o-gramotnom-ispolzovanii-bd-mysql.html

Автор: skyboy 9.11.2008, 22:27

Цитата(m1kle @  3.10.2008,  13:46 ) Вопрос, можно как-то через MySQL прописать, что поле не может принимать определенные значения. только триггерами. конечно, если говорить только про использование СУБД. потому как средствами того же PHP подобная логика решается на "раз". Цитата(bars80080 @  9.11.2008,  20:20 ) сделай varchar(16) учитывая наличие встроенных функций дя работы с IP: http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_inet-aton и INET_NTOA, а так же стопудов более быструю индексацию для целых чисел в сравнении с индексом по тексту, учитывающим кодировку и collation, все же советовал бы использовать целочисленное поле под хранение IP

Автор: bars80080 10.11.2008, 01:47

Цитата(skyboy @  9.11.2008,  21:27 ) все же советовал бы использовать целочисленное поле под хранение IP ... ссылка таки открылась и всё стало ясно

Автор: skyboy 10.11.2008, 03:28

bars80080, нет. использовать упомянутые уже inet_ntoa и inet_aton

Автор: m1kle 11.11.2008, 22:01

Почитал приведенную сдесь статью. Сделал для себя три решения в плане оптимизации: 1) Прекратил записывать поисковые машины в протокол 2) Поставил все записи в режим ожидания 3) Решил разделить таблицу на две части. Тут возник вопрос, ускорит ли это работу, в одной таблице будет хранится айпи, дата, а в другой. Откуда пришел, куда пришел, компьютер пользователя.

Автор: bars80080 11.11.2008, 23:25

Цитата(m1kle @  11.11.2008,  21:01 ) в одной таблице будет хранится айпи, дата, а в другой. Откуда пришел, куда пришел, компьютер пользователя.  а зачем разделение?

Автор: m1kle 11.11.2008, 23:36

а зачем разделение?  Чтобы таблица быстрее работала, сортировка будет идти по маленькой таблице, а остальные данные приписываться по второй.

Автор: bars80080 11.11.2008, 23:47

а она точно будет быстрее? я понял на каждую запись в первой таблице с ip будет приходиться только одна запись (от кого, данные компа пользователя...)?

Автор: m1kle 11.11.2008, 23:48

Цитата а она точно будет быстрее? я понял на каждую запись в первой таблице с ip будет приходиться только одна запись (от кого, данные компа пользователя...)? Да одна запись таблице с ip одна запись с данными о компьютере, и "сайтах".

Автор: m1kle 13.11.2008, 19:20

Чтобы не создавать новую тему, спрошу здесь. Значит есть таблица forumm. Нужно заменить тип поля айди. С инт на тиниинт. Он выдает ошибку "Duplicate entry '127' for key 1". В чем может быть проблема? Прошу прощения, сам разобрался. Там могут быть значения от -127 до 127.

Автор: Sanchezzz 14.11.2008, 22:00

m1kle ( структу бд встудию лучше в новый вопос...  перепеши все на mysqli на 40% производительней и быстрей чем простые функции mysql_ ...  да и переписывать не так много на php.su док есть.

Автор: m1kle 18.11.2008, 18:45

Цитата(Sanchezzz @  14.11.2008,  22:00 ) m1kle ( структу бд встудию лучше в новый вопос...  перепеши все на mysqli на 40% производительней и быстрей чем простые функции mysql_ ...  да и переписывать не так много на php.su док есть.  Про mysqli можно поподробнее? Пока оптимизация проходит отлично, все стало работать гораздо быстрее. Значит сделал две таблицы. Enters (Id, Ip, Time) и Entersi (comp, site, where). Программа, которая выводит данные сортирует все по Enters, и уже припизывает дополнительные данные по Entersi.

Автор: Sanchezzz 21.11.2008, 11:58

это класс в PHP5  вот самая нормальная http://www.php.su/functions/?cat=mysqli пользоватся просто: Код $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $dbname = 'myBD'; //конект и выбор БД $db = new mysqli($dbhost,$dbuser,$dbpass,$dbname); if (mysqli_connect_errno()) {     printf("Ошибка подключения: %s\n", mysqli_connect_error()); } //далее работаешь как с обычным классом $re = $db->query("Запрос"); //далее результат выбираешь из $re while ($row = $re->fetch_assoc()) {  print $row['id']."<br>"; } $db->close(); + ко всему много фичей можно на лету менять запросы (prepare и bind_param) а также создавать шаблоны запросов из простого массива или из прошлого запроса  ну об этом читать в документации подробно еще написано в phpclab.ru.  единственное главное чтобы на сервере был php5 и mysqli ну на нормальных серверах это есть.

Автор: Валерия 21.11.2008, 19:36

А есть смысл переходить на mysqli используя тот же процедурный стиль написания? Я переделала свой скрипт на mysqli, разницы в скорости работы не обнаружила

Автор: ksnk 21.11.2008, 19:54

Валерия, А может не там тормозило? ;-) можно попрофилироваться xdebug'ом и почистить  именно там, где надо...

Автор: Валерия 24.11.2008, 15:33

У меня и так 0.005 сек скрипт в среднем выполняется, но думала, а вдруг быстрее будет )

Автор: Sanchezzz 24.11.2008, 17:41

почитайте http://forum.antichat.ru/showthread.php?t=76247 мне нравится навязывать не буду.  при мальнеком запросе не уведете разницу в скорости.

Автор: Валерия 24.11.2008, 18:08

Sanchezzz, не впечатлило. Кроме подготовленных запросов, ничего нового не заметила... Да и толку от них тоже не поняла... Как ними можно сэкономить траффик?

Автор: Sanchezzz 24.11.2008, 18:27

незнаю насчет трафика но безопасность и скорость для меня важно + mysqli реализует работу с БД через ООП: классы и объекты. Что потенциально повышает гибкость кода, скорость. mysqli более мощное и полное расширения для работы с БД MySQL тоже +  зашита от иекция + работа шаблонов + скорость + итд. Ладно хватит сыр бор разводить а то тему в кашу превратили.

Автор: Валерия 24.11.2008, 18:49

Тему неплохо бы если бы разделили

Автор: m1kle 24.11.2008, 23:11

Значит я посмотрел mysqli(), по моим подсчетом если брать в расчет 1000 запросов экономия во времени 0,0004 секунды. Разделение на две таблицы принесли помиму быстро выполнения кода и проблемы, пришлось отказаться от сортировки по сайту с которого заходили, куда заходили, и компьютера, т.к. все эти данные лежат во второй таблице. Вопрос стандартный: Как еще можно оптимизировать работу Базы данных.

Автор: solenko 25.11.2008, 00:07

Цитата(m1kle @  1.10.2008,  16:53 ) Кое-как разобрался с индексами.select * from `enters` WHERE `time` LIKE '%".mysql_real_escape_string($tim)."%' $bot order by `".mysql_real_escape_string($o)."` ".mysql_real_escape_string($d)."Использую вот такой запрос. Данные за год он считал до введения индекса за 8 секунд, после введения индекса - также. Непонимаю, почему. Запрос LIKE '%... никогда не будет использовать индекс. Это всегда fullscan т.к. вы фактически говорите, что с начала может быть любой символ. Цитата(m1kle @  23.9.2008,  17:36 ) $q_stat=mysql_query("select `stat`, `where`, `year` from `stat` WHERE `status` = '1' and `category` LIKE '%$categ%' order by `id` desc LIMIT $col2, 20") or die(mysql_error()); 1. Что хоанится в поле `category`? 2. Вы перевели IP на int? 3. Приведите, наконец, полную структуру таблиц. Описанная вами структура не совпадает с вашими эе запросами на выюорку. 4. http://habrahabr.ru/blogs/mysql/44608/

Автор: m1kle 25.11.2008, 17:33

Селект я изменил  Код $sel_all=mysql_query("select * from `enters` WHERE `time` LIKE '".mysql_real_escape_string($tim)."%' order by `".mysql_real_escape_string($o)."` ".mysql_real_escape_string($d)."") or die(mysql_error()); Таблицы на данный момент таковы: Eners(id(int(8)), ip(char(16)), time(date) Entersi(comp(char(100)), site(char(70)), where(char(70))

Автор: solenko 25.11.2008, 18:03

m1kle почему вы работаете с полем типа date как с текстовым? Какие значения прихрдят в $tim что нужно использовать like?

Автор: patap 25.11.2008, 18:25

может чем-то поможет http://www.mysql.ru/docs/man/EXPLAIN.html

Автор: Sanchezzz 25.11.2008, 18:43

выкинь лайк используй лучше DATE_ADD() или DATE_FORMAT()  Код select * from `enters` WHERE `time` < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 30 DAY DATE_ADD() действует точно также только без форматов... MINUTE DAY MONTH итд

Автор: m1kle 25.11.2008, 23:54

Цитата(Sanchezzz @  25.11.2008,  18:43 ) выкинь лайк используй лучше DATE_ADD() или DATE_FORMAT()  Выделить всёкод SQL 1:      select * from `enters` WHERE `time` < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 30 DAY DATE_ADD() действует точно также только без форматов... MINUTE DAY MONTH итд  $tim у меня формата Y-m-d. использовать преведнный тобой метод я готов. Только насколько он прибавит в производительности?

Автор: Sanchezzz 26.11.2008, 06:28

ну думаю прибавит всеж ишит интервал а не совпадения. ну можно проверить инструментом на скорость запроса и как он грузит систему. http://www.simtel.net/product.php%5Bid%5D101530%5Bcid%5D52%5BSiteID%5Dsimtel.net

Автор: m1kle 29.11.2008, 18:44

Цитата(m1kle @  25.11.2008,  23:54 ) select * from `enters` WHERE `time` < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 30 DAY Так тип поиска не сократил время исполнения кода.

Автор: Sanchezzz 29.11.2008, 19:06

что даже 0,1 сек не убавилось. Можно еще сократить поиск если в запросе указывать вазрат только определенных колонок а не всех.

Автор: solenko 30.11.2008, 09:23

Цитата(m1kle @  29.11.2008,  17:44 ) Так тип поиска не сократил время исполнения кода. Index po polyu time est? Добавлено через 1 минуту и 35 секунд Giv me results of Код explain select * from `enters` WHERE `time` < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 30 DAY

Автор: m1kle 2.12.2008, 18:53

Код <table >   <tr id="header">     <th>id</th>     <th>select_type</th>     <th>table</th>     <th>type</th>     <th>possible_keys</th>     <th>key</th>     <th>key_len</th>     <th>ref</th>     <th>rows</th>     <th>Extra</th>   </tr>   <tr>     <td>1</td>     <td>SIMPLE</td>     <td>enters</td>     <td>range</td>     <td>time_i</td>     <td>time_i</td>     <td>3</td>     <td class="isnull"></td>     <td>1</td>     <td>Using where</td>   </tr> </table> Вот результат запроса. Индекс по полю time есть. Цитата(Sanchezzz @  29.11.2008,  19:06 ) что даже 0,1 сек не убавилось. Можно еще сократить поиск если в запросе указывать вазрат только определенных колонок а не всех.  А там все три колонки нужны.

Автор: ksnk 2.12.2008, 19:02

Я правильно понимаю, что в этом запросе часть  Код ... WHERE `time` < DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') - INTERVAL 30 DAY  будет для каждой строчки перевычисляться DATE_FORMAT и минус 30 дней? Или mysql значительно умнее, чем я о нем думаю и вычислит все это один раз? по идее - ума ему взять неоткуда, функция now() как ни крути - функция...

Автор: m1kle 4.12.2008, 17:26

Начнем с того, что у меня дата без времени указана

Автор: krundetz 4.12.2008, 17:52

m1kle,  да уж накатали кучу всего а первый ответ приведенный bobik02 на мой взгляд наиболее оптимальный. Делиш то что у тебя изначально было на несколько таблиц а именно таблица сегодняшняя, таблица за месяц или за неделю(зависит от того за какой период тебе важно просматривать информацию или возможно обе нужны) и самая большая таблица к которой наименее часто обрашаешся храниш в ней статистику за весь период. Первая таблица наиболее легкая и обрашения к ней не будут нагружать систему по достижение конца дня береш и данные из не переносиш в следушию таблицу по размеру когда таблица и эта таблица заполнится переносиш данные из нее в архивную. С первой таблицей система работает всегда со второй только когда подходит временной пердел и когда ты работаешь со статическими данными. К ьретий обрашения очень редки так как к тем данным ты обрашаешься крайне редко.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)