Why is my MySQL database so slow

I've created my first MySQL table 'mytable' with two fields (key_mytable,
code):
CREATE TABLE `mytable` (
`key_mytable` int(10) unsigned NOT NULL default '0',
`code` varchar(12) collate latin1_german1_ci default NULL,
PRIMARY KEY (`key_mytable`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
COMMENT='mytable';

With the following PHP script I'd like to add 2000 entries.
<?php
...
\\ Delete all entries
$table = "mytable";
$query = "TRUNCATE " . $table;
mysqli_query($link, $query);

\\ Add entries
for ($i = 1; $i <= 2000; $i++) {
$table = "mytable";
$query = "INSERT INTO " . $table . " (key_mytable, code) VALUES ('" .
$i . "', 'test')";
mysqli_query($link, $query);
}
...
?>

But this is very very slow.
After 30 seconds (after around 600 entries) PHP stops and shows the
following error message:
[01-Dec-2005 01:33:41] PHP Fatal error: Maximum execution time of 30
seconds exceeded in D:\PHP\MyScript.php on line 35

What's wrong?
Stefan

Hi,

two things come to my mind spontanously that should improve performence,
which are a) use a transaction for the INSERT statements - that prevents
MySQL from flushing the index for every row (instead, MySQL only does it
once when you commit the transaction) and b) prepare the statement, because
it only requires to transfer the actual data for every row and not the whole
SQL command.

One further thing that should improve the performence slightly would be to
put the $table = "mytable"; line outside the for loop (as it doesn't change
within the loop, it works as well to declare it outside, so this line of
code runs only once instead of 2,000 times). Finally, if you use MySQL 5,
the best solution would be to insert the rows in a Stored Procedure (this
would require only one single roundtrip to the database server, which should
give performance a boost).

However, 600 rows in 30 seconds still looks very slow - maybe there are
other factors involved which are outside the scope of programming and
database design.

Markus

There's one more idea that certainly speeds up the inserts dramatically -
you could change the PHP script to produce extended inserts, like this:

INSERT INTO " . $table . " (key_mytable, code) VALUES (1, 'table'), (2,
'table'), (3, 'table'), ...;

But you have to take care that the query doesn't get longer as
max_allowed_packets allows (which is quite long, indeed - but maybe too long
to insert 2,000 rows at once).

Markus

Stefan Mueller wrote:
> With the following PHP script I'd like to add 2000 entries.

You should try using LOAD DATA INFILE instead of INSERT, when you're
bulk-loading lots of data.

Also read MySQL docs pages such as:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html
http://dev.mysql.com/doc/refman/5.0/...ert-speed.html
http://dev.mysql.com/doc/refman/5.0/...db-tuning.html

Regards,
Bill K.

Speaking about test: are you running the database, the webserver and the
client on the same machine? In that case, you machine may be busy
emulating network traffic AND performing the task.

Best regards

Stefan Mueller wrote:
> I've created my first MySQL table 'mytable' with two fields (key_mytable,
> code):
> CREATE TABLE `mytable` (
> `key_mytable` int(10) unsigned NOT NULL default '0',
> `code` varchar(12) collate latin1_german1_ci default NULL,
> PRIMARY KEY (`key_mytable`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
> COMMENT='mytable';
>
> With the following PHP script I'd like to add 2000 entries.
> <?php
> ...
> \\ Delete all entries
> $table = "mytable";
> $query = "TRUNCATE " . $table;
> mysqli_query($link, $query);
>
> \\ Add entries
> for ($i = 1; $i <= 2000; $i++) {
> $table = "mytable";
> $query = "INSERT INTO " . $table . " (key_mytable, code) VALUES ('" .
> $i . "', 'test')";
> mysqli_query($link, $query);
> }
> ...
> ?>
>
> But this is very very slow.
> After 30 seconds (after around 600 entries) PHP stops and shows the
> following error message:
> [01-Dec-2005 01:33:41] PHP Fatal error: Maximum execution time of 30
> seconds exceeded in D:\PHP\MyScript.php on line 35
>
> What's wrong?
> Stefan
>
>

"Markus Popp" <mfp@gmx.li> wrote in message
news:438e4ce6$0$27019$91cee783@newsreader02.highwa y.telekom.at...
> There's one more idea that certainly speeds up the inserts dramatically -
> you could change the PHP script to produce extended inserts, like this:
>
> INSERT INTO " . $table . " (key_mytable, code) VALUES (1, 'table'), (2,
> 'table'), (3, 'table'), ...;
>
> But you have to take care that the query doesn't get longer as
> max_allowed_packets allows (which is quite long, indeed - but maybe too
> long
> to insert 2,000 rows at once).
>
> Markus
>
>

Of course, I can optimize it but it's soooo slow so that I think I do have a
general problem.
Within 30 seconds I can only enter around 600 entries before it stops
because of a timeout (PHP Fatal error: Maximum execution time of 30 seconds
exceeded in D:\PHP\MyScript.php on line 35).

Is there's something wrong with my script or do I have a problem with the
configuration of my MySQL server?
Stefan


Here is the whole script:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?php

// Create database
$db_host = "localhost";
$db_username = "root";
$db_password = "xxxxxxxx";
$db_name = "silvesterlauf";

// Connect to database
$link = mysqli_connect($db_host, $db_username, $db_password) or
die("MySQL access failed!<br>");
mysqli_select_db($link, $db_name) or die("Access to database '" .
$db_name . "' failed! - Error: " . mysqli_error($link) . "<br>");

// Delete table
$table = "anmeldungscode";
$query = "DROP TABLE IF EXISTS " . $table;
mysqli_query($link, $query) or die("Deleting table '" . $table . "'
failed! - Error: " . mysqli_error($link) . "<br>");

// Create table
$table = "anmeldungscode";
$query = "CREATE TABLE IF NOT EXISTS " . $table . " (";
$query .= "key_anmeldungscode int(10) unsigned NOT NULL
auto_increment,";
$query .= "code varchar(12) collate latin1_german1_ci default NULL,";
$query .= "PRIMARY KEY (key_anmeldungscode)";
$query .= ") ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE =
latin1_german1_ci COMMENT = 'Anmeldungscode' AUTO_INCREMENT = 1";
mysqli_query($link, $query) or die("Creating table '" . $table . "'
failed! - Error: " . mysqli_error($link) . "<br>");

for ($i = 1; $i <= 2000; $i++) {
echo $i . "<br>";
mysqli_query($link, "INSERT INTO anmeldungscode (key_anmeldungscode,
code) VALUES ($i, 'test')");
}
?>
</body>
</html>

> Is there's something wrong with my script or do I have a problem with the
> configuration of my MySQL server?

The table and the code can't be the problem. I've tested your script and it
took 4 seconds until all 2,000 rows were inserted.

It could eventually be a configuration issue.

Markus

"Markus Popp" <mfp@gmx.li> wrote in message
news:438f0aa4$0$20762$91cee783@newsreader01.highwa y.telekom.at...
>> Is there's something wrong with my script or do I have a problem with the
>> configuration of my MySQL server?
>
> The table and the code can't be the problem. I've tested your script and
> it
> took 4 seconds until all 2,000 rows were inserted.
>
> It could eventually be a configuration issue.
>
> Markus

Thanks for testing.
Do you think the problem could be that I'm using mysqli instead of mysql.
I have MySQL 4.1.11-nt.

Should I try to use mysql instead of mysqli?
Stefan

"Dikkie Dik" <nospam@nospam.org> wrote in message
news:dmm8le$h1m$1@news.cistron.nl...
> Speaking about test: are you running the database, the webserver and the
> client on the same machine? In that case, you machine may be busy
> emulating network traffic AND performing the task.
>
I don't know about Stefan, but some developer's generally do this because
they don't have a budget adequate to have a dedicated machine for each
server they need to use. Others do this because they're part of a general
purpose network and can't take the chance of bogging down the network, and
thus impacting colleagues, while they're developing and testing code.

I run all the servers I need to work with on my development machine, and I
generally see response times from them that compare favourably relative to
any website I browse to during what little leasure time I may have. But
then, I generally insist on my development machine being the fastest I can
afford, loaded with loads of memory and hard disk space. That said, years
ago, I was doing the same thing on a mid range P-III and it handled the load
reasonably well. I am sure that once I deploy, all will run a little
faster, but then there is the network bottleneck to worry about.

In my experience, running several servers on an appropriate development
machine should not have the impact Stefan reported. Give the other posts,
esp. those testing his script (i.e. using it to insert 2000 records in 4
seconds using his script), either he has a configuration issue or his machne
is too slow for the job. I have seen nothing in this thread about the
machines used for the work. If the job is complete in 4 seconds on a dual
processor machine using dual core Athlon64 processors, and that machine is
fully loaded with RAM, and Stephan is using an ancient P-III with only a
little RAM and hard disk space, that difference in hardware might explain
the difference: all the hardware avaailable when the first P-III machines
came out were much much slower than that which can be obtained today. It is
impossible to say anything about why the script seems to be so slow without
knowing what hardware is being used and what else the system is doing,
except that the tests already reported in this thread exclude the script as
a culprit.

Alas, I can't offer additional insight into the cause of Stefan's problems
because I spend most of my time developing code, not administering servers.
I do not have the experience administering servers required to guess at how
a mistake configuring a server could cause Stefan's problems. I have
learned, the hard way and quite recently, how a mistake in programming
strategy can turn a job that should take minutes into a job that takes more
than a day, if it finishes at all. But Stefan's problem does not seem to be
a programming problem.

Cheers,

Ted

--
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Solutions
http://www.randddecisionsupportsolutions.com/
Healthy Living Through Informed Decision Making

On Thu, 1 Dec 2005 15:24:52 +0100, "Stefan Mueller"
<seekware-remove-@yahoo.com> wrote:

>Of course, I can optimize it but it's soooo slow so that I think I do have a
>general problem.
>Within 30 seconds I can only enter around 600 entries before it stops
>because of a timeout (PHP Fatal error: Maximum execution time of 30 seconds
>exceeded in D:\PHP\MyScript.php on line 35).
>
>Is there's something wrong with my script or do I have a problem with the
>configuration of my MySQL server?
>Stefan

You really have to try 'BEGIN' before loading and 'COMMIT'
after. If you insert many rows and/or have much data per row,
you should commit and begin again every o many rows, I used 1000
in the code below. InnoDB will perform much better then.
When creating your database, be sure it isn't too small, and
start on a freshly defragmented disk.

$query = 'BEGIN';
mysqli_query($link, $query) or die(" ....
for ($i = 1; $i <= 2000; $i++) {
if (($i modulo 1000) == 0){
$query = 'COMMIT';
mysqli_query($link, $query) or die(" ....
$query = 'BEGIN';
mysqli_query($link, $query) or die(" ....
}
mysqli_query($link, "INSERT INTO ...
}
$query = 'COMMIT';
mysqli_query($link, $query) or die(" ....

I hope this helps,
--
( Kees
)
c[_] Always draw your curves, then plot the data. (#129)