wp_options – dumping data

I have issue with MySQL server (too many process) and it is related with autoload “no” set in table “wp_options” related to WP Commerce plugins:

First I have un-optimized:

: +———-+———-+
| COUNT(*) | autoload |
+———-+———-+
| 198705 | no |
| 3286 | yes |
+———-+———-+

Then added to increase performance on VPS (512 MB RAM + 512 MB SWAP):

ALTER TABLE wp_options ADD INDEX (‘autoload’);

+———-+———-+
| COUNT() | autoload |
+———-+———-+
| 198871 | no |
| 3286 | yes |
+———-+———-+
2 rows in set (0.56 sec)

After index it’s slightly inreased performance.

After added index it’s increased performance but, constantly added into wp_options data with huge list.

Then, I’ve installed this plugin https://wordpress.org/plugins/artiss-transient-cleaner/installation/ to clean-up autoloads:

mysql> SELECT COUNT(), autoload FROM wp_options GROUP BY autoload;
+———-+———-+
| COUNT() | autoload |
+———-+———-+
| 624 | no |
| 3131 | yes |
+———-+———-+
2 rows in set (0.01 sec)

After 2 days I’ve checked wp_options table and this result:

+———-+———-+
| COUNT(*) | autoload |
+———-+———-+
| 232526 | no |
| 3135 | yes |
+———-+———-+
2 rows in set (0.22 sec)

Example latest query:

| 2192228 | _transient_wpsc_customer_meta__SNZgrhl3P!pE | a:5:{s:16:”shipping_country”;s:2:”HR”;s:15:”billing_country”;s:2:”HR”;s:15:”shipping_region”;b:0;s:14:”billing_region”;b:0;s:4:”cart”;s:1127:”O:9:”wpsc_cart”:37:{s:16:”delivery_country”;s:2:”HR”;s:16:”selected_country”;s:2:”HR”;s:15:”delivery_region”;b:0;s:15:”selected_region”;b:0;s:24:”selected_shipping_method”;s:8:”flatrate”;s:24:”selected_shipping_option”;s:14:”Local Shipping”;s:24:”selected_shipping_amount”;N;s:6:”coupon”;N;s:14:”tax_percentage”;N;s:9:”unique_id”;s:40:”6b161edb0f87f9a597f753c72edff4f448784487″;s:6:”errors”;a:0:{}s:9:”total_tax”;N;s:13:”base_shipping”;N;s:19:”total_item_shipping”;N;s:14:”total_shipping”;N;s:8:”subtotal”;N;s:11:”total_price”;N;s:13:”uses_shipping”;N;s:13:”is_incomplete”;b:1;s:10:”cart_items”;a:0:{}s:9:”cart_item”;N;s:15:”cart_item_count”;i:0;s:17:”current_cart_item”;i:-1;s:11:”in_the_loop”;b:0;s:16:”shipping_methods”;a:1:{i:0;s:8:”flatrate”;}s:15:”shipping_method”;N;s:21:”shipping_method_count”;i:1;s:23:”current_shipping_method”;i:-1;s:18:”in_the_method_loop”;b:0;s:15:”shipping_quotes”;a:1:{s:14:”Local Shipping”;d:40;}s:14:”shipping_quote”;N;s:20:”shipping_quote_count”;i:0;s:22:”current_shipping_quote”;i:-1;s:17:”in_the_quote_loop”;b:0;s:12:”coupons_name”;s:0:””;s:14:”coupons_amount”;i:0;s:15:”shipping_option”;N;}”;} | no |
| 2192227 | _transient_timeout_wpsc_customer_meta__SNZgrhl3P!pE | 1416824635 | no |
| 2192226 | _transient_wpsc_customer_meta__NYJS&QGiMuvo | a:5:{s:16:”shipping_country”;s:2:”HR”;s:15:”billing_country”;s:2:”HR”;s:15:”shipping_region”;b:0;s:14:”billing_region”;b:0;s:4:”cart”;s:1127:”O:9:”wpsc_cart”:37:{s:16:”delivery_country”;s:2:”HR”;s:16:”selected_country”;s:2:”HR”;s:15:”delivery_region”;b:0;s:15:”selected_region”;b:0;s:24:”selected_shipping_method”;s:8:”flatrate”;s:24:”selected_shipping_option”;s:14:”Local Shipping”;s:24:”selected_shipping_amount”;N;s:6:”coupon”;N;s:14:”tax_percentage”;N;s:9:”unique_id”;s:40:”000f3fe5be7241da0b2bd7c0f94687e402f2c9a9″;s:6:”errors”;a:0:{}s:9:”total_tax”;N;s:13:”base_shipping”;N;s:19:”total_item_shipping”;N;s:14:”total_shipping”;N;s:8:”subtotal”;N;s:11:”total_price”;N;s:13:”uses_shipping”;N;s:13:”is_incomplete”;b:1;s:10:”cart_items”;a:0:{}s:9:”cart_item”;N;s:15:”cart_item_count”;i:0;s:17:”current_cart_item”;i:-1;s:11:”in_the_loop”;b:0;s:16:”shipping_methods”;a:1:{i:0;s:8:”flatrate”;}s:15:”shipping_method”;N;s:21:”shipping_method_count”;i:1;s:23:”current_shipping_method”;i:-1;s:18:”in_the_method_loop”;b:0;s:15:”shipping_quotes”;a:1:{s:14:”Local Shipping”;d:40;}s:14:”shipping_quote”;N;s:20:”shipping_quote_count”;i:0;s:22:”current_shipping_quote”;i:-1;s:17:”in_the_quote_loop”;b:0;s:12:”coupons_name”;s:0:””;s:14:”coupons_amount”;i:0;s:15:”shipping_option”;N;}”;} | no |
| 2192225 | _transient_timeout_wpsc_customer_meta__NYJS&QGiMuvo | 1416824635

This is transients, but this querying makes overloading VPS server and I’m not sure what is right way to do. Disable this plugin and use some another Commerce WP plugins to disable this overloading or migrate to MariaDB.

My mysql config:

mysql> exit
Bye
[root@hippygarden ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections = 400
key_buffer = 16K
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
sort_buffer_size = 64K
table_cache = 4
thread_stack = 64K
thread_cache_size = 2
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

local-infile=0

https://wordpress.org/plugins/wp-e-commerce/