MySQL Forums
Forum List  »  InnoDB

Cache parameters and select queries
Posted by: Antoine SAOUI
Date: July 17, 2018 04:10AM

Hi all,

We are experiencing a very strange behavior on a replicated Master/Slave My SQL 5.7 width InnoDB engine over Linux.
The database is small less than 300 Mo with very low loading, only some requests per second.

This is our simple case:
- We use a unique database user which have only two privileges: SELECT TABLE and SELECT COLUMN.
- We launch a java batch application: starts, selects tens of records on many relational tables
- The batch stops after some minutes.
- Another web application accesses the same tables for selecting these records.

Result:
- The selected records by the first application are no longer reachable by the second application.
- All other records not selected before still being reachable!


We find the solution, we have disabled these cache parameters and it works:
#query_cache_size = 256M
#query_cache_type = 1
#query_cache_limit = 2M

But still have no explanation why it solved the problem!
Why user with only select privilege is impacting the cache behavior?


Any help,

kind regards.

Antoine

#################################################
[mysqld12]
report-host = AAA.AAA.AAA.AAA
report-port = AAAA
user = AAA

event-scheduler = 1
net_read_timeout = 300
datadir = /app/list/AAA/socles/mysql/data/AAA
port = 3320
socket = /app/list/AAA/socles/mysql/sockets/AAA.sock
pid-file = /app/list/AAA/socles/mysql/sockets/AAA.pid
back_log = 50
skip-external-locking
tmpdir = /app/list/AAA/socles/mysql/tmp/AAA
plugin_dir = /usr/lib64/mysql/plugin

####################
##MySQL error logs##
####################
log-error = /app/list/AAA/socles/mysql/log/AAA/AAA-error.log
slow_query_log = 1
long_query_time = 5
slow_query_log_file = /app/list/AAA/socles/mysql/log/AAA/AAA-slow.log

#####################
##General Query Log##
#####################
general_log = 0
general_log_file = /app/list/AAA/socles/mysql/log/AAA/AAA-general.log

##########
##Buffer##
##########
max_connections = 200
key_buffer_size = 64M
max_allowed_packet = 64M
table_open_cache = 4096
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
thread_stack = 182K
thread_cache_size = 500

##########################################################
# THE SOLUTION
##########################################################
#query_cache_size = 256M
#query_cache_type = 1
#query_cache_limit = 2M
##########################################################

tmp_table_size = 36M
max_heap_table_size = 36M
join_buffer_size = 4M
explicit_defaults_for_timestamp = TRUE

##############
##Binary Log##
##############
server-id = 12000
binlog_cache_size = 100M
max_binlog_size = 100M
log-bin = /app/list/AAA/socles/mysql/log/AAA /AAA_bin.log
log-bin-index = /app/list/AAA/socles/mysql/log/AAA/AAA_bin.index
binlog-format = MIXED
sync-binlog = 1
expire_logs_days = 3
master_verify_checksum = 1
log_slave_updates = 1
binlog_checksum = CRC32

#####################################
##Relay log SLAVE ##
#####################################
relay-log = /app/list/AAA/socles/mysql/log/AAA/AAA-relay.log
relay-log-purge = 1
relay-log-space-limit = 5G
#read_only = 0
slave_sql_verify_checksum = 1
relay_log_recovery = 1
slave_parallel_workers = 4
slave_checkpoint_group = 32

##########
## GTID ##
##########
gtid_mode = ON
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1

##########
##InnoDB##
##########
innodb_file_per_table
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 6
innodb_log_file_size = 100M
innodb_log_buffer_size = 100M

innodb_buffer_pool_size = 128M

innodb_open_files = 1024
innodb_file_format = Barracuda
innodb_autoextend_increment = 6
#innodb_file_io_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

Options: ReplyQuote


Subject
Views
Written By
Posted
Cache parameters and select queries
182
July 17, 2018 04:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.