[geeklog-devel] MySQL issue / table lockups

[Tony Bibbs]

Any reason why we don't ship new GL installs with option of Innodb or 
MyISAM with a default of Innodb?  Or to be even slicker, once we know 
what version of MySQL supported Innodb we can query it before creating 
any new tables and figure it out on the fly.

--Tony

Dirk Haun wrote:
> Here's some other issue altogether, slighlty edited from IRC:
> 
> <mvonahn> I have noticed that the hit couter can lock the whole stories table
> <mvonahn> when viewing the story
> <mvonahn> what happens when you have large number of stories and someone
> performs a search
> <mvonahn> is that update gets locked
> <mvonahn> then everything behind the update gets locked
> <mvonahn> so, basically a long search on an active site can lock the
> whole site
> <mvonahn> it can be solved in 2 ways
> <mvonahn> changing the stories table type to innodb
> <mvonahn> or, what I have been doing, splitting the table
> <mvonahn> a stories and a stories_stat
> <mvonahn> the stories table is myisam so I an use fulltext indexing and
> the stat table is innodb to prevent locks
> <dhaun>   I see - innodb is supported in MySQL since when?
> <mvonahn> late 3
> <mvonahn> I ubderstand the problem of supporting install on ISP's with
> old setups
> <dhaun>   mysql.com isn't very clear on the version number for innodb
> support. it mentions 3.23.34a but the wording indicates it may not have
> been availabe in default installs right away ...
> <dhaun>   we have user with versions down to 3.22.something :-/
> <mvonahn> that may be, I seem to remember having to dl the max install to
> get it originally
> <mvonahn> or something like that
> <mvonahn> yeah, that is unfortunate
> <mvonahn> maybe an install option for those that know they have innodb
> installed
> <mvonahn> to set table types on appropriate tables
> <dhaun>   but maybe putting all the stats into a separate table would be
> a way around it - those that run into the problem could then change the
> type of the table
> <mvonahn> exactly
> <mvonahn> shouldn't affect anything
> <mvonahn> coul dbe posted as a note for performance on large systems
> <dhaun>   k, stuff to think about for 1.3.10 :P
> <mvonahn> so far that is the only scalability issue I have come across
> 
> Comments?
> 
> bye, Dirk
> 
>