Re: Table as fifo

[Zsolt Kendi]

First of all I know now where the problem but I do not know why this is a problem.

The "fifo purger" at bb table has two condition to purge.
The 1st is limit the size of the data table on disk. This is the DELETE FROM bb ORDER BY C4 ASC LIMIT n query explained before.

The 2nd is purge all record older than one month. This is never true (never purge record) in our test. So this is why I cannot explained you before. The query of is DELETE FROM bb WHERE C4!=0 AND DATEDIFF('[timeisnowinstring]',C4) > 30 ORDER BY C4 ASC LIMIT 50 .
Now I sure the qurey above take away 40% CPU even if no row matched.
If I replace to DELETE FROM bb WHERE C4!=0 AND C4<'[timeisbeforeonmonthstring]' ORDER BY C4 ASC LIMIT 50 then the mysqld require 40% less CPU!
Looks like if the number of records are >0.2M then using of DATEDIFF is not too efficient!?


--------------------------------------------

RJ"key_len = 5 --> C4 is NULL; did you mean to have it NULL? Usually people have NOT NULL on indexed fields. "
KZS"key = C2, key_len = 5"
Did you mean C2? This column has not NULL value if query with SELECT...

"With your key_buffer on disk all indexed operations are running at disk speed! "
Not all. See "Special index rules" topic.
But the insert/delete sure not. This is wh we use ramdisk (speed and performance requirement) .

"Even if you have the indexes on ramdisk, you are burning a lot of CPU cycles to get at them."
According our tests does not matter the key_buffer active or not take a same resources (except more ram if active) if the database files located in ramdisk.