from:"Alessandro Ren"

Re: [Maria-discuss] execute failed: Incorrect string value: '\xD6sterl...' with mariadb and perl DBD

2019-06-25 Thread Alessandro Ren

   Hey Dave,

I had some problems dealing with wrong coded UTF-8 chars.
You have to either deal with them before sending them to MariaDB, trying to
detect if the string is encoded correctly or you can change the field type
to blob, which will accept this string as is and not thrown a invalid
string error in MariaDB.

   []s.


On Wed, Jun 19, 2019 at 2:56 PM Dave Wreski 
wrote:

> Hi,
>
> I'm trying to use perl-DBD to write a buffer of text that contains an
> email with umlauts and other non-ASCII characters to a joomla database
> and having a problem.
>
>  DBD::mysql::st execute failed: Incorrect string value:
> '\xD6sterl...' for column `lsv5webstage`.`xuxgc_content`.`fulltext` at
> row 1 at /home/alerts/scripts_linstage/AdvisoryTest.pm line 373.
>
> I'm not familiar enough with how encoding works to fully understand what
> the problem is. This is a fedora29 system with mariadb-10.3.12 and
> joomla-3.9.
>
> Apparently the '\xD6' is an O with an umlaut in "Sebastian �sterlund". I
> read something about utf8 not being able to handle 4-char, but I don't
> fully understand.
>
> I found the following reference online which talks about changing the
> encoding type from utf8 to utf8mb4, but the tables all appear to already
> be using that encoding:
>
>  > SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
>  Variable_name LIKE 'collation%';
>  +--++
>  | Variable_name| Value  |
>  +--++
>  | character_set_client | utf8mb4|
>  | character_set_connection | utf8mb4|
>  | character_set_database   | utf8mb4|
>  | character_set_filesystem | binary |
>  | character_set_results| utf8mb4|
>  | character_set_server | utf8mb4|
>  | character_set_system | utf8   |
>  | collation_connection | utf8mb4_unicode_ci |
>  | collation_database   | utf8mb4_unicode_ci |
>  | collation_server | utf8mb4_unicode_ci |
>  +--++
>
> execute failed: Incorrect string value: '\xD6sterl...' with mariadb and
> perl DBD
> a novice perl programmer
> I'm not sure it's helpful, but this is the insert statement I'm using in
> my perl code:
>
>  my $sql = <  INSERT INTO xuxgc_content (title, alias, introtext, `fulltext`,
> state, catid, created, created_by, created_by_alias, modified,
> modified_by, checked_out, checked_out_time, publish_up, publish_down,
> images, urls, attribs, version, ordering, metakey, metadesc, metadata,
> access, hits, language)
>  VALUES ($title, "$title_alias", $introText, $fullText, $state,
> $catid, $created, $created_by, $created_by_alias, $modified,
> $modified_by, $checked_out, $checked_out_time, $publish_up,
> $publish_down, $images, $urls, $attribs, $version, $ordering, $metakey,
> $metadesc, $metadata, $access, $hits, $language);
>  EOF
>
>  my $sth = $dbh->prepare($sql);
>  $sth->execute();
>  db_disconnect($dbh);
>
> I don't recall having this problem in the past, and this script has been
> in use for quite a while.
>
> What am I doing wrong?
>
> ___
> 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
>
___
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

Re: [Maria-discuss] Replication Problem

2018-07-04 Thread Alessandro Ren

  About replication, I dont let it stop on duplicate keys, I prefer to use
slave-skip-errors = all and let the primary key on the tables take care of
duplicate records.
  Replication will only stop if something is corrupted, which may be your
case. Some position the the binlog got lost in the power outage and you
will have to reposition the master slave binlog, probably loosing some data
on one side.

   []s.


On Wed, Jul 4, 2018 at 8:02 AM, Guillaume Lefranc 
wrote:

> Hi Thomas,
>
> Could you please show the output of SHOW SLAVE STATUS and SHOW VARIABLES
> LIKE 'gtid%' ?
>
> Thanks
>
> Guillaume Lefranc
> signal18.io consulting
>
> Le mer. 4 juil. 2018 à 12:31, Thomas Plant  a
> écrit :
>
>> Hello,
>>
>> I'd have a question about MariaDB 10.1 replication, which has been
>> interrupted by a power outage in our datacenter.
>> We started the master server and had a duplicate id in a table on the
>> slave, so replication stopped. We did not have time to adjust this at
>> the moment, there were a lot of other systems to start and check if all
>> was alright.
>>
>> Today I had time to look at the error, removed the duplicate ID from the
>> table and started the slave thread again using 'start slave;'.
>>
>> But now I get another error:
>>
>> Last_IO_Error: Got fatal error 1236 from master when reading data from
>> binary log: 'Error: connecting slave requested to start from GTID
>> 0-2-2948175468, which is not in the master's binlog. Since the master's
>> binlog contains GTIDs with higher sequence numbers, it probably means
>> that the slave has diverged due to executing extra erroneous transactions'
>>
>> Can this be resolved? Or will I have to start replication from scratch?
>>
>> Greetings,
>> Thomas
>>
>> ___
>> 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
>>
>
> ___
> 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
>
>
___
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

Re: [Maria-discuss] Tokudb 5.7.18-16 fixes in Mariadb 10.2.8

2017-08-02 Thread Alessandro Ren

   Win win situation Sergei, tks for the quick feedback.

   []s.

On Wed, Aug 2, 2017 at 1:45 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Alessandro!
>
> On Aug 02, Alessandro Ren wrote:
> > Sergei,
> >
> > by any chance this merge will come out in MariaDB 10.0.32? It also fixes
> > the row count problem in TokuDB in versions greater than 10.0.25.
> >   This fix came out here
> > https://www.percona.com/doc/percona-server/5.6/release-
> notes/Percona-Server-5.6.36-82.1.html
>
> Yes, we've just merged it into 10.0.32.
>
> 10.0.32 release should've been out last week, but was delayed because of
> a merge difficulties, and we've used the chance to merge from 5.6.36-82.1.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
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

Re: [Maria-discuss] Tokudb 5.7.18-16 fixes in Mariadb 10.2.8

2017-08-02 Thread Alessandro Ren

Sergei,

by any chance this merge will come out in MariaDB 10.0.32? It also fixes
the row count problem in TokuDB in versions greater than 10.0.25.
  This fix came out here
https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.36-82.1.html

   Tks.



Alessandro Ren
Product Manager
OpMon Team


On Wed, Aug 2, 2017 at 10:26 AM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Reinis!
>
> On Jul 31, Reinis Rozitis wrote:
> > Hello,
> > is there any chance/possibility for these fixes from Percona Server
> > 5.7.18-16 to land in 10.2.8 release?
>
> Yes. It should be part of the pre-release merge task (MDEV-13309).
>
> But, on the other hand, if tokudb merge will happen to be too complex
> (requiring weeks to complete) or if the new tokudb won't work in mariadb
> 10.2 at all (this happened in 10.0 a couple of times), we might have to
> skip tokudb merge in 10.2.8.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
> ___
> 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
>
___
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

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

2017-07-26 Thread Alessandro Ren

  Here it goes

10.0.25
MariaDB [opperf]> show variables like '%version%';
+-+--+
| Variable_name   | Value|
+-+--+
| innodb_version  | 5.6.29-76.2  |
| protocol_version| 10   |
| slave_type_conversions  |  |
| tokudb_version  | 5.6.26-74.0  |
| version | 10.0.25-MariaDB  |
| version_comment | MariaDB Server   |
| version_compile_machine | x86_64   |
| version_compile_os  | Linux|
| version_malloc_library  | bundled jemalloc |
+-+--+
9 rows in set (0.09 sec)

+--+-+--+---+++-+--+---+--+
| id   | select_type | table| type  | possible_keys
 | key| key_len | ref  | rows  | Extra
   |
+--+-+--+---+++-+--+---+--+
|1 | SIMPLE  | service_perf_651 | range |
PRIMARY,service_perf_1_idx | service_perf_1_idx | 16  | NULL | 17880 |
Using where; Using temporary; Using filesort |
+--+-+--+---+++-+--+---+--+


10.0.31

MariaDB [opperf]> show variables like '%version%';
+-+--+
| Variable_name   | Value|
+-+--+
| innodb_version  | 5.6.36-82.0  |
| protocol_version| 10   |
| slave_type_conversions  |  |
| tokudb_version  | 5.6.36-82.0  |
| version | 10.0.31-MariaDB  |
| version_comment | MariaDB Server   |
| version_compile_machine | x86_64   |
| version_compile_os  | Linux|
| version_malloc_library  | bundled jemalloc |
+-+--+
9 rows in set (0.11 sec)


+--+-+--+---++-+-+--+--+--+
| id   | select_type | table| type  | possible_keys
 | key | key_len | ref  | rows | Extra
   |
+--+-+--+---++-+-+--+--+--+
|1 | SIMPLE  | service_perf_651 | range |
PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
Using temporary; Using filesort |
+--+-+--+---++-+-+--+--+--+


On Mon, Jul 24, 2017 at 10:53 AM, <rhys.campb...@swisscom.com> wrote:

> This is repeatable and it's still the same once data is cached?
>
>
>
> Which version is the explain from? I guess it's from 10.0.31. Can you
> include the explain from the other version(s)?
>
>
>
>
>
> The DATE_FORMAT function part of the query…
>
>
>
>   AND ( ( Date_format(entry_time, '%w') = 0
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 1
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 2
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 3
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 4
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 5
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) )
>
>   OR ( Date_format(entry_time, '%w') = 6
>
>AND (( Date_format(entry_time, '%H') >= 0
>
>   AND Date_format(entry_time, '%H') < 24 )) ) )
>
>
>
> This asks for all days and all hours from 0-23 (excluding 24). You could

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

2017-07-24 Thread Alessandro Ren

  Jocelyn,

the bug described there seems similar to my problem and it works on MariaDB
10.0.23.

[]s.


On Mon, Jul 24, 2017 at 1:31 PM, jocelyn fournier <
jocelyn.fourn...@softizy.com> wrote:

> Hi Alessandro!
>
>
> 10.0.31 should be affected by https://jira.percona.com/browse/TDB-35 , do
> you think it could be related to your issue?
>
>
> HTH,
>
> Jocelyn Fournier
> Founder
> M : +33 6 51 21 54 10 <+33%206%2051%2021%2054%2010>https://www.softizy.com
> Softizy - At your side to Optimize your PHP / MySQL applications
>
> Le 24/07/2017 à 17:41, Alessandro Ren a écrit :
>
>
>  Even if a change the order by and the query to include all 3 fields on
> the index, it still selects the PRIMARY key to query the table.
>
>Do you think this is a bug?
>
>Tks.
>
>
> On Mon, Jul 24, 2017 at 11:34 AM, Alessandro Ren <dirty@gmail.com>
> wrote:
>
>>
>>Once cached, the query returns in 0s.
>>
>>The force index solved the problem:
>>
>> MariaDB 10.0.31
>>  Force index:13 rows in set (2.31 sec)
>>  No force: 13 rows in set (12.97 sec)
>>
>>MariDB 10.0.25:
>>  Force index: 13 rows in set (1.46 sec)
>>  No force: 13 rows in set (1.70 sec)
>>
>>
>>
>> Explains per version follows:
>>
>> 10.0.25
>> MariaDB [opperf]> show variables like '%version%';
>> +-+--+
>> | Variable_name   | Value|
>> +-+--+
>> | innodb_version  | 5.6.29-76.2  |
>> | protocol_version| 10   |
>> | slave_type_conversions  |  |
>> | tokudb_version  | 5.6.26-74.0  |
>> | version | 10.0.25-MariaDB  |
>> | version_comment | MariaDB Server   |
>> | version_compile_machine | x86_64   |
>> | version_compile_os  | Linux|
>> | version_malloc_library  | bundled jemalloc |
>> +-+--+
>> 9 rows in set (0.09 sec)
>>
>> +--+-+--+---+---
>> -++-+--+
>> ---+--+
>> | id   | select_type | table| type  | possible_keys
>>| key| key_len | ref  | rows  | Extra
>>  |
>> +--+-+--+---+---
>> -++-+--+
>> ---+--+
>> |1 | SIMPLE  | service_perf_651 | range |
>> PRIMARY,service_perf_1_idx | service_perf_1_idx | 16  | NULL | 17880 |
>> Using where; Using temporary; Using filesort |
>> +--+-+--+---+---
>> -++-+--+
>> ---+--+
>>
>>
>> 10.0.31
>>
>> MariaDB [opperf]> show variables like '%version%';
>> +-+--+
>> | Variable_name   | Value|
>> +-+--+
>> | innodb_version  | 5.6.36-82.0  |
>> | protocol_version| 10   |
>> | slave_type_conversions  |  |
>> | tokudb_version  | 5.6.36-82.0  |
>> | version | 10.0.31-MariaDB  |
>> | version_comment | MariaDB Server   |
>> | version_compile_machine | x86_64   |
>> | version_compile_os  | Linux|
>> | version_malloc_library  | bundled jemalloc |
>> +-+--+
>> 9 rows in set (0.11 sec)
>>
>>
>> +--+-+--+---+---
>> -+-+-+--+--+
>> --+
>> | id   | select_type | table| type  | possible_keys
>>| key | key_len | ref  | rows | Extra
>>  |
>> +--+-+--+---+---
>> -+-+-+--+--+
>> --+
>> |1 | SIMPLE  | service_perf_651 | range |
>> PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
>> Using temporary; Using filesort |
>> +--+-+--+---+---
&

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

