← Back to team overview

maria-discuss team mailing list archive

Re: Internal tmp tables

 

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

References