← Back to team overview

maria-developers team mailing list archive

Re: an observation

 

Hi Sergei,
another tip: instead of increasing the size of the data-file to >128K
you can also decrease the size of the buffer. I did something similar
for a test and (if I remember it correctly) it's done in sys/sysvars.cc
 in the var. Sys_read_buff_size. I've set the entry for DEFAULT to 4K
and played with this.
In this case you will only need a data-file of size >4K.
Hope this helps.
AugustQ
Am Dienstag, den 21.02.2017, 16:47 +0100 schrieb AugustQ:
> 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
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp

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


References