Review performance of the site

[numeroteca]

I've tagged it as a bug because the slowness while saving posts, and sometimes 500 Internal Server Error, makes it impossible to work with.

It might be related to WPML string tranlation, automatic ajax saving or other stuff. I've looked at PHP-FPM pool and AMC in the simple hosting and I do not see a clear cause. Maybe to many php-status-www?json&full calls?. Ideas?

[numeroteca]

We should replicate the entire current data base in the dev website and test there which could be the problem. What do you think @skotperez?

Possible reasons:

• Incompatibility of plugins?
• Bad configuration of WPML?
• WPML strings plugin?

[numeroteca]

I've tested deactivating the WPML String Translation plugin and the website crashed a well.

---

Logs when crashing (all plugin activated):

error.log apache:

[Wed Jul 18 11:16:47.650602 2018] [:error] [pid 474:tid 140697482946304] (104)Connection reset by peer: [client 140.143.90.193:64641] FastCGI: failed to read from backend server [Wed Jul 18 11:16:47.720461 2018] [mpm_event:notice] [pid 141:tid 140697991686016] AH00491: caught SIGTERM, shutting down [Wed Jul 18 11:17:14.384064 2018] [mpm_event:notice] [pid 144:tid 140212135335808] AH00489: Apache/2.4.26 (Unix) configured -- resuming normal operations [Wed Jul 18 11:17:14.384209 2018] [core:notice] [pid 144:tid 140212135335808] AH00094: Command line: '/usr/sbin/apache2 -d /srv/data/.config/apache -f apache2.conf' [Wed Jul 18 11:17:16.200329 2018] [mpm_event:notice] [pid 144:tid 140212135335808] AH00493: SIGUSR1 received. Doing graceful restart [Wed Jul 18 11:17:16.213725 2018] [mpm_event:notice] [pid 144:tid 140212135335808] AH00489: Apache/2.4.26 (Unix) configured -- resuming normal operations [Wed Jul 18 11:17:16.213736 2018] [core:notice] [pid 144:tid 140212135335808] AH00094: Command line: '/usr/sbin/apache2 -d /srv/data/.config/apache -f apache2.conf'

www-error.log de PHP:

18-Jul-2018 11:16:43 UTC] PHP Warning: mysqli_query(): (70100/1317): Query execution was interrupted in /srv/data/web/vhosts/globalrec.org/htdocs/wp-includes/wp-db.php on line 1924 [18-Jul-2018 11:16:43 UTC] WordPress database error Query execution was interrupted for query SELECT t.translation_id, t.element_id, t.language_code, t.source_language_code, t.trid, t.element_type FROM wp_icl_translations t JOIN wp_posts p ON t.element_id = p.ID AND t.elementtype = CONCAT('post', p.post_type) JOIN wp_icl_translations tridt ON tridt.element_type = t.element_type AND tridt.trid = t.trid WHERE tridt.element_id IN (9513,9677,9520,9533,9977,9325,9341,9366,9799,9787,9516,9553,9370,9536) made by include('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, initialize_cmb_meta_boxes, require_once('/themes/globalrec/lib/metabox/init.php'), apply_filters('cmb_meta_boxes'), WP_Hook->apply_filters, call_user_func_array, sample_metaboxes, get_posts, WP_Query->query, WP_Query->get_posts, get_option, apply_filters('pre_option_sticky_posts'), WP_Hook->apply_filters, call_user_func_array, SitePress->option_sticky_posts, WPML_Post_Translation->pre_option_sticky_posts_filter, WPML_Element_Translation->prefetch_ids [18-Jul-2018 11:18:26 UTC] PHP Catchable fatal error: Object of class WP_Error could not be converted to string in /srv/data/web/vhosts/globalrec.org/htdocs/wp-content/plugins/sitepress-multilingual-cms/classes/seo/class-wpml-seo-headlangs.php on line 50

--

Here are previous threads in WPML documenting related issues to performance:

• https://wpml.org/forums/topic/slow-performance-in-dashboard/
• https://wpml.org/forums/topic/translation-jobs-tab-is-not-working/

I've seen that in the minimun requirements by WPML they ask for MySQL 5.6 and above and we currently have MySQL 5.5. I've asked Gandi if we can upgrade.

--

I've also enabled debug mode in Wordpress to collect errors.

[numeroteca]

MySQL can be upgraded to 5.7 in gandi, let's see if that helps.

This is how database looks. A million rows in wp_icl_translations!!!

Ups, I discovered and reported this already: https://wpml.org/forums/topic/table-wp_icl_translations-too-large/ but I did not follow through. I'll check the list of requested things.

[numeroteca]

Regarding upgrade to MySQL 5.7: Gandi says that a new instance of simple hosting should be created, they can not upgrade existing database.

Options:

• Migrate to new instance of simple hosting in Gandi and wee what happens.
• Migrate to M34 server (first make test migration to see if problems are the same there)

[skotperez]

I've made some changes in WordPress config (in wp-config):

• i've raised and limited the memory wordpress can use to 128MB. WPML was complaining about this. Now WPML status page says everything is ok.
• i've disabled xml-rpc connection. This is recommended to avoid attacks in WP installations. I've seen that there were several running processes requesting xml-rpc.php file.

Let's see if this improves performance.

[numeroteca]

I've tested editing two posts at the same time, which recently drove it to break it, and it seems that is not happening any more. Things are are slow, but it didn't crash.

Then I tried to edit 3 posts at the same time and it took around a minute (I did notmeasure) to open them. Saving and scheduling them took a long time, but it did not crash. I checked the slow queries after saving: WP_Term_Query->get_terms() and update_meta_cache().

So, I think it has improved the performance, but not yet enough.

"WPML was complaining", how this complain occurs?

[numeroteca]

Two years later the number of rows in wp_icl_translations table has increased only in 4,000 rows. So I think there must be a bunch of not used rows in there.

I've looked into what all those 1,034,335 lines in the wp_icl_translations table are and I see that 1,014,748 (98.1%) are comments!!

When I look at the dashboard I see that there are no that many comments (also not in the trash):

If we analyze by languages the comments in wp_icl_translations table we get:	language	lines
en	757052
es	136580
fr	56269
pt-br	64847

Do we need comments translation? We don't. Hypothesis: we received many spam comments that were removed, but WPML did not remove them.

Actions: We could try to remove all those lines that are, I think, unused. @skotperez? I can set WPML not to translate comments.

[skotperez]

Good point! Let's do it:

1. Make a backup of the data base
2. Remove most of the rows
3. See if something crashes
4. If it does, we'll restore the backup and we'll remove rows in a selective way using a query that exclude published comments ids

[numeroteca]

I'd would go directly with 1. and 4. with a SQL command that removes only "comment" element_type

[skotperez]

I've run this query in dev: DELETE FROM wp_icl_translations where element_type = 'comment'

Result: 1014748 rows affected. (Query took 128.8909 seconds.)