Maybe a show variables could help via pastebin or something like it

Em segunda-feira, 2 de junho de 2014, Steve Kelly <s.kelly@pivotaltechnologies.com.au> escreveu:
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/

From: "Colin Charles" <colin@mariadb.org>
To: "s kelly" <s.kelly@pivotaltechnologies.com.au>, "Maria Discuss" <maria-discuss@lists.launchpad.net>, "Sergey Petrunia" <sergey@mariadb.com>
Cc: mariadbmanager@googlegroups.com
Sent: Monday, 2 June, 2014 1:14:56 PM
Subject: Re: Internal tmp tables

Hi!

On 1 Jun 2014, at 17:37, s.kelly@pivotaltechnologies.com.au 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@googlegroups.com.
> 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




--
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle