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 > <javascript:>>: > > 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 <javascript:>> > >>> To: "s kelly" <s.k...@pivotaltechnologies.com.au <javascript:>>, > "Maria Discuss" > >>> <maria-...@lists.launchpad.net <javascript:>>, "Sergey Petrunia" < > ser...@mariadb.com <javascript:>> > >>> Cc: mariadb...@googlegroups.com <javascript:> > >>> 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 > <javascript:> 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 <javascript:>. > >>> > 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 >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
