[100 Days Check-in 004] Enable System Variable tidb_general_log for Real-time SQL Tracking

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【百天打卡004】开启系统变量tidb_general_log，实时跟踪sql

| username: Kongdom

Learning and practicing from time to time, isn’t it a pleasure~

Sharing a system variable that achieves a similar trace statement function as SQL Server. Once enabled, you can track data in real-time without needing to use slow queries to trace statements anymore~

tidb_general_log

  • Scope: GLOBAL
  • Persisted to the cluster: No, only applies to the current TiDB instance connection
  • Default value: OFF
  • This variable is used to set whether to record all SQL statements in the log. This feature is disabled by default. If system administrators need to trace all SQL records during troubleshooting, they can consider enabling this feature.
  • When the TiDB configuration item log.level is set to "info" or "debug", you can locate all records of this feature in the log by querying the "GENERAL_LOG" string. The log will record the following content:
    • conn: ID corresponding to the current session
    • user: Current session user
    • schemaVersion: Current schema version
    • txnStartTS: Start timestamp of the current transaction
    • forUpdateTS: Current timestamp of the SQL statement when the transaction mode is pessimistic. If a write conflict occurs within a pessimistic transaction, the current execution statement will be retried, and this timestamp will be updated. The number of retries is configured by max-retry-count. When the transaction mode is optimistic, this entry is equivalent to txnStartTS.
    • isReadConsistency: Whether the current transaction isolation level is Read Committed (RC)
    • current_db: Current database name
    • txn_mode: Transaction mode. Options: OPTIMISTIC (optimistic transaction mode) or PESSIMISTIC (pessimistic transaction mode)
    • sql: SQL statement corresponding to the current query
2
| username: tidb菜鸟一只 | Original post link

Check-in, check-in.

3
| username: magic | Original post link

Is there a significant performance loss?

4
| username: Kongdom | Original post link

It depends on the business volume and the read/write capability of the hard disk, generally it is enabled for a short period.

5
| username: Billmay表妹 | Original post link

This is awesome!

6
| username: Kongdom | Original post link

:grin: :grin: :grin:

7
| username: xfworld | Original post link

Posted a bit too late~~ Feels like it might break the streak.

8
| username: Kongdom | Original post link

:rofl: :rofl: :rofl:

9
| username: 人如其名 | Original post link

It should be around 10%-15%. Actually, I feel it would be better to set the slow log to 0 during that time, as the slow log provides more information. tidb_slow_log_threshold=0. Testing shows that the impact of slow logs and general logs on system performance is similar (enabling general logs in MySQL causes a sharp decline in performance).