werdermouth

So if I run the following query on the wp_commentmeta table, I can see which meta_keys it contains and how many records for each one:

select count(*) as records, meta_key from wp_commentmeta group by meta_key order by records desc

*where ‘wp_’ replaces the actual table prefix

------------------------------------
meta_key                     records
------------------------------------
jabber_published               50871
email_notification_jobid       48801
email_notification_queued      48801
comment_like_count             19680
email_notification_notqueued    2068
hc_post_as                       227
hc_avatar                        227
hc_foreign_user_id               227
comment_by_email_id                4
------------------------------------

I’m not sure what jabber_published is but the meta_value is a ten digit number, where the first nine digits are always the same as either email_notification_queued or email_notification_notqueued.

email_notification_jobid is a nine-digit number that doesn’t share any pattern of the above three.

Googling these meta_tags doesn’t shed much light but it appears they may be related or used by the Jetpack plugin – as mention my site was previously exported from wordpress.com but I don’t have Jetpack installed on my www.remarpro.com site.

Therefore, if these are Jetpack records then I should be able to delete them and reduce the table to a few thousand records instead of 170,000.

Hopefully that makes logical sense and I’ll wait to see if anyone can confirm that approach or has an alternative take.

• This reply was modified 2 years, 10 months ago by werdermouth.

I did actually have the latest version of NewStatPress, which I’d mainly installed to see where my spam registrations were coming from after I was getting 200 a day even though I’d disabled registration (but that’s off-topic for this post) – nevertheless I’m not keen on any plugin that eats up my storage so quickly.

On the plus side, after de-installing it, I’m now down to 44% of my storage limit! Though I had to drop the statpress table myself as it didn’t get removed by deleting the plugin.

I’ll probably tackle those email_notifications in the wp_commentmeta next and see if they are actually used for anything.

An interesting development – coincidence or not – after posting my support query the wp_statpress table mentioned has increased in size overnight from an overly large 23Mb to a massive 375Mb with not a significant increase in the number of records.

Can only assume a particular record contains something beyond what it is intended for.

I have no choice but to delete this table and probably deinstall the NewStatPress plugin as this can’t be secure.

Hi CorrinaRusso

Thanks for your reply, my current hosting plan is limited to 100Mb but it can be upgraded but then the monthly fee doubles, which given the actually content is less than 50Mb I’m interested in getting rid of space on those bloated tables.

I did install the wp-sweep plugin to see what it found but there weren’t much savings that it found and next to nothing in the commentmeta table.

I was tempted to delete those email_notification meta-keys records but I’ve no idea what they are used for and if they have any effect on the operation of the site – 26Mb seems a lot for non-content data.

I was also thinking of emptying the NewStatPress table as like you say I’m not exactly studying them – after all it records just as much spam traffic as genuine visitors.

btw I don’t have a caching tables plugin installed

Anyway, I prefer to keep the database only as large as it needs to be – OK they grow over time and my site is something I do as a community project in my spare time and cover the hosting costs but am not able to devote a massive amount of time to.

I’m reasonably comfortable with databases and have done a bit of programming back in the day but generally have to research to get on top of matters WordPress as things were much simpler when internet speeds were slower.

Hi Brett

Thanks for your reply, I don’t have a current local full backup but do a daily backup to a Google Drive using UpdraftPlus plugin.

I’m at the moment interested in reducing the size of a couple of big tables – especially the commentsmeta one, which I’m still not sure why it is so big and what it’s actually being used for.

I’ve had the same problem for a number of months when after getting hundreds of spam registrations. I tried renaming the register path and installed extra anti-spam plugins too. Eventually I decided to just disable registrations completely thinking that would end the problem.

However, I still get around 10-20 spam registrations on some days by people (bots?) that don’t need any registration page to be able to register on my site. There exists no page or legitimate means on my site to register but still they do. Apparently, people say the spammers can exploit WordPress security loopholes to register directly without a registration form.

I would be grateful if someone could explain what these loopholes are and why I can’t prevent registrations. At the moment the default registrations are set to ‘Pending’ with only very minimal rights so they need to be approved before they can actually post anything visible. Still I have to continually manually delete these spam registrations where they soon outnumber the small group of 50 legitimate members.

I’ve looked at T-P’s links and already have Askimet installed (free version) and doubt if the no-bot-registration would fix the problem as it’s aimed at adding questions to the registration process to thwart bots – which I don’t have.

It’s possible that blocking spam registrations with .htaccess is something worth looking into but that’s going to be a whole new learning curve for me. It would work if the bulk of IP addresses of the spammers are closely related.

Though I’m still not sure why if I don’t want registrations WordPress just can’t stop this happening? Especially if there are ways of blocking particular IP addresses from registering!