opened_files大，open_files小

Question

在我的mariadb服务器上，OPENED_FILES目前是2,775,453，而OPEN_FILES似乎从未超过65。我知道opened_files是累积的，没有一个时间段是没有意义的。UPTIME_SINCE_FLUSH_STATUS是60,287。

当我计算出每小时打开的表( opened _FILES*3600/UPTIME_FILES_FLUSH_STATUS)时，当前为165,735，但通常平均为300,000。很明显有些事情不太对劲。

以下是我的一些参数

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

我的服务器托管了将近1000个表，THREADS_CREATED =121个。

cat /proc/14883/限值

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

有些查询包括

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

和

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

我现在有

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

写入磁盘CREATED_TMP_DISK_TABLES*100/(CREATED_TMP_DISK_TABLES+CREATED_TMP_DISK_TABLES)的tmp表平均为48%，但如果使MAX_HEAP_TABLE_SIZE和TMP_TABLE_SIZE大得多，则可以将其降低到47% (耶！)

服务器有16 web的内存，但也运行apache服务器。

迄今为止的一些优化尝试

--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;

Answer 1

Opened_files还包括复杂SELECTs所需的tmp表。Created_tmp_disk_tables和Created_tmp_tables的值是多少？你有多少内存？

见鬼，只需提供SHOW GLOBAL STATUS;和SHOW VARIABLES;；可能还有其他需要检查的东西。

我们可能需要查看一些查询。如果您可以识别一个或两个；提供它们，加上SHOW CREATE TABLE和EXPLAIN SELECT ...

对修改的评论

索引前缀(如(meta_key(50),post_id,meta_value(50)) )通常是无用的。优化器看不到使用其他列的第一个前缀。稍后，您更改为VARCHAR(60)；是时候去掉meta_key的前缀了。

meta_id --你在其他表格中引用过吗？如果没有，它就被浪费了。

有些列是CHARACTER SET utf8，有些是utf8mb4？如果您对不一致的字符集进行JOIN，则无法使用索引。

若要进一步查找慢速查询，请打开慢速日志，对其进行总结，然后进行讨论。请参阅http://mysql.rjweb.org/doc.php/mysql_analysis#slow_查询_和_慢速日志