i didn't checked if it's a compile time configuration but, reading manual probably yes, and it's readonly (sorry i didn't checked before) but, please check if it's possible to change to myisam and test recompiling mariadb, i never tried this, maybe another guy here could help
https://mariadb.com/kb/en/aria-server-system-variables/ aria_used_for_temp_tables - *Description:* Readonly variable indicating whether the Aria <https://mariadb.com/kb/en/aria/> storage engine is used for temporary tables. - *Commandline:* No - *Scope:* Global - *Dynamic:* No - *Data Type:* boolean - *Default Value:* ON 2014-06-02 18:27 GMT-03:00 <s.ke...@pivotaltechnologies.com.au>: > 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 >> > -- 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
