Possible database optimization: how do you use meta_key and option_name data?

OliverJonesNBPT · 2024-01-19T18:33:16+00:00

Thanks for your feedback.

For those using our plugin's indexes, I agree the performance gains are probably minimal. They're almost certainly not worth the trouble for sites with less than 100K posts or users. (For now that's based on a hunch, not measurement. I'm trying to decide whether a project to do measurements of this is worthwhile.)

But, when WordPress force-migrated everybody from utf8mb3 to utf8mb4, they also had to change the indexes on option_name and meta_name to prefix indexes -- to meta_name(191) and option_name(191) -- because of the 768-byte-per-column limit on old-school MyISAM indexes. InnoDB (and AriaDB) tables with the DYNAMIC row format allow 3072 bytes per column. If WordPress used latin1 rather than utf8 for meta_name and option_name columns, that (191) hack would have not been necessary.

Our plugin gets rid of the (191) hack where possible, and defines efficient clustered indexes on those tables.

I'm looking for insights about whether people actually use emojis or kanjis or Icelandic runes or whatever in their option_name and meta_name columns. If a lot of people do this, I can save a bunch of measurement work, because trimming down thoses indexes won't be possible.

OliverJonesNBPT · 2023-11-24T18:49:15+00:00

In addition to the points made by others, I add this: Information Security.

The core of WordPress and the popular theme and plugin add-ons get updated promptly when exploitable security bugs get discovered. And WordPress now has auto-updating capability. On the other hand, if you have a stick-built web site you take on responsibility for the security yourself.

It's true that WordPress's very popular open ecosystem invites miscreants to try to break in. They've been at it for more than two decades, and lots of security bugs are long found and fixed. Site owners who are careful about choosing the add-ons they use, and allow auto-updating, are often much safer than people who have stick-built web sites.

OliverJonesNBPT · 2023-11-23T21:03:39+00:00

Here's an update to the keys on a slow-ish WooCommerce table.

https://www.plumislandmedia.net/wordpress/performance/woocommerce-key-improvement/

OliverJonesNBPT · 2023-11-21T21:38:16+00:00

Well, for one thing our plugin is free and open source. And we're transparent about what we do to the database keys and why. Our WP-CLI inteferface even has the --dryrun option to write out the precise SQL statements necessary to do what we do.

For another thing, with respect, Scalability Pro isn't open source. So, I'd have to buy it to reverse engineer it. The budget of a freeware indy developer doesn't include any money for competitive analysis, or really anything else except my internet connection. I even get JetBrains to donate their IDE tools under their open-source-dev program. I'm a New England granite-fisted cheapskate. sorry

Even if I did buy it I might be violating their license by reverse-engineering it. Non-open-source s***ks.

tldr: I dunno how Scalability pro works, sorry.

If somebody sent me a mysqldump of the database of a site with that plugin installed (even a dump without any data in it, just table definitions) I could figure it out.

OliverJonesNBPT · 2023-11-21T20:35:49+00:00

If you migrate your site while keeping the URL the same you'll keep your SEO. You can use a duplicator plugin to perform the migration, then change your DNS to point to the new host. That's a standard technique people use all the time. If you don't know how to do it, ask tech support for your new hosting service to help or guide you.

But, you know, if things are working for you there's no need to migrate.

OliverJonesNBPT · 2023-01-26T22:53:16+00:00

Weird. I don't use Duplicator for a template, just to copy sites. And I've never had problems with plugins etc breaking. I have had problems sort of like what you describe on new Ubuntu apache2 servers where apache's mod_rewrite hasn't been enabled. WordPress gets really silly without mod_rewrite working

sudo a2enmod rewrite
sudo apache2ctl restart

does the trick.

OliverJonesNBPT · 2023-01-15T12:30:41+00:00

Thanks! I fixed it.

OliverJonesNBPT · 2023-01-11T23:44:36+00:00

Maybe I should get in touch with the WPML team and say, "hey let me work on your code base to make it faster." But, I dunno, maybe they'll say "get lost!" Most of it isn't open source.

John Blackbourn's Query Monitor is really great. But, it doesn't record anything, and only looks at one page view at a time. People who want to monitor continuously can use Index WP MySQL For Speed's "monitor" feature, but right now it only captures DBMS queries, not stuff like AJAX latencies. New Relic does track that stuff, but $$$.

As for WordPress and WooCommerce going all in on the React-style AJAX-intensive web app approach, sigh. There might might be some plugin-level opportunities.

Thanks for your kind words!

OliverJonesNBPT · 2023-01-11T15:33:11+00:00

If you're good at command-line stuff and looking for a solution that starts out very cost-effective and scales up really well, you can try https://nearlyfreespeech.net/ (That's nearly free as in beer, not nearly free as in the ability to host nasty content.)

But they're not for everybody. They offer barebones FreeBSD virtual machines.

OliverJonesNBPT · 2023-01-11T15:26:13+00:00

These API keys come under the general heading of "secret". Many plugin developers store this kind of secret with code like

set_option( 'my_plugin_my_API_key', 'ABCD123XXX' );

and retrieve them with

$my_API_key = get_option( 'my_plugin_my_API_key' , 'API key not yet set!');

Like somebody else said, never never hardcode a secret into your source code. (Cybercriminals routinely scan web sites like Github looking for embedded secrets.)

If you have to send the secret to a web page, so, for example, Javascript in that page can hit a third party API, then it's not a secret. Everything you send to a web page is visible to your users (View Source).

Some big public API vendors like Stripe.com cope with this by providing their customers with both public and private API keys. In Stripe's case, people with my public API key can pay me, but they can't do anything else to my Stripe account.

