0

on my mariadb server OPENED_FILES is currently 2,775,453 and OPEN_FILES never seems to exceed 65. I understand that opened_files is cumulative and is meaningless without a time period. UPTIME_SINCE_FLUSH_STATUS is 60,287.

When I work out the opened tables per hour (OPENED_FILES*3600/UPTIME_SINCE_FLUSH_STATUS) it is currently 165,735 but usually averages to 300,000. Obviously something isn't quite right.

Here are some of my parameters

OPEN_FILES_LIMIT = 10,162
TABLE_DEFINITION_CACHE = 1,000
TABLE_OPEN_CACHE = 5,000

my server hosts just under 1,000 tables and THREADS_CREATED = 121.

cat /proc/14883/limits gives me

Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            8388608              unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             64110                64110                processes
Max open files            10162                10162                files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       64110                64110                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

some of the queries include

Count: 7  Time=0.35s (2s)  Lock=0.00s (0s)  Rows_sent=7.4 (52), 

Rows_examined=3626.3 (25384), user@localhost
  #
  # explain: id select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  # explain: N  SIMPLE  posts   range   PRIMARY,type_status_date        type_status_date        N       NULL    N       Using where; Using index; Using temporary; Using filesort
  # explain: N  SIMPLE  meta__order_tax ref     PRIMARY,post_id,post_id_key_value       PRIMARY N       mydb.posts.ID,const       N       Using where
  # explain: N  SIMPLE  meta__order_shipping_tax        ref     PRIMARY,post_id,post_id_key_value       PRIMARY N       mydb.posts.ID,const       N       Using where
  # explain: N  SIMPLE  meta__order_total       ref     PRIMARY,post_id,post_id_key_value       PRIMARY N       mydb.posts.ID,const       N       Using where
  # explain: N  SIMPLE  meta__order_shipping    ref     PRIMARY,post_id,post_id_key_value       PRIMARY N       mydb.posts.ID,const       N       Using where
  #
  SET timestamp=N;
  SELECT SUM( meta__order_total.meta_value) as total_sales,SUM( meta__order_shipping.meta_value) as total_shipping,SUM( meta__order_tax.meta_value) as total_tax,SUM( meta__order_shipping_tax.meta_value) as total_shipping_tax, posts.post_date as post_date FROM wp_posts AS posts INNER JOIN wp_postmeta AS meta__order_total ON ( posts.ID = meta__order_total.post_id AND meta__order_total.meta_key = 'S' ) INNER JOIN wp_postmeta AS meta__order_shipping ON ( posts.ID = meta__order_shipping.post_id AND meta__order_shipping.meta_key = 'S' ) INNER JOIN wp_postmeta AS meta__order_tax ON ( posts.ID = meta__order_tax.post_id AND meta__order_tax.meta_key = 'S' ) INNER JOIN wp_postmeta AS meta__order_shipping_tax ON ( posts.ID = meta__order_shipping_tax.post_id AND meta__order_shipping_tax.meta_key = 'S' )
  WHERE         posts.post_type         IN ( 'S' )
  AND   posts.post_status       IN ( 'S','S','S','S','S','S','S','S','S','S','S','S')
  AND   posts.post_date >= 'S'
  AND   posts.post_date < 'S'
  GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), DAY(posts.post_date) ORDER BY post_date ASC

and

Count: 777  Time=0.00s (3s)  Lock=0.00s (0s)  Rows_sent=525.2 (408109), Rows_examined=5163.5 (4012019), c10_sql[c10_sql]@localhost
  #
  # explain: id select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  # explain: N  SIMPLE  tt      ALL     PRIMARY,term_id_taxonomy,taxonomy       NULL    NULL    NULL    N       Using where; Using temporary; Using filesort
  # explain: N  SIMPLE  t       eq_ref  PRIMARY PRIMARY N       mydb.tt.term_id   N
  # explain: N  SIMPLE  tm      ref     term_id,meta_key        term_id N       mydb.tt.term_id   N       Using where
  # explain: N  SIMPLE  tr      ref     PRIMARY,term_taxonomy_id,term_taxonomy_id_object_id     term_taxonomy_id        N       mydb.tt.term_taxonomy_id  N       Using where; Using index
  #
  SET timestamp=N;
  SELECT  t.*, tt.*, tr.object_id, tm.meta_value FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN wp_termmeta AS tm ON (t.term_id = tm.term_id AND tm.meta_key = 'S')  WHERE tt.taxonomy IN ('S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S') AND tr.object_id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)  GROUP BY t.term_id, tr.object_id ORDER BY tm.meta_value+N ASC, t.term_order ASC

