Options table' varchar 64.

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Options table' varchar 64.

Haluk Karamete
Is there a harm to change the options table so that the field names for the
transients  can have varchar 128 as opposed to varchar 64  and  if that's
okay, what's the best way to do this?

In other words, what would be  the best or the earliest hook for this kind
of s change? Or should this be done at wp-config?
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

J.D. Grimes
This is something that you need to change in the database table’s schema directly. So you need to run a single ALTER TABLE query. You don’t want to be running this every page load. If this is part of a plugin, you should do it on activation.

Also, see https://core.trac.wordpress.org/ticket/13310

-J.D.

On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]> wrote:

> Is there a harm to change the options table so that the field names for the
> transients  can have varchar 128 as opposed to varchar 64  and  if that's
> okay, what's the best way to do this?
>
> In other words, what would be  the best or the earliest hook for this kind
> of s change? Or should this be done at wp-config?
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers

_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Haluk Karamete
It makes total sense to me to do this on plug-in activation, as opposed to
wp-config.
Thank you for your advise.


On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]> wrote:

> This is something that you need to change in the database table’s schema
> directly. So you need to run a single ALTER TABLE query. You don’t want to
> be running this every page load. If this is part of a plugin, you should do
> it on activation.
>
> Also, see https://core.trac.wordpress.org/ticket/13310
>
> -J.D.
>
> On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
> wrote:
>
> > Is there a harm to change the options table so that the field names for
> the
> > transients  can have varchar 128 as opposed to varchar 64  and  if that's
> > okay, what's the best way to do this?
> >
> > In other words, what would be  the best or the earliest hook for this
> kind
> > of s change? Or should this be done at wp-config?
> > _______________________________________________
> > wp-hackers mailing list
> > [hidden email]
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Haluk Karamete
On the trac page (https://core.trac.wordpress.org/ticket/13310) , it is
said that "This schema change would have a significant impact on large WP
networks."

Could someone eloborate on this as to why the problem occurs on 'large WP
networks' as opposed to 'large WP sites'? So it's problematic on
multi-sites only?




On Tue, Jun 24, 2014 at 10:23 AM, Haluk Karamete <[hidden email]>
wrote:

> It makes total sense to me to do this on plug-in activation, as opposed to
> wp-config.
> Thank you for your advise.
>
>
> On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]> wrote:
>
>> This is something that you need to change in the database table’s schema
>> directly. So you need to run a single ALTER TABLE query. You don’t want to
>> be running this every page load. If this is part of a plugin, you should do
>> it on activation.
>>
>> Also, see https://core.trac.wordpress.org/ticket/13310
>>
>> -J.D.
>>
>> On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
>> wrote:
>>
>> > Is there a harm to change the options table so that the field names for
>> the
>> > transients  can have varchar 128 as opposed to varchar 64  and  if
>> that's
>> > okay, what's the best way to do this?
>> >
>> > In other words, what would be  the best or the earliest hook for this
>> kind
>> > of s change? Or should this be done at wp-config?
>> > _______________________________________________
>> > wp-hackers mailing list
>> > [hidden email]
>> > http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>> _______________________________________________
>> wp-hackers mailing list
>> [hidden email]
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Andrew Nacin-2
Large networks have a lot of options tables. For a sufficiently large table
(which might depend also on the storage engine), things could lock up or go
awry. Rather than just doing it once, we're doing it n times, where n is
the number of sites. More chances for things to go wrong.

My take is this:
If you are in a position to be able to run an ALTER TABLE query, you are
also probably hosting your own thing, which means you should stop storing
transients in the database. Instead, install an object cache. Transients in
the database are a last-resort kind of thing. If you're doing heavy stuff
with transients, look for alternatives.

Also, if your transient names are too long, use a prefix plus a hash of the
name. It'll make it constant-length.

Nacin


On Tue, Jun 24, 2014 at 1:27 PM, Haluk Karamete <[hidden email]>
wrote:

> On the trac page (https://core.trac.wordpress.org/ticket/13310) , it is
> said that "This schema change would have a significant impact on large WP
> networks."
>
> Could someone eloborate on this as to why the problem occurs on 'large WP
> networks' as opposed to 'large WP sites'? So it's problematic on
> multi-sites only?
>
>
>
>
> On Tue, Jun 24, 2014 at 10:23 AM, Haluk Karamete <[hidden email]>
> wrote:
>
> > It makes total sense to me to do this on plug-in activation, as opposed
> to
> > wp-config.
> > Thank you for your advise.
> >
> >
> > On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]>
> wrote:
> >
> >> This is something that you need to change in the database table’s schema
> >> directly. So you need to run a single ALTER TABLE query. You don’t want
> to
> >> be running this every page load. If this is part of a plugin, you
> should do
> >> it on activation.
> >>
> >> Also, see https://core.trac.wordpress.org/ticket/13310
> >>
> >> -J.D.
> >>
> >> On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
> >> wrote:
> >>
> >> > Is there a harm to change the options table so that the field names
> for
> >> the
> >> > transients  can have varchar 128 as opposed to varchar 64  and  if
> >> that's
> >> > okay, what's the best way to do this?
> >> >
> >> > In other words, what would be  the best or the earliest hook for this
> >> kind
> >> > of s change? Or should this be done at wp-config?
> >> > _______________________________________________
> >> > wp-hackers mailing list
> >> > [hidden email]
> >> > http://lists.automattic.com/mailman/listinfo/wp-hackers
> >>
> >> _______________________________________________
> >> wp-hackers mailing list
> >> [hidden email]
> >> http://lists.automattic.com/mailman/listinfo/wp-hackers
> >>
> >
> >
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Morgan Tocker
In reply to this post by Haluk Karamete
Hi!

On Jun 24, 2014, at 6:42 AM, Haluk Karamete <[hidden email]> wrote:

> Is there a harm to change the options table so that the field names for the
> transients  can have varchar 128 as opposed to varchar 64  and  if that's
> okay, what's the best way to do this?

It will lock up the table to extend the VARCHAR, while in the background it fills a temporary table to replace the existing table.

I loaded the wp_options table with 104994 dummy records to test how long this would take.  On my local machine:

 * 1.71 sec using InnoDB
* ~2.84 sec when using MyISAM.

Gist available for anyone wanting to try and reproduce:
https://gist.github.com/morgo/4f23b48fd3df09fb47f6

In MySQL 5.7 this will be online for InnoDB tables:
http://dev.mysql.com/worklog/task/?id=6554

- Morgan

--
Morgan Tocker
MySQL Community Manager
Oracle Canada - Hardware and Software, Engineered to Work Together.
Office: Toronto, Canada
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Haluk Karamete
THank you Morgan.

So, assuming if it's a 100 site, multi-site network where each site has as
many options as you described, this one time pain of schema change will
come at the expense of a 2.87*100 sec?

Is this understanding correct in a nut-shell?




On Tue, Jun 24, 2014 at 11:01 AM, Morgan Tocker <[hidden email]>
wrote:

> Hi!
>
> On Jun 24, 2014, at 6:42 AM, Haluk Karamete <[hidden email]>
> wrote:
>
> > Is there a harm to change the options table so that the field names for
> the
> > transients  can have varchar 128 as opposed to varchar 64  and  if that's
> > okay, what's the best way to do this?
>
> It will lock up the table to extend the VARCHAR, while in the background
> it fills a temporary table to replace the existing table.
>
> I loaded the wp_options table with 104994 dummy records to test how long
> this would take.  On my local machine:
>
>  * 1.71 sec using InnoDB
> * ~2.84 sec when using MyISAM.
>
> Gist available for anyone wanting to try and reproduce:
> https://gist.github.com/morgo/4f23b48fd3df09fb47f6
>
> In MySQL 5.7 this will be online for InnoDB tables:
> http://dev.mysql.com/worklog/task/?id=6554
>
> - Morgan
>
> --
> Morgan Tocker
> MySQL Community Manager
> Oracle Canada - Hardware and Software, Engineered to Work Together.
> Office: Toronto, Canada
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Morgan Tocker
Hi Haluk,

> So, assuming if it's a 100 site, multi-site network where each site has as
> many options as you described, this one time pain of schema change will
> come at the expense of a 2.87*100 sec?

On my blog, I only have 163 rows in wp_options, but in my test I generated 104994 rows of dummy data.

(Others will have more than 163 rows - I am probably a simple use case.)

> Is this understanding correct in a nut-shell?

I tried to create the test so any skew showed closer to the worst-case than the best case.  This is not always easy to do, and I can already identify two cases where this will not be true:

