I did try that the other day thinking it could be the aria engine, problem is I couldn't work out how to do it.
Putting aria_used_for_temp_tables=OFF in my.cnf yields this error: [ERROR] /usr/sbin/mysqld: unknown variable 'aria_used_for_temp_tables=OFF'
Is there a way you can disable it?
On Tuesday, June 3, 2014 6:46:29 AM UTC+10, Roberto Spadim wrote:try changin aria_used_for_temp_tables ON, to OFF
just to check if the problem is aria engine or not
2014-06-02 2:24 GMT-03:00 <s.k...@pivotaltechnologies.com.au>:
> Attached is a dump of show variables.
>
> Steve
>
>
> On Monday, June 2, 2014 2:56:48 PM UTC+10, Roberto Spadim wrote:
>>
>> Maybe a show variables could help via pastebin or something like it
>>
>> Em segunda-feira, 2 de junho de 2014, Steve Kelly
>> <s.k...@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" <co...@mariadb.org>
>>> To: "s kelly" <s.k...@pivotaltechnologies.com.au>, "Maria Discuss"
>>> <maria-...@lists.launchpad.net>, "Sergey Petrunia" <ser...@mariadb.com>
>>> Cc: mariadb...@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.k...@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 mariadbmanage...@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
>>
>
--
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle