Professional Web Applications Themes

Why is my MySQL database so slow - MySQL

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 CHT=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, ...

  1. #1

    Default 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 CHT=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


    Stefan Mueller Guest

  2. #2

    Default Re: Why is my MySQL database so slow

    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


    Markus Popp Guest

  3. #3

    Default Re: Why is my MySQL database so slow

    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


    Markus Popp Guest

  4. #4

    Default Re: Why is my MySQL database so slow

    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:

    [url]http://dev.mysql.com/doc/refman/5.0/en/load-data.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: Why is my MySQL database so slow

    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 CHT=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
    >
    >
    Dikkie Dik Guest

  6. #6

    Default Re: Why is my MySQL database so slow


    "Markus Popp" <mfpgmx.li> wrote in message
    news:438e4ce6$0$27019$91cee783newsreader02.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 = "xx";
    $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 CHT = 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>


    Stefan Mueller Guest

  7. #7

    Default Re: Why is my MySQL database so slow

    > 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 Guest

  8. #8

    Default Re: Why is my MySQL database so slow


    "Markus Popp" <mfpgmx.li> wrote in message
    news:438f0aa4$0$20762$91cee783newsreader01.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



    Stefan Mueller Guest

  9. #9

    Default Re: Why is my MySQL database so slow


    "Dikkie Dik" <nospamnospam.org> wrote in message
    news:dmm8le$h1m$1news.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
    [url]http://www.randddecisionsupportsolutions.com/[/url]
    Healthy Living Through Informed Decision Making


    Ted Byers Guest

  10. #10

    Default Re: Why is my MySQL database so slow

    On Thu, 1 Dec 2005 15:24:52 +0100, "Stefan Mueller"
    <seekware-remove-> 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)
    Kees Nuyt Guest

  11. #11

    Default Re: Why is my MySQL database so slow

    Another specific suggestion I can offer is to turn off autocommit in
    your PHP script.

    With InnoDB tables (which you are using), transactions have some
    overhead, and when using autocommit you are starting and committing a
    new transaction for each of the 2000 INSERT statements. It would be
    better to disable autocommit, do your INSERT statements, and then commit
    the whole batch with mysqli_commit().

    See:
    [url]http://us2.php.net/manual/en/function.mysqli-autocommit.php[/url]
    [url]http://us2.php.net/manual/en/function.mysqli-commit.php[/url]

    Try it out and let us know if it improves your script's performance.

    Regards,
    Bill K.
    Bill Karwin Guest

  12. #12

    Default Re: Why is my MySQL database so slow

    > Do you think the problem could be that I'm using mysqli instead of mysql.

    I don't think so - I also tested it with mysqli and generally, mysqli should
    rather be faster than mysql.

    The script is more or less alright (OK, there are some things that could be
    done, as I already listed them up: transaction, extended inserts, prepared
    statements a.s.o.).

    Generally, using a transaction is a very good choice. I once tried to insert
    100,000 test records into a table (using a stored procedure in MySQL 5) -
    using a transaction reduced the process time from over 3 minutes to a little
    more than 20 seconds. That would probably be what could help you most to
    raise performence from the programming perspective (Kees Nuyt shows you how
    it works ;-)). LOAD DATA INFILE would be an even better choice, as it's the
    fastest way at all how to insert records.

    So far, so good - but that's no explaination, why your script works SO
    extremely slow. Are there any other activities on the server while you do
    the inserts (other clients that connect and do different things or so)? Have
    you changed the configuration of the MySQL server? I have used the default
    configuration (using MySQL 5.0.15 on Windows XP) for testing the script.

    Markus


    Markus Popp Guest

  13. #13

    Default Re: Why is my MySQL database so slow

    Stefan Mueller wrote:
    > Do you think the problem could be that I'm using mysqli instead of mysql.
    > I have MySQL 4.1.11-nt.
    Question: is your MySQL data directory on a drive that is physically
    attached to the host computer, or are you trying to store the data on a
    mapped drive?

    Regards,
    Bill K.
    Bill Karwin Guest

  14. #14

    Default Re: Why is my MySQL database so slow

    > With InnoDB tables (which you are using), transactions have some overhead,
    > and when using autocommit you are starting and committing a new
    > transaction for each of the 2000 INSERT statements. It would be better to
    > disable autocommit, do your INSERT statements, and then commit the whole
    > batch with mysqli_commit().
    >
    > See:
    > [url]http://us2.php.net/manual/en/function.mysqli-autocommit.php[/url]
    > [url]http://us2.php.net/manual/en/function.mysqli-commit.php[/url]
    >
    > Try it out and let us know if it improves your script's performance.
    Hello guys, you are great!
    Now it takes less than 1 second to add 2000 entries to my MySQL database. I
    just had to add mysqli_autocommit and mysqli_commit.

    mysqli_autocommit($link, false);

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

    mysqli_commit($link);

    My questions are now
    1) why do other people not need these two commands?
    2) can I configure this behavior in a configuration file?
    3) do I need to use these two commands each time I add an entry to the
    database or only if I add several entries?

    Many thanks to all of you
    Stefan


    Stefan Mueller Guest

  15. #15

    Default Re: Why is my MySQL database so slow

    > Question: is your MySQL data directory on a drive that is physically
    > attached to the host computer, or are you trying to store the data on a
    > mapped drive?
    It's physically attached and while entering the entries its LED is red (very
    busy) the whole time.
    However, I got a solution. Please see my post (01.12.2005 23:12).

    Stefan


    Stefan Mueller Guest

  16. #16

    Default Re: Why is my MySQL database so slow

    Stefan Mueller wrote:
    > My questions are now
    > 1) why do other people not need these two commands?
    They _do_ need those commands, if they run a lot of SQL operations in
    one PHP request. Or if they need to be able to be explicit about
    rollback in case of errors, etc.

    Transactions are usually intended to provide atomicity over several
    operations; a group of SQL commands either succeeds, or else we want all
    of them to be "undone". But the side-effect of transactions is that
    they are expensive to start and to commit.
    > 2) can I configure this behavior in a configuration file?
    I can't find any docs on the mysqli package about a configuration file
    or otherwise gives you the ability to set a site-wide default for
    autocommit = false. But I don't know for certain that it's not there.

    I suppose you could hack the PHP code for mysqli and change the default
    for autocommit. But then you'd need to remember to explicitly use
    mysqli_commit(), or else the changes done by your SQL operations would
    never be saved. So either way, you'll need to remember to do extra code.
    > 3) do I need to use these two commands each time I add an entry to the
    > database or only if I add several entries?
    The autocommit = false should persist for the duration of the current
    connection to the database. So after you do mysqli_connect(), do
    mysqli_autocommit(false) and that should last through to the end of your
    PHP script.

    If you only need to do one entry with SQL, it's not necessary to disable
    autocommit. All autocommit does is issue a commit after each SQL
    operation, and starts a new transaction. If you only do need to do one
    SQL operation, then it makes no difference. At that point, it's up to
    you; I recommend at least coding it consistently in all your PHP code,
    or else it'll be confusing upon first glance whether transactions are
    being used implicitly or explicitly in one of your PHP scripts versus
    another.

    I don't have much experience with PHP or the mysqli package for PHP.
    I'm just going by general knowledge of MySQL, databases, and
    transactions in application code.

    Regards,
    Bill K.
    Bill Karwin Guest

  17. #17

    Default Re: Why is my MySQL database so slow

    Many thanks for your help and all your explanations.
    Stefan


    Stefan Mueller Guest

  18. #18

    Default Re: Why is my MySQL database so slow

    Stefan Mueller wrote:
    >>With InnoDB tables (which you are using), transactions have some overhead,
    >>and when using autocommit you are starting and committing a new
    >>transaction for each of the 2000 INSERT statements. It would be better to
    >>disable autocommit, do your INSERT statements, and then commit the whole
    >>batch with mysqli_commit().
    >>
    >>See:
    >>[url]http://us2.php.net/manual/en/function.mysqli-autocommit.php[/url]
    >>[url]http://us2.php.net/manual/en/function.mysqli-commit.php[/url]
    >>
    >>Try it out and let us know if it improves your script's performance.
    >
    >
    > Hello guys, you are great!
    > Now it takes less than 1 second to add 2000 entries to my MySQL database. I
    > just had to add mysqli_autocommit and mysqli_commit.
    >
    > mysqli_autocommit($link, false);
    >
    > for ($i = 1; $i <= 2000; $i++) {
    > echo $i . "<br>";
    > mysqli_query($link, "INSERT INTO anmeldungscode (key_anmeldungscode,
    > code) VALUES ($i, 'test')");
    > }
    >
    > mysqli_commit($link);
    >
    The last line is superfluous, unless PHP is even weirder than I think.
    > My questions are now
    > 1) why do other people not need these two commands?
    Because autocommit is the(ir) default?

    BTW: Did you consider reading the MySQL doentation at dev.mysql.com/doc?
    Christian Kirsch Guest

  19. #19

    Default Re: Why is my MySQL database so slow

    Christian Kirsch wrote:
    > Stefan Mueller wrote:
    >>Hello guys, you are great!
    >>Now it takes less than 1 second to add 2000 entries to my MySQL database. I
    >>just had to add mysqli_autocommit and mysqli_commit.
    >>
    >> mysqli_autocommit($link, false);
    >>
    >> for ($i = 1; $i <= 2000; $i++) {
    >> echo $i . "<br>";
    >> mysqli_query($link, "INSERT INTO anmeldungscode (key_anmeldungscode,
    >>code) VALUES ($i, 'test')");
    >> }
    >>
    >> mysqli_commit($link);
    >>
    >
    > The last line is superfluous, unless PHP is even weirder than I think.
    I don't think so.
    mysqli_autocommit($link,false);
    switches off MySQLs autocommit (START TRANSACTION).
    Without a COMMIT aka mysqli_commit($link) the data would be dropped at
    the end of the current connection.

    Greetings
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

  20. #20

    Default Re: Why is my MySQL database so slow

    Stefan,

    please post your my.cnf and the printout of:

    SHOW INNODB STATUS\G

    during the very slow insert operation.

    A commit cannot explain a delay of several seconds unless there is an
    extremely high concurrent disk workload on your computer. What does the Task
    Manager say about the CPU usage?

    Best regards,

    Heikki

    Oracle Corp./Innobase Oy
    InnoDB - transactions, row level locking, and foreign keys for MySQL

    InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
    tables
    [url]http://www.innodb.com/order.php[/url]


    "Stefan Mueller" <seekware-remove-> kirjoitti
    viestissä:dmo5tr$1b21$1news.imp.ch...
    > Many thanks for your help and all your explanations.
    > Stefan
    >
    >

    Heikki Tuuri Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. MySQL Database not retrieving the full database
    By geetha.veeraiah@gmail.com in forum MySQL
    Replies: 4
    Last Post: July 21st, 09:34 PM
  2. Help! MySQL Connector/J too slow for production use!
    By shirai in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 13th, 01:24 PM
  3. slow ASP-SQL database connection
    By John Davis in forum ASP Database
    Replies: 10
    Last Post: September 23rd, 06:22 AM
  4. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM
  5. Database is extremely slow
    By Scott Ashby in forum Oracle Server
    Replies: 4
    Last Post: June 26th, 06:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139