Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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':

+---------------------------------+----------------------------+
| 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                        |
+---------------------------------+----------------------------+
share|improve this question
    
I know this sounds silly, but could you comment the process you followed for disabling the logs, show any warnings on the error log and the output of SELECT * FROM information_schema.global_variables WHERE VARIABLE_Ne 'log\_%' OR variable_name like '%\_log';. Just to be sure. –  jynus Jul 30 at 14:44
    
@jynus: I have updated my answer. –  ursitesion Jul 30 at 14:49
    
The slow query log is on. It probably won't change anything, but just to start with correct assumptions. –  jynus Jul 30 at 14:52

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.