Is better to convert every table to MyISAM or just some of them ?

[FabioPaz]

Hello,

Searching on the forum I came to the conclusion that InnoDB can boost my mysql performance.

Ok, but I have to convert every table or it's better to convert just some of them and leave the rest as myIsam ?

Another question. I'm considering running MariaDB too. Can I continue to use myphpadmin with Maria, since it's the "sucessor" of mysql or I will have to everything via SSH ?

Thank you and have a nice day!

[Rhett]

You can still use phpmyadmin, on converting to innodb, I would first and look at any issues you are seeing, that may not be the answer depending on many other factors. There are downsides to innodb as well, and one size doesn't fit all in this case.

What troubles are you seeing? are you getting table locks? if so what tables?

[FabioPaz]

You can still use phpmyadmin, on converting to innodb, I would first and look at any issues you are seeing, that may not be the answer depending on many other factors. There are downsides to innodb as well, and one size doesn't fit all in this case.

What troubles are you seeing? are you getting table locks? if so what tables?

Hello,

According to my host, the major performance problem are tables (or queries ?) getting locked.

| 28571 | XXXXX | localhost | XXXX | Query | 0 | Locked | INSERT INTO ibf_content_cache_posts

| 32856 | x | localhost | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |
| 32858 | x | localhost | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |
| 32859 | x | localhost | x | Query | 1 | Locked | SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,ccb.cache_content FROM ibf_members m LEFT JOIN |
| 32860 | x | localhost | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |
| 32861 | x | localhost | x | Query | 1 | Locked | SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.las |

I run the IPB on a dedicated server with 16GB ram, i7-3770 (4x3.4GHz 8MB).

Sphinx + Cloudflare + MaxCDN

Thank you for your attention.

[Rhett]

Can you have them get you the full query, those above are only a partial, often these items can be fixed with a couple adjustments rather then re-working your database

[FabioPaz]

# Query_time: 87.031015 Lock_time: 85.995404 Rows_sent: 0 Rows_examined: 0
SET timestamp=1354478720;
INSERT INTO ibf_content_cache_posts (`cache_content_id`,`cache_content`,`cache_updated`) VALUES(805932,'<blockquote class='ipsBlockquote'data-author="&#39;busarello&#39;" data-cid="805810" data-time="1320659787"><p>Olhem o universo da pesquisa:<br />16 Jovens<br />que nunca treinaram<br />por 15 semanas.<br /><br />S&oacute; eu que achei pouca gente por pouco tempo?<br />Fora o fato de &quot;nunca terem treinado&quot;, que inviabiliza extrapolar o resultado para um universo de pessoas que treinam por anos a fio.<br /></p></blockquote><br />- S&oacute; com esse questionamento j&aacute; se pode jogar o experimento por terra, todos sabemos que existe uma diferen&ccedil;a colossal entre uma pessoa que nunca treinou e uma que treina a algum tempo.',1354478633) ON DUPLICATE KEY UPDATE cache_content_id=VALUES(cache_content_id),cache_content=VALUES(cache_content),cache_updated=VALUES(cache_updated);

# Query_time: 5.394811 Lock_time: 1.234719 Rows_sent: 9 Rows_examined: 7012
SET timestamp=1354441936;
SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title as member_title, m.warn_level, m.warn_lastwarn, m.members_display_name, m.members_seo_name, m.member_banned, m.has_gallery, m.has_blog, m.members_bitoptions,m.mgroup_others,pp.*,w.wl_id,rep_index.rep_rating as has_given_rep,rep_cache.rep_points, rep_cache.rep_like_cache,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ( SELECT pid FROM ibf_posts WHERE topic_id=95946 AND queued=0 ORDER BY pid asc LIMIT 0,20 ) z LEFT JOIN ibf_posts p ON ( p.pid=z.pid )
LEFT JOIN ibf_members m ON ( m.member_id=p.author_id )
LEFT JOIN ibf_profile_portal pp ON ( m.member_id=pp.pp_member_id )
LEFT JOIN ibf_members_warn_logs w ON ( w.wl_content_app='forums' and w.wl_content_id1=p.pid )
LEFT JOIN ibf_reputation_index rep_index ON ( rep_index.app='forums' AND
rep_index.type='pid' AND
rep_index.type_id=p.pid AND
rep_index.member_id=60752 )
LEFT JOIN ibf_reputation_cache rep_cache ON ( rep_cache.app='forums' AND rep_cache.type='pid' AND rep_cache.type_id=p.pid )
LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid )
LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id );

I think those are the full queries from the respectives examples above.

Thank you

[GreenLinks]

What is the amount of rows you have on those tables ? If total number is over 50k then you shall consider converting the table to InnoDB otherwise you can eliminate this issue with MySQL optimisation

[Grumpy]

You can try converting ibf_content_cache_posts to innodb with minimal risk and see if it alleviates the issue.

Be sure to adjust innodb settings too.

[FabioPaz]

You can try converting ibf_content_cache_posts to innodb with minimal risk and see if it alleviates the issue.

Be sure to adjust innodb settings too.

What do you mean about innoDb settings ? I thought I just have to change to InnoDB :rofl:

(my knowledge is very limited in this area).

[GreenLinks]

What do you mean about innoDb settings ? I thought I just have to change to InnoDB :rofl:

(my knowledge is very limited in this area).

innodb has different mysql optimisations , what he is advising you is to

1. Make sure you remove skip-innodb from your my.cnf file to enable InnoDb engine
2. configure your innodb settings through my.cnf

[FabioPaz]

innodb has different mysql optimisations , what he is advising you is to

1. Make sure you remove skip-innodb from your my.cnf file to enable InnoDb engine
2. configure your innodb settings through my.cnf

Great! Thank you

Just one little question: It is better to turn the board off-line (via Admin CP) to convert the tables to innoDB ?

[GreenLinks]

Yes

[xtech]

Don't forget to make a backup dump before proceeding with the conversion.