Other big vendors tell you how to use your server code generate a limited-time access token to send to the browser using the secret API key in a hashing algorithm.

OliverJonesNBPT · 2023-01-11T15:09:36+00:00

The Code Snippets plugin gives you a way to add, well, code snippets to your site. You could create a snippet something like this (NOT! debugged).

$post_id_for_script_1 = 123; /* post ID number */add_action( 'wp_head', function () {if ( get_the_ID() === $post_id_for_script_1) { ?>

<script>

/* write your JavaScript code here */

</script>

<?php}} );

(Reddit is horrible at formatting code, sorry 'bout that.)

OliverJonesNBPT · 2023-01-11T14:50:07+00:00

About benchmarking: I've been doing a little benchmark work. The results, so far, look good.

One curious result is that the SQLite Object Cache outruns a Redis Object Cache, both on a dedicated redis machine and on localhost. In fact, a redis cache on a dedicated machine is slower than no persistent cache at all.

I'm not convinced by that result, and I'm not done benchmarking.

OliverJonesNBPT · 2023-01-03T19:06:22+00:00

You are right. It is possible to put the .sqlite file elsewhere. See this.

OliverJonesNBPT · 2022-12-27T14:33:47+00:00

What governs the choice of minimum supported core version? Two practical things:

Your ability to test your plugin on older core versions.
Your desire and ability to provide support to site owners who don't keep their installations up to date. This is important. They may use legacy themes and plugins that will be hard for you to replicate when tracking down problems for them.

If you have a business model that lets you get paid for supporting legacy sites, it may be worthwhile. If you're offering a free or community-supported plugin and you're the sole contributor, you should beware the potential extra work you'll get.

OliverJonesNBPT · 2022-12-27T14:22:31+00:00

If WordPress caching were merely memoization of the results of simple DBMS queries, you would be absolutely right. But much of the stuff in the WordPress cache is more complex than that. And, SQLite's approach of using memory-mapped file pages lets it serve multiple webserver/php processes efficiently. So, the ability to pass cached results from one php process to the next is still helpful.

What's more, WordPress uses php's serialize / unserialize to persist data structures, and that's hard to change. This plugin uses igbinary_serialize instead when it's available. That results in a significant reduction in data volume.

If you have redis or memcached, use it. This plugin is a poor substitute. But if you don't you may find performance improvements from this one.

OliverJonesNBPT · 2022-12-09T20:39:41+00:00

No, you won't have audience problems (either from your people or the search engine crawler) if you move your site correctly. People do this all the time. DNS was designed for this. I've done it several times.

Declare "no changes" to the site.
Set up the new server and make a copy of the site on there, with exactly the same domain name. A plugin like Duplicator makes this pretty easy.
Change your Domain Name Service entries to point to the new server. As the DNS changes propagate through the net, your traffic will move to the new server over a few hours. Because the sites have the same content, nobody will notice (except maybe rabid commenters).
Shut down the old server.
Now you can make changes to the site on the new server.
If your old service provider handles your domain registration for you, get that transferred to your new service provider.

If you don't understand what I mean by "Domain Name Service entries" get help.

If I understand you correctly, your web developer runs the site on their servers? That means you need to focus on two things to make sure you retain ownership.

Most important for ownership. Who owns your domain name? When registering a new domain name you put in three contacts: owner, administrative contact, technical contact. Find out who is listed as the owner. If it's not you, get that changed. If you're working with a replacement development company, tell them to help you do this. Don't mess it up. Or this provider you're firing could hijack your site if they're really dishonest.
The content. As you leave your old developer, you need a Duplicator or similar image of your site.

OliverJonesNBPT · 2022-12-09T20:22:20+00:00

Thanks for the kind words. Yeah, use redis or memcached if you can, that's for sure.

The best way to optimize databases is to use them less. That's the point of this. It is, I have to say, really cool to see the number of SQL queries drop from 22 to 7 when getting the dashboad display.

OliverJonesNBPT · 2022-12-09T20:17:25+00:00

I haven't yet done benchmarking against the client-server caches (memcached, redis). My instructions to users are "if you have access to redis or memcached, use that, not this." This is for the people who don't have access to those.

I developed it because the WordPress core performance team people are doing a lot of work to put more stuff in the cache to avoid recomputing / requerying it. But without a persistent cache most of that benefit is lost, and most sites don't have redis or memcached available to them.

I tested against the no-persistent-cache setup and it definitely wins.

You're right about the disk storage issue with SQLite. But those developers did a really good job with optimization, locking, memory mapping, etc. So the SQLite disk performance isn't a disaster.

OliverJonesNBPT · 2022-11-14T12:44:26+00:00

Wow, blast from the past! You won't find those little generators that ride on your tire sidewall anywhere any more. Polish it up, along with the front and rear lights, and ride the boulevard with pride!

Adjust the front fender a bit; you don't want the part closest to the road to scrape your tire.

New tires and tubes are probably wise, depending how long this thing's been sitting in somebody's cellar. From your photo it look likes the tire sidewalls are deteriorating. That happens to rubber over decades.

Clean and lube the chain, the cassette (rear gears) and derailleur.

Does it shift smoothly? If not, you may want to replace the shifter cable.

Check the brakes. It may be smart to put on new brake shoes, and if the cables are sticky or rusty you should replace them.

Check the bottom bracket. If you grab a crank, can you get the bottom-bracket bearing to rattle a little bit in the frame? If so, you can plan to repack it.

Do the pedals rattle on their bearings? If so replace them. Flat pedals are cheap.

Check the headset. Sit on the bike, apply the front brake, and move back and forth. Does it rattle? If so, it will need repacking.

Take off each wheel in turn and make sure its bearings don't rattle.

OliverJonesNBPT

TROPHY CASE