Re: R2 and MySql

> -----Original Message-----
> From: bitweaver-core-bounceslists.sourceforge.net
[mailto:bitweaver-
> core-bounceslists.sourceforge.net] On Behalf Of James
H Thompson
> Sent: 27 November 2007 02:46
> To: bitweaver-corelists.sourceforge.net
> Subject: Re: [bitweaver-core] R2 and MySql
> 
> I use myisam for most tables in BW and innodb for a few
selected
> tables.
> I've had no problems with foreign keys.
> 

I think it all depends on what function the foreign keys
have, from the
Mysql 5.0 docs:
" In MySQL Server 3.23.44 and up, the InnoDB  storage
engine supports
checking of foreign key constraints, including CASCADE, ON
DELETE, and ON
UPDATE. See Section 13.2.6.4, "FOREIGN KEY
Constraints".

For storage engines other than InnoDB, MySQL Server parses
the FOREIGN KEY
syntax in CREATE TABLE statements, but does not use or store
it. In the
future, the implementation will be extended to store this
information in the
table specification file so that it may be retrieved by
mysqldump and ODBC.
At a later stage, foreign key constraints will be
implemented for MyISAM
tables as well."

So what I mean is that the problem may not manifest in a
"visible" maner,
that's why I ask as I still have been too short time with BW
to grasp the
whole code.

Btw, in which way do you use InnoDB for some and MyIsam for
other tables? As
far as I can see BW in current state doesn't create InnoDB
tables unless
they are default on the server, but then install fail,
probably due some
still is created as MyIsam but I haven't explored if that's
the case.

yettyn

> 
> ----- Original Message -----
> From: "Yettyn" <yettynastrocalc.com>
> To: <bitweaver-corelists.sourceforge.net>
> Sent: Monday, November 26, 2007 2:19 PM
> Subject: Re: [bitweaver-core] R2 and MySql
> 
> 
> >
> >
> >> -----Original Message-----
> >> From: bitweaver-core-bounceslists.sourceforge.net
> [mailto:bitweaver-
> >> core-bounceslists.sourceforge.net] On
Behalf Of Christian Fowler
> >> Sent: 26 November 2007 19:26
> >> To: bitweaver-corelists.sourceforge.net
> >> Subject: Re: [bitweaver-core] R2 and MySql
> >>
> >>
> >> While supporting exclusively InnoDB is a great
idea, there are many
> >> (most)
> >> shared hosting environments that do not offer
InnoDB. In fact, we
> are
> >> lucky to get MySQL 4.1+ these days, which
gives us subselects (which
> we
> >
> > I was just thinking of one thing, BW make use of
foreign keys, these
> are
> > only supported by InnoDB with mysql, so how will
this effect things
> if
> > MyIsam is used/supported? Will they just be
ignored and without any
> side
> > effects?
> >
> > yettyn
> >
> >
> >
> >
------------------------------------------------------------
---------
> ----
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio
2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > bitweaver-core mailing list
> > bitweaver-corelists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/bitweave
r-core
> 
>
------------------------------------------------------------
-----------
> --
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> bitweaver-core mailing list
> bitweaver-corelists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/bitweave
r-core


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
bitweaver-core mailing list
bitweaver-corelists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bitweave
r-core

> Btw, in which way do you use InnoDB for some and MyIsam
for other tables? 
> As
> far as I can see BW in current state doesn't create
InnoDB tables unless
> they are default on the server, but then install fail,
probably due some
> still is created as MyIsam but I haven't explored if
that's the case.

I was running a BW install with all Myisam tables. I was
having performance 
problems
so I went into the DB and changed some tables to innoDB.  I
did this because 
although Myisam is usually faster than innodb, it locks the
entire table when doing updates. So for tables with lots of
updates, myisam 
really slows down.
In my case it was the table with the hit counter that was
slowing down the 
entire system.
Changing this table to innodb so that it uses row level
locking for updates 
dramatically speeded up BW.

The hit counter was moved into a separate table for R2 so it
will be less of 
a problem, but I suspect that
having to do an update of the hit counter for every page
read is probably 
not the best update strategy for really high
performance.  Some kind of periodic batch updates to the
statistics would 
probably inprove performance.









------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
bitweaver-core mailing list
bitweaver-corelists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bitweave
r-core