X
  • MySQL |
    December 11, 2014

Performance Schema memory tables and rightless users

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| lj@127.0.0.1 | lj@%           |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------+
| Grants for lj@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO 'lj'@'%' |
+--------------------------------+
1 row in set (0.00 sec)


As you see user 'lj'@'%' has single privilege: USAGE. It even does not have read access! Although it still can create temporary tables.

mysql> create temporary table t1(f1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t3 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t4 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t5 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t6 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t7 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t8 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t9 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t0 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0


Nothing wrong with it: current connection uses only 235.34 KiB RAM.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 303
 current_allocated: 235.34 KiB
 current_avg_alloc: 795 bytes
 current_max_alloc: 45.70 KiB
   total_allocated: 2.33 MiB
1 row in set (2.78 sec)


(Note here thread_id is not processlist_id, you should query P_S.threads table to find necessary thread_id)

And even if 'lj'@'%' will run quite complicated JOIN memory usage won't increase much if default options used.

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 322
 current_allocated: 18.23 MiB
 current_avg_alloc: 57.97 KiB
 current_max_alloc: 18.00 MiB
   total_allocated: 20.38 MiB

1 row in set (2.62 sec)

However privilege USAGE allows 'lj'@'%' not only create temporary tables, but change session variables as it wants. For example, increase value of join_buffer_size up to maximum.

mysql> set join_buffer_size=4294967295;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


And with this value we will see completely different picture.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 325
 current_allocated: 36.00 GiB
 current_avg_alloc: 113.43 MiB
 current_max_alloc: 36.00 GiB
   total_allocated: 37.95 GiB

1 row in set (2.70 sec)

36G is huge even in year 2014.

I am happy we finally have instrument which allows to both demonstrate and catch such issues.

Join the discussion

Comments ( 4 )
  • anton Thursday, December 11, 2014

    Good we now can catch such issues! Thanks!


  • guest Monday, December 22, 2014

    select * from sys.memory_by_thread_by_current_bytes where thread_id in(CONNECTION_ID())


  • Sveta Smirnova Monday, December 22, 2014

    @guest

    CONNECTION_ID() returns id of current connection, but we are usually interested in memory usage of another connection.


  • Sveta Smirnova Monday, December 22, 2014

    And to be absolutely pedantic even if you want to examine memory usage of current connection you should write a bit more complicated query:

    mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(select thread_id from performance_schema.threads where processlist_id=CONNECTION_ID())\G

    *************************** 1. row ***************************

    thread_id: 20

    user: root@localhost

    current_count_used: 625

    current_allocated: 1.59 MiB

    current_avg_alloc: 2.60 KiB

    current_max_alloc: 454.50 KiB

    total_allocated: 16.30 MiB

    1 row in set (0.21 sec)

    thread_id != processlist_id


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services