← Back to team overview

maria-developers team mailing list archive

Re: an observation


Hi Sergei,
thnkas for your reply.
In my tests I found that the first table-scan takes about 12 seconds,
the second table-scan takes about 25 seconds (and all folowing) scans
When I call the function reinit_io_cache() all scans take about 12
seonds. But my implementation was quick and dirty....
I found the reason for this behaviour in the function  _mi_read_cache()
 where the first if()-statement was false in the case of the first scan
and true for all other scans.My conclusion was that the IO_CACHE-struct
ure had to be reset when the second scan begins. And this seems to
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