- Veritabanının davranışını okumamızı sağlayan değişkenler (status)
- Veritabanının davranışını değiştirmemizi sağlayan değişkenler (variable)
Yine bu değişkenler kendi içinde global ve session olmak üzere iki farklı gruba ayrılırlar. Bu değişkenler INFORMATION_SCHEMA veritabanında tablolarda yer alır:
MariaDB [(safirdepo)]> show tables from information_schema like '%STATUS%';
+-----------------------------------------+
| Tables_in_information_schema (%STATUS%) |
+-----------------------------------------+
| GLOBAL_STATUS |
| SESSION_STATUS |
+-----------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(safirdepo)]> show tables from information_schema like '%VARIAB%';
+-----------------------------------------+
| Tables_in_information_schema (%VARIAB%) |
+-----------------------------------------+
| GLOBAL_VARIABLES |
| SESSION_VARIABLES |
| SYSTEM_VARIABLES |
+-----------------------------------------+
3 rows in set (0.00 sec)
Bu değişkenlerin değerini okumak için yukarıda listelenen tabloları SELECT ile sorgulamak yerine kısayolu olan show komutundan yararlanırız:
MariaDB [(safirdepo)]> show global status like 'ROWS_SENT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Rows_sent | 1725 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> show session status like 'ROWS_SENT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Rows_sent | 1 |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Rows_sent | 1725 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> show session status like 'ROWS_SENT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Rows_sent | 1 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.GLOBAL_STATUS;
+----------+
| count(*) |
+----------+
| 516 |
+----------+
1 row in set (0.01 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.SESSION_STATUS;
+----------+
| count(*) |
+----------+
| 516 |
+----------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.SESSION_VARIABLES;
+----------+
| count(*) |
+----------+
| 605 |
+----------+
1 row in set (0.01 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.GLOBAL_VARIABLES;
+----------+
| count(*) |
+----------+
| 586 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 516 |
+----------+
1 row in set (0.01 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.SESSION_STATUS;
+----------+
| count(*) |
+----------+
| 516 |
+----------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.SESSION_VARIABLES;
+----------+
| count(*) |
+----------+
| 605 |
+----------+
1 row in set (0.01 sec)
MariaDB [(safirdepo)]> select count(*) from INFORMATION_SCHEMA.GLOBAL_VARIABLES;
+----------+
| count(*) |
+----------+
| 586 |
+----------+
1 row in set (0.00 sec)
Değişkenlerin değerini değiştirmek için set komutundan yararlanıyoruz:
MariaDB [(safirdepo)]> set session SQL_MODE = 'TRADITIONAL';Query OK, 0 rows affected (0.00 sec)
MariaDB [(safirdepo)]> select * from INFORMATION_SCHEMA.SESSION_VARIABLES where variable_name like '%MODE%';
+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| WSREP_GTID_MODE | OFF |
| SQL_MODE | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| INNODB_STRICT_MODE | OFF |
| INNODB_AUTOINC_LOCK_MODE | 1 |
| PSEUDO_SLAVE_MODE | OFF |
| SLAVE_DDL_EXEC_MODE | IDEMPOTENT |
| SLAVE_EXEC_MODE | STRICT |
| GTID_STRICT_MODE | OFF |
| OLD_MODE | |
| SLAVE_PARALLEL_MODE | conservative |
+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
MariaDB [(safirdepo)]> set session SQL_MODE = 'TRADITIONAL';Query OK, 0 rows affected (0.00 sec)
MariaDB [(safirdepo)]> select @@SQL_MODE;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@SQL_MODE |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(safirdepo)]> select @@session.SQL_MODE;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.SQL_MODE |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MariaDB [(nsafirdepoone)]> select @@global.SQL_MODE;
+--------------------------------------------+
| @@global.SQL_MODE |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.01 sec)
Değişkenlerin bir kısmını canlı sistemde değiştirmek mümkün değildir:
mysql> set global innodb_log_file_size=5000;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable
Üretim ortamında tavsiye etmemekle beraber değişkenlerin bazıları doğrudan bellek gözüne erişerek değiştirmek mümkündür. Aşağıdaki örnekte tüm bağlantıların dolu olduğu bir MySQL sunucusunda GNU hata ayıklayıcısı (=debugger) kullanılarak max_connections değişkeni değiştiriliyor:
[centos@server1 ~]$ mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.11-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show global variables like 'max_c%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
[centos@server1 ~]$ sudo gdb -p 1269 -ex "set max_connections=4096" -batch
[sudo] password for centos:
[New LWP 1444]
[New LWP 1443]
[New LWP 1442]
[New LWP 1441]
[New LWP 1440]
[New LWP 1439]
[New LWP 1438]
[New LWP 1436]
[New LWP 1435]
[New LWP 1434]
[New LWP 1433]
[New LWP 1432]
[New LWP 1405]
[New LWP 1404]
[New LWP 1403]
[New LWP 1402]
[New LWP 1401]
[New LWP 1400]
[New LWP 1399]
[New LWP 1398]
[New LWP 1397]
[New LWP 1396]
[New LWP 1367]
[New LWP 1314]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00007f71a9ea464d in poll () from /lib64/libc.so.6
[centos@server1 ~]$ mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.11-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show global variables like 'max_c%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 4096 |
+--------------------+-------+
2 rows in set (0.00 sec)
No comments:
Post a Comment