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/ ----- Original Message ----- From: "Colin Charles" <[email protected]> To: "s kelly" <[email protected]>, "Maria Discuss" <[email protected]>, "Sergey Petrunia" <[email protected]> Cc: [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] 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]. > 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
