Thanks for your informative reply. I tried some of your suggestions, but am not yet able to solve the problem.

First I should mention that this is an example I made by simplifying a much larger API I have been developing, so there are reasons for doing some things despite there being no reason in this example.

Also, I realize there are things I can do to get faster insertion performance (such as using MySQL's LOAD mechanism), but I want the flexibility of making a perl module to interact with this database. I should change my original "as fast as possible" to "as fast as I can do it with perl DBI", which should be plenty fast if the speed doesn't decrease exponentially!

(1.) Did that. I was fooling around with AutoCommit and forgot to turn it to 0 as I intend.

(2.) I tried changing it to INSERT ... VALUES. Same behavior.

(3.) You mean that I have 3 keys on COND_dummy, despite SHOW INDEXES saying I only have the two that I defined? Also, I am relying on the foreign key checks to ensure integrity of the relationship, so I think I should keep it. I tried to use SET FOREIGN_KEY_CHECKS=0 and the behavior was the same.

(4.) The values I will really store will be FLOAT. (These are measurements)

(5.) Not sure what you mean by this. Could you explain a bit more?

The most interesting thing I got from your post is that A) DBD::mysql wasn't doing real binding and B) It will soon. I'm going to try the beta software and see if the server-prepared statements make the difference.

The real question is whether that will make the difference. The java example I tried (made by a colleague helping me with this problem) used J/Connector 3.0.11... maybe it has "real" prepared statements...

Here is a changed section of my code

# insert and write performance
print "Begin insertions...\n\n";
my $iov_seed = time;
my $logic_id = 9999;
                                                                      
+          
my $count=0;
my $MAXCOUNT = 1000;
my $total=0;
my $start = gettimeofday;
my $commit_cnt = 0;
my $commit_size = 100;
                                                                      
+          
$sql = qq[ SELECT logic_id, since, till FROM $cond_name
             WHERE logic_id = ?
             AND ((since >= ? AND since < ?)
             OR (till  >  ? AND till  < ?)
             OR (? >= since AND ? < till))
             LIMIT 1 ];
my $overlap_check_h = $dbh->prepare_cached($sql);
                                                                      
+          
# $sql = qq[INSERT INTO $cond_name SET logic_id = ?,
#                                    since = ?,
#                                    till = ?,
#                                    value = ?];
$sql = qq[ INSERT INTO $cond_name VALUES (?, ?, ?, ?) ];
my $insert_h = $dbh->prepare_cached($sql);
                                                                      
+          
$dbh->do( qq[ SET FOREIGN_KEY_CHECKS=0 ] );
for (0..10000) {
  my ($t1, $t2) = make_iov();
  $overlap_check_h->bind_param(1, $logic_id, SQL_INTEGER);
  $overlap_check_h->bind_param(2, $t1, SQL_DATETIME);
  $overlap_check_h->bind_param(3, $t2, SQL_DATETIME);
  $overlap_check_h->bind_param(4, $t1, SQL_DATETIME);
  $overlap_check_h->bind_param(5, $t2, SQL_DATETIME);
  $overlap_check_h->bind_param(6, $t1, SQL_DATETIME);
  $overlap_check_h->bind_param(7, $t2, SQL_DATETIME);
  $overlap_check_h->execute();
  my @overlap = $overlap_check_h->fetchrow_array();
  if (@overlap) { print "OVERLAP!  That's impossible!\n"; }
  else {
    $insert_h->bind_param(1, $logic_id, SQL_INTEGER);
    $insert_h->bind_param(2, $t1, SQL_DATETIME);
    $insert_h->bind_param(3, $t2, SQL_DATETIME);
    $insert_h->bind_param(4, 3.1415, SQL_FLOAT);
    $insert_h->execute();
    $count++;
    $commit_cnt++;
    if ($commit_cnt == $commit_size) {
      $dbh->commit();
      $commit_cnt = 0;
    }
    if ($count == $MAXCOUNT) { report(); }
  }
}
$dbh->do( qq[ SET FOREIGN_KEY_CHECKS=1 ] );
$dbh->commit();
$dbh->disconnect();

[download]

and the results of these changes...

[insert]         [date time]   [ins/s]
     1000 2005-03-08 16:02:23  1053.265
     2000 2005-03-08 16:02:25   555.494
     3000 2005-03-08 16:02:27   374.252
     4000 2005-03-08 16:02:31   280.796
     5000 2005-03-08 16:02:35   221.845
     6000 2005-03-08 16:02:41   189.839
     7000 2005-03-08 16:02:47   162.636
     8000 2005-03-08 16:02:54   142.633
     9000 2005-03-08 16:03:02   126.241
    10000 2005-03-08 16:03:10   114.398

[download]

... and here is the performance of the java test which accomplishes the same. This is my desired level of performance which I think should be attainable with DBI...

0: 587.5440658049354 ins/s
1000: 1117.31843575419 ins/s
2000: 1658.374792703151 ins/s
3000: 1968.503937007874 ins/s
4000: 1968.503937007874 ins/s
5000: 2150.537634408602 ins/s
6000: 2212.3893805309735 ins/s
7000: 2262.443438914027 ins/s
8000: 2188.1838074398247 ins/s
9000: 2066.115702479339 ins/s
10000: 2314.814814814815 ins/s

[download]

Thanks again for your help.

-e2ka

Update

Just installed the DBD::mysql 2.9015_1 and re-ran the test. Same behavior

[#insert]         [date time]   [ins/s]
     1000 2005-03-08 16:32:06  1109.196
     2000 2005-03-08 16:32:08   568.175
     3000 2005-03-08 16:32:11   373.548
     4000 2005-03-08 16:32:14   284.840
     5000 2005-03-08 16:32:19   227.884
     6000 2005-03-08 16:32:24   189.410
     7000 2005-03-08 16:32:31   154.263
     8000 2005-03-08 16:32:38   137.563
     9000 2005-03-08 16:32:47   110.940
    10000 2005-03-08 16:32:59    82.182

[download]

Assuming all I need to do is install it and add mysql_server_prepare=1 to my connect string.

Update 2

I should also mention that I've tried setting the DBI_TRACE and it looks like everything is being executed correctly and in the right order.

In reply to Re^2: DBI performance problem with MySQL by e2ka
in thread DBI performance problem with MySQL by e2ka

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.