Autor Zpráva
quiced
Profil
Zdravím,

řeším aktuálně problém, jak upravit aktualizační script produktů v e-shopu tak, aby script tolik nezatěžoval server. Jde o to, že mám aktualizaci rozdělenou na jednotlivé části, například aktualizace skladu, textů atd.. podle toho jak často potřebuji jednotlivé části pouštět.

Problém nastává ve chvíli kdy aktulizuji script ve kterém aktualizuji zařazení do kategorií a texty. E-shop běží na Prestashopu a tudíž je nutné obsloužit poměrně dost tabulek.

Zkoušel jsem několik variant:

1. Všechny SQL dotazy pro všechny produkty jsem dal do jednoho stringu a poslal přes multi_query nicméně zde nastal problém, protože to házelo chybu "Command out of sync" tak jsem si řekl, že tam bude asi moc SQL dotazů a šel druhou cestou.

2. Všechny SQL dotazy k jednomu produktu jsem dal do jednoho stringu a přes multi_query zpracoval. Jenže tohle moc nefungovalo. Moc tomu nerozumím, ale pokud jsem to pochopil tak, multi_query v podstatě pracuje napozadí a proto když jsem chtěl udělat 5000 multi_query zasebou se to zaseklo hned u druhého produktu.

3. Aktuálně funkční varianta kdy každý SQL dotaz posílám ke zpracování zvlášť. Problém je v tom, že to hrozně zatěžuje server, když se má aktualizovat 5000 produktů.

Můžete mi někdo poradit ideální řešení jak provést větší množství SQL dotazů rychle a ideální s co nejmenší zátěží na server.

Díky za odpovědi.

Michal
Enko
Profil
Ahoj,
využíváš pro to nějaký modul z administrace presty? Jede ti to zpracování SQL přes jádro presty nebo využíváš nějaký svůj skript, který funguje nezávisle na prestě a rovnou něco dělá v databázi?
Spravuji několik prestashopů a v jednom se dělají importy produktů v řádech desetitisíců. Pokud zpracováváš řádově tisíce a více produktů, dělat to přes jádro presty je extrémě pomalé (1 položka trvá cca 1s) a musel by jsi mít hodně dobrý hosting kvůli časovým a paměťovým limitům. Nejlepší máš dělat to buď modulem, který nejede přes jádro (jeden takový mám) a zpracování tisíce produktů trvá v řádech sekund, anebo si na to napsat nějaký vlastní skript. Dej mi vědět a uvidíme, co a jak dál :-)
quiced
Profil
Mám na to vlastní script, který jde úplně mimo Prestashop ale stejně nevím jak to poskládat. Ty používáš klasický mysqli query nebo multi_query? Potřebuji jenom nakopnout správným směrem jak zpracovat takový počet produktů. Ty e-shopy jedou na VMS od ceskyhosting.cz takže výkon by tam byl, ale stejně stávající aktualizační scripty vyžerou hodně výkonu.
juriad
Profil
quiced:
Možná by šlo provést jeden (nebo několik málo) update, viz: stackoverflow.com/questions/3432/multiple-updates-in-mysql
Nebo další možností je napřed všechny změny nasypat jedním insertem do tempové tabulky a následně provést jeden update s joinem na tuto tabulku.
Během update můžeš zrušit nenutné indexy a foreign key - jejich update a kontrola může zdržovat.
quiced
Profil
juriad:
To co je popisováno na stackoverflow by šlo použít otázka je kolik produktů by to sneslo, protože si nemyslím, že kdybych tam dal všech cca 5000 tak, že by to jeden dotaz unesl.

Té druhé možnosti co popisuješ moc nerozumím můžeš to prosím nějak rozvést pro amatéra, třeba jednoduchým příkladem? Díky
Alphard
Profil
quiced:
že kdybych tam dal všech cca 5000 tak, že by to jeden dotaz unesl
Délka SQL dotazu je omezená, to máte pravdu. Ale nemusíte do něj vygenerovat celý balík dat, můžete to dělat třeba po stovkách, i to pomůže.
Čemu nerozumíte, tomu updatu, nebo zrušení indexů?
quiced
Profil
Alphard:
Ano to je pravda rozdělení určitě pomůže.

