Hallo Winfred,
If your database supports InnoDB then this is the only thing you have to do.
I assume that switching to innodb will not help in your lock issue, but
you could give it a try. How locks are treated depend more on the
isolation level of your connection. I never investigated issue with
mmbase and mysql lock so I don't have a lot of suggestions on that.
Nico
Winfred Peereboom wrote:
Thanks for your reply Nico,
I worked out a couple of things and hope to see (in the near future)
the some results. i added some indexes and changed some related nodes
tags as you explain.
For setting the mm_images and mm_icaches table to innodb, are there
any other changes for this to make it work. Or just give the alter
command ?
One other question is, how do you think of changing the bp_pos (or
other relation table) to innodb, because when our editor changes
something the table will be locked. Other query's have to wait before
he finished updating. Or is this something i need to fix somewhere
else so the table would not be used.
Met vriendelijke groeten,
Winfred Peereboom
Van: Nico Klasens <[EMAIL PROTECTED]>
Aan: Discussion list for developers <developers@lists.mmbase.org>
Datum: 02-07-2008 10:24
Onderwerp: Re: [Developers] Performance tuning, slow query's
------------------------------------------------------------------------
Hallo Winfred,
For all our mysql installations we do the following. Binary fields are
slow when you do a orderby or table scan on a MyIsam table. The full
record is read with an MyIsam which kills your perfromance when you have
MBs stored in a column. A MyIsam table is more suitable when you do full
text searches.
alter table mm_attachments ENGINE = INNODB;
alter table mm_images ENGINE = INNODB;
alter table mm_icaches ENGINE = INNODB;
Very important too are the indexes on the relation tables (insrel,
posrel, *rel, etc). This is how a "show create table mm_insrel;" should
look like
| mm_insrel | CREATE TABLE `mm_insrel` (
`number` int(11) NOT NULL default '0',
`otype` int(11) NOT NULL default '0',
`owner` varchar(12) NOT NULL default '',
`snumber` int(11) NOT NULL default '0',
`dnumber` int(11) NOT NULL default '0',
`rnumber` int(11) NOT NULL default '0',
`dir` int(11) default NULL,
PRIMARY KEY (`number`),
KEY `otype` (`otype`),
KEY `snumber` (`snumber`),
KEY `dnumber` (`dnumber`),
KEY `rnumber` (`rnumber`),
KEY `mm_insrel_relation_idx` (`snumber`,`dnumber`,`rnumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
When the mmbase application is written well then the index
"mm_insrel_relation_idx" is always used. You only get this when the
application is very specific what it wants.
<mm:relatednodes type="attachments" role="posrel" orderby="posrel.pos"
searchdir="destination"> will use this index, because it specifies type,
role and searchdir
A <mm:relatednodes type="attachments"> will give you very bad
performance, because mmbase will not use any of the indexes. The sql
query created will do a where clause with an OR which can never be
resolved with 1 index. Usually the database decides to do a full table
scan (which is not nice when you have 100.000+ relations). Mysql 5
sometimss uses an index merge of snumber and dnumber, but then you are
lucky. A change to <mm:relatednodes type="attachments"
searchdir="destination"> will remove the OR part and will give you some
benefit of an index.
Another thing which kills performance is the usage of "single node in
list retrieval". Below is one of the many examples which can trigger this
<mm:list path="sometype" fields="sometype.number" max="100"> <%-- list
query to the database --%>
<mm:field name="sometype.number" id="sometypeNumber" />
<mm:node number="${sometypeNumber}"> <%--query to the database which
retrieves one record 'where number = 1234' --%>
</mm:node>
</mm:list>
This example will do 101 queries on the database This is done in
sequence, because it is only one thread. This will give slow response
times no matter how fast your server is I have seen mmbase sites doing
2000+ queries in sequences on a database for one request, The customer
was complaining that their fast machine was serving pages very slow :)
Just add this to the log4j.xml and see the horror on your site :)
<logger name="org.mmbase.storage.search" additivity="false">
<level class="&mmlevel;" value="debug" />
</logger>
Nico
Winfred Peereboom wrote:
>
> We are running mmbase 1.8 and i am busy with some performance tuning.
> When i look to the different query's that are produced i notice that
> there are not very efficient (example not using joins but where
> statement to join). Besides that I see in my mysql proces list that an
> update query on the bp_pos table while lock most of all other query's
> . The tables are MyIsam. My question now is if someone has some
> experience to change tables to innodb. And if he/she knows if i need
> to change anything in mmbase conf.
>
> Thanks for any anwer
>
> Met vriendelijke groeten,
>
> winfred Peereboom
> VNUMedia
>
>
>
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Developers mailing list
>Developers@lists.mmbase.org
>http://lists.mmbase.org/mailman/listinfo/developers
>
>
_______________________________________________
Developers mailing list
Developers@lists.mmbase.org
http://lists.mmbase.org/mailman/listinfo/developers
------------------------------------------------------------------------
_______________________________________________
Developers mailing list
Developers@lists.mmbase.org
http://lists.mmbase.org/mailman/listinfo/developers
_______________________________________________
Developers mailing list
Developers@lists.mmbase.org
http://lists.mmbase.org/mailman/listinfo/developers
