List:Internals« Previous MessageNext Message »
From:Benjamin Pflugmann Date:August 30 2001 12:10am
Subject:Re: adding INSERT support to MERGE tables
View as plain text  
Hi!

Sorry that it was quite quiet around me, but last week took completely
another path as I had planned. But now I am back to the matter.

On Fri, Aug 17, 2001 at 03:50:16PM +0300, monty@stripped wrote:
> >>>>> "Sergei" == Sergei Golubchik <serg@stripped> writes:
> 
> Sergei> Hi!
> Sergei> On Aug 17, Benjamin Pflugmann wrote:
> >> Hi.
> >> 
> >> I am seriously considering to add INSERT support to MERGE tables
> >> (MyISAM) in 3.23.x. I have spent some hours reading the source and
> >> now, before I start to put more effort into it, I wanted to know
> >> whether it would make sense as contribution, in order to know if I do
> >> this as a hack or a configurable mechanism.
> 
> Sergei> Good!

I am still missing one point. I learned that this would only make it
into 4.x. Since currently I am only making changes to 3.23.x, I wanted
to know whether the official tree could benefit from such patches,
i.e. is 4.x similar enough to use these patches? If not, I am probably
not going to implement the configuration mechanism for 3.23.x, because
in my private version fixed constant would be sufficient.

[...]
> Sergei> Well, I can forget about some black magic of locking here -
> Sergei> Monty will correct me if I did.
> 
> The problem with locks is that if, you don't take care of it properly,
> you would lock all tables in the MERGE table with a WRITE lock, which
> isn't that good.

Surely, my first intended approach was this way. I will only have a
MERGE table over two tables and about 10% inserts and can live with
the speed loss.

> The way to fix this is to ensure that when you do an insert,
> ha_myisammrg::lock_count() would return 1 and
> ha_myisammrg::store_lock() would only stores the table you are going
> to modify in the lock array.

This will be my next plan, then. :-) 

[...]
On Mon, Aug 20, 2001 at 03:11:56PM +0300, monty@stripped wrote:
>
> >>>>> "Sergei" == Sergei Golubchik <serg@stripped> writes:
> 
> Sergei> Hi!
> Sergei> On Aug 18, Michael Widenius wrote:
> >> 
> Sergei> Also, INSERT_METHOD should also go to MRG file, for isam layer to know
> it.
> >> 
> >> Why?
> >> It's should be enough for MySQL to know about this.
> 
> Sergei> It CAN be done that way - that is to pass INSERT_METHOD as an argument
> Sergei> to mrg_write().
> 
> Sergei> But to be consistent it should be stored on isam layer - it's isam
> information,
> Sergei> and MySQL shouldn't ever worry about it.
> 
> It's actually MYISAM-MERGE information and not MyISAM. 
> 
> If we define the INSERT to be a dynamic property that is always to be
> sent to mrg_write(), then it's a MySQL problem.

(I did not find any mrg_write() - I assume you mean myrg_write()?)

I had expected this info to be kept in the same place as the info
which tables are in the union, i.e. it would be passed to myrg_write()
as an attribute of the struct MYRG_INFO.

Is there any problem with this idea?

This would mean that myrg_open() would have to read it somewhere and
store it in the struct.

> Sergei> Also in ROUND_ROBIN it has somehow to preserve the last-target-table
> number
> Sergei> on shutdown. This will change MRG files from simple ``ls *.MYI >
> table.MRG''
> Sergei> to something more complex. In ROUND_ROBIN they also won't be read-only.
> >> 
> >> Sorry, but I didn't get that.
> 
> Sergei> Well, suppose you have three tables in MERGE, and
> INSERT_METHOD=ROUND_ROBIN
> Sergei> First insert goes to the first table, the second one - to the second.
> Sergei> Now one does ``mysql.server stop; mysql.server start'' - or simply mysqld
> Sergei> dies and safe_mysqld restarts it. I would prefer the first insert after
> restart
> Sergei> to go to the third table.
> 
> I don't think that Benjamin is planing a round_robin.

In fact, I had mentioned it, because someone else once mentioned need
for it in a discussion about MERGE tables. 

> I actually don't think this would be very useful extension anyway.
> (One can't even do this to speed up inserts as the table locks on
> the MERGE file would interfere with this).
> If one needs things like this, then it's better to use a hardware
> raid.

I agree.

I need this INSERT mechanism to hide from a (hard to change)
application the fact that I am splitting the tables. Splitting the
tables will enable me to only backup the "insert"-table, while just
keeping the old backups for the (never changing) other tables and it
will look like one table for the application.

So I need only insert into first/last table.

Bye,

	Benjamin.

Thread
adding INSERT support to MERGE tablesBenjamin Pflugmann17 Aug
  • Re: adding INSERT support to MERGE tablesSergei Golubchik17 Aug
    • Re: adding INSERT support to MERGE tablesMichael Widenius17 Aug
      • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
        • Re: adding INSERT support to MERGE tablesSergei Golubchik30 Aug
          • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
        • Re: adding INSERT support to MERGE tablesMichael Widenius30 Aug
          • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
            • Re: adding INSERT support to MERGE tablesMichael Widenius1 Sep
              • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann5 Sep
                • Re: adding INSERT support to MERGE tablesMichael Widenius6 Sep
                  • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann10 Sep
                    • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann11 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius12 Sep
                        • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann12 Sep
                          • Re: adding INSERT support to MERGE tablesMichael Widenius13 Sep
                            • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann18 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius22 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius24 Sep
                        • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann27 Sep
    • Re: adding INSERT support to MERGE tablesSergei Golubchik17 Aug
      • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann17 Aug
      • Re: adding INSERT support to MERGE tablesMichael Widenius18 Aug
        • Re: adding INSERT support to MERGE tablesSergei Golubchik18 Aug
          • Re: adding INSERT support to MERGE tablesMichael Widenius20 Aug
RE: adding INSERT support to MERGE tablesThe Tilghman20 Aug