Re: [Maria-developers] How to change max key length for MyISAM?

[jocelyn fournier]

Just FYI, I confirm Prestashop (at least since 1.6.1.x) is designed to work 
much better on InnoDB than MyISAM

BR,
  Jocelyn Fournier

> Le 15 janv. 2021 à 10:09, Reindl Harald  a écrit :
> 
> 
> 
> Am 15.01.21 um 09:01 schrieb Luuk:
>> On 14-1-2021 21:45, Reindl Harald wrote:
>>>> I'm trying to install prestashop-1.7.7.1 on MyISAM
>>> 
>>> and why does that piece of software don't work with sensible defaults and 
>>> force you too pacth source code and recompile your database server in a way 
>>> nobody but you is running?
>>> 
>>> this is asking for troubles and until someone can prove a *real* benefit i 
>>> call it bullshit and won't install "prestashop-1.7.7.1 on MyISAM"
>>> 
>>> the other possibilty is that it's designed to work with innoDB and *not* 
>>> MyISAM which means: just don't enforce MyISAM, key sizes are your smallest 
>>> problems in reality
>> A: It is a MySQL issue. 
>> http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes
>>  
> 
> as expected DO NOT INSTALL IT ON MYISAM jesus christ
> ENGINE=InnoDB
> 
> and no it's not a mysql issue when a software is written in a way than even 
> the installer dies
> 
> CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
>  `menu_id` varchar(32) NOT NULL,
>  `parent_menu_id` int(32) unsigned DEFAULT NULL,
>  `menu_name` varchar(255) DEFAULT NULL,
>  `menu_link` varchar(255) DEFAULT NULL,
>  `plugin` varchar(255) DEFAULT NULL,
>  `menu_type` int(1) DEFAULT NULL,
>  `extend` varchar(255) DEFAULT NULL,
>  `new_window` int(1) DEFAULT NULL,
>  `rank` int(100) DEFAULT NULL,
>  `hide` int(1) DEFAULT NULL,
>  `template_id` int(32) unsigned DEFAULT NULL,
>  `alias` varchar(255) DEFAULT NULL,
>  `layout` varchar(255) DEFAULT NULL,
>  PRIMARY KEY (`menu_id`),
>  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Performance of index intersection

[jocelyn fournier]

Hi Sergey!

Actually fetching min & max value for each keys (key1=1 and key2=2 in your 
case) should not cost much?
Then you can compute the overlap zone (Zone B in your graph) and directly 
perform a jump on the beginning of Zone B and stop at the end of Zone B instead 
of EOF, using the index with the less amount of matching rows.

BR,
  Jocelyn


