30 sekund å fylle inn 1000 verdier i MySQL?

[r2d290]

Hallo

 

Planen er å kunne fylle opp 20 kollonner med litt over 30000 rader innen en tid på under et sekund. Jeg prøvde å kjøre en for-loop i et php-script som gikk til 1000 og skrev til databasen hver gang.

 

Resultatet er at det tar ca. 25 sekund å bare fylle inn 1000 rader. Hvordan kan jeg da få til å fylle inn over 30000 rader på kort tid?

 

$query_num_rows = mysql_num_rows($query_run);
                    if ($query_num_rows==0)
                    {
                        $before = microtime(true);
                        //for ($i = 1; $i < 34952; $i++)
                        //for ($i = 0; $i < 3000; $i++)
                        //{
                        $inside = microtime(true);
                        $query = ("SELECT userAttempt from voltageOut WHERE username = '$username' ORDER BY userAttempt desc LIMIT 1");

                        $query_run = mysql_query($query);
                        for ($i = 0; $i < 1000; $i++)
                        {
                            $row = (mysql_fetch_array($query_run));
                            $userAttempt = $row[0];

                           
                            $query = "INSERT INTO voltageOut VALUES ('', '$username', '', '$userAttempt'+1, '42', '$outA', '$outB', '$outC', '$outD', '$i', '$inside', '', '', '', '', '', '', '', '', '', '', '', '', '', '')"; //Remember mysql_real_escape_string 
                            //}
                            //$query_run = mysql_query($query);
                            if ($query_run = mysql_query($query))
                            {
                                    //header('Location: send_success.php');
                            }
                            else
                            {
                                    ?>
                                    <DIV id="allFields" style="position: absolute; left:100px; top:560px; height:25px">
                                    ERROR: Sorry, we could not send these values at this time. Try again later or contact the administrator.
                                    </DIV>
                                    <?php
                            }
                        }
                        $after = microtime(true);
                        //echo ($before) . " is before";
                        echo ($after -$before) . " sec/serialize\n";
                    }

Endret 8. mai 2014 av r2d290

[etse]

Noe av problemet med løsningen du kjører nå er at du for hver spørring må parse strengen på nytt. Det å parse en slik streng er ofte en meget tidkrevende jobb, og det ville derfor kanskje gitt deg stor gevinst ved å bruke prepared statements, slik at den trenger kun å parse spørringen 1 gang, for så å kjøre den 1000 ganger med ulike verdier.

Endret 8. mai 2014 av etse

[r2d290]

Det gir jo litt mening.

 

Har du mulighet til å gi et raskt kodeeksempel på hvordan jeg kan teste dette?

Endret 8. mai 2014 av r2d290

[Karl Skapeland]

Nei, men vi kan peke deg i riktig retning, slik at du finner ut av det selv:

 

http://www.91weblessons.com/mysql-prepared-statements-with-php/

[quantum]

Ikke vet jeg hvordan php virker - eller ikke virker - men inserter du en rad pr. trans så går det også antagelig langsommere enn nødvendig.

[r2d290]

Takker for svar! Skal se litt mer på dette i morgen =)

[Crowly]

Hvis du leser dataene fra en fil ville jeg sett på LOAD DATA INFILE

 

Alternativt til mysqli som benyttes i linken fra Karl Skapeland, kan man bruke pdo: http://no1.php.net/pdo.prepared-statements

[r2d290]

Takk! Dette er nok noe jeg må se nermere på.

 

Kom ikke så mye lenger med Prepared Statement. Brukte 63 sec på å kjøre 3000 inserts:

 

<?php
if (isset($_POST['submit'])) {
    

$mysqli = new mysqli('localhost', 'root', '123456', 'a_database');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = $mysqli->prepare("INSERT INTO SampleTable VALUES (?)");
$stmt->bind_param('s', $sample);   // bind $sample to the parameter

$before = microtime(true);
for ($i = 0; $i < 3000; $i++)
{
    // escape the POST data for added protection
    $sample = isset($_POST['sample'])
              ? $mysqli->real_escape_string($_POST['sample'])
              : '';

    /* execute prepared statement */
    $stmt->execute();
}
$after = microtime(true);

echo ($after -$before) . " sec/serialize\n";

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* close connection */
$mysqli->close();
}
?>


<form action="sample.php" method="POST">
<input name="sample" type="text">
<input name="submit" type="submit" value="Submit">
</form>

//Time: 63 sec

Endret 9. mai 2014 av r2d290

[Crowly]

Testet skriptet ditt nå med 5000 rader, det tok 1,2181968689 sekunder. Så hvis du bruker 63 sekunder på 3000 rader så er det mulig det er noen andre faktorer som påvirker ytelsen negativt på ditt system.

Endret 9. mai 2014 av Crowly

[r2d290]

Altså 5000 i for-løkka der jeg har 3000?

 

Hmm, kjører dette på en RaspberryPi, er nok kanskje ikke den raskeste prosessoren på den. Med slike tall kan det fort være mulig å få til 63000 rader på en rimelig tid også.

 

Takk!

Endret 9. mai 2014 av r2d290

[etse]

Ikke bare er prosessoren på en RaspberryPi treg, men den har og vesentlig tregere Disk-IO som også blir en stor flaskehals her. (siden du skal skrive til disk).

[r2d290]

Ahh, jeg forstår. Hadde bare ikke sett for meg at det var SÅ stor forskjell.

 

Planen er at verdiene som leses av fra databasen (63000 ord) blir sendt over SPI på RPI.

Deretter skal RPi motta 63000 ord og lagre disse til database.

 

Er det da nærliggende å tro at dette vil gå raskere ved å flytte databasen til ekstern PC?

[r2d290]

Mulig jeg spør dumt nå, men... prøvde å kjøre samme koden på en annen PC jeg har LAMP på. Begge med en løkke på 30.

 

Hva kan det ha seg at på RPi-en blir det lagt inn 30 oppføringer, mens på den andre pc-en blir det bare lagt inn 1 oppføring for hver gang? :S

Har det noe med mangel på HTML5 eller noe å gjøre?

Endret 9. mai 2014 av r2d290

[Crowly]

Koden skal kun legge inn en og en rad. Mysql støtter å legge inn flere rader med en insert, opp til 50 har jeg testet meg frem til

INSERT INTO tabell (felt1,felt2)
VALUES (1,'Test 1'),
(2,'Test 2'),
<rad 3-49>,
(50,'Test 50');

Det er vesentlig raskere enn enkelt rader Endret 9. mai 2014 av Crowly

[r2d290]

Det er jo greit å Være klar over

 

Du sier koden kun skal legge inn en og en rad. Men siden jeg har en for-løkke rundt den Bør den jo legge inn en og en rad 30 ganger.

 

Og det får jeg i databasen på rpi-en. Men den andre pcen sin database legger kun inn en linje.

 

Virker som at for-løkka ikke blir registrert

 

Edit: Never mind. Ser at den nyere versjonen av MySQL automatisk fjerner duplikater.

Endret 9. mai 2014 av r2d290

[r2d290]

21 sekunder 63000 linjer. Fremgang jo!

[etse]

MySQL skal kun fjerne duplikater om enkelte felt er satt som unique (eller primary key, som også er unique) og du får duplikat av denne. Dette vil som oftest også gi deg feilmeldinger.

[r2d290]

Ja, mulig jeg feilaktig satt den til "primary" :/

 

Men men. Løst seg nå =)