List:General Discussion« Previous MessageNext Message »
From:spameden Date:October 16 2012 12:42pm
Subject:Re: mysql logs query with indexes used to the slow-log and not
logging if there is index in reverse order
View as plain text  
Will do.

mysql>  SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name                           | Value
|
+-----------------------------------------+---------------------------------+
| back_log                                | 50
|
| binlog_cache_size                       | 32768
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format                           | MIXED
|
| expire_logs_days                        | 5
|
| general_log                             | OFF
|
| general_log_file                        | /var/run/mysqld/mysqld.log
|
| innodb_flush_log_at_trx_commit          | 2
|
| innodb_flush_log_at_trx_commit_session  | 3
|
| innodb_locks_unsafe_for_binlog          | OFF
|
| innodb_log_block_size                   | 512
|
| innodb_log_buffer_size                  | 8388608
|
| innodb_log_file_size                    | 2145386496
|
| innodb_log_files_in_group               | 2
|
| innodb_log_group_home_dir               | ./
|
| innodb_mirrored_log_groups              | 1
|
| innodb_overwrite_relay_log_info         | OFF
|
| log                                     | OFF
|
| log_bin                                 | ON
|
| log_bin_trust_function_creators         | ON
|
| log_bin_trust_routine_creators          | ON
|
| log_error                               | /var/log/mysql-error.log
|
| log_output                              | FILE
|
| log_queries_not_using_indexes           | ON
|
| log_slave_updates                       | OFF
|
| log_slow_admin_statements               | OFF
|
| log_slow_filter                         |
|
| log_slow_queries                        | ON
|
| log_slow_rate_limit                     | 1
|
| log_slow_slave_statements               | OFF
|
| log_slow_sp_statements                  | ON
|
| log_slow_timestamp_every                | OFF
|
| log_slow_verbosity                      | microtime
|
| log_warnings                            | 1
|
| max_binlog_cache_size                   | 18446744073709547520
|
| max_binlog_size                         | 104857600
|
| max_relay_log_size                      | 0
|
| relay_log                               | /var/log/mysql/mysqld-relay-bin
|
| relay_log_index                         |
|
| relay_log_info_file                     | relay-log.info
|
| relay_log_purge                         | ON
|
| relay_log_space_limit                   | 0
|
| slow_query_log                          | ON
|
| slow_query_log_file                     | /var/log/mysql/mysql-slow.log
|
| slow_query_log_microseconds_timestamp   | OFF
|
| sql_log_bin                             | ON
|
| sql_log_off                             | OFF
|
| sql_log_update                          | ON
|
| suppress_log_warning_1592               | OFF
|
| sync_binlog                             | 0
|
| use_global_log_slow_control             | none
|
+-----------------------------------------+---------------------------------+
51 rows in set (0.01 sec)

Here is full output, but writing happens ONLY if
log_queries_not_using_indexes turned ON.

Query takes:
# Query_time: 0.291280  Lock_time: 0.000050  Rows_sent: 0  Rows_examined:
133876  Rows_affected: 0  Rows_read: 1
# Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: F229398
SET timestamp=1350389078;
SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
service, account, id, sms_type, mclass, mwi, coding, compress, validity,
deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
WHERE time <= UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;


2012/10/16 Shawn Green <shawn.l.green@stripped>

> On 10/15/2012 7:15 PM, spameden wrote:
>
>> Thanks a lot for all your comments!
>>
>> I did disable Query cache before testing with
>>
>> set query_cache_type=OFF
>>
>> for the current session.
>>
>> I will report this to the MySQL bugs site later.
>>
>>
>>
> First. What are all of your logging settings?
>
>     SHOW GLOBAL VARIABLES LIKE '%log%';
>
> Next. When you physically look in the slow query log, how long does it say
> that it took this command to execute?
>
> And last, before you can ask MySQL to fix a bug, you must first ensure
> it's a MySQL bug. Please try to reproduce your results using official
> binaries, not those constructed by a third party.  If the problem exists in
> our packages, do tell us about it. If the problem is not reproducible using
> official MySQL products, then please report it to the appropriate channel
> for the product you are using.
>
> MySQL Bugs -
> http://bugs.mysql.com/
>
> Thanks!
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

Thread
mysql logs query with indexes used to the slow-log and not logging ifthere is index in reverse orderspameden15 Oct
  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
  • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct
    • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
      • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
        • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct
          • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden15 Oct
            • Re: mysql logs query with indexes used to the slow-log and not loggingif there is index in reverse orderShawn Green16 Oct
              • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
                • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderMichael Dykman16 Oct
                  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
                  • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderhsv16 Oct
                    • Re: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderspameden16 Oct
      • RE: mysql logs query with indexes used to the slow-log and notlogging if there is index in reverse orderRick James15 Oct