2017-07-24 Thread Alessandro Ren

 Even if a change the order by and the query to include all 3 fields on the
index, it still selects the PRIMARY key to query the table.

   Do you think this is a bug?

   Tks.


On Mon, Jul 24, 2017 at 11:34 AM, Alessandro Ren <dirty@gmail.com>
wrote:

>
>Once cached, the query returns in 0s.
>
>The force index solved the problem:
>
> MariaDB 10.0.31
>  Force index:13 rows in set (2.31 sec)
>  No force: 13 rows in set (12.97 sec)
>
>MariDB 10.0.25:
>  Force index: 13 rows in set (1.46 sec)
>  No force: 13 rows in set (1.70 sec)
>
>
>
> Explains per version follows:
>
> 10.0.25
> MariaDB [opperf]> show variables like '%version%';
> +-+--+
> | Variable_name   | Value|
> +-+--+
> | innodb_version  | 5.6.29-76.2  |
> | protocol_version| 10   |
> | slave_type_conversions  |  |
> | tokudb_version  | 5.6.26-74.0  |
> | version | 10.0.25-MariaDB  |
> | version_comment | MariaDB Server   |
> | version_compile_machine | x86_64   |
> | version_compile_os  | Linux|
> | version_malloc_library  | bundled jemalloc |
> +-+--+
> 9 rows in set (0.09 sec)
>
> +--+-+--+---+---
> -++-+--+
> ---+--+
> | id   | select_type | table| type  | possible_keys
>| key| key_len | ref  | rows  | Extra
>  |
> +--+-+--+---+---
> -++-+--+
> ---+--+
> |1 | SIMPLE  | service_perf_651 | range |
> PRIMARY,service_perf_1_idx | service_perf_1_idx | 16  | NULL | 17880 |
> Using where; Using temporary; Using filesort |
> +--+-+--+---+---
> -++-+--+
> ---+--+
>
>
> 10.0.31
>
> MariaDB [opperf]> show variables like '%version%';
> +-+--+
> | Variable_name   | Value|
> +-+--+
> | innodb_version  | 5.6.36-82.0  |
> | protocol_version| 10   |
> | slave_type_conversions  |  |
> | tokudb_version  | 5.6.36-82.0  |
> | version | 10.0.31-MariaDB  |
> | version_comment | MariaDB Server   |
> | version_compile_machine | x86_64   |
> | version_compile_os  | Linux|
> | version_malloc_library  | bundled jemalloc |
> +-+--+
> 9 rows in set (0.11 sec)
>
>
> +--+-+--+---+---
> -+-+-+--+--+
> --+
> | id   | select_type | table| type  | possible_keys
>| key | key_len | ref  | rows | Extra
>  |
> +--+-+--+---+---
> -+-+-+--+--+
> --+
> |1 | SIMPLE  | service_perf_651 | range |
> PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
> Using temporary; Using filesort |
> +--+-+--+---+---
> -+-+-+--+--+
> --+
>
>
> tks.
>
>
>
>
>
> On Mon, Jul 24, 2017 at 11:10 AM, Reinis Rozitis <r...@roze.lv> wrote:
>
>> MariaDB 10.0.25 - 13 rows in set (1.67 sec)
>>> MariaDB 10.0.31 - 13 rows in set (29.06 sec)
>>>
>>> +--+-+--+---+---
>>> -+-+-+--+--+
>>> --+
>>> | id   | select_type | table| type  | possible_keys | key
>>>  | key_len | ref  | rows | Extra |
>>> +--+-+--+---+---
>>> -+-+-+--+--+
>>> --+
>>> |1 | SIMPLE  | service_perf_651 | range |
>>> PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
>>> Using temporary; Using filesort |

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

