MySQL: How turning off the logging affect the performance

MySQLmysql-5.1performanceperformance-tuning

OS: RHEL5
DB: MySQL 5.1.73 community edition
DB size: ~1GB

I have setup a testing environment and turned off all logging – general logs, binary logs, slow query logs etc.

I am using Oracle application testing suite (OATS) to run the load test. I am getting poor load test result (higher timing and less passed transactions) after logging off. Ideally I should get better result.

  • Is it correct or I am missing something?
  • What is the affect of getting log off?

Below is the result of 

SELECT * FROM information_schema.global_variables WHERE VARIABLE_Name like 'log\_%' OR variable_name like '%\_log'

gives me:

+---------------------------------+----------------------------+
| VARIABLE_NAME                   | VARIABLE_VALUE             |
+---------------------------------+----------------------------+
| BACK_LOG                        | 50                         |
| LOG_BIN_TRUST_ROUTINE_CREATORS  | OFF                        |
| LOG_WARNINGS                    | 1                          |
| LOG_OUTPUT                      | FILE                       |
| LOG_QUERIES_NOT_USING_INDEXES   | OFF                        |
| LOG_ERROR                       | /var/lib/mysql/rpc4939.err |
| GENERAL_LOG                     | OFF                        |
| LOG_BIN                         | OFF                        |
| RELAY_LOG                       |                            |
| LOG_SLOW_QUERIES                | ON                         |
| SLOW_QUERY_LOG                  | ON                         |
| LOG_BIN_TRUST_FUNCTION_CREATORS | OFF                        |
| LOG_SLAVE_UPDATES               | OFF                        |
+---------------------------------+----------------------------+

Best Answer

Except for the general log and the slow log with very small long_query_time, performance shouldn't get affected too much when on, as most logging should be written to the filesystem cache (those logs do not need to be transactional safe).

There are certain combination of configuration and workloads that certainly can affect negatively performance:

  • example: binary log with binlog_sync and no group commit on a heavy-write server
  • binlog disallows group commit for innodb in earlier versions of mysql
  • huge transactions altering blobs in row-based replication
  • workload is mostly very fast primary key lookups, que log those queries in text (a read generates a write)
  • Error log constantly outputing critical errors with GBs of information

But in general, with a health filesystem cache (not too much memory pressure), things should be ok (small to almost negible amount of overhead, or admissible thanks to the extra features that they provide). For example, even the binary log added, let's say, a 5% overhead, it allows read scales through replication and point in time recovery.

What should not happen is that disabling them reduces performance. Either that is an error of measure or it is provoked by something else.

Also, for future readers, be careful not to confuse "server logs" (error, general, slow, binary) with the InnoDB transaction log.

Related Question