Re: [Maria-discuss] Internal tmp tables
Hi! On 1 Jun 2014, at 17:37, s.kelly@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+unsubscribe@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
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" <colin@mariadb.org> To: "s kelly" <s.kelly@pivotaltechnologies.com.au>, "Maria Discuss" <maria-discuss@lists.launchpad.net>, "Sergey Petrunia" <sergey@mariadb.com> Cc: mariadbmanager@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.kelly@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+unsubscribe@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
Maybe a show variables could help via pastebin or something like it Em segunda-feira, 2 de junho de 2014, Steve Kelly < s.kelly@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" <colin@mariadb.org <javascript:_e(%7B%7D,'cvml','colin@mariadb.org');>> *To: *"s kelly" <s.kelly@pivotaltechnologies.com.au <javascript:_e(%7B%7D,'cvml','s.kelly@pivotaltechnologies.com.au');>>, "Maria Discuss" <maria-discuss@lists.launchpad.net <javascript:_e(%7B%7D,'cvml','maria-discuss@lists.launchpad.net');>>, "Sergey Petrunia" <sergey@mariadb.com <javascript:_e(%7B%7D,'cvml','sergey@mariadb.com');>> *Cc: *mariadbmanager@googlegroups.com <javascript:_e(%7B%7D,'cvml','mariadbmanager@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.kelly@pivotaltechnologies.com.au <javascript:_e(%7B%7D,'cvml','s.kelly@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+unsubscribe@googlegroups.com <javascript:_e(%7B%7D,'cvml','mariadbmanager%2Bunsubscribe@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
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 <javascript:>> 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" <colin@mariadb.org> *To: *"s kelly" <s.kelly@pivotaltechnologies.com.au>, "Maria Discuss" < maria-discuss@lists.launchpad.net>, "Sergey Petrunia" <sergey@mariadb.com
*Cc: *mariadbmanager@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.kelly@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+unsubscribe@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
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.kelly@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" <colin@mariadb.org> To: "s kelly" <s.kelly@pivotaltechnologies.com.au>, "Maria Discuss" <maria-discuss@lists.launchpad.net>, "Sergey Petrunia" <sergey@mariadb.com> Cc: mariadbmanager@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.kelly@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+unsubscribe@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
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
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
2014-06-02 2:24 GMT-03:00 <s.k...@pivotaltechnologies.com.au <javascript:>>: 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 <javascript:>> To: "s kelly" <s.k...@pivotaltechnologies.com.au <javascript:>>, "Maria Discuss" <maria-...@lists.launchpad.net <javascript:>>, "Sergey Petrunia" < ser...@mariadb.com <javascript:>> Cc: mariadb...@googlegroups.com <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...@pivotaltechnologies.com.au <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...@googlegroups.com <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
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@pivotaltechnologies.com.au>:
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
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
2014-06-02 2:24 GMT-03:00 <s.k...@pivotaltechnologies.com.au>: 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.k...@pivotaltechnologies.com.au>, "Maria Discuss" <maria-...@lists.launchpad.net>, "Sergey Petrunia" < ser...@mariadb.com> Cc: mariadb...@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.k...@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 mariadbmanage...@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
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
participants (4)
-
Colin Charles -
Roberto Spadim -
s.kelly＠pivotaltechnologies.com.au -
Steve Kelly