- My local machine is more powerful than virtual machines / VPS hosting environments.
- There might be some very long option_value texts creating a larger table.  I used REPEAT('a', 200), but should have probably gone for REPEAT(‘a’, 1889), as this is the average option_value length in my installation.

TL;DR: I think it will be less than 2.87*100.  You can try it for yourself though:
https://gist.github.com/morgo/4f23b48fd3df09fb47f6

- Morgan
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Haluk Karamete
In reply to this post by J.D. Grimes
Now giving it a second thought, and considering what Morgan has brought
into the picture on this very quesition, doing this at a plug-in activation
time does not sound to me as good of an idea as it did an hour ago.  :)

If it's gonna take that much of time, then every time WP updates, ( and as
part of this update, all plugins are automatically deactivated and then
re-activated ) a nd as a result of this, some users will experience such
slowness, and my plug in would be the one causing that, it's hard for me to
take that route... See what I mean?

I think if that code were to be planted in the wp-config.php file instead,
then that would have been better.

At wp-config, and every time it runs ( meaning at each page load), a
constant can be probed to see if it is defined or not.

If that constant is defined, I can drive the fact that the  option names
has already been upped to varchar(128).

What say you?


On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]> wrote:

> This is something that you need to change in the database table’s schema
> directly. So you need to run a single ALTER TABLE query. You don’t want to
> be running this every page load. If this is part of a plugin, you should do
> it on activation.
>
> Also, see https://core.trac.wordpress.org/ticket/13310
>
> -J.D.
>
> On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
> wrote:
>
> > Is there a harm to change the options table so that the field names for
> the
> > transients  can have varchar 128 as opposed to varchar 64  and  if that's
> > okay, what's the best way to do this?
> >
> > In other words, what would be  the best or the earliest hook for this
> kind
> > of s change? Or should this be done at wp-config?
> > _______________________________________________
> > wp-hackers mailing list
> > [hidden email]
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Andrew Nacin-2
I don't understand why you require anything more than 64 characters. It
would be *nice to have*, but it's not a requirement.

Stick to the existing limits and wait to see if core makes an adjustment in
https://core.trac.wordpress.org/ticket/13310.


On Tue, Jun 24, 2014 at 3:47 PM, Haluk Karamete <[hidden email]>
wrote:

> Now giving it a second thought, and considering what Morgan has brought
> into the picture on this very quesition, doing this at a plug-in activation
> time does not sound to me as good of an idea as it did an hour ago.  :)
>
> If it's gonna take that much of time, then every time WP updates, ( and as
> part of this update, all plugins are automatically deactivated and then
> re-activated ) a nd as a result of this, some users will experience such
> slowness, and my plug in would be the one causing that, it's hard for me to
> take that route... See what I mean?
>
> I think if that code were to be planted in the wp-config.php file instead,
> then that would have been better.
>
> At wp-config, and every time it runs ( meaning at each page load), a
> constant can be probed to see if it is defined or not.
>
> If that constant is defined, I can drive the fact that the  option names
> has already been upped to varchar(128).
>
> What say you?
>
>
> On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]> wrote:
>
> > This is something that you need to change in the database table’s schema
> > directly. So you need to run a single ALTER TABLE query. You don’t want
> to
> > be running this every page load. If this is part of a plugin, you should
> do
> > it on activation.
> >
> > Also, see https://core.trac.wordpress.org/ticket/13310
> >
> > -J.D.
> >
> > On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
> > wrote:
> >
> > > Is there a harm to change the options table so that the field names for
> > the
> > > transients  can have varchar 128 as opposed to varchar 64  and  if
> that's
> > > okay, what's the best way to do this?
> > >
> > > In other words, what would be  the best or the earliest hook for this
> > kind
> > > of s change? Or should this be done at wp-config?
> > > _______________________________________________
> > > wp-hackers mailing list
> > > [hidden email]
> > > http://lists.automattic.com/mailman/listinfo/wp-hackers
> >
> > _______________________________________________
> > wp-hackers mailing list
> > [hidden email]
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
> >
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

Nikola Nikolov
What I've used before(and now I see is not the best) is to simply do

$key = md5( "my_{$complicated}_{$multi}_component_{$key}" );

Instead a better approach is

// Should not be longer than 13 characters, since md5() returns 32
characters => 32+13 = 45, which is the limit
$prefix = 'my_';
$key = $prefix . md5( "{$complicated}_{$multi}_component_{$key}" );

This way you can

