Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.16
-
None
Description
On slow SATA disk our monitoring API try to compute CRC32 of DDL using information schema
Such trivial queries piling in processlist up to 40s , and so guessing a table lock is hold for the same duration .
Profiling show the time is spend in removing temp tables
| Removing tmp table | 7.341188 | |
|
|
|
|
| Created_tmp_disk_tables | 132 |
|
|
|
|
|
MariaDB [(none)]> SELECT CRC32(b.column_name) + CRC32(b.column_type)+CRC32(COALESCE(b.is_nullable,''))+CRC32(COALESCE(b.CHARACTER_SET_NAME,''))+ CRC32(COALESCE(b.COLLATION_NAME,''))+ CRC32(COALESCE(b.COLUMN_DEFAULT,''))+CRC32(COALESCE(c.CONSTRAINT_NAME,''))+CRC32(COALESCE(c.ORDINAL_POSITION,'')) as CRC FROM information_schema.COLUMNS b left join information_schema.KEY_COLUMN_USAGE c ON b.table_schema=c.table_schema AND b.table_name=c.table_name where b.table_schema='wpmixrgen' AND b.table_name='wp_comments'; |
+-------------+ |
| CRC |
|
+-------------+ |
| 10559682178 |
|
| 13586448768 |
|
| 15640549970 |
|
| 17764828815 |
|
| 18237274403 |
|
| 18860079141 |
|
| 11228629995 |
|
| 11446859360 |
|
| 14178433118 |
|
| 15610854266 |
|
| 19381875266 |
|
| 17733767415 |
|
| 18959763247 |
|
| 17370551431 |
|
| 16025333695 |
|
+-------------+ |
15 rows in set (0.019 sec) |
|
|
MariaDB [(none)]> show status like '%tmp%'; |
+-------------------------+-------+ |
| Variable_name | Value |
|
+-------------------------+-------+ |
| Created_tmp_disk_tables | 144 |
|
Found a workaround by using show create table instead but i d like to understand why information_schema produce multiple on disk temp tables , any pointer would be appreciate , thanks i advance .
Found that changing join level from 6 to 2 change from BNLH to (flat, BNL ) and reduce the number of temp table per 2 . Still so few rows should not produce disk temp table but memory instead no ?
Attachments
Issue Links
- relates to
-
MDEV-19 Memory tables: VARCHAR and BLOB support
-
- Stalled
-
-
-
- Open
-