Nerozumněl jsem tomuto:

Nebo další možností je napřed všechny změny nasypat jedním insertem do tempové tabulky a následně provést jeden update s joinem na tuto tabulku.
juriad
Profil
quiced:
Vytvoříš si novou tabulku s daty, které budeš chtít změnit. Pokud chceš změnit cenu produktů s id 1, 5, 15, bude to vypadat následovně:
CREATE TABLE new_prices_tmp  (id INT PRIMARY KEY, price DECIMAL);
INSERT INTO new_prices_tmp VALUES (1, 10), (5, 13), (15, 34);
UPDATE prices p JOIN new_prices_tmp t ON p.id = t.id SET p.price = t.price;
DROP TABLE new_prices_tmp;
Toto se hodí, pokud potřebuješ provést několik různých updatů, které závisí na stejných datech.
Enko
Profil
quiced:
Ty používáš klasický mysqli query nebo multi_query?
Používám mysql_query v cyklu, který mi každou položku zpracovává samostatně. Vyhneš se tím omezím maximální délky sql dotazu a dá se tak zpracovávat na případné dávky, pokud by ti např docházel maximální čas běhu skriptu nebo paměť. Neznám ale tvůj konkrétní případ, mě to takto funguje v pohodě (např update 6k produktů trvá do 20s na sdíleném hostingu u C4 webhosting). Můžeš ale zkusit zaexperimentovat a vše si časově změřit. Podle toho se pak rozhodneš pro lepší řešení.
quiced
Profil
Enko:
A necháváš všechny produkty aktualizovat najednou nebo to nějak "balíčkuješ"? Takže jedním mysql_query aktualizuješ třeba sklad druhým text atd..?
Alphard
Profil
quiced:
Jak ty dotazy vypadají? Kdyžtak sem dejte ukázku těch nejnáročnějších (ale rozumně, žádné stovky řádků kódu :-)), jinak je to jen tipování. Záleží na tom, jestli je třeba redukovat velké množství jednoduchých dotazů, nebo zrychlit dotazy pomalé samy o sobě (jde o relativní režii na dotaz, i náročných dotazů je zanedbatelná, u jednoduchých významná).
quiced
Profil
Ukázka:
//Vybírám všechny atributy, které jsou v databázi zapsané u produktu
        $product_stocks = $mysqli->query("SELECT * FROM ps_stock_available WHERE id_product=" . $id_product . " AND id_product_attribute!=0");

