T
T
tobut2015-05-02 21:48:30
PHP
tobut, 2015-05-02 21:48:30

Why are some INSERTs not added?

while(1) {
$PDO = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$PDO->exec("TRUNCATE TABLE fonbet");

foreach($data as $object) {
   $sql = "INSERT INTO fonbet (id, password, куча полей)  VALUES (:id, :password, куча значений)";
   $query = $PDO->prepare($sql);
   $query->execute(array(':id'=>$object->id, ':password'=>$object->password, куда соответствий));
}
}

There is a table ~ 100 rows, which is updated approximately 2 times per second. Since the data is different, you have to erase it and add data in a loop. The problem is that after each such operation, from 0 to 100 percent of the data gets into the table. I will add that "a bunch of fields" ~ 20.
As for the load:
show processlist - does not show queues (subjectively, since it is impossible to click often)
show status - pastebin.com/jyKz83qS (for some reason it does not show the top entries at all)
top -
PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
 2342 root      20   0  172m  34m  13m S  32.6  6.9   7:01.99 php
 2248 mysql     20   0  890m 225m  15m S   6.3 45.7   0:54.46 mysqld

I will be glad for any help. At a minimum, it seems to me that the request can be made more correctly.
UPD: I'll add the my.cnf settings of my mysql server - pastebin.com/qdFeQdr3 (only 512 MB of RAM)

Answer the question

In order to leave comments, you need to log in

2 answer(s)
V
Valentine, 2015-05-03
@tobut

It would not be bad to process errors
php.net/manual/ru/pdo.errorinfo.php
when doing prepare/execute
And yet - an eternal loop? Maybe pull the crown? The same script execution time should be made eternal, is it set in your php.ini or on the script? At least set the minimum slip, otherwise it’s not ice.
And something tells me that the ideology is violated?
It seems to me that prepare should be moved out of the loop and made into a transaction, no?

O
OnYourLips, 2015-05-05
@OnYourLips

You need to add data inside the transaction - it will be faster this way.
Also, I don't think mysql is a good choice in this case.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question