maria-discuss team mailing list archive

[s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx]

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...@xxxxxxxxxxxxxxxxxxxxxxxxxx 
> <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...@xxxxxxxxxxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxxxx <javascript:>> 
> >>> To: "s kelly" <s.k...@xxxxxxxxxxxxxxxxxxxxxxxxxx <javascript:>>, 
> "Maria Discuss" 
> >>> <maria-...@xxxxxxxxxxxxxxxxxxx <javascript:>>, "Sergey Petrunia" <
> ser...@xxxxxxxxxxx <javascript:>> 
> >>> Cc: mariadb...@xxxxxxxxxxxxxxxx <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...@xxxxxxxxxxxxxxxxxxxxxxxxxx 
> <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...@xxxxxxxxxxxxxxxx <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 
>