> Le 1 oct. 2019 à 21:31, Sergey Petrunia  a écrit :
> 
> Hello,
> 
> More thoughts on this:
> 
> Let's consider a query 
> 
> select * from t1 where key1=1 and key2=2
> 
> let's assume it uses index itnersection.
> 
> Check the attached picture. It shows the table rowids at the center,
> key1 on the left, key2 on the right.
> 
> There are three zones:
> 
> - Zone A, where rowids matching 'key1=1' have no overlap with any rowids for 
> 'key2=2'
> 
> - Zone B, where rowids from two scans overlap.
> 
> - Zone C, similar to zone A but at the end of the scan. Here, rowids matching 
> key2=2 have no overlap.
> 
> The current "merge-ordered-streams" algorithm takes care of "Zone C".
> the code will return EOF as soon as one of the merged streams has produced 
> EOF.
> 
> Now, let's look at Zone B.  Here, the code would scan both indexes forward
> and search for records with key=1 that have matches with key=2.
> 
> The search forward is this loop in QUICK_ROR_INTERSECT_SELECT::get_next:
> 
>do
>{
>  DBUG_EXECUTE_IF("innodb_quick_report_deadlock",
>  DBUG_SET("+d,innodb_report_deadlock"););
>  if (unlikely((error= quick->get_next(
>  {
>/* On certain errors like deadlock, trx might be rolled back.*/
>if (!thd->transaction_rollback_request)
>  quick_with_last_rowid->file->unlock_row();
>DBUG_RETURN(error);
>  }
>  quick->file->position(quick->record);
>  cmp= head->file->cmp_ref(quick->file->ref, last_rowid);
>  if (cmp < 0)
>  {
>/* This row is being skipped.  Release lock on it. */
>quick->file->unlock_row();
>  }
>} while (cmp < 0);
> 
> 
> the quick->get_next() call (after several levels of indirection) do this
> storage engine API call:
> 
>  handler->index_next_same();
> 
> And the question is whether it would be faster if this call was made instead:
> 
>  handler->index_read_map(key_value, rowid_of_match_candidate);
> 
> The answer is that it's faster to do a jump when we will jump over many 
> records. When we jump over a few, it is actually slower than making several
> index_next_same() calls.  The first reason for this is that doing seeks will
> break InnoDB's prefetch buffer optimization. There might be other reasons.
> 
> I don't have any idea how one could predict the jump size.
> 
> As for Zone A, we could jump it over with one jump. If we first read the rowid
> from "key2=2", then we could already use it when making a lookup with key1=1.
> 
> But what if we first did an index lookup on key1=1? Then we will miss the
> chance to jump to the first rowid for that one sees for key2=2...
> 
> If we assume that the first jump is more important than others, we could just 
> look at the first record in each of the merged indexes and then feed them all
> back the maximum rowid we saw as the starting point. This would let us jump
> over zone A.
> 
> I think, implementing "jump over zone A" is easier than inventing a way to do 
> jumps while in zone B. (And this is actually what your idea was).
> 
> 
> A different question - are there any example datasets or queries we
> could try this on? One can of course construct an artificial dataset and
> queries but it would be much nicer to try this on a real dataset and a real
> query.
> 
> BR
> -- Sergei P.
> 
> On Fri, Sep 20, 2019 at 12:31:10PM +0300, Sergey Petrunia wrote:
>> Hello David,
>> 
>> On Mon, Sep 16, 2019 at 09:07:09PM +1200, David Sickmiller wrote:
>>> I've been using MySQL/MariaDB for two decades but have more recently been
>>> working with Elasticsearch.  I knew to expect an inverted index to speed up
>>> querying full text fields, but I've been surprised (and a bit annoyed) at
>>> how fast ES can query structured data.  (In my case, I'm largely looking
>>> for intersections of a number of varchar fields with lowish cardinality,
>>> e.g. WHERE country = 'US' AND client = 'Microsoft' AND status =
>>> 'Completed'.)
>>> 
>>> Elasticsearch seems to have several things going on, but I think a core
>>> aspect, to use RDMS terminology, is that each column is indexed, and index
>>> unions/intersections are used if the WHERE clause references multiple
>>> columns.
>>> 
>>> I've heard that MySQL/MariaDB has the ability to merge indexes, but I've
>>> rarely observed it in person.  Googling for it yields a bunch of
>>> StackOverflow posts complaining how slow it is, with responses agreeing and
>>> explaining how to disable it.
>>> 
>>> If I'm reading the MySQL/MariaDB code correctly, it looks like MariaDB will
>>> intersect indexes by looping through each index, reading the rowids of all
>>> matching 

Re: [Maria-developers] [Commits] 190a29d06: Make Lock Tree's lock waits use externally provided mutexes/conditions

[jocelyn fournier]

Hi Sergei!

Just wondering why keeping the toku_ prefix after renaming toku_* to 
toku_external_* since it’s also used by other engine like RockDB?

BR,
  Jocelyn Fournier


> Le 12 mars 2019 à 09:52, Sergei Petrunia  a écrit :
> 
> revision-id: 190a29d06f4e79d2df4cb513944ac34bd133caa0 (v5.8-1024-g190a29d06)
> parent(s): 7a8c777d4880cde8fd96135f6cc216223371a9f4
> author: Sergei Petrunia
> committer: Sergei Petrunia
> timestamp: 2019-03-12 11:52:17 +0300
> message:
> 
> Make Lock Tree's lock waits use externally provided mutexes/conditions
> 
> In MyRocks, this allows:
> - SHOW PROCESSLIST to show "waiting for row lock" for waiting threads
> - KILL statement to abort the lock wait in the same way as it does it with
>  point locking (without use of handlerton::kill_connection() method)
> 
> - Remove previously added TransactionDB::KillLockWait() method as it is
>  no longer needed.
> 
> ---
> include/rocksdb/utilities/transaction_db.h |  1 -
> .../transactions/pessimistic_transaction_db.cc | 12 +++-
> .../transactions/pessimistic_transaction_db.h  |  4 --
> .../range_locking/locktree/lock_request.cc | 35 ++
> .../range_locking/locktree/lock_request.h  |  9 ++-
> .../range_locking/locktree/locktree.cc | 12 ++--
> .../transactions/range_locking/locktree/locktree.h | 13 ++--
> .../transactions/range_locking/locktree/manager.cc | 17 +++--
> .../portability/toku_external_pthread.h| 81 ++
> utilities/transactions/transaction_lock_mgr.cc | 15 ++--
> utilities/transactions/transaction_lock_mgr.h  |  6 +-
> 11 files changed, 155 insertions(+), 50 deletions(-)
> 
> diff --git a/include/rocksdb/utilities/transaction_db.h 
> b/include/rocksdb/utilities/transaction_db.h
> index 2a98a8d3d..fb3c6a88c 100644
> --- a/include/rocksdb/utilities/transaction_db.h
> +++ b/include/rocksdb/utilities/transaction_db.h
> @@ -291,7 +291,6 @@ class TransactionDB : public StackableDB {
>   bool use_range_locking;
>   virtual RangeLockMgrControl* get_range_lock_manager() { return nullptr; }
> 
> -  virtual void KillLockWait(TransactionID txnid){};
>  protected:
>   // To Create an TransactionDB, call Open()
>   // The ownership of db is transferred to the base StackableDB
> diff --git a/utilities/transactions/pessimistic_transaction_db.cc 
> b/utilities/transactions/pessimistic_transaction_db.cc
> index d44f329e5..308187e13 100644
> --- a/utilities/transactions/pessimistic_transaction_db.cc
> +++ b/utilities/transactions/pessimistic_transaction_db.cc
> @@ -41,7 +41,11 @@ PessimisticTransactionDB::PessimisticTransactionDB(
> ? txn_db_options_.custom_mutex_factory
> : std::shared_ptr(
>   new TransactionDBMutexFactoryImpl())),
> -  range_lock_mgr_(this) {
> +  range_lock_mgr_(this,
> +  txn_db_options_.custom_mutex_factory?
> + txn_db_options_.custom_mutex_factory :
> +   std::shared_ptr(
> +  new TransactionDBMutexFactoryImpl())) {
>   assert(db_impl_ != nullptr);
>   info_log_ = db_impl_->GetDBOptions().info_log;
> }
> @@ -73,7 +77,11 @@ PessimisticTransactionDB::PessimisticTransactionDB(
> ? txn_db_options_.custom_mutex_factory
> : std::shared_ptr(
>   new TransactionDBMutexFactoryImpl())),
> -  range_lock_mgr_(this) {
> +  range_lock_mgr_(this,
> +  txn_db_options_.custom_mutex_factory
> +?  txn_db_options_.custom_mutex_factory
> +: std::shared_ptr(
> +  new TransactionDBMutexFactoryImpl())) {
>   assert(db_impl_ != nullptr);
> }
> 
> diff --git a/utilities/transactions/pessimistic_transaction_db.h 
> b/utilities/transactions/pessimistic_transaction_db.h
> index 07ed3fed2..388ed4099 100644
> --- a/utilities/transactions/pessimistic_transaction_db.h
> +++ b/utilities/transactions/pessimistic_transaction_db.h
> @@ -127,10 +127,6 @@ class PessimisticTransactionDB : public TransactionDB {
>   virtual void UpdateCFComparatorMap(const std::vector&) 
> {}
>   virtual void UpdateCFComparatorMap(ColumnFamilyHandle*) {}
> 
> -  void KillLockWait(TransactionID txnid) override {
> -if (use_range_locking)
> -  range_lock_mgr_.KillLockWait(txnid);
> -  }
>  protected:
>   DBImpl* db_impl_;
>   std::shared_ptr info_log_;
> diff --git a/utilities/transactions/range_locking/locktree/lock_request.cc 
> b/utilities/transactions/range_locking/locktree/lock_request.cc
> index 0b96b2051..84cfdbf62 100644
> --- a/utiliti

Re: [Maria-developers] Deprecate INT(M) in SHOW?

[jocelyn fournier]

> Le 23 avr. 2018 à 15:51, Alexander Barkov  a écrit :
> 
> Does anybody see any problems if in 10.4 we fix all metadata statements,
> such as:
> 
> - SHOW CREATE TABLE t1
> - DESCRIBE t1
> - SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> not to print the default length for non-ZEROFILL columns?
> 

Hi Alexander,

The only issue I can see is for existing applications which try to parse the 
output of those statements and are expecting the length in their regex.
So I'm not sure it's worth changing this output?

  Jocelyn
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MDEV-14425: Is appending never slower than writing to a preallocated file?

[jocelyn fournier]

Hi Marko,

> Le 2 févr. 2018 à 12:27, Marko Mäkelä <marko.mak...@mariadb.com> a écrit :
> 
> I would like to redesign the InnoDB redo log format for better
> performance in MariaDB 10.4. Part of this would involve minimizing
> write amplification and optimizing for journaled file systems.
> 
> Jun Su from Microsoft suggested to me that regular writes (as opposed
> to appends) could cause some write amplification inside journaled file
> systems.
> 
> InnoDB traditionally pre-allocates both data and log files. Maybe it
> was a good idea in 1994 when the code was initially conceived. But we
> have had journaled or copy-on-write file systems and also SSDs for
> quite some time now.
> 
> I wrote two test programs that write a 2GiB file in 2KiB blocks,
> either pre-allocating the file upfront, or appending to the file. On
> the two SSDs that I tested (with ext4fs), appending was always faster.
> The programs are attached to
> https://jira.mariadb.org/browse/MDEV-14425
> 
> I would appreciate it if someone can provide a counterexample where
> writing to a preallocated file would be faster than appending, on a
> modern file system. Also, I would like to see how HDDs would perform.


I've just tested your program on an XFS filesystem, the preallocate performance 
is almost the same than append:

time ./append foo1 ./append foo2 ./append foo3 ./append foo4 

 <12:57:26
[1] 38171
[2] 38172
[3] 38174
./append foo3  0.03s user 1.86s system 30% cpu 6.297 total
./append foo1  0.03s user 1.81s system 28% cpu 6.554 total
./append foo2  0.02s user 1.99s system 30% cpu 6.610 total
./append foo4  0.02s user 1.84s system 27% cpu 6.711 total

time ./preallocate foo1 ./preallocate foo2 ./preallocate foo3 
./preallocate foo4  

<12:59:09
[1] 39461
[2] 39462
[3] 39464
./preallocate foo4  0.02s user 1.98s system 32% cpu 6.119 total
./preallocate foo1  0.03s user 2.01s system 33% cpu 6.120 total
./preallocate foo2  0.02s user 2.16s system 34% cpu 6.396 total 

 <12:59:16
./preallocate foo3  0.04s user 1.88s system 28% cpu 6.687 total 

 <12:59:17

Disk are 4 Intel SSD 520 (480GB) in Raid5 configuration on controller DELL PERC 
H710P
(tested several times)

I've also tested it with 4GB files, and on a slower system (still Intel SSD 
disks in Raid5 with XFS), append is also performing quite the same than 
preallocate.


HTH,

--
Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] 10.2 and merges (e.g. TokuDB)

[jocelyn fournier]

Hi Sergei!


Le 05/07/2017 à 15:36, Sergei Golubchik a écrit :

Hi, Phil!

On Jun 25, Phil Sweeney wrote:

Hi Sergei,

Appreciate you considering other options.  Unfortunately in this case
it seems Percona did not release a new 5.6 version in time for this
10.1.25 merge.

However, interestingly, they did tag a release for 5.7.18-15 in the
PerconaFT repository (which is common to both 5.6 and 5.7) in late
May, that did include the fix I wanted :)

Percona tells me that they will only be doing a 5.6 release once
Oracle releases 5.6.37, which could be quite some time (plus the time
for Percona to do their thing).

Is there potentially a way to deal with this, e.g. accept any tagged
release from PerconaFT (be it 5.6 or 5.7 .. but perhaps use a
different versioning indicator for consistency)

Yes. Technically, we can merge TokuDB from 5.7 into 10.2.
May be we start doing it later, after a first few GA releases of 10.2
This fix is really critical for TokuDB users, right now it's almost 
unusable (I have to fix the server for one of my customer several time a 
week because the cardinality reaches 0 due to this bug).

So the sooner the better :)


Thanks!
  Jocelyn

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] cte page

[jocelyn fournier]

Hi!


Le 27/04/2017 à 18:52, Galina Shalygina a écrit :

Hi!

I'm so sorry for this, I was in a hurry and chose a wrong address to 
mail:(


All in all, thank you! It is still a raw version and I will improve it.

Later I will send you a final version in a normal email:)

Sorry for this again,
Best regards,
Galina
Just curious, how MySQL 8 is performing compared to PostgreSQL and 
MariaDB? (I think it could be interesting since it uses both CTE merge & 
CTE reuse optimizations)


Thanks!
  Jocelyn

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] RFC: new replication feature "per-engine mysql.gtid_slave pos"

[jocelyn fournier]

Hi Kristian!

Le 08/03/2017 à 09:58, Kristian Nielsen a écrit :

Will Fong  writes:

But even better woulf be if most users did not have to do anything to get
the improved performance. Maybe we could just have a hardcoded list of
engines that are suitable for mysql.gtid_slave_pos (eg. innodb, tokudb,
myrocks). And replication will automatically create a suitable
mysql.gtid_slave_pos_XXX if it sees a transaction in one of those engines,
unless --skip-gtid-auto-create-pos-table is set. For more
exotic/experimental engine, the user can manually call
mysql.gtid_pos_add_engine() if desired.
Why not using the XA support flag of the engine to check if we should 
create the gtid_slave_pos_{engine} file ?


  Jocelyn


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

When running the server in debug, I hit the following assert :


2016-12-09 18:12:03 140057763633920 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001445' at position 4, relay log 
'./sql-slave-relay-bin.01' position: 4; GTID position '0-1-483188364'
mysqld: /home/jfournier/server/sql/rpl_parallel.cc:210: void 
finish_event_group(rpl_parallel_thread*, uint64, rpl_parallel_entry*, 
rpl_group_info*): Assertion `!tmp_gco->next_gco || tmp_gco->last_sub_id 
> sub_id' failed.

161209 18:12:06 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.1.19-MariaDB-debug
key_buffer_size=0
read_buffer_size=2097152
max_used_connections=0
max_threads=1502
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 
6183865 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x7f60fc016070
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f61e08f61b8 thread_stack 0x48400
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x29)[0xd349f9]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x2dd)[0x7b9f4d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7f61e5e2d330]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f61e5484c37]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f61e5488028]
/lib/x86_64-linux-gnu/libc.so.6(+0x2fbf6)[0x7f61e547dbf6]
/lib/x86_64-linux-gnu/libc.so.6(+0x2fca2)[0x7f61e547dca2]
/usr/local/mysql/bin/mysqld[0x745b63]
/usr/local/mysql/bin/mysqld(handle_rpl_parallel_thread+0x685)[0x7468b5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7f61e5e25184]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f61e554837d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x0): is an invalid pointer
Connection ID (thread ID): 5
Status: NOT_KILLED

HTH,


Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications

Le 09/12/2016 à 15:16, Kristian Nielsen a écrit :

jocelyn fournier <jocelyn.fourn...@softizy.com> writes:


It seems the entry corresponds to a table having a trigger, don't know
if it could have an impact on the parallel replication ?

Triggers should work ok, otherwise it would be a bug.

But it's hard to tell what is going on without a way to reproduce...
I don't recall seeing before the SQL "stopping by itself". Even if parallel
replication worker threads would somehow end up killing the SQL thread
instead of a conflicting parallel transaction, I would expect something in
the log...

  - Kristian.



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

 meta=0 nullable=1 is_null=1 */
###   @32=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @33=1481280635 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
###   @34=1481280635 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @35=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @36='cron-01' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
# at 1046751463
#161209 11:50:35 server id 1  end_log_pos 1046751490 Xid = 9097980179
COMMIT/*!*/;
# at 1046751490

SHOW CREATE TRIGGER pt_osc_sc_2_sc_feed_ins\G
*** 1. row ***
   Trigger: pt_osc_sc_2_sc_feed_ins
  sql_mode: 
NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER 
`pt_osc_sc_2_sc_feed_ins` AFTER INSERT ON `sc_2`.`sc_feed` FOR EACH ROW 
REPLACE INTO `sc_2`.`_sc_feed_new` (`id`, `user_id`, `user_id_scout`, 
`product_id`, `contact_id`, `collection_id`, `type_id`, `subtype_id`, 
`rating`, `is_shopping_list`, `is_wish_list`, `is_wish_list_bis`, 
`is_recommend`, `is_review`, `is_current`, `list_id_product`, 
`list_id_list`, `is_like`, `is_comment_list`, `is_comment_review`, 
`feed_id_related`, `format_id`, `badge_id`, `param_index_id_trailer`, 
`message_id`, `shout_id`, `forum_topic_id`, `is_archive`, 
`application_id`, `gen_like_count`, `gen_comment_count`, 
`gen_message_topic_id`, `date_creation`, `date_last_update`, `flag_gen`, 
`host`) VALUES (NEW.`id`, NEW.`user_id`, NEW.`user_id_scout`, 
NEW.`product_id`, NEW.`contact_id`, NEW.`collection_id`, NEW.`type_id`, 
NEW.`subtype_id`, NEW.`rating`, NEW.`is_shopping_list`, 
NEW.`is_wish_list`, NEW.`is_wish_list_bis`, NEW.`is_recommend`, 
NEW.`is_review`, NEW.`is_current`, NEW.`list_id_product`, 
NEW.`list_id_list`, NEW.`is_like`, NEW.`is_comment_list`, 
NEW.`is_comment_review`, NEW.`feed_id_related`, NEW.`format_id`, 
NEW.`badge_id`, NEW.`param_index_id_trailer`, NEW.`message_id`, 
NEW.`shout_id`, NEW.`forum_topic_id`, NEW.`is_archive`, 
NEW.`application_id`, NEW.`gen_like_count`, NEW.`gen_comment_count`, 
NEW.`gen_message_topic_id`, NEW.`date_creation`, NEW.`date_last_update`, 
NEW.`flag_gen`, NEW.`host`)

  character_set_client: latin1
  collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)



Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications

Le 09/12/2016 à 13:29, jocelyn fournier a écrit :


Ok, to make it clear, here are the actions related to the log entries :


*** Server startup : ***

2016-12-09 11:48:05 140259564768000 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482499150'
2016-12-09 11:48:05 140259546905344 [Note] Slave SQL thread 
initialized, starting replication in log 'mysql-bin.001443' at 
position 973807119, relay log './sql-slave-relay-bin.01' position: 
4; GTID position '0-1-482499150'


stop slave; set global slave_parallel_mode=optimistic;

2016-12-09 11:50:29 140259546905344 [Note] Error reading relay log 
event: slave SQL thread was killed
2016-12-09 11:50:29 140259564768000 [Note] Slave I/O thread exiting, 
read up to log 'mysql-bin.001443', position 1041861449; GTID position 
0-1-482509090


*** start slave; ***

2016-12-09 11:50:43 140259546905344 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482509090'
2016-12-09 11:50:43 140259548117760 [Note] Slave SQL thread 
initialized, starting replication in log 'mysql-bin.001443' at 
position 1041861449, relay log './sql-slave-relay-bin.01' 
position: 4; GTID position '0-1-482509090'


*** SQL thread stopping by itself : ***

2016-12-09 11:50:45 140259548117760 [Note] Error reading relay log 
event: slave SQL thread was killed


(no message "Slave SQL thread exiting...")

Associated show slave status :

MariaDB [(none)]> show slave status\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.4.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001443
Read_Master_Log_Pos: 1047027234
Relay_Log_File: sql-slave-relay-bin.02
Relay_Log_Pos: 4890750
Relay_Master_Log_File: mysql-bin.001443
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: sc_2,percona
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1046751490
Relay_Log_Space: 5166796
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

Ok, to make it clear, here are the actions related to the log entries :


*** Server startup : ***

2016-12-09 11:48:05 140259564768000 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482499150'
2016-12-09 11:48:05 140259546905344 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 973807119, 
relay log './sql-slave-relay-bin.01' position: 4; GTID position 
'0-1-482499150'


stop slave; set global slave_parallel_mode=optimistic;

2016-12-09 11:50:29 140259546905344 [Note] Error reading relay log 
event: slave SQL thread was killed
2016-12-09 11:50:29 140259564768000 [Note] Slave I/O thread exiting, 
read up to log 'mysql-bin.001443', position 1041861449; GTID position 
0-1-482509090


*** start slave; ***

2016-12-09 11:50:43 140259546905344 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482509090'
2016-12-09 11:50:43 140259548117760 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 1041861449, 
relay log './sql-slave-relay-bin.01' position: 4; GTID position 
'0-1-482509090'


*** SQL thread stopping by itself : ***

2016-12-09 11:50:45 140259548117760 [Note] Error reading relay log 
event: slave SQL thread was killed


(no message "Slave SQL thread exiting...")

Associated show slave status :

MariaDB [(none)]> show slave status\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.4.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001443
Read_Master_Log_Pos: 1047027234
Relay_Log_File: sql-slave-relay-bin.02
Relay_Log_Pos: 4890750
Relay_Master_Log_File: mysql-bin.001443
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: sc_2,percona
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1046751490
Relay_Log_Space: 5166796
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-482509567
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
1 row in set (0.00 sec)


*** start slave; ***

2016-12-09 11:52:03 140259547511552 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 1046751490, 
relay log './sql-slave-relay-bin.02' position: 4890750; GTID 
position '0-1-482509269'


*** SQL thread stopping by itself ***

This time no slave "SQL thread was killed" message.

2016-12-09 11:52:05 140259547511552 [Note] Slave SQL thread exiting, 
replication stopped in log 'mysql-bin.001443' at position 1047219058; 
GTID position '0-1-482509775'


MariaDB [(none)]> show slave status\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.4.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001444
Read_Master_Log_Pos: 6700262
Relay_Log_File: sql-slave-relay-bin.02
Relay_Log_Pos: 5358318
Relay_Master_Log_File: mysql-bin.001443
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: sc_2,percona
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1047219058
Relay_Log_Space: 38586524
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-1-482514272
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
1 row in set (0.00 sec)


HTH,


Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications

Le 09/12/2016 à 12:01, jocelyn fournier a écrit :

Hi Kristian,


I've just tried your tokudb_optimistic_parallel_replication branch, 
and it behaves very strangely: the SQL thread stop by itself without 
any replication error when the parallel_mode is set to optimistic.


In the error.log :

2016-12-09 11:48:05 140259564768000 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

Actually it's normal stop slave.

The first one is when I shutted down the server and switched to the new 
version. The second one when I stop slave & set global 
slave_parallel_mode=optimistic; and the last one when I put back the 
conservative mode.


So there's actually nothing in the log related to the Slave_SQL_Running 
stopping by itself, while the Slave_IO_Running is still set to Yes.




Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications

Le 09/12/2016 à 12:51, Kristian Nielsen a écrit :

jocelyn fournier <jocelyn.fourn...@softizy.com> writes:


I've just tried your tokudb_optimistic_parallel_replication branch,
and it behaves very strangely: the SQL thread stop by itself without
any replication error when the parallel_mode is set to optimistic.

That's strange, the log looks like a normal STOP SLAVE.
Do you have a way I could reproduce the issue?

  - Kristian.



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

Hi Kristian,


I've just tried your tokudb_optimistic_parallel_replication branch, and 
it behaves very strangely: the SQL thread stop by itself without any 
replication error when the parallel_mode is set to optimistic.


In the error.log :

2016-12-09 11:48:05 140259564768000 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482499150'
2016-12-09 11:48:05 140259546905344 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 973807119, 
relay log './sql-slave-relay-bin.01' position: 4; GTID position 
'0-1-482499150'
2016-12-09 11:50:29 140259546905344 [Note] Error reading relay log 
event: slave SQL thread was killed
2016-12-09 11:50:29 140259564768000 [Note] Slave I/O thread exiting, 
read up to log 'mysql-bin.001443', position 1041861449; GTID position 
0-1-482509090
2016-12-09 11:50:43 140259546905344 [Note] Slave I/O thread: connected 
to master 'repl@172.16.4.1:3306',replication starts at GTID position 
'0-1-482509090'
2016-12-09 11:50:43 140259548117760 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 1041861449, 
relay log './sql-slave-relay-bin.01' position: 4; GTID position 
'0-1-482509090'
2016-12-09 11:50:45 140259548117760 [Note] Error reading relay log 
event: slave SQL thread was killed
2016-12-09 11:52:03 140259547511552 [Note] Slave SQL thread initialized, 
starting replication in log 'mysql-bin.001443' at position 1046751490, 
relay log './sql-slave-relay-bin.02' position: 4890750; GTID 
position '0-1-482509269'
2016-12-09 11:52:05 140259547511552 [Note] Slave SQL thread exiting, 
replication stopped in log 'mysql-bin.001443' at position 1047219058; 
GTID position '0-1-482509775'


Switching back to conservative mode, and all is working properly. Any 
idea what could be wrong?



Thanks!


Jocelyn Fournier
Founder
M : +33 6 51 21 54 10
https://www.softizy.com
Softizy - At your side to Optimize your PHP / MySQL applications

Le 28/11/2016 à 10:10, Kristian Nielsen a écrit :

Parallel replication so far did not work well with TokuDB, as some people
who tried it found out. I have now pushed to 10.1 some patches to solve the
problems. There are two main fixes:

1. Fix some races where a waiting transaction would miss its wakeup and get
a lock timeout on a waiting row lock, even though the lock was released by
the holding transaction. This fix is due to great work by Rich Prohaska.
This problem is actually not specific to replication, normal transactions on
a master will experience it too. But it hurts replication a lot, since
replication must commit transactions in order, so one stalled transaction
stalls all following transactions as well.

2. Implement the conflict detection and handling necessary for optimistic
parallel replication to work. This basically implements
thd_rpl_deadlock_check() and hton->kill_query methods. This should solve the
problems where optimistic parallel replication with TokuDB breaks with lock
wait timeouts.

If someone wants to test it, I have made a tree available with just these
fixes on top of MariaDB 10.1.19:

   
https://github.com/knielsen/server/tree/tokudb_optimistic_parallel_replication

The fix should appear in 10.1.20 eventually.

The first part of the patch has also been submitted by Rich to
upstream. When this is (hopefully) merged upstream, and upstream merged into
MariaDB, the MariaDB version of the fix should be replaced with the Percona
one. I tried making the MariaDB version of the fix identical to Rich's pull
request and keeping it in a separate commit, so this should hopefully be
simple to do when the time comes.

  - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Fix for TokuDB and parallel replication

[jocelyn fournier]

Hi Kristian!


Thanks for the fix, I should be able to give it a try sometimes this week.


  Jocelyn


Le 28/11/2016 à 10:10, Kristian Nielsen a écrit :

Parallel replication so far did not work well with TokuDB, as some people
who tried it found out. I have now pushed to 10.1 some patches to solve the
problems. There are two main fixes:

1. Fix some races where a waiting transaction would miss its wakeup and get
a lock timeout on a waiting row lock, even though the lock was released by
the holding transaction. This fix is due to great work by Rich Prohaska.
This problem is actually not specific to replication, normal transactions on
a master will experience it too. But it hurts replication a lot, since
replication must commit transactions in order, so one stalled transaction
stalls all following transactions as well.

2. Implement the conflict detection and handling necessary for optimistic
parallel replication to work. This basically implements
thd_rpl_deadlock_check() and hton->kill_query methods. This should solve the
problems where optimistic parallel replication with TokuDB breaks with lock
wait timeouts.

If someone wants to test it, I have made a tree available with just these
fixes on top of MariaDB 10.1.19:

   
https://github.com/knielsen/server/tree/tokudb_optimistic_parallel_replication

The fix should appear in 10.1.20 eventually.

The first part of the patch has also been submitted by Rich to
upstream. When this is (hopefully) merged upstream, and upstream merged into
MariaDB, the MariaDB version of the fix should be replaced with the Percona
one. I tried making the MariaDB version of the fix identical to Rich's pull
request and keeping it in a separate commit, so this should hopefully be
simple to do when the time comes.

  - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [Commits] 4bff8b0: MDEV-7004 - Merge scalability fixes from 10.0-power

[jocelyn fournier]

Hi Sergey,

Just to be sure, currently MariaDB doesn't implement any Hardware 
Transactional Memory Built-in Functions ?

If so, is there any plan to evaluate the gain they could offer to MariaDB ?

Thanks and regards,
  Jocelyn Fournier

Le 04/12/2014 14:45, s...@mariadb.org a écrit :

revision-id: 4bff8b0b03ba82cd0ae6817b54de1780e89047ca
parent(s): 3526fe1b6000b2558290007e5cc3a1018850787c
committer: Sergey Vojtovich
branch nick: 10.1
timestamp: 2014-12-04 17:44:46 +0400
message:

MDEV-7004 - Merge scalability fixes from 10.0-power

Preallocate dynamic array and bitmap on mem_root to avoid expensive malloc.
This reduces number of allocations from 39 to 31 per OLTP RO transaction.

---
  sql/opt_range.cc | 12 ++--
  1 file changed, 6 insertions(+), 6 deletions(-)

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 1a9ae3d..813508c 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1838,8 +1838,6 @@ void SQL_SELECT::cleanup()
index= key_nr;
head=  table;
key_part_info= head-key_info[index].key_part;
-  my_init_dynamic_array(ranges, sizeof(QUICK_RANGE*), 16, 16,
-MYF(MY_THREAD_SPECIFIC));
  
/* 'thd' is not accessible in QUICK_RANGE_SELECT::reset(). */

mrr_buf_size= thd-variables.mrr_buff_size;
@@ -1857,9 +1855,12 @@ void SQL_SELECT::cleanup()
file= head-file;
record= head-record[0];
  
-  /* Allocate a bitmap for used columns (Q: why not on MEM_ROOT?) */

-  if (!(bitmap= (my_bitmap_map*) my_malloc(head-s-column_bitmap_size,
-   MYF(MY_WME | MY_THREAD_SPECIFIC
+  my_init_dynamic_array2(ranges, sizeof(QUICK_RANGE*),
+ thd-alloc(sizeof(QUICK_RANGE*) * 16), 16, 16,
+ MYF(MY_THREAD_SPECIFIC));
+
+  /* Allocate a bitmap for used columns */
+  if (!(bitmap= (my_bitmap_map*) thd-alloc(head-s-column_bitmap_size)))
{
  column_bitmap.bitmap= 0;
  *create_error= 1;
@@ -1923,7 +1924,6 @@ void QUICK_RANGE_SELECT::range_end()
  }
  delete_dynamic(ranges); /* ranges are allocated in alloc */
  free_root(alloc,MYF(0));
-my_free(column_bitmap.bitmap);
}
my_free(mrr_buf_desc);
DBUG_VOID_RETURN;
___
commits mailing list
comm...@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] doubt about read uncommited

[jocelyn fournier]

Hi Jean,

Read uncommited is definitly treated differently on TokuDB (no snapshot 
read). You have a description of the kind of lock taken on TokuDB 
depending on the isolation level here :

https://github.com/Tokutek/tokudb-engine/wiki/Transactions-and-Concurrency

  Jocelyn




Le 25/11/2014 04:04, Jean Weisbuch a écrit :
Transaction level only has effect on transactional engines (and note 
that read uncommited is treated as read commited on TokuDB if i recall 
correctly) thus the variable has no effect on MyISAM and Aria.


Le 25/11/2014 03:48, Roberto Spadim a écrit :
hi guys, i use read uncommited sometimes with innodb, that's nice and 
work

but now, i'm using a myisam table, and a aria table
does read uncommited work with this kind of engine? i tested and 
table stay 'waiting table lock' while a long update occurs
i don't know what's the internal diference but is possible to 
easily implement read uncommited to myisam/aria?


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [Commits] Rev 4275: MDEV-5867 ALTER TABLE t1 ENGINE=InnoDB keeps bad options when t1 ENGINE is CONNECT

[Jocelyn Fournier]

Hi,

Why not displaying the ignored options as comments ?

  Jocelyn


Le 08/07/2014 11:34, Sergey Vojtovich a écrit :

Hi Sergei,

frankly speaking I have no better idea. Let's give your patch a try.

Regards,
Sergey

On Tue, Jul 08, 2014 at 10:43:24AM +0200, Sergei Golubchik wrote:

Hi, Sergey!

On Jul 08, Sergey Vojtovich wrote:

Hi Sergei,

I believe options handling is way too confusing in general:
- CREATE TABLE warns/errors about unknown options
- ALTER TABLE ... ENGINE doesn't
- ALTER TABLE ... UNKNOWN_OPTION=1 does
- CREATE TABLE ... LIKE doesn't


This is consistent. You get a warning or an error about options that you
explicitly specify, but not about options that are implicitly ihnerited.


- different engines may assign different meaning to the same option
- IGNORE_BAD_TABLE_OPTIONS doesn't actually _ignore_ _bad_ options but rather
   _accepts_ _unkown_ options


Yes, I know :(

That's why I tried to say in the manual that SHOW CREATE TABLE *filters
out* invalid options, and IGNORE_BAD_TABLE_OPTIONS disables this
filtering, they are ignored and not filtered out.


But the question is if we should show unknown options by default. I
have no opinion: both cases are equally wrong to me.

On the one hand we get unusable CREATE TABLE statement, on the other
hand hidden metadata which may affect further statements.

I'm fine with hidding unknown options that were accepted due to
IGNORE_BAD_TABLE_OPTIONS, but hidding things that were accepted by
fully successful ALTER TABLE statement sounds confusing.


Do you have a better idea?

My logic was - SHOW CREATE TABLE should generate a valid CREATE TABLE
statement.

Regards,
Sergei



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [GSoC] self-tuning optimizer

[Jocelyn Fournier]

Hi,

In this case WHERE a  6 should have a better chance to trigger a full 
table scan than a  21.


  Jocelyn

Le 11/06/2014 09:51, Anshu Avinash a écrit :

Hi serg,

Yes, I had tried that. I had tried many combinations. I have updated the
test however on github code:
https://github.com/igniting/server/tree/selfTuningOptimizer.

Regards
Anshu


On Wed, Jun 11, 2014 at 1:13 PM, Sergei Golubchik s...@mariadb.org
mailto:s...@mariadb.org wrote:

Hi, Anshu!

On Jun 11, Anshu Avinash wrote:
  Hi serg,
 
  I have attached the updated diff. I'm still unable to observe the
effect
  introduced by the cost factors. Maybe I just need to study the
usage of
  scan_time() and read_time() in greater detail.

Ok, see one suggestion below. If that won't help - push your changes to
github, I'll try myself.

  diff --git a/mysql-test/r/costmodel.result
b/mysql-test/r/costmodel.result
  new file mode 100644
  index 000..6668758
  --- /dev/null
  +++ b/mysql-test/r/costmodel.result
  @@ -0,0 +1,20 @@
  +DROP TABLE IF EXISTS t1;
  +CREATE TABLE t1 (a int auto_increment primary key, b int);
  +INSERT INTO t1(b) values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
  +(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25);
  +EXPLAIN
  +SELECT * FROM t1
  +WHERE a  21;
  +id   select_type table   typepossible_keys   key
key_len ref rowsExtra
  +1SIMPLE  t1  range   PRIMARY PRIMARY 4   NULL5
 Using index condition
  +use mysql;
  +UPDATE optimizer_cost_factors
  +SET const_value=2.0
  +WHERE const_name='SCAN_TIME_FACTOR';
  +use test;
  +EXPLAIN
  +SELECT * FROM t1
  +WHERE a  21;
  +id   select_type table   typepossible_keys   key
key_len ref rowsExtra
  +1SIMPLE  t1  range   PRIMARY PRIMARY 4   NULL5
 Using index condition
  +DROP TABLE t1;

See? Your first EXPLAIN already shows type=range. And then you increase
the cost of the table scan. Of course, nothing can happen anymore.

Try to make the first explain to show type=ALL (which means table scan),
then increase the cost of the table scan and see how optimizer will
switch to type=range.

Regards,
Sergei




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [Maria-discuss] PERFORMANCE_SCHEMA to be disabled in 10.0 (10.0.12)

[Jocelyn Fournier]

Hi,

Never used it on production. From my point of view, it should be 
disabled by default, but included.


The whole purpose of this variable is to have a way to analyze/optimize 
performances easily, when needed; if it's enable by default, we have a 
perf hit, so that's counterproductive.
I assume it should be possible to work to remove the performance hit 
when disabled but included ?



Thanks,
  Jocelyn

Le 03/06/2014 10:20, Colin Charles a écrit :

Hi all,

Recently there was chat about how PERFORMANCE_SCHEMA was enabled by mistake and 
it should be disabled in the 10.0 series. I'm curious - how many of you are 
using PERFORMANCE_SCHEMA? Is it a problem to turn it on, if you use it?

I'm referring to:
https://mariadb.atlassian.net/browse/MDEV-6249

Looking from the webscalesql list, there are examples of performance 
degradation:
https://groups.google.com/d/msg/webscalesql/B-4bPIb8mHI/eUNsl0lSfbMJ
https://groups.google.com/d/msg/webscalesql/B-4bPIb8mHI/-YZMMCuPw3QJ

quote: Our perf testing agrees with your assessment (we see about a 5%-6% perf hit 
when it's included and on, and a 2%-3% hit when it's included but off)

Please discuss this, either here or on MDEV-6249

Thanks

cheers,
-colin

--
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 : maria-disc...@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MariaDB 10.1 is now on github!

[Jocelyn Fournier]

Hi Sergei,

Do you plan to integrate GitHub commit on JIRA as well ?

Thanks,
  Jocelyn


Le 25/05/2014 22:09, Sergei Golubchik a écrit :

Hi!

I'm happy to announce that MariaDB-10.1 tree has been completely
migrated to github. Since now on we'll use github for the new
development in MariaDB. It's https://github.com/MariaDB/server, go on,
fork it, hack around, submit pull requests. Have fun!

Older trees (10.0, 5.5, 5.3, 5.2, 5.1) are not on github - we do hope to
migrate them too eventually, but at the moment they are still on
launchpad.

If you're a maria-captain on launchpad - for you to get write access to
the mariadb repository on github you need to tell me your github account
name.

Regards,
Sergei

P.S.: Don't forget that 10.1 is still *pre-alpha*. You've been warned.


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] MariaDB 10.0.6 using more file descriptors than 5.5 ?

[Jocelyn Fournier]

Hi,

I've just migrated MariaDB (+tokudb) from 5.5.33a to 10.0.6, and I've 
encountered a '[ERROR] Error in accept: Too many open files' error 
during a backup.
As this was not occuring with MariaDB 5.5, is this expected to have 
10.0.6 using much more file descriptors ? (open_files_limit was set to 
about 8100 with 5.5, I raised it to 65535 now to fix this issue).


Thanks,
  Jocelyn Fournier

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] feedback on proposed fix to MySQL bug 57430

[Jocelyn Fournier]

Hi,

Will this fix also solve the bug http://bugs.mysql.com/bug.php?id=36817  
(and reopened in maria as https://bugs.launchpad.net/maria/+bug/639949 ) ?


Thanks and regards,
  Jocelyn Fournier


Le 19/05/2011 16:46, Michael Widenius a écrit :

Hi!


Zardosht == Zardosht Kasheffzardo...@gmail.com  writes:

Zardosht  The issue that I see with this proposal is that if quick_records is
Zardosht  much much greater than best_records, then we may not want to set
Zardosht  best_key to nr even though this nr is a covering index.

Agree, but this is what is happening with covering indexes anyway if
they where found before a normal index.

This comes from the code a bit higher up:

 if ((is_best_covering  !is_covering) ||
 (is_covering  ref_key_quick_rows  select_limit))
   continue;

The first part of this test means that as soon as we have found a
covering index, all other index are disregarded.
My change fixes things so that all clustered indexes are treated
equal, in spite of their position among the keys.

The right way to fix this is to calculate the cost of the index and
remove the testing of number of key parts and covering keys.

I will look at this as soon as all my other 5.3 tasks are done.
(ETA: 1-2 weeks)

Regards,
Monty

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MariaDB 5.2.5

[Jocelyn Fournier]

Hi,

The following paths in the Bug Fixes section of the release note 
http://kb.askmonty.org/v/mariadb-525-release-notes are wrong :


http://kb.askmonty.org/v/mariadb-5.2.5-changelog
http://kb.askmonty.org/v/mariadb-5.1.55-changelog


Regards,
  Jocelyn Fournier


Le 03/03/2011 18:05, Daniel Bartholomew a écrit :

Here are the download, release notes, and changelog pages for 5.2.5:

* http://downloads.askmonty.org/mariadb/5.2.5
* http://kb.askmonty.org/v/mariadb-525-release-notes
* http://kb.askmonty.org/v/mariadb-525-changelog

If any of you have a moment, please check over the above pages to see if
there are any mistakes or omissions and let me know (or jump in and fix
if you have editing rights).

Community team: I tested a sample of the mirrors and they all had the
files so please announce the release on the announce list, blogs, and
so on.

Thanks.



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB

[Jocelyn Fournier]

Hi,

Just FYI, it's created as https://bugs.launchpad.net/maria/+bug/639949
I really hope it could be fixed soon, since it could impact mysql 
performances for a lot of customers.


Thanks and regards,
  Jocelyn Fournier

Le 15/09/2010 18:43, Igor Babaev a écrit :

Jocelyn Fournier wrote:

  Hi Igor,

Just curious, is there any bug corresponding to this issue in launchpad ?

Hi Jocelyn,

No, feel yourself free to add it for MariaDB 5.1. In this case the
chances are higher that the problem will be fixed in one of the nearest
releases.

Regards,
Igor.


Thanks !
   Jocelyn


Le 27/08/10 18:04, Igor Babaev a écrit :

Jocelyn Fournier wrote:

   Hi,

There's a really annoying bug in the mysql optimiser existing for a
really long time now, and which has a direct impact on performances :

http://bugs.mysql.com/bug.php?id=36817

Since MySQL definitly doesn't seem to want to fix it, I hope MariaDB
could do something to help the poor developpers who need to heavily hack
there apps with USE INDEX ;)

Note I've tested with mariadb 5.2.1-beta, and the bug is still present.

Thanks a lot !
Jocelyn Fournier

Jocelyn,

I will take care of this bug.

Regards,
Igor.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MariaDB 5.2.3

[Jocelyn Fournier]

Hi Daniel,

On the release notes page ( 
http://kb.askmonty.org/v/mariadb-523-release-notes ) the latest link to 
the changelog still point to 5.2.2 instead of 5.2.3.


Cheers,
  Jocelyn


Le 10/11/10 06:06, Daniel Bartholomew a écrit :

All,

I've created the download, release notes, and changelog pages for
MariaDB 5.2.3:

http://askmonty.org/wiki/MariaDB:Download:MariaDB_5.2.3
http://kb.askmonty.org/v/mariadb-523-release-notes
http://kb.askmonty.org/v/mariadb-523-changelog

The mirrors are still updating currently but all of them should
have the release soon. If you can't wait, the osuosl.org mirror is the
primary one and is always up-to-date.

Please let me know if anything is missing from the release notes or
changelog or if there are any errors. If you have editing rights, feel
free to make the corrections or additions directly.

Thanks.



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB

[Jocelyn Fournier]

 Hi,

There's a really annoying bug in the mysql optimiser existing for a 
really long time now, and which has a direct impact on performances :


http://bugs.mysql.com/bug.php?id=36817

Since MySQL definitly doesn't seem to want to fix it, I hope MariaDB 
could do something to help the poor developpers who need to heavily hack 
there apps with USE INDEX ;)


Note I've tested with mariadb 5.2.1-beta, and the bug is still present.

Thanks a lot !
  Jocelyn Fournier


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Ipv6

[Jocelyn Fournier]

 Hi,

ipv6 INET_ATON and INET_NTOA would be mainly usefull to application 
compatibility, and painless ipv6 migration.


For example, in my PHP script I'm using almost everywhere INET_ATON to 
store $_SERVER['REMOTE_ADDR'].
If INET_ATON and NTOA are supported, I only have to change the column 
type storing my IP values to be able to store 128-bit integers, without 
changing anything else in the code.


Thanks,
  Jocelyn


Le 23/08/2010 18:53, Sergei Golubchik a écrit :

Hi, Jocelyn!

On Aug 23, Jocelyn Fournier wrote:

Hi Sergei,

What about INET_ATON / INET_NTOA ipv6 support ?
(there's a feature request opened on mysql since jan 2008 :
http://bugs.mysql.com/bug.php?id=34037 )

They would need to convert to and from DECIMAL.
It's doable.

If you mean 128-bit integers, adding them is more complex, I guess, than
adding a native ipv6 type (which is implemented on top of binary(16)
internally).

Why one would need INET_NTOA6 and INET_ATON6 ?

INET_ATON can be used to convert an ip address to store it in the table,
to save storage space.

INET_ATON can be used to convert an ip address to a number to check
against a subnet mask.

Anything else ?

Because if there is IPv6 native type there will be no need to convert
for storage and no need to convert for a mask check.

Regards,
Sergei

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] LIMIT optimisations

[Jocelyn Fournier]

Hi !

On a bulletin board context, that's quite simple :

Let's say we want to display a forum thread containing a lot of posts.

To simplify, I have the following table 'posts' which contains :

- id_thread
- id_post
- content
- id_author

If I want to display a paginated posts list of a given topic, with 30 
posts per page, I have to do :


SELECT content, author_name FROM posts LEFT JOIN author USING 
(id_author) ... WHERE id_thread= ORDER BY id_post ASC LIMIT x,30

I have a PK on (id_thread, id_post).

If I have a lot of posts in this thread, I could have easily a big LIMIT 
to get the last pages of the thread, which are the more often read (and 
the query will be triggered quite often especially if google like my 
bulletin board :)).
The current behaviour of MyISAM seems to be to always scan all the rows; 
than means if I have a LIMIT 12000,40, the first useless 12000 rows will 
be scanned, and this is especially bad if content is a TEXT field (no 
static lengths row here).

That means the more pages I have, to slower is the query.
The only workarounds I have found for now are :
- to maintain in my application a (id_post = position) mapping, 
which allows me to do a range search without limit clause which is quite 
fast.
- to do a SELECT id_post FROM id_thread= ORDER BY id_post ASC 
LIMIT x,1 and SELECT id_post FROM id_thread=... ORDER BY id_post ASC 
LIMIT x+31,1, and use these two positions to make a range search without 
limit clause (a kind of manually ICP)


HANDLER doesn't seem to solve the issue, because I cannot jump easily on 
the row number 12000 in index (id_thread, id_post). Moreover I cannot 
make my JOIN as well.


So to sum up :

- I want rows in key order, but key order should most of the time matches the 
row position
- I need to read a row from a specific position
- I don't need to read rows backwards

I don't know if index condition pushdown is currently used to optimize LIMIT 
clause, but than could indeed improve a lot those kind of issues.
MRR should not help here since usually rows in the data file are in the same 
order than in this specific index.


Thanks,
  Jocelyn



Le 10/03/10 21:12, Michael Widenius a écrit :

Hi!

Catching up with my old emails...
   

Jocelyn == Jocelyn Fournierj...@doctissimo.fr  writes:
 

Jocelyn  Hi,
Jocelyn  Following this discussion in 2007 : 
http://lists.mysql.com/internals/34287

Jocelyn  Is there any plan to implement such an optimisation in MariaDB ? (I
Jocelyn  think a lot of web app using pagination could take benefit of such an
Jocelyn  optimisation, although there are some workarounds to avoid big LIMIT 
for
Jocelyn  pagination)

Thanks for the reference to the old discussion, I had missed the
original and it was interesting reading.

The problem here is that if you do a lot of deletes of rows or updates
of keys, it would be hard to impossible to efficiently store a position
id for each row.

However some of the storage engines have a direct position to rows.

- For MyISAM (and Maria) with static lengths row, you can directly
   jump to row 'X'.

- For Maria, each 'dynamic length row' has an ID that one can use for
   positioning (There may be 'holes' in the ID, but that could be
   taken care of).

However, neither of the above would help if you want to have position
based on an index.

Exactly what problem is it that you want to solve ?

I saw your question of:

How to optimize select * from abc limit 1000,30?

Can't you use the HANDLER calls for this ?

(This allows you to freely read backwards/forwards on an index with limit)

In this case you don't know exactly where you are in the table, but
you can freely move 30 rows from your current position.

If you could describe your application a bit, there is maybe ways to
easily extend the handler interface to solve your problem...

For example, knowing this would help:
- Do you want rows in position order, not key order?
- Do you need to read a row from a specific position (1000) or
   do you need to read rows backward/forward based on an old position?
- Do you need to read rows backwards?

(By the way, I don't understand Sergei's comment about read_set in
this context)

Regards,
Monty

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp
   


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] LIMIT optimisations

[Jocelyn Fournier]

Hi,

Following this discussion in 2007 : http://lists.mysql.com/internals/34287

Is there any plan to implement such an optimisation in MariaDB ? (I 
think a lot of web app using pagination could take benefit of such an 
optimisation, although there are some workarounds to avoid big LIMIT for 
pagination)


Thanks !
  Jocelyn

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp