[Forum] Its good iddea use memcached?

[gnrx]

Hello and Happy new Year.
My host contact with me for differents terms (one of them, they want me to migrate to a higher plan because the one I have now is a legacy plan).
I've been looking at the possibility of using memcached, and after reading a lot, I'm not sure if it's a good idea or not.
At the moment, I do not use any cache system.
You advise me to use memcached?
Thanks and best regards.

[Wayne Luke]

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.

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.

[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!

[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]

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:

ALTER TABLE my_table ENGINE = InnoDB;

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.

[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.

[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]

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]

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 ENGINE = InnoDB;
exit

[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 ENGINE = InnoDB;
exit

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.

[gnrx]

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

Regards!

[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.

[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]

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.

[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.