2017-07-24 Thread Alessandro Ren

   Once cached, the query returns in 0s.

   The force index solved the problem:

MariaDB 10.0.31
 Force index:13 rows in set (2.31 sec)
 No force: 13 rows in set (12.97 sec)

   MariDB 10.0.25:
 Force index: 13 rows in set (1.46 sec)
 No force: 13 rows in set (1.70 sec)



Explains per version follows:

10.0.25
MariaDB [opperf]> show variables like '%version%';
+-+--+
| Variable_name   | Value|
+-+--+
| innodb_version  | 5.6.29-76.2  |
| protocol_version| 10   |
| slave_type_conversions  |  |
| tokudb_version  | 5.6.26-74.0  |
| version | 10.0.25-MariaDB  |
| version_comment | MariaDB Server   |
| version_compile_machine | x86_64   |
| version_compile_os  | Linux|
| version_malloc_library  | bundled jemalloc |
+-+--+
9 rows in set (0.09 sec)

+--+-+--+---+++-+--+---+--+
| id   | select_type | table| type  | possible_keys
 | key| key_len | ref  | rows  | Extra
   |
+--+-+--+---+++-+--+---+--+
|1 | SIMPLE  | service_perf_651 | range |
PRIMARY,service_perf_1_idx | service_perf_1_idx | 16  | NULL | 17880 |
Using where; Using temporary; Using filesort |
+--+-+--+---+++-+--+---+--+


10.0.31

MariaDB [opperf]> show variables like '%version%';
+-+--+
| Variable_name   | Value|
+-+--+
| innodb_version  | 5.6.36-82.0  |
| protocol_version| 10   |
| slave_type_conversions  |  |
| tokudb_version  | 5.6.36-82.0  |
| version | 10.0.31-MariaDB  |
| version_comment | MariaDB Server   |
| version_compile_machine | x86_64   |
| version_compile_os  | Linux|
| version_malloc_library  | bundled jemalloc |
+-+--+
9 rows in set (0.11 sec)


+--+-+--+---++-+-+--+--+--+
| id   | select_type | table| type  | possible_keys
 | key | key_len | ref  | rows | Extra
   |
+--+-+--+---++-+-+--+--+--+
|1 | SIMPLE  | service_perf_651 | range |
PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
Using temporary; Using filesort |
+--+-+--+---++-+-+--+--+--+


tks.





On Mon, Jul 24, 2017 at 11:10 AM, Reinis Rozitis  wrote:

> MariaDB 10.0.25 - 13 rows in set (1.67 sec)
>> MariaDB 10.0.31 - 13 rows in set (29.06 sec)
>>
>> +--+-+--+---+---
>> -+-+-+--+--+
>> --+
>> | id   | select_type | table| type  | possible_keys | key
>>  | key_len | ref  | rows | Extra |
>> +--+-+--+---+---
>> -+-+-+--+--+
>> --+
>> |1 | SIMPLE  | service_perf_651 | range |
>> PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
>> Using temporary; Using filesort |
>> +--+-+--+---+---
>> -+-+-+--+--+
>> --+
>>
>
>
> This is typical when the MySQL query optimiser decides that it will be
> faster to use an index for sorting rather than selecting.
> Sometimes the query plans change because of versions or table/index
> statistics.
>
>
> For testing purposes you can try to drop the "order by entry_time" part or
> add FORCE INDEX:
>
> SELECT ... from service_perf_651  FORCE INDEX(service_perf_1_idx) WHERE ...
>
> rr
>
___
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

