|
wmnf
|
November 10, 2017 at 5:55 am
We have been seeing a high load on our server and find the wp_options table involved in a slow query in the mysql log…
https://gist.github.com/lorenzocaum/e10ba04bc411428f6db54dcbccb6f9a4
Then I find almst 780K records in that table starting with ‘_transient_timeout_ee_ssn_’. Should these be expirting? I’ve found posts on the WordPress forums regarding _transient_ records and it appears all are safe to delete?
|
|
wmnf
|
November 10, 2017 at 5:58 am
I have now updated to 4.9.50.p from 4.9.46.p.
|
|
Josh
|
November 10, 2017 at 6:57 am
You might find that those will be deleted now that the site is on 4.9.50.p. If they’re still in the database they’re safe to delete.
|
|
wmnf
|
November 10, 2017 at 7:23 am
Thanks, for our records, this was done:
MariaDB [wmnf_www]> DELETE FROMwp_optionsWHEREoption_name` LIKE (‘%\_transient\_timeout\_ee\_ssn\_%’);
Query OK, 806051 rows affected (12.76 sec)`
|
|
wmnf
|
November 10, 2017 at 7:53 am
Also, it was necessary to delete others:
MariaDB [wmnf_www]> DELETE FROMwp_optionsWHEREoption_name` LIKE (‘%\_transient\_ee\_ssn\_%’);
Query OK, 807138 rows affected (14.18 sec)`
And it does not seem to stop, is that normal for there to be a limited amount of these records present?
|
|
Josh
|
November 10, 2017 at 8:04 am
There’s likely a query limit being enforced. Another approach you could take is install this plugin:
https://wordpress.org/plugins/transients-manager/
Then you go to the Tools > Transients manager page and click the Delete Expired Transients button.
|
|
Josh
|
November 10, 2017 at 10:26 am
One other related option that will be good to check is the
ee_transient_schedule option. If that option has a lot of data recorded to it then you can clear that option, which will also speed things up.
|
|
wmnf
|
November 11, 2017 at 7:42 am
Still suffering high loads and CPU usage by MariaDB. I’ve used the Transient Manager to reduce transients down to ~2500 and see lots of these entries in the web server error log:
https://gist.github.com/lorenzocaum/b2ea1b0e23fce634285c2088eeb09022
|
|
wmnf
|
November 11, 2017 at 7:44 am
The ee_transient_schedule option is set to 1 and load average has reduced from ~20 to 7-15 range now that expired transients were removed. I already have 100 more transients in the table a couple of minutes after deletion.
|
|
wmnf
|
November 11, 2017 at 9:59 am
Still having the issue of records growing in the wp_options table even though we are now running Version 4.9.50.p. Now grown from ~2500 to over 8000 since last purge of expired items 2 1/2 hours ago.
|
|
wmnf
|
November 11, 2017 at 10:00 am
That is over 8000 records of just _transient_timeout_ee_ssn_ transient records.
|
|
wmnf
|
November 11, 2017 at 11:10 am
Also, what is the ee_transient_schedule entry in wp_options? Is that related to the web server error above? Here is a list of the autoload records by size and it is the largest by far.
https://gist.github.com/lorenzocaum/36c5b53278d326aad294ef1bc128b9e2
|
|
wmnf
|
November 11, 2017 at 11:39 am
Since this has the name transient in it and there were over 1 million columns in the value, I thought it best to delete and did. Immediately restoring the server to a normalized load. Has this issue been addressed?
|
|
wmnf
|
November 11, 2017 at 12:30 pm
This does not seem to be resolved in the latest version. The ee_transient_schedule option value is already back up to over 80K columns and the number of _transient_timeout_ee_ssn* records keep growing quickly after purging expired transient records.
|
|
wmnf
|
November 12, 2017 at 5:33 am
Woke this morning to server alerts about high load again. Found ee_transient_schedule option value with over 700K characters. As soon as I delete, load normalized.
|
|
DMAClinical
|
November 13, 2017 at 1:36 am
We are having the same issue
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3261 mysql 20 0 1043m 111m 6296 S 132.2 3.7 2:30.24 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=host.*****.com.err –open-files-limit=10000 —
The below queries are causing the issue:
Id User Host db Command Time State Info
10 clinical_maincms localhost clinical_maincms Query 0 Sending data SELECT autoload FROM 8Lb7ng1_options WHERE option_name = ‘_transient_ee_shc_ec03912a517902614e1b5ed6
14 clinical_maincms localhost clinical_maincms Query 0 Sending data SELECT autoload FROM 8Lb7ng1_options WHERE option_name = ‘_transient_ee_shc_e543d536f77b3fa319e7cca2
24 clinical_maincms localhost clinical_maincms Query 0 Sending data SELECT autoload FROM 8Lb7ng1_options WHERE option_name = ‘_transient_ee_ssn_2380923b559c64882883aab1
29 clinical_maincms localhost clinical_maincms Query 0 Sending data SELECT autoload FROM 8Lb7ng1_options WHERE option_name = ‘_transient_ee_ssn_6c4b5146a85d2a73a58d8f46
|
|
wmnf
|
November 13, 2017 at 2:39 am
This issue is causing our wp_options.ibd file to grow rapidly, it is now 35GB as I keep manually deleting transient records from the database. Once this is resolved, I’ll need to run OPTIMIZE TABLE and I’m afraid it will take long and require us to take the website offline to avoid database locks since this is the highly active wp_options table. But we must get this resolved first to keep it from growing again, any ideas how to resolve?
|
|
wmnf
|
November 13, 2017 at 6:05 am
This is a critical problem for our production website, the index file continue to eat all our free disk space as transients records continue to grow by the thousands. Please respond with a solution ASAP!
|
|
Tony
|
November 13, 2017 at 7:29 am
Hi there,
My apologies for the delay, support staff aren’t usually available on the weekends which is why you haven’t had a reply so far.
I’ve created a ticket for some further feedback from our developers on this, but at the rate in which your site is creating transients (~2200 per hour) it sounds like your site is being hit by at least one bot. Do you have any security plugins enabled on the site? You could try adding the Blackhole for Bad Bots plugin (note there is a little more to installing that plugin that just installing it, check the installation tab from above) and see if that helps at all, or check your server access logs and see if you’re being excessively hit by the same bot(s) which you can then block server side by IP.
|
|
wmnf
|
November 13, 2017 at 7:41 am
I’ve already viewed our server access log and watching our Apache web server server-status page all weekend. Nothing excessive with only 25-50% of the connections active at one time. Relatively low load over the weekend as usual. The access_log actually pauses between hits.
|
|
wmnf
|
November 13, 2017 at 7:44 am
I will tell you once the ee_transient_schedule option reaches 1 million characters, error being to appear as noted previously and load causes server to become unresponsive. Also, on the MariaDB, while I intentionally request wp-admin, the only processes on the db are…
https://gist.github.com/lorenzocaum/2b8ffa67fe79f26291d607adce135604
|
|
Tony
|
November 13, 2017 at 8:56 am
Nothing excessive with only 25-50% of the connections active at one time. Relatively low load over the weekend as usual. The access_log actually pauses between hits.
My apologies and maybe I’m misunderstanding, but I’m not sure what the above has to do with bots hitting the sites and causing the transients to be saved?
If all/most of the connections mentioned are bots hitting the Event Espresso ticket selector and submitting the form a transient will be created for each one, if that’s repeatedly happening on your server the transients will be created quicker than they are removed.
So have you checked the access logs to confirm the same IPs are not constantly hitting the site?
|
|
wmnf
|
November 13, 2017 at 9:12 am
Yes I have check the access log and actively watching it, this is how I know it actually pauses between some hits. If I bot was hitting the site that many times, from my experience, the log would be spitting out the same IP constantly. The current Apache stats page shows this below. I don’t see any requests for EE pages, but we are still adding records to the wp_options table as described above. Luckily it seems the index file stopped growing at 39GB a few hours ago, however, high server loads will again occur when the ee_transient_schedule gets above 500K characters. I am actively deleting each hour.
https://gist.github.com/lorenzocaum/517a489391fd0186d16a635653419ce7
|
|
Josh
|
November 13, 2017 at 1:10 pm
I mentioned this before, but it bears repeating: You can empty the
ee_transient_schedule option. Then please consider installing a plugin like Blackhole for bad bots or add some kind of rate limiting to your site. Event Espresso currently does not check whether a site visit it by a bot or a browser user agent before it starts a session.
|
|
wmnf
|
November 13, 2017 at 3:47 pm
Really see no activity on our server to suggest a bot, but okay. Can you tell me when the ee_transient_schedule option clears on its own? It appears to grow until I clear and causes issues once it reaches several 100K characters. It’s still increasing 1K per minute or more and Apache or direct monitoring of the access logs shows no major activity.
|
|
Josh
|
November 13, 2017 at 4:00 pm
You can check the ee_ssn_ transient values themselves, each one will have a user agent. Garbage collection should run every hour, but you can tweak the rate and limit by adding the following to a custom functions plugin:
add_action('plugins_loaded', 'my_custom_transient_cleanup');
function my_custom_transient_cleanup() {
add_filter(
'FHEE__TransientCacheStorage__clearExpiredTransients__limit',
function(){return 100;}
);
add_filter(
'FHEE__EE_Session__garbage_collection___expired_session_transient_delete_query_limit',
function(){return 100;}
);
add_filter(
'FHEE__TransientCacheStorage__transient_cleanup_schedule',
function(){return '15-minutes';}
);
}
|
|
wmnf
|
November 14, 2017 at 6:17 am
Installed Blackhole plugin yesterday, woke again this morning to alerts by our monitoring system of heavy load and found over 450K characters in the ee_transient_schedule value, 9500K records for _transient_timeout_ee_ and 9000K records for _transient_ee_ssn_. As soon as all purged, load normalized. I have added the code you sent for add_action above, however, all seems to be growing as usual in the minutes since purged.
|
|
wmnf
|
November 14, 2017 at 6:19 am
Sorry, that’s 9500 and 9000 records of _transient_timeout_ee_ and _transient_ee_ssn_, respectively. Already in minutes up to 27488 ee_transient_schedule characters.
|
|
wmnf
|
November 14, 2017 at 6:35 am
Already up to 44588 ee_transient_schedule characters.
|
|
Josh
|
November 14, 2017 at 7:09 am
Have you checked the User Agent Strings within the individual transient_ee_ssn_ option values?
|
|
wmnf
|
November 14, 2017 at 7:31 am
Yes, bots, so Blackhole is not doing its job? I see bing.com and google.com bots but also opensiteexplorer.org and yandex.com and ‘James BOT’. The settings for Blackhole are very simple and straightforward, I am not receving any email alerts. I have added the robots.txt snippet and running since yesterday.
|
|
Josh
|
November 14, 2017 at 8:08 am
Blackhole for bad bot’s effectiveness will vary depending on what the bots do. Is your server apache?
If so you can black the bad bots with a few simple htaccess rules, before WP even loads (which will save your server a lot of bandwidth and resources if the bad bots are hammering the server).
http://www.inmotionhosting.com/support/website/security/block-unwanted-users-from-your-site-using-htaccess
|
|
wmnf
|
November 15, 2017 at 6:39 am
Okay, I setup a lot of blocks of bots yesterday via the .htaccess file and it has slowed the growth of the _transient_ee_ssn_ records, leaving only legitimate Google, Bingbot and others. The number of these records still growing with over 6000 each of the _transient_timeout_ee_ as well. The big issue seems to be the ee_transient_schedule option finding over 350K characters again this morning and causing high load on our server again. What can be done about this?
|
|
wmnf
|
November 15, 2017 at 6:55 am
Also, the snippet you sent me before for transient cleanup with limits does not seem to be doing anything.
|
|
Josh
|
November 15, 2017 at 10:00 am
You should probably remove it then, and then try adding something like
define( 'ALTERNATE_WP_CRON', true );
to the site’s wp-config.php file.
Do you have a way to enforce some rate limiting on the server to reduce frequent hits by the same IP?
|
|
wmnf
|
November 15, 2017 at 1:22 pm
I’ve never used any rate limiting. The server does just fine otherwise and we don’t want to take the chance or blocking legitimate search engines. I don’t see any evidence of being needed, the entries seem to come from legitimate sources in the transient records and slower, wouldn’t these transients be an issue for highly active websites? Still having to clear all transient records matching _ee_ as well as the ee_transient_schedule option to maintain to normal load. None of our other plugins are generating this type of issue including highly used WooCommerce.
I’ll read up on ALTERNATE_WP_CRON.
|
|
Josh
|
November 15, 2017 at 1:29 pm
The reason I suggest rate limiting is because of the number of transients being created. That’s not normal. What is normal is the transients get automatically deleted, but that’s not working on your site for some reason too. These issues have nothing to do with WooCommerce.
|
|
wmnf
|
November 15, 2017 at 2:18 pm
Would it be better to just run a cron job on the server to call wp_cron.php versus using ALTERNATE_WP_CRON? From what I read, this is all the setting does is run wp_cron on visits and when jobs need to be ran.
|
|
Josh
|
November 15, 2017 at 2:39 pm
If you can set up your own cron job on the server, sure.
One other thing you can do that will help reduce the number of session transients is you can use the Plugin Organizer plugin to disable Event Espresso on specific pages. So for example if you get a lot of traffic on your home page, and nothing from Event Espresso gets displayed on the home page (like shortcodes or widgets from EE), you can go into edit the home page and set Event Espresso to be disabled for that page. Then repeat for any pages where Event Espresso isn’t used that gets high traffic.
|
|
wmnf
|
November 16, 2017 at 6:10 am
The home page does get the most traffic and does have EE top events listed. Can you tell me what purpose the ee_transient_schedule and transient records of _transient_timeout_ee_ and _transient_ee_ssn_ serve? I was mentioning WooCommmerce just has another highly used plugin that I guess does not use transients or any other plugins let them get out of control like this. It definitely seems to be isolcated only to EE.
|
|
Josh
|
November 16, 2017 at 6:58 am
Transients are used to track the session and the data related to that session. So for example if someone selects a ticket then moves on to the registration checkout page, EE uses that transient to keep track of their ticket selections.
|
|
wmnf
|
November 18, 2017 at 9:53 am
I am not able to get the Plugin Organizer to work on our dev site. The docs are very limited and vague, I listed EE under the Global Plugins section and Saved. The site does not seem any different and all EE functionality still seems to be working. From what I understand in the docs, that is the first step and then I must enable EE on the pages we want to be enabled.
Still, the transient records pile up and have to be purged with ee_transient_schedule to keep the server from experiencing a high load. We have upgraded to latest version 4.9.51p. I’ve set up a cron job to run wp-cron.php in the site directory every 5 minutes as well.
|
|
wmnf
|
November 19, 2017 at 8:25 am
It does appear in the latest version to be fixed. The transient records are automatically purging at certain levels.
|
