Maybe a show variables could help via pastebin or something like it Em segunda-feira, 2 de junho de 2014, Steve Kelly < [email protected]> 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" <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>> > *To: *"s kelly" <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>>, > "Maria Discuss" <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>>, > "Sergey Petrunia" <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>> > *Cc: *[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');> > *Sent: *Monday, 2 June, 2014 1:14:56 PM > *Subject: *Re: Internal tmp tables > > Hi! > > On 1 Jun 2014, at 17:37, [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');> > 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 [email protected] > <javascript:_e(%7B%7D,'cvml','mariadbmanager%[email protected]');> > . > > 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
