Its good iddea use memcached?

[gnrx]

Like always, thanks Wayne, thank you very much!
A few days have passed since your reply, I was reading the links that you indicate and other types of tests ...
Well, I made a mistake, I just made the server change when I made the switch to Innodb tables, and I suffered a small problem as I mentioned before ...
I thought it was because I had gone to innodb or it was a possibility that barajana, I missed so much because, if you had told me what better in Innodb, that should not be the problem.
And I made a beginner mistake ...
Despite reviewing everything, there was something on the new server that I did not check ...
The routing tables in my new server were not correctly, and although the site worked, sometimes suffered small "problems", to that I added that, when accessing an installed plugin gave me error.
So my previous questions if there was any repair utility, I know that in miysiam, it is not that a database is repaired, it does not, but it was a way to ask if something could have damaged its structure ...
Nothing is further from reality.
Once the routing tables on my server were repaired, everything went back to normal.
And that mod that caused me that error of connection to database, the reason was that, in the configuration of my.cnf should disable "only_full_group_by".
All the problems solved ...

That yes, I detect or give me the impression that, innodb requires more resources, and now, what I have to do, is what you mentioned earlier, or configure the server to give resources resource to mysql or find someone let me do it (if I'm not able to do it).

As always, very grateful!

Edit: I forgot, you told me that there are 3 tables that should remain in myisiam, for some specific reason?
I guess this next question you can not answer me, I have mods installed, I was also thinking about converting your tables to innodb, I guess the answer is to ask the developers

[Wayne Luke]

I can go back to innodb, if you advise using innodb, your best nobody knows what type of tables is the best for vbulletin.

Actually we do know what table engine is best for vBulletin with current versions of MySQL. That is INNODB.

https://www.vbulletin.com/forum/articles/4401683-vbulletin-5-database-best-practices

We've recommended INNODB for almost a decade now -

https://www.vbulletin.com/forum/blogs/ibxanders/3935816-

https://www.vbulletin.com/forum/blogs/ibxanders/3935819-

https://www.vbulletin.com/forum/blogs/ibxanders/3935820-

https://www.vbulletin.com/forum/blogs/ibxanders/3935821-

[Mark.B]

'Repair' on databases doesn't do what you may think it does. It is ONLY for when a table is actually marked as 'crashed', and it will attempt to repair it.
It doesn't work on INNODB tables at all either.

What it WON'T do is speed anything up, restore deleted columns, set tables back to their default state, or anything like.
Repair is a function of MySQL and nothing to do with vBulletin.

[gnrx]

I agree with everything you say, in the fields where I can have knowledge, and in those fields, such as database optimization.
Indeed, perhaps the fact that now the installation of some mod or its uninstallation is delayed, perhaps also happened before but I did not pay attention to it, as you say, it is not something that is done daily.
I can go back to innodb, if you advise using innodb, your best nobody knows what type of tables is the best for vbulletin.
Apart from that, look for help / support to optimize my server for mysql.
But something "happened" when I converted to innodb, from that moment, there is a mod that never worked again, even now it is in MyISAM does not work (and I have uninstalled and reinstalled).

What gives me "fear" is that, some table of users or groups of users has been "touched" hence my query of whether there is any utility vb repair, not optimize server, but repair database, if there was "something" that was "messy" (I hope to explain well).

Like always, thank you.

[Wayne Luke]

Checking / Repairing the database will not speed up the installation of third-party addons. Those addons will run code, modify databases, this takes time. Honestly, this is a road that shouldn't be followed. You're not going to be installing/uninstalling addons on a daily basis so the time they take is not relevant to the overall performance of a server. Many things in the AdminCP will take time. Often this is by design.

For performance reasons, I can only recommend INNODB with the vBulletin database. The main reason for this is how locks are handled. INNODB only locks individual rows. This means multiple users can save a post at the same time. MyISAM has table locking. This means only one person can save a post at the same time. I can't provide any advice on Addons. These are often hacks with and their database structure might not be the best. This will vary from addon to addon though.

My guess on your instabilities is that MySQL is using default configuration options and needs to be optimized for your needs. Web Hosting companies don't do this. A good source of optimization information is the Percona MySQL Blog. Usually, you want to allocate up to 80% of a server's resources to MYSQL. However, every server is going to be different.

Without knowing how your server is configured, we can go round and round on this issue and NEVER resolve it. You need to hire someone to optimize MySQL at the root level of your server.

[gnrx]

Hello (again) Wayne,

I have been innodb these days and the result has not been very good.
I converted all the tables to innodb, and perhaps the stability in the use of cpu was better, but if it requires more consumption of ram and cpu (not much) and disk space.
That's why there is no problem, because I migrated from server to a self managed with more resources and I could see that for resources there was no problem.
I converted the tables to innodb except the ones mentioned in this thread and the tables of different mods that I have.
I waited a few days, but I started to notice instability, some new mod that I wanted to install was not installed, some mod that I wanted to uninstall was not uninstalled either.
To not extend, summarizing; Maybe it was not a good idea to convert all the tables to innodb because I started having problems with the mods, so I converted them back to miysiam, from that moment the problems disappeared, I can install and uninstall mods.
But ... now I notice something that did not happen to me before, when installing a mod or uninstalling it, it takes a lot of time, a lot.
And there is a mod that, although its developer has looked at my site (with access to admincp) tells me that, its like it does not see or read the user and usergroup data.
Looks like I still have some kind of data base problems.

The site works well, cpu loads and memory usage are correct.
Through my WHM I started the database repair process, it ended ok.
But I still have the right problem, and when installing or uninstalling some mod, it takes a lot of time.

Is there a vbulletin tool that checks / repairs the database?

Thanks and regards.

[gnrx]

Thanks Wayne,
I made the change via SSH, all perfect.

Regards!

[gnrx]

Searchcore_text can stay MyISAM. It needs to be that in MySQL 5.6 and lower anyway.

To change it via SSH you would use these commands -

mysql -u %username% -p
use %databasename%;
alter table searchcore_text [I]ENGINE = InnoDB;
exit
[/I]

Thanks,
I have mysql 5.7.

Tomorrow I change it via SSH with the commands you indicate, today not because now, I have a lot users connect and I preferer wait to made the change when I have lower users connect.

Again, thanks.

[Wayne Luke]

Searchcore_text can stay MyISAM. It needs to be that in MySQL 5.6 and lower anyway.

To change it via SSH you would use these commands -

mysql -u %username% -p
use %databasename%;
alter table searchcore_text [I]ENGINE = InnoDB;
exit
[/I]

[gnrx]

Sorry for the dalay in reply, a lot tables for convert

I convert all tables except the language, phrase, and userfield (this in MYISAM) and spsession and session (this in MEMORY).

Only one table I can't, and its: searchcore_text

This table its too bog, and after a some time appears a error message that connection lost (I think its because its to big and made this under phpmyadmin).

The host indicate to me that I need made the change via SSH, but I don't know what command I need in SSH for made this change.

Host not want help me in this.

You know the command? (I search in google but I think I don't search very well).

Other doubt, I have some tables of some mods (vbulletin.org), at the first, I don't convert, I think its better ask to creators of this mods.

Again, thanks and regards.

[Wayne Luke]

Yes. the language, phrase, and userfield tables should remain MYISAM. You can use your query to change the rest of the tables. Whether you upgraded from vBulletin 3 or not isn't relevant.

[gnrx]

Apologies for my "re-question"
Can you confirm the latest? Especially the tables I can convert and the mode for convert.

Thanks and regards.

[gnrx]

Thanks for your reply!
I reply per quotes, for understand all

The tables you have as INNODB wouldn't be in a default installation.

I don't know and/or I don't remeber why I have t his tables in inoodb...

In a default installation of vBulletin 4.2.5 the following tables are always MYISAM.

• blog
• blog_text
• contenttype
• groupmessage
• language
• post
• postedithistory
• searchcore
• searchcore_text
• searchgroup
• searchgroup_text
• searchlog
• socialgroup
• tag
• tagcontent
• thread
• userfield

The following tables use the MEMORY engine

• cpsession
• session

All other tables should be INNODB. If you're using MySQL 5.7 or higher, then you can experiement with converting all tables to INNODB except the following:

• language
• phrase
• userfield

All tables should have a DYNAMIC row format if you're using INNODB and you will need to enable the 'innodb_large_prefix' system variable if you're using a version of MySQL prior to 5.7.7. You will need a minimum version of MySQL 5.5.14.

My forum isn't a default 4.2.5, are from 3.x and updated to 4.2.5

My version of mysql is 5.7.24

If I understand well, I can convert all tables to innodb except:

• language
  phrase
  userfield

Is this correct?
If its yes, the table phrase need MYISAM?

I search info for this change, I view this sql query for made the change:

[I]ALTER TABLE [B]my_table[/B] ENGINE = InnoDB;[/I]

Is this correct also for this?:

All tables should have a DYNAMIC row format if you're using INNODB and you will need to enable the 'innodb_large_prefix' system variable

Thanks and regards.

[Wayne Luke]

The tables you have as INNODB wouldn't be in a default installation.

In a default installation of vBulletin 4.2.5 the following tables are always MYISAM.

• blog
• blog_text
• contenttype
• groupmessage
• language
• post
• postedithistory
• searchcore
• searchcore_text
• searchgroup
• searchgroup_text
• searchlog
• socialgroup
• tag
• tagcontent
• thread
• userfield

The following tables use the MEMORY engine

• cpsession
• session

All other tables should be INNODB. If you're using MySQL 5.7 or higher, then you can experiement with converting all tables to INNODB except the following:

• language
• phrase
• userfield

All tables should have a DYNAMIC row format if you're using INNODB and you will need to enable the 'innodb_large_prefix' system variable if you're using a version of MySQL prior to 5.7.7. You will need a minimum version of MySQL 5.5.14.

[gnrx]

With vBulletin 4, Memcached can provide a benefit but it is relatively small. You can offload your Datastore to Memcached. It won't hurt to try it though. Depending on your server, you might get a better boost using OPCACHE built into PHP.

Thanks Wayne!
Ok, I understand, really, its good iddea install opcache?
I contact with my host, and yes, I can install opcache, but the host indicate that, my problem, is in the querys sql.

Using PHP 7.1, will give you a performance boost "by an order of magnitude". Upgrading to MySQL 5.7 (or even 8) will give you a significant performance boost as well. Making sure your tables are INNODB can also help. If you use MySQL 5.7 or higher, tables with fulltext indexes can be INNODB. With lower versions they should remain MyISAM. MyISAM will slow your site down and increase its required resources.

Yes, I use php 7.1 and mysql 5.7
I contact about this with the host, and indicate that:

Only six tables in the database use the InnoDB engine.
====
# ls -1 /var/lib/mysql/myforum*/*.ibd
/contenttype.ibd
/searchcore.ibd
/searchgroup.ibd
/searchlog.ibd
/tag.ibd
/tagcontent.ibd
====

All other tables use MyISAM.

I can convert all other tables to inoobd?

Thanks and regards!