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.ke...@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.ke...@pivotaltechnologies.com.au>, "Maria Discuss" >>> <maria-discuss@lists.launchpad.net>, "Sergey Petrunia" <ser...@mariadb.com> >>> Cc: mariadbmana...@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.ke...@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 mariadbmanager+unsubscr...@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 _______________________________________________ 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
