MYSQL sleep 상태 발생시 확인
페이지 정보
본문
set-variable = interactive_timeout=144000
set-variable = wait_timeout=144000
set-variable = max_allowed_packet=2M
등. ^^
sleep 상태에서 시간이 올라가는건, db 를 사용하는 어플리케이션에서 mysql 접속을 한 뒤에 접속세션을 종료하지 않고 그냥 놔두면 발생. 어플리케이션에서 db작업후 db 와의 연결관리를 확인해봐야 할듯.
아니면 무식한 방법으로는 my.cnf 에서 wait_timeout 을 줄여버리세요.
wait_timeout 이상 sleep 상태일 경우에는 접속을 끊어버림
# top
9:11pm up 2 days, 10:39, 1 user, load average: 8.78, 11.72, 11.68
63 processes: 51 sleeping, 12 running, 0 zombie, 0 stopped
CPU0 states: 39.0% user, 60.4% system, 0.0% nice, 0.0% idle
CPU1 states: 38.0% user, 61.4% system, 0.0% nice, 0.0% idle
CPU2 states: 45.3% user, 54.1% system, 0.0% nice, 0.0% idle
CPU3 states: 36.1% user, 63.3% system, 0.0% nice, 0.0% idle
Mem: 2068520K av, 1512880K used, 555640K free, 0K shrd, 90436K buff
Swap: 2097136K av, 0K used, 2097136K free 1225032K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
593 mysql 9 0 98.6M 98M 2972 S 99.9 4.8 7:49 mysqld
우선 저 cpu 가 mysql 에서 점유되는건지, 다른 프로세스가 점유하는지를 알아야 겠네요. mysql 이 점유한다고 가정했을때,
system 이 많이 먹힌다면, IO 나 네트웍 쪽일텐데, 아마 IO 쪽이 문제가 될거 같네요. 쿼리를 좀 봐야할거 같습니다.
분명 서버가 저렇게 버벅댄다면,(근데 실제로 버벅대나요?) 분명 쿼리들이 slow log 로 들어갈거 같네요. 그럼 그 쿼리들을 보고 분셕해서 문제를 수정하면 될거 같습니다.
근데 저정도 서버사양에 query/s 가 50 밖에 안나오면서 CPU 가 저렇게 올라간다는건 확실히 문제가 있다는 겁니다.-_-;; slow query 들을 explain 등을 써서 분석해보세요.
[sdf@dsf#]top
4032 mysql 15 0 150M 150M 2984 S 99.9 7.4 18:53 mysqld
.
.
위에서 4032 pid(MySQL프로세서) 에 cpu 99.9% 사용중이란 의미니깐
[sdf@dsf#]lsof -p 4032
4032 pid 가 실행하는 파일들이 출력될겁니다.
출력되는 DB 중에서 부하가 걸리는것 같습니다.
만약에 웹스크립트에서 쿼리를 본의아니게 무한루프가 실행되는
스크립트가 실행되도 저럴수 있구... 조인문을 잘못 써도 그럴수있고..
아묻튼...저런씩으로 찾아가셔서...찾아보시길...
top -d10 한 화면입니다.
4:16pm up 332 days, 9:09, 1 user, load average: 2.43, 1.89, 1.58
220 processes: 203 sleeping, 17 running, 0 zombie, 0 stopped
CPU states: 0.1% user, 0.0% system, 0.0% nice, 0.0% idle
Mem: 1290004K av, 1187472K used, 102532K free, 0K shrd, 129552K buff
Swap: 522072K av, 29500K used, 492572K free 529592K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
12933 mysql 17 0 36132 18M 1272 R 11.1 1.4 0:00 mysqld
12413 nobody 15 0 3700 3644 2280 S 7.9 0.2 0:00 httpd
12541 nobody 16 0 4320 4268 2280 R 7.9 0.3 0:00 httpd
11985 nobody 15 0 4624 4572 2284 S 7.1 0.3 0:00 httpd
11989 nobody 16 0 3736 3684 2280 R 5.5 0.2 0:00 httpd
543 nobody 15 0 3716 3668 2320 R 3.9 0.2 0:04 httpd
12376 nobody 15 0 4480 4428 2292 S 3.9 0.3 0:00 httpd
31796 nobody 16 0 4824 4784 2380 S 3.1 0.3 0:40 httpd
28159 nobody 16 0 4152 4104 2300 S 3.1 0.3 0:09 httpd
12399 nobody 16 0 3980 3928 2300 R 3.1 0.3 0:00 httpd
12947 root 16 0 1152 1152 848 R 3.1 0.0 0:00 top
12545 nobody 15 0 4300 4248 2224 S 2.3 0.3 0:00 httpd
4820 nobody 15 0 4268 4220 2388 S 1.5 0.3 0:38 httpd
8311 nobody 15 0 4076 4024 2320 S 1.5 0.3 0:00 httpd
12387 nobody 15 0 3776 3724 2304 S 1.5 0.2 0:00 httpd
12956 mysql 16 0 36132 18M 1272 S 1.5 1.4 0:00 mysqld
12960 mysql 15 0 36132 18M 1272 S 1.5 1.4 0:00 mysqld
17286 nobody 15 0 3960 3836 2448 S 0.7 0.2 14:40 httpd
[lalala@ns mysql]# /home/mysql/bin/mysqladmin -u root -p status
Enter password:
Uptime: 34454 Threads: 1 Questions: 3085379 Slow queries: 1 Opens: 194 Flus
h tables: 1 Open tables: 184 Queries per second avg: 89.551
[lalala@ns mysql]# /home/mysql/bin/mysqladmin -u root -p extended-status
Enter password:
튜닝은 일단 쿼리부터 시작하는 것이 좋습니다.
현재 slow-query는 10초 이상 걸리는 쿼리가 로그에 남도록 되어 있습니다. 이 시간을 1초로 줄인 뒤에 로그를 남겨 보시구요.
또한 페이지 로딩이 느려지는 상황에서 SHOW PROCESSLIST; 결과를 보시는 것도 도움이 될 것입니다.
slow log 남기기
설정한 것은 my-large.cnf을 /etc/my.cnf로 저장하고
하단부분에다가 아래 부분을 추가해 주었습니다.
[mysqld]
log_slow_queries=/var/lib/mysql/slow-queries.log
long_query_time=1
경로 log 파일이 쌓이는 경로는 맞고요..
log_slow_queries = ON
log-slow-queries = /data/database/slow-query.log
long_query_time = 10
튜닝
혹시나 하고 적으니 혹시나 하고 읽으시길 바랍니다. ^^;
평균 connections 당 8개의 쿼리를 날렸다는 얘기가 되겠죠.
최대 400 connections 부분이 많이 걸립니다.
- 동시접속자수를 정확하게 측정할 필요가 있습니다.
http://database.sarang.net/?inc=read&criteria=mysql&subcrit=columns&aid=16454
- MySQL For over 500 Connections
http://www.oops.org/?t=lecture&sb=mysql&n=3
- 서버상태를 모니터링에 도움을
http://linuxchannel.net/?vhost=mysql
SELECT CONNECTION_ID();
13.8.3 Information Functions
BENCHMARK(count,expr)
The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is from within the mysql client, which reports query execution times: mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)
The time reported is elapsed time on the client end, not CPU time on the server end. It is advisable to execute BENCHMARK() several times, and to interpret the result with regard to how heavily loaded the server machine is.
CHARSET(str)
Returns the character set of the string argument. mysql> SELECT CHARSET('abc');
-> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
-> 'utf8'
mysql> SELECT CHARSET(USER());
-> 'utf8'
CHARSET() was added in MySQL 4.1.0.
COERCIBILITY(str)
Returns the collation coercibility value of the string argument. mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY('abc');
-> 3
mysql> SELECT COERCIBILITY(USER());
-> 2
The return values have the following meanings: Coercibility Meaning
0 Explicit collation
1 No collation
2 Implicit collation
3 Coercible
Lower values have higher precedence. COERCIBILITY() was added in MySQL 4.1.1.
COLLATION(str)
Returns the collation for the character set of the string argument. mysql> SELECT COLLATION('abc');
-> 'latin1_swedish_ci'
mysql> SELECT COLLATION(_utf8'abc');
-> 'utf8_general_ci'
COLLATION() was added in MySQL 4.1.0.
CONNECTION_ID()
Returns the connection ID (thread ID) for the connection. Every connection has its own unique ID. mysql> SELECT CONNECTION_ID();
-> 23786
CONNECTION_ID() was added in MySQL 3.23.14.
CURRENT_USER()
Returns the username and hostname combination that the current session was authenticated as. This value corresponds to the MySQL account that determines your access privileges. It can be different from the value of USER(). mysql> SELECT USER();
-> 'davida@localhost'
mysql> SELECT * FROM mysql.user;
ERROR 1044: Access denied for user ''@'localhost' to
database 'mysql'
mysql> SELECT CURRENT_USER();
-> '@localhost'
The example illustrates that although the client specified a username of davida (as indicated by the value of the USER() function), the server authenticated the client using an anonymous user account (as seen by the empty username part of the CURRENT_USER() value). One way this might occur is that there is no account listed in the grant tables for davida. CURRENT_USER() was added in MySQL 4.0.6.
DATABASE()
Returns the default (current) database name. mysql> SELECT DATABASE();
-> 'test'
If there is no default database, DATABASE() returns NULL as of MySQL 4.1.1, and the empty string before that.
FOUND_ROWS()
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To get this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, then invoke FOUND_ROWS() afterward: mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. (If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not.) Note that if you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client. SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result. The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole. The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:
The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL 4.0.0.
LAST_INSERT_ID()
LAST_INSERT_ID(expr)
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. If you use INSERT IGNORE and the record is ignored, the AUTO_INCREMENT counter still is incremented and LAST_INSERT_ID() returns the new value. If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:
Create a table to hold the sequence counter and initialize it: mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Use the table to generate sequence numbers like this: mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See section 21.2.3.32 mysql_insert_id().
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values. Note that mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.
ROW_COUNT()
ROW_COUNT() returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function. mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
ROW_COUNT() was added in MySQL 5.0.1.
SESSION_USER()
SESSION_USER() is a synonym for USER().
SYSTEM_USER()
SYSTEM_USER() is a synonym for USER().
USER()
Returns the current MySQL username and hostname. mysql> SELECT USER();
-> 'davida@localhost'
The value indicates the username you specified when connecting to the server, and the client host from which you connected. The value can be different than that of CURRENT_USER(). Prior to MySQL 3.22.11, the function value does not include the client hostname. You can extract just the username part, regardless of whether the value includes a hostname part, like this: mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
-> 'davida'
As of MySQL 4.1, USER() returns a value in the utf8 character set, so you should also make sure that the '@' string literal is interpreted in that character set: mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
-> 'davida'
VERSION()
Returns a string that indicates the MySQL server version. mysql> SELECT VERSION();
-> '4.1.3-beta-log'
Note that if your version string ends with -log this means that logging is enabled.
###############
출처 : http://test.forbbs.net/board/zboard.php?id=tiptech&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=211
set-variable = wait_timeout=144000
set-variable = max_allowed_packet=2M
등. ^^
sleep 상태에서 시간이 올라가는건, db 를 사용하는 어플리케이션에서 mysql 접속을 한 뒤에 접속세션을 종료하지 않고 그냥 놔두면 발생. 어플리케이션에서 db작업후 db 와의 연결관리를 확인해봐야 할듯.
아니면 무식한 방법으로는 my.cnf 에서 wait_timeout 을 줄여버리세요.
wait_timeout 이상 sleep 상태일 경우에는 접속을 끊어버림
# top
9:11pm up 2 days, 10:39, 1 user, load average: 8.78, 11.72, 11.68
63 processes: 51 sleeping, 12 running, 0 zombie, 0 stopped
CPU0 states: 39.0% user, 60.4% system, 0.0% nice, 0.0% idle
CPU1 states: 38.0% user, 61.4% system, 0.0% nice, 0.0% idle
CPU2 states: 45.3% user, 54.1% system, 0.0% nice, 0.0% idle
CPU3 states: 36.1% user, 63.3% system, 0.0% nice, 0.0% idle
Mem: 2068520K av, 1512880K used, 555640K free, 0K shrd, 90436K buff
Swap: 2097136K av, 0K used, 2097136K free 1225032K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
593 mysql 9 0 98.6M 98M 2972 S 99.9 4.8 7:49 mysqld
우선 저 cpu 가 mysql 에서 점유되는건지, 다른 프로세스가 점유하는지를 알아야 겠네요. mysql 이 점유한다고 가정했을때,
system 이 많이 먹힌다면, IO 나 네트웍 쪽일텐데, 아마 IO 쪽이 문제가 될거 같네요. 쿼리를 좀 봐야할거 같습니다.
분명 서버가 저렇게 버벅댄다면,(근데 실제로 버벅대나요?) 분명 쿼리들이 slow log 로 들어갈거 같네요. 그럼 그 쿼리들을 보고 분셕해서 문제를 수정하면 될거 같습니다.
근데 저정도 서버사양에 query/s 가 50 밖에 안나오면서 CPU 가 저렇게 올라간다는건 확실히 문제가 있다는 겁니다.-_-;; slow query 들을 explain 등을 써서 분석해보세요.
[sdf@dsf#]top
4032 mysql 15 0 150M 150M 2984 S 99.9 7.4 18:53 mysqld
.
.
위에서 4032 pid(MySQL프로세서) 에 cpu 99.9% 사용중이란 의미니깐
[sdf@dsf#]lsof -p 4032
4032 pid 가 실행하는 파일들이 출력될겁니다.
출력되는 DB 중에서 부하가 걸리는것 같습니다.
만약에 웹스크립트에서 쿼리를 본의아니게 무한루프가 실행되는
스크립트가 실행되도 저럴수 있구... 조인문을 잘못 써도 그럴수있고..
아묻튼...저런씩으로 찾아가셔서...찾아보시길...
top -d10 한 화면입니다.
4:16pm up 332 days, 9:09, 1 user, load average: 2.43, 1.89, 1.58
220 processes: 203 sleeping, 17 running, 0 zombie, 0 stopped
CPU states: 0.1% user, 0.0% system, 0.0% nice, 0.0% idle
Mem: 1290004K av, 1187472K used, 102532K free, 0K shrd, 129552K buff
Swap: 522072K av, 29500K used, 492572K free 529592K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
12933 mysql 17 0 36132 18M 1272 R 11.1 1.4 0:00 mysqld
12413 nobody 15 0 3700 3644 2280 S 7.9 0.2 0:00 httpd
12541 nobody 16 0 4320 4268 2280 R 7.9 0.3 0:00 httpd
11985 nobody 15 0 4624 4572 2284 S 7.1 0.3 0:00 httpd
11989 nobody 16 0 3736 3684 2280 R 5.5 0.2 0:00 httpd
543 nobody 15 0 3716 3668 2320 R 3.9 0.2 0:04 httpd
12376 nobody 15 0 4480 4428 2292 S 3.9 0.3 0:00 httpd
31796 nobody 16 0 4824 4784 2380 S 3.1 0.3 0:40 httpd
28159 nobody 16 0 4152 4104 2300 S 3.1 0.3 0:09 httpd
12399 nobody 16 0 3980 3928 2300 R 3.1 0.3 0:00 httpd
12947 root 16 0 1152 1152 848 R 3.1 0.0 0:00 top
12545 nobody 15 0 4300 4248 2224 S 2.3 0.3 0:00 httpd
4820 nobody 15 0 4268 4220 2388 S 1.5 0.3 0:38 httpd
8311 nobody 15 0 4076 4024 2320 S 1.5 0.3 0:00 httpd
12387 nobody 15 0 3776 3724 2304 S 1.5 0.2 0:00 httpd
12956 mysql 16 0 36132 18M 1272 S 1.5 1.4 0:00 mysqld
12960 mysql 15 0 36132 18M 1272 S 1.5 1.4 0:00 mysqld
17286 nobody 15 0 3960 3836 2448 S 0.7 0.2 14:40 httpd
[lalala@ns mysql]# /home/mysql/bin/mysqladmin -u root -p status
Enter password:
Uptime: 34454 Threads: 1 Questions: 3085379 Slow queries: 1 Opens: 194 Flus
h tables: 1 Open tables: 184 Queries per second avg: 89.551
[lalala@ns mysql]# /home/mysql/bin/mysqladmin -u root -p extended-status
Enter password:
튜닝은 일단 쿼리부터 시작하는 것이 좋습니다.
현재 slow-query는 10초 이상 걸리는 쿼리가 로그에 남도록 되어 있습니다. 이 시간을 1초로 줄인 뒤에 로그를 남겨 보시구요.
또한 페이지 로딩이 느려지는 상황에서 SHOW PROCESSLIST; 결과를 보시는 것도 도움이 될 것입니다.
slow log 남기기
설정한 것은 my-large.cnf을 /etc/my.cnf로 저장하고
하단부분에다가 아래 부분을 추가해 주었습니다.
[mysqld]
log_slow_queries=/var/lib/mysql/slow-queries.log
long_query_time=1
경로 log 파일이 쌓이는 경로는 맞고요..
log_slow_queries = ON
log-slow-queries = /data/database/slow-query.log
long_query_time = 10
튜닝
혹시나 하고 적으니 혹시나 하고 읽으시길 바랍니다. ^^;
평균 connections 당 8개의 쿼리를 날렸다는 얘기가 되겠죠.
최대 400 connections 부분이 많이 걸립니다.
- 동시접속자수를 정확하게 측정할 필요가 있습니다.
http://database.sarang.net/?inc=read&criteria=mysql&subcrit=columns&aid=16454
- MySQL For over 500 Connections
http://www.oops.org/?t=lecture&sb=mysql&n=3
- 서버상태를 모니터링에 도움을
http://linuxchannel.net/?vhost=mysql
SELECT CONNECTION_ID();
13.8.3 Information Functions
BENCHMARK(count,expr)
The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is from within the mysql client, which reports query execution times: mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)
The time reported is elapsed time on the client end, not CPU time on the server end. It is advisable to execute BENCHMARK() several times, and to interpret the result with regard to how heavily loaded the server machine is.
CHARSET(str)
Returns the character set of the string argument. mysql> SELECT CHARSET('abc');
-> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
-> 'utf8'
mysql> SELECT CHARSET(USER());
-> 'utf8'
CHARSET() was added in MySQL 4.1.0.
COERCIBILITY(str)
Returns the collation coercibility value of the string argument. mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY('abc');
-> 3
mysql> SELECT COERCIBILITY(USER());
-> 2
The return values have the following meanings: Coercibility Meaning
0 Explicit collation
1 No collation
2 Implicit collation
3 Coercible
Lower values have higher precedence. COERCIBILITY() was added in MySQL 4.1.1.
COLLATION(str)
Returns the collation for the character set of the string argument. mysql> SELECT COLLATION('abc');
-> 'latin1_swedish_ci'
mysql> SELECT COLLATION(_utf8'abc');
-> 'utf8_general_ci'
COLLATION() was added in MySQL 4.1.0.
CONNECTION_ID()
Returns the connection ID (thread ID) for the connection. Every connection has its own unique ID. mysql> SELECT CONNECTION_ID();
-> 23786
CONNECTION_ID() was added in MySQL 3.23.14.
CURRENT_USER()
Returns the username and hostname combination that the current session was authenticated as. This value corresponds to the MySQL account that determines your access privileges. It can be different from the value of USER(). mysql> SELECT USER();
-> 'davida@localhost'
mysql> SELECT * FROM mysql.user;
ERROR 1044: Access denied for user ''@'localhost' to
database 'mysql'
mysql> SELECT CURRENT_USER();
-> '@localhost'
The example illustrates that although the client specified a username of davida (as indicated by the value of the USER() function), the server authenticated the client using an anonymous user account (as seen by the empty username part of the CURRENT_USER() value). One way this might occur is that there is no account listed in the grant tables for davida. CURRENT_USER() was added in MySQL 4.0.6.
DATABASE()
Returns the default (current) database name. mysql> SELECT DATABASE();
-> 'test'
If there is no default database, DATABASE() returns NULL as of MySQL 4.1.1, and the empty string before that.
FOUND_ROWS()
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To get this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, then invoke FOUND_ROWS() afterward: mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. (If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not.) Note that if you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client. SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result. The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole. The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:
The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL 4.0.0.
LAST_INSERT_ID()
LAST_INSERT_ID(expr)
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. If you use INSERT IGNORE and the record is ignored, the AUTO_INCREMENT counter still is incremented and LAST_INSERT_ID() returns the new value. If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:
Create a table to hold the sequence counter and initialize it: mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Use the table to generate sequence numbers like this: mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See section 21.2.3.32 mysql_insert_id().
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values. Note that mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.
ROW_COUNT()
ROW_COUNT() returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function. mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
ROW_COUNT() was added in MySQL 5.0.1.
SESSION_USER()
SESSION_USER() is a synonym for USER().
SYSTEM_USER()
SYSTEM_USER() is a synonym for USER().
USER()
Returns the current MySQL username and hostname. mysql> SELECT USER();
-> 'davida@localhost'
The value indicates the username you specified when connecting to the server, and the client host from which you connected. The value can be different than that of CURRENT_USER(). Prior to MySQL 3.22.11, the function value does not include the client hostname. You can extract just the username part, regardless of whether the value includes a hostname part, like this: mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
-> 'davida'
As of MySQL 4.1, USER() returns a value in the utf8 character set, so you should also make sure that the '@' string literal is interpreted in that character set: mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
-> 'davida'
VERSION()
Returns a string that indicates the MySQL server version. mysql> SELECT VERSION();
-> '4.1.3-beta-log'
Note that if your version string ends with -log this means that logging is enabled.
###############
출처 : http://test.forbbs.net/board/zboard.php?id=tiptech&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=211
- 이전글초보도 알아야 할 MySQL 튜닝 18 가지 21.02.10
- 다음글MySQL 명령어좀 알려주세요 21.02.10
댓글목록
등록된 댓글이 없습니다.