Professional Web Applications Themes

Implementing an history with a limited number of entries with MySQL - MySQL

Hello All, I'm searching for the faster way (performance-wise) to create the following structure with mysql: I've a table where I take an history of things that happen in a system. This table contains just some integer and a creation timestamp in UNIX time Every time there is a new event to add to the history I just INSERT it The problem is that I want this history to contain at MAX 10000 elements. Older elements should be removed in some way. What's the faster way to take the history limited in length? This is my best try so far. ...

  1. #1

    Default Implementing an history with a limited number of entries with MySQL

    Hello All,

    I'm searching for the faster way (performance-wise) to create the
    following structure with mysql:

    I've a table where I take an history of things that happen in a
    system.
    This table contains just some integer and a creation timestamp in UNIX
    time
    Every time there is a new event to add to the history I just INSERT it

    The problem is that I want this history to contain at MAX 10000
    elements.
    Older elements should be removed in some way.

    What's the faster way to take the history limited in length?

    This is my best try so far.

    I take two max-length instead of just 10000, that I call low and high
    here where:

    high = 12000
    low = 10000

    high is a tollerance value, so I can do the following.

    - every time I've to insert a new element:
    - if the current history length > high
    - row = SELECT time FROM history ORDER BY time DESC LIMIT low,1
    - timelimt = row.time
    - DELETE FROM history WHERE time >= timelimit

    With the high/low trick I can do this queries just 1 time every 2000
    new history rows added,
    still I need all this to be very fast once it needs to be done.

    Of course time is INDEXed.

    Are there better ways to do this stuff?

    Regards,
    Salvatore

    Salvatore Guest

  2. #2

    Default Re: Implementing an history with a limited number of entries with MySQL

    If I were doing it, I would write a script on the server that would
    run at 2am (or whenever) to check the database and remove history.
    Unless it's a priority to have history removed constantly on the go,
    this is the best way to ensure it can be done properly and off peak
    hours. I run several scripts under cronjobs to manipulate the database
    at night.

    Nino


    On May 11, 4:28 pm, Salvatore Sanfilippo <com> wrote: 


    nino9stars@yahoo.com Guest

  3. #3

    Default Re: Implementing an history with a limited number of entries with MySQL

    On May 12, 1:53 am, "com" <com>
    wrote: 

    Hello Nino, Thanks for the suggestion, I also use to run scripts via
    cron, or even better to have sort of daemons in separated processes
    running DB operations incrementally. Here the problem is a bit harder
    since I've multiple histories (one for customer) and there are no
    "idle"
    periods on the server since we are serving customers in different
    timezones.

    Probably the best solution from the point of view of performances is
    to switch from DB to plain files with fixed record length implementing
    a circular buffer, but I could like to take the flexibility of the DB.

    Regards,
    Salvatore

    Salvatore Guest

  4. #4

    Default Re: Implementing an history with a limited number of entries with MySQL

    Salvatore Sanfilippo wrote: 

    Here's another way to think about it. Once you have 10000 rows, then every
    time you insert a new row, you can just delete the oldest one.
    So, if you're using MyISAM, you can check the result of SHOW TABLE STATUS
    and if it is 10000 then insert your row and delete the oldest one.


    Paul Guest

  5. #5

    Default Re: Implementing an history with a limited number of entries with MySQL

    On May 12, 10:32 am, "Paul Lautman" <com>
    wrote: 













    >
    > Here's another way to think about it. Once you have 10000 rows, then every
    > time you insert a new row, you can just delete the oldest one.
    > So, if you're using MyISAM, you can check the result of SHOW TABLE STATUS
    > and if it is 10000 then insert your row and delete the oldest one.[/ref]


    Or build a table with 10000 rows and then just UPDATE the oldest one
    each time

    strawberry Guest

  6. #6

    Default Re: Implementing an history with a limited number of entries withMySQL

    Salvatore Sanfilippo wrote: 
    >
    > Hello Nino, Thanks for the suggestion, I also use to run scripts via
    > cron, or even better to have sort of daemons in separated processes
    > running DB operations incrementally. Here the problem is a bit harder
    > since I've multiple histories (one for customer) and there are no
    > "idle"
    > periods on the server since we are serving customers in different
    > timezones.
    >
    > Probably the best solution from the point of view of performances is
    > to switch from DB to plain files with fixed record length implementing
    > a circular buffer, but I could like to take the flexibility of the DB.
    >
    > Regards,
    > Salvatore
    >[/ref]

    Why would that be the "best solution". In fact, if your file gets
    large, it will probably not perform as well as a database - unless you
    add your own indicies and other performance improvements.

    And you don't need "idle times" to run these queries. None of them are
    going to take hours to execute.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Implementing an history with a limited number of entries with MySQL

    On May 12, 4:24 pm, Jerry Stuckle <net> wrote:
     [/ref]
     

    Hello Jerry,

    thank you for your reply, the fact that a file-based schema is so fast
    for this application is because you can prebuild a fixed record
    length table of N elements, and take as first 4 bytes of the table
    a 32bit counter that points to the next record to write. The counter
    gets incremented every time a new record is instered.

    This means O(1) insert, with minimal constant times, and the
    query I need to run against this is just "find all the records with
    ID > X", this is trivial as I just need to check the current pointer,
    subtract X from this number, seek(2) (the actual POSIX syscall)
    and read fixed length records.

    Again O(1) seek + O(N) (with N being the length of the records to
    read) to read all the rest.

    If it is so good, why I'm posting questions about SQL you may
    wonder... because if I can find a solution with SQL I can, for
    instance,
    backup the DB and have everyting saved, from the users, to the
    records,
    and so on.

    Also this is not the first application I develop. I know that things
    tend
    to become more complex, so I bet in some week, month or year
    I'll need to perform more complex queries against this records.
     

    Sure but my requirements is that the DELETE query I need will never
    take more than 200 milliseconds in the worst case.
    For now I've 100 records for user and this is working very well.
    When I'll switch to 10000 records for user I hope it will continue
    to work well.

    About this, I wonder if it's better to create a tabl for every
    customer
    or instead, (like I'm doing currently), to have a csutomer_id in the
    table. The latter solution is more handy for me as I can do for
    example SELECT DISTINCT(customer_id) if I want to see in
    real time the active customers, but if a table for every customer
    will perform better in pratice I'll switch to this model.

    Thanks again,
    Salvatore

    Salvatore Guest

  8. #8

    Default Re: Implementing an history with a limited number of entries with MySQL

    On May 12, 11:32 am, "Paul Lautman" <com>
    wrote:
     

    Hello Paul,

    yes this can work as far as there is a way to make "delete the oldest
    one"
    very fast. AFAIK the faster I can get is:

    row = SELECT MIN(time) FROM history WHERE customer_id=$mycustomer
    DELETE FROM row WHERE time=row[0];

    I wonder if there are faster ways.

    Regards,
    Salvatore

    Salvatore Guest

  9. #9

    Default Re: Implementing an history with a limited number of entries with MySQL

    Salvatore Sanfilippo wrote: 
    >
    > Hello Paul,
    >
    > yes this can work as far as there is a way to make "delete the oldest
    > one"
    > very fast. AFAIK the faster I can get is:
    >
    > row = SELECT MIN(time) FROM history WHERE customer_id=$mycustomer
    > DELETE FROM row WHERE time=row[0];
    >
    > I wonder if there are faster ways.
    >
    > Regards,
    > Salvatore[/ref]

    I find the "Strawberry Query" for finding the row with the minimum value
    (LEFT (self)JOIN and NULL test), many times faster than the MIN() approach.


    Paul Guest

  10. #10

    Default Re: Implementing an history with a limited number of entries with MySQL

    Salvatore Sanfilippo wrote: 
    >
    > Hello Paul,
    >
    > yes this can work as far as there is a way to make "delete the oldest
    > one"
    > very fast. AFAIK the faster I can get is:
    >
    > row = SELECT MIN(time) FROM history WHERE customer_id=$mycustomer
    > DELETE FROM row WHERE time=row[0];
    >
    > I wonder if there are faster ways.
    >
    > Regards,
    > Salvatore[/ref]

    Indeed, you should be able to do this in a single query thus:
    DELETE
    FROM `h1`
    USING `history` `h1`
    LEFT JOIN `history` `h2` ON `h1`.`time` > `h2`.`time`
    WHERE `h2`.`time` IS NULL


    Paul Guest

  11. #11

    Default Re: Implementing an history with a limited number of entries with MySQL

    On 11 May 2007 16:28:21 -0700, Salvatore Sanfilippo
    <com> wrote:
     

    REPLACE the oldest one ?
    Keep a variable n somewhere with the ID of the last changed, and
    UPDATE the id n+1 (mod 10000) ?
    subtenante Guest

  12. #12

    Default Re: Implementing an history with a limited number of entries withMySQL

    subtenante wrote: 
    >
    > REPLACE the oldest one ?
    > Keep a variable n somewhere with the ID of the last changed, and
    > UPDATE the id n+1 (mod 10000) ?[/ref]

    Or keep a timestamp field and replace the oldest one.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Limited number of instert records
    By btn in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 17th, 10:37 PM
  2. Number of WebMethods limited in .asmx file?
    By Scott in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 1st, 07:56 PM
  3. is the number of variables limited?
    By Michael_Stoll@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: June 8th, 07:36 PM
  4. Limited the number of decimal places returned?
    By Clive Sweeting in forum PHP Development
    Replies: 2
    Last Post: December 13th, 09:16 PM

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