maria-discuss team mailing list archive

[Steve Kelly <s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx>]

Hi Colin 

Thanks for the reply. The older version of mysql was: Ver 14.14 Distrib 5.1.72 

The only difference the join_cache_level seemed to make was when I looked at the Explain, it no longer stated it was using the join buffer. As far as execution time, it was identical. 

I don't really have any Aria settings in my.cnf, it is mainly Innodb that has been tweaked. 

# MariaDB database server configuration file. 
# 
[client] 
port = 3306 
socket = /var/run/mysqld/mysqld.sock 

[mysqld_safe] 
socket = /var/run/mysqld/mysqld.sock 
nice = 0 

[mysqld] 
pid-file = /var/run/mysqld/mysqld.pid 
socket = /var/run/mysqld/mysqld.sock 
port = 3306 
basedir = /usr 
datadir = /var/lib/mysql 
tmpdir = /tmp 
lc_messages_dir = /usr/share/mysql 
lc_messages = en_US 
skip-external-locking 
bind-address = 127.0.0.1 

max_connections = 200 
connect_timeout = 10 
wait_timeout = 180 
max_allowed_packet = 16M 
thread_cache_size = 256 
sort_buffer_size = 256M 
bulk_insert_buffer_size = 16M 
tmp_table_size = 256M 
max_heap_table_size = 256M 
join_buffer_size = 8M 
sort_buffer_size = 8M 

myisam_recover = BACKUP 
key_buffer_size = 128M 
open-files-limit = 2000 
table_open_cache = 4096 
myisam_sort_buffer_size = 512M 
concurrent_insert = 2 
read_buffer_size = 2M 
read_rnd_buffer_size = 1M 
query_cache_limit = 4M 
query_cache_size = 128M 

log_error = /var/log/mysql/error.log 
slow_query_log_file = /var/log/mysql/mariadb-slow. 
log 
long_query_time = 2 
log_slow_verbosity = query_plan 
expire_logs_days = 10 
max_binlog_size = 100M 

default_storage_engine = InnoDB 
innodb_buffer_pool_size = 9216M 
innodb_log_buffer_size = 8M 
innodb_file_per_table = 1 
innodb_open_files = 400 
innodb_io_capacity = 400 
innodb_flush_method = O_DIRECT 
innodb_thread_concurrency = 16 
aria_pagecache_buffer_size = 512M 
join_cache_level = 0 

[mysqldump] 
quick 
quote-names 
max_allowed_packet = 16M 

[mysql] 

[isamchk] 
key_buffer = 128M 


!includedir /etc/mysql/conf.d/ 

----- Original Message -----

From: "Colin Charles" <colin@xxxxxxxxxxx> 
To: "s kelly" <s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx>, "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>, "Sergey Petrunia" <sergey@xxxxxxxxxxx> 
Cc: mariadbmanager@xxxxxxxxxxxxxxxx 
Sent: Monday, 2 June, 2014 1:14:56 PM 
Subject: Re: Internal tmp tables 

Hi! 

On 1 Jun 2014, at 17:37, s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx wrote: 

> I have recently moved from an older version of Mysql to MariaDB, I have a few queries which use internal tmp tables (according to EXPLAIN) and the performance is almost un-usable on MariaDB for some reason. 
> Is there anything different in MariaDB that would be causing this issue. The version I am using is: Ver 15.1 Distrib 10.0.10-MariaDB. 
> 

What are your aria engine settings in my.cnf out of curiosity? 

Try setting join_cache_level = 0 

Please also provide @@optimizer_switch output 

Thanks 

P/S: what was the older version of mysql that you're referring to? 


> Here is the explain from the query: 
> +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ 
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
> +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using temporary; Using filesort | 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using where; Using join buffer (flat, BNL join) | 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using where; Using join buffer (incremental, BNL join) | 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using where; Using join buffer (incremental, BNL join) | 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using where; Using join buffer (incremental, BNL join) | 
> | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | NULL | 2939 | Using index condition; Using where; Using join buffer (incremental, BNL join) | 
> +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ 
> 
> To give you an idea, the exact same query on the old version of mysql took about 1 second, this version of MariaDB is taking 1min 48secs on the same hardware. 
> Any help would be appreciated. 
> 
> Thanks Steve. 
> 
> -- 
> You received this message because you are subscribed to the Google Groups "MariaDB-Manager" group. 
> To unsubscribe from this group and stop receiving emails from it, send an email to mariadbmanager+unsubscribe@xxxxxxxxxxxxxxxx. 
> For more options, visit https://groups.google.com/d/optout. 

-- 
Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company 
blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles