Re: AW: limited threads to two but 25 waiting?!

[Gleb Paharenko]

Hello.


Very often InnoDB provides a better performance in
environment where a lot of concurrent SELECTs and
UPDATEs. Check if the problem still exists if you change
the engine of you tables from MyISAM to InnoDB.


M.E. Koch wrote:
> Hello:
> 
> After haveing a look at the `show processlist`
> I realized that querys will be locked if there
> is an update or insert statement otherwise
> the `STATUS` is as regular "copying to temp",
> "sorting" etc. So that not all querys will be
> locked automatically. (But when checking there
> where lots of insert intos so that every query
> seemd to be locked)
> 
> some select querys
> 
> `SELECT kk2.k_id FROM katalog_katalog kk
> LEFT JOIN katalog_katalog kk1 ON k_id=kk1.kat_id
> LEFT JOIN katalog_katalog kk2 ON kk1.k_kat_id=kk2.kat_id
> WHERE kk.kat_id=34678 AND kk1.k_kategorie_id=56
> AND kk2.k_kategorie_id=24;
> 
> 
> An insert into/update looks like this:
> 
> INSERT INTO katalog
> SET kategorie_id=36, titel="foo foo bar",
> artikel="bar bar bar foo"
> 
> The Update gets an additonal `WHERE id=1234`
> 
> about the create statements:
> Two of many tables hopefully give an insight
> //----- snip
> 
> CREATE TABLE `katalog` (
>   `id` int(11) NOT NULL auto_increment,
>   `kategorie_id` int(11) NOT NULL default '0',
>   `datum` datetime NOT NULL default '0000-00-00 00:00:00',
>   `titel` varchar(200) collate latin1_german2_ci default NULL,
>   `untertitel` text collate latin1_german2_ci,
>   `einleitung` text collate latin1_german2_ci,
>   `artikel` text collate latin1_german2_ci,
>   `quelle` text collate latin1_german2_ci,
>   `bild_id` int(11) default NULL,
>   `txt1` text collate latin1_german2_ci,
>   `txt2` text collate latin1_german2_ci,
>   `txt3` text collate latin1_german2_ci,
>   `txt4` text collate latin1_german2_ci,
>   PRIMARY KEY  (`id`),
>   KEY `kategorie_id_idx` (`kategorie_id`),
>   KEY `datum_idx` (`datum`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1
> ;
> 
> and
> 
> 
> CREATE TABLE `katalog_katalog` (
>   `kat_id` int(11) NOT NULL default '0',
>   `kategorie_id` int(11) NOT NULL default '0',
>   `k_id` int(11) NOT NULL default '0',
>   `k_kategorie_id` int(11) NOT NULL default '0',
>   KEY `kat_id_idx` (`kat_id`),
>   KEY `kategorie_id_idx` (`kategorie_id`),
>   KEY `k_id_idx` (`k_id`),
>   KEY `k_kategorie_id_idx` (`k_kategorie_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
> PACK_KEYS=1;
> 
> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com