I've currently got

MAX_HEAP_TABLE_SIZE = 268,435,456
SORT_BUFFER_SIZE = 4,194,304
TMP_TABLE_SIZE = 268,435,456

tmp tables written to disk CREATED_TMP_DISK_TABLES*100/(CREATED_TMP_DISK_TABLES+CREATED_TMP_DISK_TABLES) averages 48% but if I make MAX_HEAP_TABLE_SIZE and TMP_TABLE_SIZE much bigger I can get it down to 47% (yah!)

The server has 16GB ram but also runs apache web server.

some optimisation attempts to date

--30-November-2018 performance changes

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (meta_key,post_id);
ALTER TABLE `wp_postmeta` DROP INDEX `meta_key`;
ALTER TABLE wp_postmeta ADD INDEX post_id_key_value (meta_key(50),post_id,meta_value(50));
ALTER TABLE wp_postmeta ADD INDEX meta_value_key (meta_value(50),meta_key(50));
ALTER TABLE wp_woocommerce_order_itemmeta ADD INDEX order_item_id_key_value (order_item_id,meta_key(50),meta_value(50));
ALTER TABLE wp_options ADD INDEX autoload (autoload);
ALTER TABLE wp_woocommerce_order_items ADD INDEX type_name (order_item_type, order_item_name(128));
ALTER TABLE wp_wfConfig ADD INDEX autoload (autoload);
ALTER TABLE wp_term_relationships ADD INDEX term_taxonomy_id_object_id (term_taxonomy_id,object_id);

--5-December-2018 performance changes

ALTER TABLE `wp_postmeta` CHANGE `meta_id` `meta_id` BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE wp_postmeta DROP PRIMARY KEY, ADD PRIMARY KEY(post_id, meta_key, meta_id);
ALTER TABLE `wp_postmeta` ADD UNIQUE `meta_id` (`meta_id`);
ALTER TABLE `wp_postmeta` CHANGE `meta_id` `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--25-December-2018 performance changes

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` VARCHAR(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `wp_terms` CHANGE `name` `name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';
ALTER TABLE `wp_terms` CHANGE `slug` `slug` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';
ALTER TABLE `wp_termmeta` CHANGE `meta_key` `meta_key` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `wp_woocommerce_order_itemmeta` CHANGE `meta_key` `meta_key` VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
1
  • Additional information request. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; requested by Rick James, D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, apache configuration file, for server tuning analysis. Dec 30, 2018 at 18:54

1 Answer 1

0

Opened_files also includes tmp tables needed for complex SELECTs. What are the values of Created_tmp_disk_tables and Created_tmp_tables? And how much RAM do you have?

Heck, simply provide SHOW GLOBAL STATUS; and SHOW VARIABLES;; there are probably other things to check.

We will probably need to look at some of the queries. If you can identify one or two; provide them, plus SHOW CREATE TABLE and EXPLAIN SELECT ...

Comments on the ALTERs

Index prefixing, such as (meta_key(50),post_id,meta_value(50)) is generally useless. The Optimizer cannot see past the first prefix to use the other columns. Later you changed to VARCHAR(60); time to get rid of the prefixing for meta_key.

meta_id -- Do you reference it in some other table? If not, it is wasted.

Some columns are CHARACTER SET utf8, some are utf8mb4? If you JOIN on inconsistent charsets, indexes can't be used.

For further fishing around for slow queries, turn on the slowlog, summarize it, and let's discuss it. See http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

9
  • CREATED_TMP_TABLES = 733,692 CREATED_TMP_DISK_TABLES = 690,566 This was asat yesterday with the 60K uptime Dec 30, 2018 at 3:23
  • Is the Uptime still 60K? 10/second is rather high. Also, the percentage that goes to disk is very high. This won't quite 'prove' that the 2.7M comes from tmp tables, but is comes close. Let's see one of those queries.
    – Rick James
    Dec 30, 2018 at 3:25
  • tmp tables is something I'm also working on. CREATED_TMP_DISK_TABLES is way too big. The biggest culprit seems to be wordpress. I would expect a system with such a huge user base to have more optimised queries. Dec 30, 2018 at 3:29
  • Whenever you fetch meta_value, which is LONGTEXT, you will need a temp table on disk. Meanwhile, here are some tips on speeding up wp_postmeta: mysql.rjweb.org/doc.php/…
    – Rick James
    Dec 30, 2018 at 3:34
  • so even if you don't sort by the long text, it will still need a tmp table? Dec 30, 2018 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.