//Existují skladové varianty pro dané produkt
            while($product_stock = $product_stocks->fetch_object()){
                //Aktualizuju sklad na nulu
                $mysqli->query("UPDATE ps_stock_available SET quantity='0' WHERE id_stock_available='" . $product_stock->id_stock_available . "'");
                //Vybírám si id_attribute
                $product_attribute = $mysqli->query("SELECT * FROM ps_product_attribute_combination WHERE id_product_attribute=" . $product_stock->id_product_attribute . "");

                if($product_attribute->num_rows>0){
                    $product_attribute = $product_attribute->fetch_object();
 
                    if(isset($stock['sizes'][$product_attribute->id_attribute])){
                        //Daná varianta u produktu existuje a aktualizuji aktuální sklad
                        $product_supplier_stock = $stock['sizes'][$product_attribute->id_attribute];
                        $quantity = $product_supplier_stock['quantity'];

                        //Zjišťuji zda pro danou variantu existuje outlet
                        $outlet_exist = $mysqli->query("SELECT * FROM ps_stock_available_outlet WHERE id_stock_available=" . $product_stock->id_stock_available . "");

                        if($outlet_exist->num_rows>0){
                            //Daná varianta se nachází v outletu musíme sečíst množství v outletu a množství u dodavatele
                            $outlet_exist = $outlet_exist->fetch_object();
                            $outlet_quantity = $outlet_exist->quantity;
                            $total_quantity = $quantity+$outlet_quantity;
                            $total_product_quantity += $total_quantity;
                            $update_stock = $mysqli->query("UPDATE ps_stock_available SET quantity=" . $total_quantity . " WHERE id_stock_available=" . $product_stock->id_stock_available . "");

                            if($update_stock==TRUE){
                                //Aktualizace skladu byla úspěšná
                                echo '<div style="background: green">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " byla úspěšná</div>";
                            }else{
                                //Při aktualizaci se stala chyba
                                echo '<div style="background: red">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " nebyla úspěšná</div>";
                            }

                        }else{
                            //Daná variatna není v outletu aktualizuji na množství podle dodavatele
                            $total_product_quantity += $quantity;
                            $update_stock = $mysqli->query("UPDATE ps_stock_available SET quantity=" . $quantity . " WHERE id_stock_available=" . $product_stock->id_stock_available . "");

                            if($update_stock==TRUE){
                                //Aktualizace skladu byla úspěšná
                                echo '<div style="background: green">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " byla úspěšná</div>";
                            }else{
                                //Při aktualizaci se stala chyba
                                echo '<div style="background: red">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " nebyla úspěšná</div>";
                            }

                        }

                        //Mažu aktualizovanou hodnotu skladu z array $stock
                        unset($stock['sizes'][$product_attribute->id_attribute]);
                    }else{

                        //Testuji zda je daná varianta pouze v outletu
                        $outlet_exist = $mysqli->query("SELECT * FROM ps_stock_available_outlet WHERE id_stock_available=" . $product_stock->id_stock_available . "");
                        if($outlet_exist->num_rows>0){
                            //Daná varianta u produktu je v outletu

                            /* TODO: Zde dodělat automatické vytváření nových atributů pokud atribut neexistuje */

                            $outlet_exist = $outlet_exist->fetch_object();
                            $update_stock = $mysqli->query("UPDATE ps_stock_available SET quantity=" . $outlet_exist->quantity . " WHERE id_stock_available=" . $product_stock->id_stock_available . "");
                            $total_product_quantity += $outlet_exist->quantity;
                            if($update_stock==TRUE){
                                //Aktualizace skladu byla úspěšná
                                echo '<div style="background: green">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " byla úspěšná</div>";
                            }else{
                                //Při aktualizaci se stala chyba
                                echo '<div style="background: red">Aktualizace skladu ID:' . $product_stock->id_stock_available . " u produktu ID:" . $id_product . " nebyla úspěšná</div>";
                            }

                        }

                    }
                }
            }

Posílám toho trochu více aby byly vidět souvislosti. Ono tam je také relativně dost výběrů dat z databáze. Snažil jsem se to částečně obejít tím, že si na začátku aktualizace poskládám array s některými daty a poté už pracuju jenom s array proměnou a nemusím se ke každému produktu dotazovat.
Alphard
Profil
Snad něco nepřehlížím, kdyžtak mě upozorněte.

1. Třeba ten update na 7. řádku se mi zdá neužitečný, v mnoha případech se ta hodnota přepíše v dalším kroku. Bylo by lepší uložit si jen hodnotu $total_quantity do proměnné a provést jeden update na závěr.
2. Teď se podívejme na ten while cyklus. Jsou tam selecty založené na $product_stock->id_stock_available. Kdyby se ty hodnoty přednačetly do pole (where id_stock_... in (1, 2, 3)), budou stačit 2 selecty na 1 $id_product (tj. 2 selecty na celý cyklus).
3. No jo, ale update se musí provádět pro každý záznam. Nemusí! Stačí si připravit pole [$product_stock1->id_stock_available=> $total_quantity1, $product_stock2->id_stock_available => $total_quantity2, ...] a to updatovat najednou.

Jestli něco nepřehlížím, stačí mi na zpracování celého while cyklu 3 dotazy. 2 selecty na začátek, výpočet v čistém PHP a jeden složitější update na závěr. Dále by snad bylo možné takto najednou zpracovat více produktů.
quiced
Profil
Alphard:
Super, díky moc za tipy. Je tam nějaký limit, kolik hodnot to může aktualizovat najednou?

