Форум MySQL

Всем привет.

Скажите нужно ли как-то предварительно настраивать БД при записи в нее большого массива данных, к примеру 10000 тыс строк?

у меня стоит только:

<? ini_set('max_execution_time',0); ?>

Пробовал записать 300, 1700 строк, все записывается, а вот большие файлы, не дают никакого результата. (не записывает ни одной строчки). Начиная где-то с 4000 тыс. строк. Хотя я распечатываю массив и просматриваю его, все ок. Ранее никогда такие большие файлы не приходилось писать..

зависит от того, как именно пишете.
В общем случае существует как минимум ограничение на размер пакета обмена клиент-сервер.

пишу так:

<? //$array - массив с данными array_walk($array, create_function('&$s','$s = "(\'" . implode("\',\'", array_map(mysql_real_escape_string, $s)) . "\')";')); $array = implode(',',$array);  mysql_query("INSERT INTO `sometable` (`name1`, `name2`) VALUES " . $array);  ?>

>>>В общем случае существует как минимум ограничение на размер пакета обмена клиент-сервер.
текс.. и где сие ограничение можно расширить и можно ли?

то есть всё одним запросом.
Так может стоит попробовать записать отдельными?

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

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

>отдельными вы имеете ввиду для каждого столбца отдельный запрос?

для каждой строки таблицы.

то есть нужно как-то зацикливать, я правильно понимаю?
но тогда нужно менять арх БД. а именно (id) auto_increment убирать?

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

нет ненадо ничего убирать
просто разбейте свой массив 10000 элементов на 10 частей, в итоге получится 10 массивов по 1000 элементов.
и будет у Вас 10 запросов по одному запросу на каждый массив.
___
можно и по-другому, но мне кажется для Вас так будет легче

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

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

Вас не затруднит пример привести? спасибо.

Вы уже тут, бедолага. ) Какой пример, ведь опять же приходиться говорить о уже сказанном. Ну допустим, после 4000 строк ругается. Но вам то не на 4000 делить данные нужно, а на число кратное числу полей записываемых, которое бы не выходило за пределы 4000.
То есть, если у вас к примеру, строк для записи 39970, вы не можете более 4000 за один запрос записать, а записываете вы 7 полей, то нужно узнать сколько групп/строк для семи полей не будет превышать 4000:

<? $grp = floor(4000/7); //571 группа //всего данных для одного запроса: $date = $grp*7; //3997 //на сколько частей разбить массив данных для записи в цикле: $n = ceil(39970/3997); //10

Не удивляйтесь, что так сошлось все, оно и должно быть так - кратно числу полей записываемых, и если будет остаток при делении для $n, то и он должен быть кратен 7 (на одну, две.... строки записи), иначе с входными данными непорядок. Далее уже известное - входной массив делим на 10:

<? $arr = array_chunk($arr, $n); //и циклом производим запись foreach($arr as $val) function getStringForMySQL($val, 7);

>. Ну допустим, после 4000 строк ругается.

Ну допустим, оно ругается не на 4000 строк, а на определенное в MAX_ALLOWED_PACKET количество даже не символов, а байт.
И всё это можно посчитать, и неприблизительно а предельно точно.
Да только кой смысл это делать, если человек одну строку добавить не могет?


sypex...
сказал бы я...

Ну если одну не может, тогда я не знаю. )

PS. Я так полагаю, что пишутся фиксированные данные, почему так четко за неким пределом N отваливается. Хотя посчитать объем конечно можно.

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

А если поменять, параметр max_allowed_packet, на более большой, это будет являться нормальным решением?

потому что если бы Вы записывали данные по одной строке на запрос, эта ошибка не возникла бы.

sim5 вы не подумайте, что я ваши слова сквозь уши пропустил и все, наоборот)
я просто тестирую, теперь сей способ. ну а всего знать не возможно, и как вы можете видеть из названия темы, и первого моего поста я пришел сюда с вопросом. И я думал что тут дело в возможно каких-то надстройках БД..

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

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

В какой момент разделить? А что вы записываете, те данные которые вы в РНР разделе приводили?

да, только я там пример приводил, небольшой, простой..
а на деле видите, сколько еще моментов.. о которых я просто не знал..
а можно ваш пример по делению массива объединить в одну функцию getStringForMySQL?

то есть наибольшим пакетом, который может быть передан к или от MySQL сервера и клиента, является - 1 GB.
Правильно ли расширять до такого объема max_allowed_packet?
Или правильно все таки делить большой массив в PHP?

The server's default max_allowed_packet value is 1MB. Вы не то прочли.

Можно все что угодно объеденить, был бы только смысл. В данном случае его нет, ибо нужно проходить циклом разбитый массив на N строк для записи, отдавая его в функцию. Если ваши данные это фактически фиксированные данные, то есть, например, вы записываете, к примеру, 4 поля в таблицу, и все эти четыре поля VARCHAR, при этом вы их ограничили по длине, например 100 символов, то не сложно посчитать число сток записи, которые не будут выходить за пределы установки по умолчанию. Другими словами вы знаете некую постоянную величину, и расчетов практически не потребуется.
Другое дело, если вы, например, записываете эти же четыре поля, но одно из них TEXT, и в это поле поступают данные пусть от textarea, но в одной строке это будет 30 слов, а в другой уже на несколько килобайт, и т.п.. В данном случае объем данных от строки к строке может быть различным и очень, значит нужно будет производить некий расчет и группировать число строк записи в цикле исходя из объема данных.
Коли вы знаете что за данные вам поступают, и это не произвольные по длине данные, а имеют некий максимум, то в этом случае тоже не сложно просто в уме посчитать на какое число строк нужно разбить массив, по максимальному значению. Главное чтобы это число было кратно числу записываемых полей. Далее в цикле отдавайте по частям этот массив для преобразования в строку для многострочной записи, и записывайте эту строку.

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

