[geeklog-devel] MySQL issue / table lockups

[Dirk Haun]

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


-- 
http://www.haun-online.de/
http://geeklog.info/