Je fakt, že jsem to programovat narychlo, aby to bylo funkční a teď teprve začínám přemýšlet nad tím, jak to vymyslet aby to fungovalo co nejrychleji.
Alphard
Profil
Limit v počtu hodnot myslím není, ale je na délku paketu (max_allowed_packet) a další různé timeouty (např. net_read_timeout). Tenhle update by měl být rychlý, spíš půjde o tu velikost.
quiced
Profil
Jednom ještě poslední věc co se týče těch dotazů. Pochopil jsem to tak, že stačí si do array pole dát to co píšeš výše. Ale jak to potom zpracuji? Koukal jsem na ten odkaz co jsi také posílal, ale z toho mi moc není jasné jak ten array použít. Můžeš prosím uvést konkrétní příklad pro update.

Díky
Alphard
Profil
quiced [#16]:
Dám sem svoji hračku, ale není to ještě hotové a už vůbec ne vyzkoušené :-)

/**
* Creates MySQL multi update and if necessary, split it into multiple queries according to the maximum length.
* @param  string Table name
* @param  array Columns names ['key_column' => 'value_column'] or ['key_column' => ['value_column_1', 'value_column_2']]
* @param  array Data collection, each row must have the same shape names.
* @param  callable Function for escape input data. In this implementation is used same function for all data types.
* @param  int Optional parameter. If specified, the resulting query will be divided into multiple queries with a maximum length.
* @return string | array Result update query.
*/
function buildUpdateArray($table, array $names, array $data, callable $fceEscape, $maxQueryLength = null) {
    if (count($data) == 0) {
        throw new Exception('Input array is empty.');
    }
    list($keyColumnName, $columnNames) = each($names);
    if (!is_array($columnNames)) {
        $columnNames = [$columnNames];
    }

    $update = sprintf('update `%s` t join', $table);
    $on = sprintf(' on t.`%s` = f.`%s`', $keyColumnName, $keyColumnName);

    $setStats = [];
    foreach ($columnNames as $columnName) {
        $setStats[] = sprintf('t.`%s` = f.`%s`', $columnName, $columnName);
    }
    $set = ' set '.implode(', ', $setStats);

    $updateSize = strlen($update) + strlen($on) + strlen($set);
    $subQueryParts = [];
    $joinInnerSelects = function($innerSelect) { return ' ('.implode(' union all ', $innerSelect).') f'; };

    $innerSelect = [];
    $subQuerySize = -6; // strlen(' (') + strlen(') f') - strlen(' union all ')
    foreach ($data as $key => $values) {
        $valueKeyPairs = [];
        foreach ($columnNames as $index => $columnName) {
            if ($index > 0 && count($values) <= $index) {
                throw new Exception('Missing values to build a query.');
            }
            $value = is_array($values) ? $values[$index] : $values;
            $valueKeyPairs[] = sprintf('%s `%s`', $fceEscape($value), $columnName);
        }
        $selectQuery = sprintf('(select %s `%s`, %s)', $fceEscape($key), $keyColumnName, implode(', ', $valueKeyPairs));
        $currentSize = strlen($selectQuery) + 11;
        if ($maxQueryLength && ($updateSize + $subQuerySize + $currentSize > $maxQueryLength)) {
            $subQueryParts[] = $joinInnerSelects($innerSelect);
            $innerSelect = [];
            $subQuerySize = -6;
        }
        if ($maxQueryLength && ($updateSize + $subQuerySize + $currentSize > $maxQueryLength)) {
            throw new Exception('Given max query size is too small.');
        }
        $innerSelect[] = $selectQuery;
        $subQuerySize += $currentSize;
    }
    if (count($innerSelect) > 0) {
        $subQueryParts[] = $joinInnerSelects($innerSelect);
    }

    if ($maxQueryLength) {
        $updates = [];
        foreach ($subQueryParts as $subQuery)  {
            $updates[] = $update.$subQuery.$on.$set;
        }
        return $updates;
    } else {
        $update .= $subQueryParts[0];
        $update .= $on;
        $update .= $set;
        return $update;
    }
}

