Re: [Maria-developers] How to change max key length for MyISAM?
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
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] Performance of index intersection
Hi Sergey! >> In the best >> case scenario, I think this could enable InnoDB to use its page directory >> to skip past some of the keys, improving the performance from O(n) to O(log >> n). > > Agree. > If the scans being merged produce data with non-overlapping rowid ranges, > then things could be sped up. I'm just wondering how often this is the case > in practice. Do you have any thoughts this? In case of a ecommerce solution using a Faceted Search, it happens really often! Usually users are moving away the Search part from MySQL to ElasticSearch / Algolia because the index merge performance is really poor. If we have this missing feature, it could improve a lot performances of such cases! BR, 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] [Commits] 190a29d06: Make Lock Tree's lock waits use externally provided mutexes/conditions
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/ut
Re: [Maria-developers] Deprecate INT(M) in SHOW?
> 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?
Hi Marko, > Le 2 févr. 2018 à 12:27, Marko Mäkelä 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&time ./append foo2&time ./append foo3&time ./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&time ./preallocate foo2&time ./preallocate foo3&time ./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)
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
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"
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
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 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
ull=1 */ ### @31=NULL /* INT 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_Behin
Re: [Maria-developers] Fix for TokuDB and parallel replication
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 th
Re: [Maria-developers] Fix for TokuDB and parallel replication
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 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
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
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
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
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
Hi Sergei, Le 08/07/2014 12:25, Sergei Golubchik a écrit : Hi, Jocelyn! On Jul 08, Jocelyn Fournier wrote: Hi, Why not displaying the ignored options as comments ? Jocelyn Good question. This is easy to do, I've just tried. It might look confusing, though, the table from connect.alter test would then be: SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `d` char(10) NOT NULL /* `FLAG`=11 */, `c` int(11) NOT NULL /* `FLAG`=0 */ ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 /* `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1 */ Perhaps something like : SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `d` char(10) NOT NULL /* `FLAG`=11 */, `c` int(11) NOT NULL /* `FLAG`=0 */ ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 /* Ignored Options : `TABLE_TYPE`=fix `FILE_NAME`='tf1.txt' `ENDING`=1 */ would be clearer ? 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] [Commits] Rev 4275: MDEV-5867 ALTER TABLE t1 ENGINE=InnoDB keeps bad options when t1 ENGINE is CONNECT
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
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 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)
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!
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] get is now a reserved word in mariadb 10 ?
Hi, I have a query with a column named "get". i.e. SELECT get FROM my_table... It works perfectly in 5.5, but in 10.0.6 it fails : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'get FROM my_table' at line 1 I assume "get" is a new reserved word, probably inherited from MySQL 5.6 memcached support, although memcached is not supported in MariaDB ? (it's mentionned here : http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-6.html#ftn.idm47406466381872 ) Is there a specific KB entry for MariaDB 10.0.6 reserved words ? (I only see an entry for 5.5 here : https://mariadb.com/kb/en/reserved-words/ ) Thanks and regards, 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] MariaDB 10.0.6 using more file descriptors than 5.5 ?
Hi, Actually on 5.5 I was also using TokuDB, I didn't convert my DB. And AFAIK 5.5 and 10.0.6 are using the same TokuDB version (7.0.4). Thanks, Jocelyn Le 20/11/2013 15:28, Jean Weisbuch a écrit : TokuDB uses many file descriptors, it should work the same on 5.5 and 10.x but if you came from an InnoDB DB it could be an issue if you didnt raise the open_files_limit. Here is an explaination on how TokuDB uses filedescriptors : https://github.com/Tokutek/ft-engine/wiki/TokuDB-Files-and-File-Descriptors Regards. On 20/11/2013 15:19, Jocelyn Fournier wrote: 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 ___ 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 ?
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
[Maria-developers] MariaDB & InnoDB native aio disabled
Hi, I've just figured out after installing MariaDB 5.5.23 on two server (one directly from the x64 binary, and the other using the debian packing) that innodb_use_native_aio is OFF on the two servers. Before upgrading to MariaDB, I was using percona 5.5.21-rel25.1-234.squeeze, and innodb_use_native_aio was properly used. There's nothing wrong reported in the MySQL log. The log with percona 5.5.21 : 120425 3:55:46 InnoDB: The InnoDB memory heap is disabled 120425 3:55:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120425 3:55:46 InnoDB: Compressed tables use zlib 1.2.3 120425 3:55:46 InnoDB: Using Linux native AIO 120425 3:55:46 InnoDB: Initializing buffer pool, size = 9.0G 120425 3:55:47 InnoDB: Completed initialization of buffer pool 120425 3:55:47 InnoDB: highest supported file format is Barracuda. 120425 3:55:50 InnoDB: Waiting for the background threads to start 120425 3:55:51 Percona XtraDB (http://www.percona.com) 1.1.8-rel25.1 started; log sequence number 33812567651 The log with mariadb 5.5.23 : 120425 3:41:04 InnoDB: The InnoDB memory heap is disabled 120425 3:41:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins 120425 3:41:04 InnoDB: Compressed tables use zlib 1.2.3.4 120425 3:41:04 InnoDB: Initializing buffer pool, size = 9.0G 120425 3:41:04 InnoDB: Completed initialization of buffer pool 120425 3:41:04 InnoDB: highest supported file format is Barracuda. 120425 3:41:08 InnoDB: Waiting for the background threads to start 120425 3:41:09 Percona XtraDB (http://www.percona.com) 1.1.8-24.1 started; log sequence number 33812411088 (no more line saying Linux native AIO is used) Am I missing something ? Thanks and regards, Jocelyn Fournier -- This message was sent from Launchpad by jocelyn fournier (https://launchpad.net/~joce) to each member of the Maria Discuss team using the "Contact this team" link on the Maria Discuss team page (https://launchpad.net/~maria-discuss). For more information see https://help.launchpad.net/YourAccount/ContactingPeople ___ 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
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 Kasheff 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
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
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
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
Re: [Maria-developers] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB
Hi Igor, Just curious, is there any bug corresponding to this issue in launchpad ? 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] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB
Thanks a lot Igor ! This will definitly help me to finished to convince my system admins to switch from MySQL to MariaDB ;) Jocelyn Le 27/08/2010 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
[Maria-developers] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB
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
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] Ipv6
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 ) Thanks, Jocelyn Le 23/08/2010 15:47, Sergei Golubchik a écrit : Hi, Dolf! On Aug 23, Dolf Schimmel wrote: My proposal would be to introduce two new datatypes, one for 128bit integers, and one extending the 128bit integer type for ipv6 addresses. Meaning that the latter accept ipv6 addresses only, but relies on the first one for storing it, and displaying it again as ipv6 address. With ipv6 one usually will do much more rangechecks than ipv4 because with ipv6 residential homes simply get a /64 block (2^(128-64)= a lot of addresses) instead of simply one address. Therefore a nice feature would be to allow queries like: `SELECT * FROM table WHERE columnName IN 2001:db8:85a3::8a2e:370:7334/62` which essentially would select all rows where columnName is in the same /64 block as 2001:db8:85a3::8a2e:370:7334 is. I'm a simple PHP developer so I can't supply a patch, but I would be really curious in what you think about the above, and hope someone is able and willing to pick it up. This looks moderately easy to do. Without 128-bit integers, at least. But you don't need them, as far as I understand, who needs math operations on ipv6 addresses. Making MySQL to use indexes for selects like in your example would be a bit more involved, but still doable. If, indeed, "someone is able and willing to pick it up", I could help this someone to understand what to change, where, and how. 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
Hi Mark ! Unfortunately, if you want to have the ability to jump to random page, you're stuck and cannot use this workaround ! And in the case of a forum, you're most of the time jumping to the latest page of a topic. Anyway I'm pretty sure we could add some optimisations to improve the current behaviour (like ICP), if it's not already the case (I've not checked this with MySQL 5.5 / Maria yet) Thanks, Jocelyn Le 11/03/10 01:35, MARK CALLAGHAN a écrit : On Wed, Mar 10, 2010 at 4:01 PM, Jocelyn Fournier wrote: 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). This is the behavior for all storage engines. I don't think you are going to get the optimization in MySQL that I think you are asking for. I have written about the performance problems of pagination and a workaround in http://www.facebook.com/note.php?note_id=206034210932. ___ 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
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 Fournier 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
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
