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
