MySQL Bugs: #26137: Record cache flushing is not efficient enough

Bug #26137	Record cache flushing is not efficient enough
Submitted:	7 Feb 2007 8:05	Modified:	7 Feb 2007 11:33
Reporter:	Mikko metsola	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.2.0-alpha-win32	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
I did done some performance tests with Falcon 5.2.0-alpha in comparison with MyISAM 5.1 on Win XP.
The test was simply done by INSERTing rows as fast as possible via ODBC. (Data queries were not done - Falcon has full record cache, MyISAM does not have...)

Falcon was (of course) quicker there, but it looks like record cache flushing to serial logs and database file does not work in a reliable way.
In my "full speed" test, Falcon starts buffering items to memory, and max record cache size seems to have no effect. Meaning it looks like the flushing thread(s) do not get time at all, making all appear like the insertion speed never slows down and memory usage eventually quickly grows up to the computer's physical limit. When the full speed burst is over, data is quickly flushed from the memory, but I am not sure if it is all going to disk...

You can see the excellent full speed rows/s results as an attachment.

When I tried e.g. 200-300 rows per second (as an example let's say 20% of the full speed I was able to use on my machine), record cache size tries to justify its being, mysql server clearly starting to flush data via serial log files to database file.

So my point is that there seems to come a breaking point when data flushing does not get enough time, and Falcon record max size has no meaning at all. Something like: the flush-responsible thread(s) do not have high enough priority or their timers do not work or...
It would be expected that the record cache max size is the dominant factor at all circumstances, and data insert would slow down at the expense of data flushing.

Rgds, MMe

How to repeat:
make small program that inserts data to Falcon engine as fast as possible.

test results

Attachment: falcon_max_record_memory_test.txt (text/plain), 9.10 KiB.

Mikko,

writing the records which are in memory to disk also depends on the transaction. If you did the INSERT test in single transaction, then all records are kept in memory until a COMMIT is done.

Did you run your test with autocommit = 0?

Regards, Hakan

The "upper limit" on the record cache is not actually a limit, but the threshold point for the record scavenger.  Since the scavenger is only activated every 30 seconds (by default), the record cache size will almost always exceed the upper bound.

That said, lets take a moment to review data flow in Falcon:

    1.  Transactional updates are made only to memory
    2.  At commit (and autocommit) time, updates are copied to the serial
        log.  After all data has been written to the serial log, the
        serial log is flushed to disk.
    3.  Post commit, the "gopher" thread copies data from the serial
        log to page images in the page cache.
    4.  Periodically, the check point process writes dirty pages from
        the page cache to disk.

There is not a single thread that moves data from memory to disk.  It is a multi-stage operation.

In the early alpha releases of Falcon, all uncommitted records are retained in memory.  In a future baselevel, uncommitted records and index updates will be flushed to the serial log and subject to record scavenging.  This will be both automatic and transparent.

Hi,

and thanks for your comments.

In my test, I executed a commit after every 2000 rows.

Looking at my test environment (the attached test table with 150 columns and maximum insert speed), what are the tools to make the data transfer from memory to disk more efficient when the upper limit is about to be reached? For example, can I configure the record scavenger to wake up "five times" more often? Together with commiting more often?

Rgds, MMe