В цикле, в котором собираете запрос, ставьте проверку, если текущая длина запроса + длина добавляемого куска больше чем max_allowed_packet, то отправляйте собранный запрос в СУБД и продолжайте собирать следующий запрос.
Можно отправлять запрос на вставку каждого ряда отдельно или цель именно в том, чтобы уложить максимум данных в каждый запрос?

да нее такой цели нет, я просто хотел использовать пример sim5.
ну вобщем сделал так:

<? foreach ($data as $index=>$val) mysql_query("INSERT INTO `data` (`name1`, `name2`) VALUES ('$val[0]','$val[1]')"); ?>

видимо об этом мне говорил Трианон, а я его не понимал и продолжал смотреть на пример sim5.
И думал как из примера sim5 можно сделать построчную запись)

Конечно можно, вы только не поймете одной вещи - на сколько разбить ваш массив, чтобы его N элементов для будующих M строк записи не выходили за пределы установленного в настройках MySQL по умолчанию. И сделать это можно даже на глаз. Ваши данные, это скорее всего результат парсинга html-страниц, а даже в сотни КБ текста трудно найти страницу, а тем более много элементов с одной страницы по сотни КБ. Из этого следует, что вы даже без РНР расчетов можете узнать "безопасное" число строк для записи, а следовательно и разбиение массива на N элементов.
Записывать же массив из многих тысяч строк отдельным запросом для каждой, это ужас.

понять то я понимаю) ,а вот сделать, к сожалению, пока не могу сам..
а почему ужас? вот например 20 тыс. строк записалось за 30 сек.. или дело в другом?

Последняя попытка. ) Это просто пример. Он основан на том, что просто по масимальной длине некоего значения для записи получаем количество одновременно записываемых значений (строк значений). Конечно, если одно значение, это 512 байт, например, а остальные по 2 байта, то это получается "расточительный" расчет, но и в этом случае будет выгода. Можно конечно подсчитать все как в бухгалтерии, но это несколько сложнее будет выглядеть. Тут думайте сами. Запись производится исходя из установок сервера. И так:

<? //формирование строки для MySQL запроса function getStringForMySQL($a, $n, $r = false) {   $a = array_chunk($a, $n);   array_walk($a, create_function('&$s','$s = "(\'" .                   implode("\',\'", array_map("mysql_real_escape_string", $s)) .                   "\')";'));   $a = implode(',',$a);   if($r) $a = str_replace("'", "`", $a); } //получаем установки сервера $q = mysql_query("SHOW VARIABLES LIKE 'max_allowed_packet'"); $max = mysql_result($q,0,1); echo "Максимальный пакет для записи (установки сервера): " . $max . " байт<br>"; //не нужно выбирать по максимуму, mysql ругнется,  //так как не только буковки для записи ему свои отдаем //поэтому устанавливаем размер пакета для записи //округляя его кратным 1 МБ $max -= $max % 1000000; echo "Выбираем максимальный пакет для записи: " . $max . " байт<br>"; //имена полей для записи $fields = array('name1', 'name2'); //число полей $n = count($fields); echo "Записываемых полей: " . $n . "<br>"; //формируем тест-массив //можете сформировать любого размера для проверки //но по числу элементов кратных числу $n //проверте, что будет, если число элементов будет некратным $a = array_fill(0, 64382, 'Эти функции дают возможность работать с массивами различными способами.  Массивы очень удобны для хранения, обслуживания и работы с наборами переменных. Поддерживаются одно-  и многомерные массивы, могут быть даже массивы, создаваемые пользователем.'); echo "Всего строк для записи: " . count($a)/$n . '<br>'; //получаем максимальную длину записываемого значения  $len = max(array_map('strlen',$a)); //получаем число элементов массива для одновременной записи $str = floor($max/$len); //устанавливаем это число элементов кратным числу записываемых полей if($str % $n) $str -= $str % $n; //здесь "по N байт каждая....", справедливо только для этого примера echo "За один запрос будет записано строк: " . $str/$n . ", по " . $len*$n. " байт каждая, и общим объемом " .$str*$len. " байт<br>"; //разбиваем массив на число одновременно записываемых значений $a = array_chunk($a, $str); echo "Всего запросов на запись: " . count($a) . '<br><br>'; //можете просмотреть массив в результате //echo '<pre>'; //print_r($a); $error = false; //формируем строку имен полей для запроса getStringForMySQL(&$fields, $n, true); //производим запись foreach($a as $val) {   //формируем строки записываемых значений для запроса   getStringForMySQL(&$val, $n);   //многострочная запись одним запросом   $q = "INSERT INTO `test` $fields VALUES $val";   if(!mysql_query($q)) {     $error = mysql_error();     break;   } } echo !$error ? 'Данные записаны успешно!' : 'Что-то не срослось! ' . $error;

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

PS. Обратите внимание на вызов функции mysql_real_escape_string в функции getStringForMySQL, она взята в кавычки. Именно так и должно быть прописано, ранее я просто упустил из виду это, хотя работать будет, и вы не увидете предупреждения, если они подавляются.

Уже несколько лет пользую это чудо - sypex.net, так как phpmyadmin не справляется с большими дампами.

А речь разве о РМА идет?