[Maria-discuss] TokuDB performance hit after 10.0.25

2017-07-24 Thread Alessandro Ren

Hello,

I've noticed a great performance hit after I upgraded my MariaDB install to
10.0.28, 10.0.29, 10.0.30 and 10.0.31.
I even tried upgrading to MariaDB 10.2.7 and had the same problem. Bellow
de details.

MariaDB 10.0.25 - 13 rows in set (1.67 sec)
MariaDB 10.0.31 - 13 rows in set (29.06 sec)

The query:

SELECT metric_id,date_format(entry_time, '%m:%Y') as date_group,
unix_timestamp(entry_time) as entry_time, entry_time as datetime,
avg(perf_value) as perf_value, warning, critical, baseline, lower_limit,
upper_limit from service_perf_651 where service_id='56551' and
metric_id='90183701' and entry_time>='2016-07-24 09:41:42' and
entry_time<='2017-07-24 09:41:42' and ( (date_format(entry_time,'%w')=0 and
((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H') < 24)))
or (date_format(entry_time,'%w')=1 and ((date_format(entry_time,'%H')>=0
and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=2
and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
)) or (date_format(entry_time,'%w')=3 and ((date_format(entry_time,'%H')>=0
and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=4
and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
)) or (date_format(entry_time,'%w')=5 and ((date_format(entry_time,'%H')>=0
and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=6
and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
)) ) group by date_group order by entry_time

Explain query:

+--+-+--+---++-+-+--+--+--+
| id   | select_type | table| type  | possible_keys
 | key | key_len | ref  | rows | Extra
   |
+--+-+--+---++-+-+--+--+--+
|1 | SIMPLE  | service_perf_651 | range |
PRIMARY,service_perf_1_idx | PRIMARY | 16  | NULL |1 | Using where;
Using temporary; Using filesort |
+--+-+--+---++-+-+--+--+--+
1 row in set (0.06 sec)


The table:

CREATE TABLE `service_perf_651` (
  `entry_time` datetime NOT NULL DEFAULT '-00-00 00:00:00',
  `service_id` int(11) unsigned NOT NULL DEFAULT '0',
  `metric_id` int(11) unsigned NOT NULL DEFAULT '0',
  `perf_value` float(13,3) DEFAULT NULL,
  `warning` float(13,3) DEFAULT NULL,
  `critical` float(13,3) DEFAULT NULL,
  `baseline` float(13,3) DEFAULT NULL,
  `lower_limit` float(13,3) DEFAULT NULL,
  `upper_limit` float(13,3) DEFAULT NULL,
  `reserved0` float(13,3) DEFAULT NULL,
  `reserved1` float(13,3) DEFAULT NULL,
  `reserved2` float(13,3) DEFAULT NULL,
  PRIMARY KEY (`entry_time`,`service_id`,`metric_id`),
  KEY `service_perf_1_idx` (`service_id`,`metric_id`,`entry_time`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 `compression`='tokudb_snappy'

The size:

32497415 records


  Any idea what could be wrong? I even tried everything above on a
different HW where the databse fit in memory in TokuDB, with the same
performance hit.

   Tks for the help

   Alessandro Ren
___
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

Re: [Maria-discuss] execute failed: Incorrect string value: '\xD6sterl...' with mariadb and perl DBD

Re: [Maria-discuss] Replication Problem

Re: [Maria-discuss] Tokudb 5.7.18-16 fixes in Mariadb 10.2.8

Re: [Maria-discuss] Tokudb 5.7.18-16 fixes in Mariadb 10.2.8

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

[Maria-discuss] TokuDB performance hit after 10.0.25

9 matches

Site Navigation

Mail list logo

Footer information