$fceEscape = function($expr) { return '\''.addslashes($expr).'\''; /* doplnit spravnou funkci! */ };

Použití:
echo buildUpdateArray('num', ['id' => 'num'], [21 => 118], $fceEscape), PHP_EOL;
update `num` t join ((select '21' `id`, '118' `num`)) f on t.id = f.id set t.num = f.num

$data = [
    15 => [118, 'czk', 300],
    13 => [230, 'eur', 200],
];
echo buildUpdateArray('offers', ['id' => ['price', 'currency', 'offers_image_id']], $data, $fceEscape), PHP_EOL;
update `offers` t join ((select '15' `id`, '118' `price`, 'czk' `currency`, '300' `offers_image_id`) union all (select '13' `id`, '230' `price`, 'eur' `currency`, '200' `offers_image_id`)) f on t.id = f.id set t.price = f.price, t.currency = f.currency, t.offers_image_id = f.offers_image_id

$data = array_combine(range(1, 50), range(50, 1, -1));
print_r(buildUpdateArray('test', ['id' => ['num']], $data, $fceEscape, 500));
Array
(
    [0] => update `test` t join ((select '1' `id`, '50' `num`) union all (select '2' `id`, '49' `num`) union all (select '3' `id`, '48' `num`) union all (select '4' `id`, '47' `num`) union all (select '5' `id`, '46' `num`) union all (select '6' `id`, '45' `num`) union all (select '7' `id`, '44' `num`) union all (select '8' `id`, '43' `num`) union all (select '9' `id`, '42' `num`) union all (select '10' `id`, '41' `num`) union all (select '11' `id`, '40' `num`)) f on t.id = f.id set t.num = f.num
    [1] => update `test` t join ((select '12' `id`, '39' `num`) union all (select '13' `id`, '38' `num`) union all (select '14' `id`, '37' `num`) union all (select '15' `id`, '36' `num`) union all (select '16' `id`, '35' `num`) union all (select '17' `id`, '34' `num`) union all (select '18' `id`, '33' `num`) union all (select '19' `id`, '32' `num`) union all (select '20' `id`, '31' `num`) union all (select '21' `id`, '30' `num`) union all (select '22' `id`, '29' `num`)) f on t.id = f.id set t.num = f.num
    [2] => update `test` t join ((select '23' `id`, '28' `num`) union all (select '24' `id`, '27' `num`) union all (select '25' `id`, '26' `num`) union all (select '26' `id`, '25' `num`) union all (select '27' `id`, '24' `num`) union all (select '28' `id`, '23' `num`) union all (select '29' `id`, '22' `num`) union all (select '30' `id`, '21' `num`) union all (select '31' `id`, '20' `num`) union all (select '32' `id`, '19' `num`) union all (select '33' `id`, '18' `num`)) f on t.id = f.id set t.num = f.num
    [3] => update `test` t join ((select '34' `id`, '17' `num`) union all (select '35' `id`, '16' `num`) union all (select '36' `id`, '15' `num`) union all (select '37' `id`, '14' `num`) union all (select '38' `id`, '13' `num`) union all (select '39' `id`, '12' `num`) union all (select '40' `id`, '11' `num`) union all (select '41' `id`, '10' `num`) union all (select '42' `id`, '9' `num`) union all (select '43' `id`, '8' `num`) union all (select '44' `id`, '7' `num`)) f on t.id = f.id set t.num = f.num
    [4] => update `test` t join ((select '45' `id`, '6' `num`) union all (select '46' `id`, '5' `num`) union all (select '47' `id`, '4' `num`) union all (select '48' `id`, '3' `num`) union all (select '49' `id`, '2' `num`) union all (select '50' `id`, '1' `num`)) f on t.id = f.id set t.num = f.num
)
quiced
Profil
Alphard:
Uff to je masakr, ale vypadá to skvěle pokusím si s tím trochu pohrát :)

Díky moc za ochotu

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: