maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Maybe a show variables could help via pastebin or something like it

Em segunda-feira, 2 de junho de 2014, Steve Kelly <
s.kelly@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" <colin@xxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','colin@xxxxxxxxxxx');>>
> *To: *"s kelly" <s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx');>>,
> "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','maria-discuss@xxxxxxxxxxxxxxxxxxx');>>,
> "Sergey Petrunia" <sergey@xxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','sergey@xxxxxxxxxxx');>>
> *Cc: *mariadbmanager@xxxxxxxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','mariadbmanager@xxxxxxxxxxxxxxxx');>
> *Sent: *Monday, 2 June, 2014 1:14:56 PM
> *Subject: *Re: Internal tmp tables
>
> Hi!
>
> On 1 Jun 2014, at 17:37, s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','s.kelly@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 mariadbmanager+unsubscribe@xxxxxxxxxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','mariadbmanager%2Bunsubscribe@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