Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
This task is about improving memory utilization and performance for
Information schema
Some work has recently been done in bb-10.2-ext to free memory early for
tables and views used be performance schema. The next step is to create
more efficient temporary tables that doesn't store information that we don't
need.
MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
|
+-------------+-----------------+
|
| MEMORY_USED | MAX_MEMORY_USED |
|
+-------------+-----------------+
|
| 86120 | 245768 |
|
+-------------+-----------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> select table_name from information_schema.tables where table_schema="mysql";
|
....
|
MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
|
+-------------+-----------------+
|
| MEMORY_USED | MAX_MEMORY_USED |
|
+-------------+-----------------+
|
| 86120 | 696880 |
|
+-------------+-----------------+
|
Here we used 600K memory for a simple query
MariaDB [test]> select count(*) from information_schema.tables where table_schema="mysql";
|
MariaDB [test]> select table_name from information_schema.tables;
|
...
|
MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
|
+-------------+-----------------+
|
| MEMORY_USED | MAX_MEMORY_USED |
|
+-------------+-----------------+
|
| 86120 | 5293216 |
|
+-------------+-----------------+
|
Here we used 5M memory for a simple query over 341 tables.
The reason for the excessive memory used comes from that the temporary table
created has a very wide record:
While running:
select table_name from information_schema.tables; |
in gdb:
(gdb) break handler::ha_write_tmp_row
|
(gdb) p table->s->reclength
|
$2 = 14829
|
Two possible ways to fix this:
1) Extend heap tables to store VARCHAR and BLOB efficiently
2) In sql_show, change all fields that are not used to be CHAR(1)
1) is a major tasks and we can't get that done in time for 10.3
2) will help even if we do 1) as we have less to store.
This task is to do 2)
This should not be that hard as information_schema already knows which
fields are accessed in the query. This is already used to decide if we
can solve the information_schema access without opening the table.
This should be done against the bb-10.2-ext tree, which has the new
MAX_MEMORY_USED column in information_schema.processlist.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Attachment | improve_memory.diff [ 44792 ] |
| Description |
This task is about improving memory utilization and performance for
Information schema Some work has recently been done in bb-10.2-ext to free memory early for tables and views used be performance schema. The next step is to create more efficient temporary tables that doesn't store information that we don't need. ---------- MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 245768 | +-------------+-----------------+ 1 row in set (0.00 sec) MariaDB [test]> select table_name from information_schema.tables where table_schema="mysql"; .... MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 696880 | +-------------+-----------------+ Here we used 600K memory for a simple query MariaDB [test]> select count(*) from information_schema.tables where table_schema="mysql"; MariaDB [test]> select table_name from information_schema.tables; ... MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 5293216 | +-------------+-----------------+ Here we used 5M memory for a simple query over 341 tables. The reason for the excessive memory used comes from that the temporary table created has a very wide record: While running: select table_name from information_schema.tables; in gdb: break handler::ha_write_tmp_row (gdb) p table->s->reclength $2 = 14829 Two possible ways to fix this: 1) Extend heap tables to store VARCHAR and BLOB efficiently 2) In sql_show, change all fields that are not used to be CHAR(1) 1) is a major tasks and we can't get that done in time for 10.3 2) will help even if we do 1) as we have less to store. This task is to do 2) This should not be that hard as information_schema already knows which fields are accessed in the query. This is already used to decide if we can solve the information_schema access without opening the table. This should be done against the bb-10.2-ext tree, which has the new MAX_MEMORY_USED column in information_schema.processlist. |
This task is about improving memory utilization and performance for
Information schema Some work has recently been done in bb-10.2-ext to free memory early for tables and views used be performance schema. The next step is to create more efficient temporary tables that doesn't store information that we don't need. {noformat} MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 245768 | +-------------+-----------------+ 1 row in set (0.00 sec) MariaDB [test]> select table_name from information_schema.tables where table_schema="mysql"; .... MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 696880 | +-------------+-----------------+ {noformat} Here we used 600K memory for a simple query {noformat} MariaDB [test]> select count(*) from information_schema.tables where table_schema="mysql"; MariaDB [test]> select table_name from information_schema.tables; ... MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test"; +-------------+-----------------+ | MEMORY_USED | MAX_MEMORY_USED | +-------------+-----------------+ | 86120 | 5293216 | +-------------+-----------------+ {noformat} Here we used 5M memory for a simple query over 341 tables. The reason for the excessive memory used comes from that the temporary table created has a very wide record: While running: {code:sql} select table_name from information_schema.tables; {code} in gdb: {noformat} (gdb) break handler::ha_write_tmp_row (gdb) p table->s->reclength $2 = 14829 {noformat} Two possible ways to fix this: 1) Extend heap tables to store VARCHAR and BLOB efficiently 2) In sql_show, change all fields that are not used to be CHAR(1) 1) is a major tasks and we can't get that done in time for 10.3 2) will help even if we do 1) as we have less to store. This task is to do 2) This should not be that hard as information_schema already knows which fields are accessed in the query. This is already used to decide if we can solve the information_schema access without opening the table. This should be done against the bb-10.2-ext tree, which has the new MAX_MEMORY_USED column in information_schema.processlist. |
| Fix Version/s | 10.3 [ 22126 ] |
| Assignee | Sergey Vojtovich [ svoj ] |
| Labels | contribution foundation |
| Fix Version/s | 10.4 [ 22408 ] |
| Assignee | Sergey Vojtovich [ svoj ] | Michael Widenius [ monty ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Attachment | show.diff [ 45401 ] |
| Attachment | show.diff [ 45401 ] |
| Attachment | improve_memory-final.diff [ 45402 ] |
| issue.field.resolutiondate | 2018-04-05 11:12:09.0 | 2018-04-05 11:12:09.849 |
| Component/s | N/A [ 14411 ] | |
| Component/s | Admin statements [ 11400 ] | |
| Fix Version/s | 10.3.6 [ 23003 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Component/s | Information Schema [ 14413 ] | |
| Component/s | N/A [ 14411 ] |
| Support case ID | not-26307 |
| NRE Projects | AC-2610/ADDITIONAL |
| Workflow | MariaDB v3 [ 83612 ] | MariaDB v4 [ 133385 ] |