List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:May 31 2002 10:12am
Subject:Re: I need 50.000 inserts / second
View as plain text  
Depending on your available RAM & length of your sampling runs, you 
could write records to heap (in-memory) tables -

	http://www.mysql.com/doc/H/E/HEAP.html

- and then dump those to disk after the sample was done. You might 
even be able to use heap tables as a buffer with one process 
acquiring data to the heap table, another writing records to disk & 
deleting from the heap table. Never used heap tables myself, though.

	-steve


At 1:49 AM -0300 5/31/02, Cesar Mello - Axi wrote:
>Hello,
>
>I intend to use MySQL in a data acquisition software. The actual version
>stores the acquired data straight in files. The sample rate can get up to 50
>kHz. I would like to know if there is some way to improve MySQL insert rate.
>The following C++ code with mysql++ takes 5 seconds to execute in my Athlon
>1.33 machine:
>
>sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)
>
>int main() {
>   try { // its in one big try block
>
>     Connection con(use_exceptions);
>     con.connect("cesar");
>     Query query = con.query();
>
>     teste1 row;
>     // create an empty stock object
>
>  for (int i=1;i<50000;i++)
>  {
>   row.datahora = (double) i;
>   row.valor1 = i / 1000;
>
>   query.insert(row);
>   query.execute();
>  }
>
>
>As you can see there are only two fields: a double timestamp and a double
>value. In the real application there are some more double values. I need to
>decrease this time to less than 1 second. Is there any kind of buffered
>inserts or maybe a way that I could pass a matrix?
>
>I'm shocked with the performance of MySQL, a similar query to compute 1
>million records takes 1.17 seconds in MySQL and around 6 seconds in the
>current system. So if I can decrease the insert time I'll definetly use
>MySQL!
>
>Thank you for the attention.
>
>Best regards,
>Cesar
>


-- 
+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| "If only life would imitate toys."                                     |
|                  - Ted Raimi, March 2002                               |
|                  - http://www.whoosh.org/issue67/friends67a.html#raimi |
+------------------------------------------------------------------------+
Thread
I need 50.000 inserts / secondCesar Mello - Axi31 May
  • Re: I need 50.000 inserts / secondJeremy Zawodny31 May
  • Re: I need 50.000 inserts / secondSteve Edberg31 May
  • Re: I need 50.000 inserts / secondHarald Fuchs31 May
  • Re: I need 50.000 inserts / secondMark31 May
    • Re: I need 50.000 inserts / secondBenjamin Pflugmann31 May
    • Re: I need 50.000 inserts / secondDan Nelson31 May
      • Re: I need 50.000 inserts / secondJeremy Zawodny1 Jun
  • Re: I need 50.000 inserts / secondMark31 May
  • Re: I need 50.000 inserts / secondCesar Mello - Axi31 May
    • Re: I need 50.000 inserts / secondmos2 Jun
  • Re: I need 50.000 inserts / secondRichard Clarke2 Jun
RE: I need 50.000 inserts / seconddomi31 May
  • Re: I need 50.000 inserts / secondCesar Mello - Axi31 May
    • Re: I need 50.000 inserts / secondT├Ánu Samuel31 May
    • Re: I need 50.000 inserts / secondBrent Baisley31 May