DELETE FROM $wpdb->options WHERE option_name LIKE '$prefix%';

Which is good to do when your plugin is deactivated.


On Tue, Jun 24, 2014 at 10:52 PM, Andrew Nacin <[hidden email]> wrote:

> I don't understand why you require anything more than 64 characters. It
> would be *nice to have*, but it's not a requirement.
>
> Stick to the existing limits and wait to see if core makes an adjustment in
> https://core.trac.wordpress.org/ticket/13310.
>
>
> On Tue, Jun 24, 2014 at 3:47 PM, Haluk Karamete <[hidden email]>
> wrote:
>
> > Now giving it a second thought, and considering what Morgan has brought
> > into the picture on this very quesition, doing this at a plug-in
> activation
> > time does not sound to me as good of an idea as it did an hour ago.  :)
> >
> > If it's gonna take that much of time, then every time WP updates, ( and
> as
> > part of this update, all plugins are automatically deactivated and then
> > re-activated ) a nd as a result of this, some users will experience such
> > slowness, and my plug in would be the one causing that, it's hard for me
> to
> > take that route... See what I mean?
> >
> > I think if that code were to be planted in the wp-config.php file
> instead,
> > then that would have been better.
> >
> > At wp-config, and every time it runs ( meaning at each page load), a
> > constant can be probed to see if it is defined or not.
> >
> > If that constant is defined, I can drive the fact that the  option names
> > has already been upped to varchar(128).
> >
> > What say you?
> >
> >
> > On Tue, Jun 24, 2014 at 7:06 AM, J.D. Grimes <[hidden email]>
> wrote:
> >
> > > This is something that you need to change in the database table’s
> schema
> > > directly. So you need to run a single ALTER TABLE query. You don’t want
> > to
> > > be running this every page load. If this is part of a plugin, you
> should
> > do
> > > it on activation.
> > >
> > > Also, see https://core.trac.wordpress.org/ticket/13310
> > >
> > > -J.D.
> > >
> > > On Jun 24, 2014, at 9:42 AM, Haluk Karamete <[hidden email]>
> > > wrote:
> > >
> > > > Is there a harm to change the options table so that the field names
> for
> > > the
> > > > transients  can have varchar 128 as opposed to varchar 64  and  if
> > that's
> > > > okay, what's the best way to do this?
> > > >
> > > > In other words, what would be  the best or the earliest hook for this
> > > kind
> > > > of s change? Or should this be done at wp-config?
> > > > _______________________________________________
> > > > wp-hackers mailing list
> > > > [hidden email]
> > > > http://lists.automattic.com/mailman/listinfo/wp-hackers
> > >
> > > _______________________________________________
> > > wp-hackers mailing list
> > > [hidden email]
> > > http://lists.automattic.com/mailman/listinfo/wp-hackers
> > >
> > _______________________________________________
> > wp-hackers mailing list
> > [hidden email]
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
> >
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Options table' varchar 64.

J.D. Grimes
In reply to this post by Haluk Karamete
Nacin and Nikola are right.

But I’d also like to add that I think you have a misconception about the plugin activation when WordPress is updated. I don’t think the activation hooks actually get fired, it is just a silent de/activation (someone correct me if I’m wrong). You are correct though, that you wouldn’t want to run this every time. You would ideally check the length of the column, and only change it if it wasn’t at the desired length already. Or, you could set an option in the database after the first time, and only run the update if it did not exist (this is how many plugins keep track of their database version).

Of course, you really shouldn’t try to do this anyway. :0)

-J.D.

On Jun 24, 2014, at 3:47 PM, Haluk Karamete <[hidden email]> wrote:

> Now giving it a second thought, and considering what Morgan has brought
> into the picture on this very quesition, doing this at a plug-in activation
> time does not sound to me as good of an idea as it did an hour ago.  :)
>
> If it's gonna take that much of time, then every time WP updates, ( and as
> part of this update, all plugins are automatically deactivated and then
> re-activated ) a nd as a result of this, some users will experience such
> slowness, and my plug in would be the one causing that, it's hard for me to
> take that route... See what I mean?
>
> I think if that code were to be planted in the wp-config.php file instead,
> then that would have been better.
>
> At wp-config, and every time it runs ( meaning at each page load), a
> constant can be probed to see if it is defined or not.
>
> If that constant is defined, I can drive the fact that the  option names
> has already been upped to varchar(128).
>
> What say you?
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers