maria-developers team mailing list archive

[AugustQ <augustq@xxxxxxx>]

Hi Sergei,
coming back to my observation.
With the text-file I already sent you in another thread here you can
also try to reproduce the effect I described here.
Here is how:
- modify the INSERT INTO TestBig-statement so that it will include much
more records into the table
- execute the statement and check the code I mentioned
Please keep in mind that the statement here is a different one (not the
same statement as in the other thread).
And please keep in mind that the effect only happens when the data-file 
for the table TestBig has a size >128K. You can simply double the lines
with the INSERT-statement (and double again as the current statement
creates a file of approx. 880 bytes in size only). You can also execute
the statement again and again as there are no constraints on the table.
hope that helps.
AugustQ
Am Montag, den 30.01.2017, 12:27 +0100 schrieb Sergei Golubchik:
> Hi, AugustQ!
> 
> On Jan 29, AugustQ wrote:
> > 
> > Hi,
> > 
> > by playing with the code I think I found something interesting.
> > 
> > My environment: MariaDB 10.0.10, MyISAM-engine
> > 
> > I played with a table-scan, no index is defined on this table. When
> > I
> > execute a SQL-statement that forces the server to do a second
> > table-
> > scan on a table this 2nd table-scan will be slow. 
> > 
> > The reason for this behaviour is the usage of a buffer: during the
> > 1st
> > scan this buffer is filled, used and filled again until the whole
> > table is processed. At the end of the 1st scan it contains the last
> > bytes of the file. When a 2nd scan is started the reading of the
> > table
> > starts from the beginning of the file but the buffer and all
> > associated variables are not reset:  the buffer still contains the
> > bytes from the end of the file, the request cannot be fulfilled by
> > the
> > buffer so the request has to be handled by reading the bytes
> > directly
> > from the file  using the read()-function of the Std-library. This
> > takes much more time then simply copying the bytes from the
> > internal
> > buffer.
> Right... MyISAM does not know how you're going to access the table.
> It
> might be a second full table scan. Or may be you'll just want to read
> the end of the table?
> 
> > 
> > My idea is: somewhere in the code this situation must be detected
> > and
> > the buffer (and all associated variables) reset to initial
> > values. reinit_io_cache() looks like the right candidate for this.
> How would that help? You'll get faster execution if MyISAM would
> preload
> first pages of the table. But it doesn't know you're going to do a
> full
> table scan, so why would it preload it?
> 
> Regards,
> Sergei
> Chief Architect MariaDB
> and security@xxxxxxxxxxx

Attachment: signature.asc
Description: This is a digitally signed message part