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.