- developers - lists.mariadb.org

[Maria-developers] Testing group commit with XtraBackup
by Kristian Nielsen 19 Apr '11

19 Apr '11

Hi Philip, I thought of another test of group commit that ought to be done (not sure why I didn't think to mention this earlier, sorry about that). It's not urgent, just something for the list. The test is basically the same you already did for non-blocking `mysqldump --master-data --single-transaction` used to provision a slave, but using XtraBackup instead of mysqldump. >From what I could learn, the original reason that InnoDB added the mutex that kills group commit when binlog is enabled is the following: To ensure that commit order in the InnoDB transaction log is the same as in the binlog. This is needed when XtraBackup (innobackup really, but they work the same and XtraBackup is Free Software) takes a non-blocking backup of the InnoDB data (only), and this is used to provision a new slave. If commit order was different, there might not be any binlog position on the master corresponding to the backup snapshot used to setup the new slave. Now, my group commit work also guarantees consistent commit order, but using a completely different method that does not kill group commit. So we ought to test this. So the test would be similar to your existing mysqldump test. But while the master is running, we would use non-blocking XtraBackup to take a snapshot. This snapshot we would then restore onto a new slave. The slave would then be configured with CHANGE MASTER TO, I believe one gets the correct binlog position to start from in the log file of XtraDB and/or slave mysqld (I need to check up on details). Then the test proceeds as the mysqldump case, letting the slave catch up and checking that replication is ok etc. What do you think? Now, to do this we would need to be able to use XtraBackup. I am wondering if it wouldn't make sense to at the same time include XtraBackup into MariaDB? XtraBackup seems to me quite mature already, and a very good product besides. It is arguably long overdue for us to include it. So if we include XtraBackup in mariadb-5.2-rpl (/5.3), I think this should be possible to setup without too much trouble (I can provide specific details/scripts for how to actually run XtraBackup and setup slave start position, I just need to research that a bit). Or we could also do the test first with XtraBackup as external, shouldn't be much harder, and would give us some experience with it. What do you think? Maybe we can look at it in more detail in Lisbon. - Kristian.

2 3

[Maria-developers] WL#197 New (by Mdcallag): table lock wait timeouts
by worklog-noreply＠askmonty.org 13 Apr '11

13 Apr '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: table lock wait timeouts CREATION DATE..: Wed, 13 Apr 2011, 17:09 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 197 (http://askmonty.org/worklog/?tid=197) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: MySQL has table_lock_wait_timeout but that doesn't do anything. For some apps it would help to timeout sessions blocked on a table lock. For example, when ALTER TABLE runs it implicitly gets an X lock on the table cache entry. Queries that follow are blocked until it finishes. The client-side read timeout might expire and disconnect the client but the server-side thread will continue blocking and the server can fill with blocked sessions. Right now the stack when the client blocks is open_tables -> open_table -> wait_for_condition. I implemented a demo for this by: 1) use pthread_cond_timedwait in wait_for_condition 2) check for timeout expiration in open_tables and error out Without the change in 2) the session will loop within open_tables on calls to open_table. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] Windows installer MWL#55 finished.
by Vladislav Vaintroub 13 Apr '11

13 Apr '11

> -----Original Message----- > From: kahzoo(a)gmail.com [mailto:kahzoo@gmail.com] On Behalf Of Kazuhisa > Ichikawa > Sent: Sonntag, 6. Februar 2011 09:32 > To: Vladislav Vaintroub > Cc: Philip Stoev; maria-developers(a)lists.launchpad.net > Subject: Re: [Maria-developers] Windows installer MWL#55 finished. Hello, Kazuhisa > No :-). My experience with MySQL installers is limited to fresh > install. This is the first time I tried 'upgrade' and my expectation > was purely imaginary. Right, there is no upgrade functionality in MySQLs installers up-to-now (nor I heard or any planning for it recently). But I noticed that both you and Philip had quite similar requests for the changed UI in case of "upgrade possible". Both of you recommended changes at the "Database Properties" dialog, I think it is wrong place, by the time you enter this dialog it is too late, you should not have entered this dialog, as the intention was not "create new", but "upgrade existing" anyway.. So since both of my users have pointed to usability issues, I added a new dialog that comes after license agreement that and tells user that upgradable instances were found and please make a choice between installing a new instance and not installing a new instance(optional upgrade one or more existing ones). This sounds almost like your first suggestion, but in own dialog. This dialog comes right before feature selection, and only thing it might do is to unselect "Database Instance" feature in the following dialog . Technically, it duplicates functionality of the feature selection tree. So what... I hope it adds some clarity for the user - at least tells the user that his existing instances *can* be upgraded. Or maybe it won't help that much, since user never looks at the UI anyway :) (nice article about that is here http://www.codinghorror.com/blog/2009/10/treating-user-myopia.html ) Anyway, I uploaded the newest msis to the usual place, if somebody wants to play with it. Thanks, Vladislav.

2 4

[Maria-developers] MariaDB 5.6
by Peter Laursen 12 Apr '11

12 Apr '11

I noticed this on planet.mysql: http://kb.askmonty.org/v/plans-for-56 I *again* strongly want to discourage a major version number identical with a MySQL/Oracle release. MySQL plans a 5.6 too and I believe that there is already a source-tree available on launchpad. I think I understand that MariaDB 5.6 is planned to use MySQL 5.5 codebase. Am I correct? The problem arises with *generic clients* (ie clients that are supposed to work with not only one specific server version but a broad range of server versions) - including all GUI clients. Such clients will execute a "SELECT VERSION()" after connection to decide what features in the client should be enabled or not (necessary as "SHOW VARIABLES LIKE 'have_***"" and "SELECT ... FROM I_S" cannot always be used due to incomplete instrumentation with those) I have now posted this complaint 2 or 3 times (including the times I complained about the use of 5.2 for a mariaDB based on MySQL 5.1 code as there is alose a (now abandoned) MySQL 5.2 tree. I never had a reply from a MariaDB 'decisionmaker'. Could I at least request a reply this time? Please! :-) I repeat: It is really causing problems for *generic clients* if there are too different 5.6's with different feature sets. What solution would you suggest for a client that shall be able to connect to both MySQL5.6 and MariaDB 5.6 without confusing the features of one server with the other? Peter Webyog

3 5

[Maria-developers] PBXT test failures after your patch for lp:732124
by Kristian Nielsen 12 Apr '11

12 Apr '11

Hi Monty, After you pushed the patch for lp:732124, test failures appeared in Buildbot for test case pbxt.union. Most were just that the pbxt union.result file needed same update as for main.union. But there was one more issue: create table t1 (a int); insert into t1 values (1),(2),(3); create table t2 (a int); insert into t2 values (3),(4),(5); # ... SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2; select found_rows(); In pbxt.union, this now (after your patch) return 5. However, the result file for main.union has a return value of 6, and the test succeeds with MyISAM. The thing is ... the value that 5 PBXT returns is correct, and what MyISAM does and what is in the result file is wrong. At least according to the docs. The full query would return 5 rows (as it is union distinct), not 6... What I will do is that I will fix the PBXT union.result to have the correct value "5" to get the test suite to pass in Buildbot. However, there seems to be some deeper problem here, given that MyISAM does something different (and wrong?). There are also other cases that are wrong: SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2; a 1 3 4 5 select found_rows(); found_rows() 6 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2; a 1 3 select found_rows(); found_rows() 6 (the result should have been 5) So maybe there are still bugs in the code? Or if this is a limitation of SQL_CALC_FOUND_ROWS with UNION (there is a comment in the test case "# The following examples will not be exact" that might hint at this), then we should document this at least ... - Kristian.

1 0

[Maria-developers] WL#196 New (by Mdcallag): add is_connection_alive for server-side checks to find disconnected clients
by worklog-noreply＠askmonty.org 08 Apr '11

08 Apr '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: add is_connection_alive for server-side checks to find disconnected clients CREATION DATE..: Fri, 08 Apr 2011, 14:06 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 196 (http://askmonty.org/worklog/?tid=196) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are a few places in the server where the thread for a user connection will wait. With upcoming features in the FB patch there will be more places. I don't want a thread to continue waiting after the client has disconnected. Is it possible to check whether the client has disconnected without reading/writing more than 0 bytes from the socket? Assuming the check is is_connection_alive(), it can return TRUE when the client has disconnected (but not all of the time). But it must not return FALSE when the client has not disconnected. The check does not have to be cheap. It can require a system call as it won't be made frequently. For example, assume that a row lock wait is implemented as a sequent of timed pthread condition variable waits (1 second each). At the end of each 1 second timeout there can be a check for whether the connection was killed and a check for whether the client has disconnected. Disconnected clients on the server-side can be a serious problem for high throughput servers. The worst case is when the client starts a long-running and blocking query (external sort) that won't return a row for a long time and thus won't detect the disconnected client for a long time. I have used tool in the past that join output from netstat with SHOW PROCESSLIST to find sockets for disconnected clients. This works but is very expensive. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#195 New (by Mdcallag): SHOW QUERY STATUS
by worklog-noreply＠askmonty.org 07 Apr '11

07 Apr '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: SHOW QUERY STATUS CREATION DATE..: Thu, 07 Apr 2011, 13:54 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 195 (http://askmonty.org/worklog/?tid=195) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: I want some performance counters to be maintained per-query in addition to per- session. This could then be used to implement SHOW QUERY STATUS. It could also be used to augment slow query log output with additional performance data per statement. The Facebook patch and probably Percona already put more data per statement in the slow query log. With the Facebook patch the option --log-slow- extra enables this. Unfortunately, that uses the session counters and can be confusing. Some details on the use of this in the facebook patch are at: https://www.facebook.com/note.php?note_id=390420710932 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Summary of the storage engine API changes for group commit
by Kristian Nielsen 07 Apr '11

07 Apr '11

Hi Serg, As promised, here is a summary of the changes to the storage engine API that I made as part of group commit. Two new handlerton methods are added: void (*prepare_ordered)(handlerton *hton, THD *thd, bool all); void (*commit_ordered)(handlerton *hton, THD *thd, bool all); commit_ordered() will be called just before commit(). It should commit the transaction in memory; calls to commit_ordered() will be serialised, so order of commit is determined by order of calls to commit_ordered(). MVCC engines should make the transaction visible. No time-consuming operations like flushing logs to disk should be done; this should happen in commit(). prepare_ordered() will be called just after prepare(). Calls will again be serialised and happen in commit order, so an engine can do here any part of the prepare step that depends on commit order. Again no time-consuming operations like I/O should be done. Order of calls to the new methods is guaranteed to be consistent between engines and with commit order in the binlog. Locking is done so that START TRANSACTION WITH CONSISTENT SNAPSHOT becomes consistent between storage engines. The methods are optional; a storage engine is free to not set them. Group commit will still work, but there will be no guarantee that commits happen in the same order in engine and in binlog, nor that START TRANSACTION WITH CONSISTENT SNAPSHOT will be consistent between engines/binlog. (The prepare_commit_mutex in built-in InnoDB will still ensure consistent commit order, and will still destroy group commit). There are no other changes to the storage engine API by me. Hope this helps, - Kristian.

3 4

[Maria-developers] Default parameter settings / Windows MSI installer
by Vladislav Vaintroub 07 Apr '11

07 Apr '11

Hello, After recent discussion about appropriate or not appropriate default setting on in my.cnf , I have created a prototype minimal settings for Windows installer. Here is the picture of this attempt http://cid-ff0c950417b4f8a4.office.live.com/self.aspx/share/dbsettings.png For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and input field for innodb bufferpool size (preset with RAM/8, in my case it is 8191/8=1023). "Standard configuration" is on by default, if it is "on", then 4 parameters are added to the my.ini file 1) default_storage_engine=innodb 2) innodb_buffer_pool_size=[VALUE_IN_EDIT_BOX]M 3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50) I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range. 4) sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION If "Standard configuration" is off, hen input field for bufferpool size is disabled, and none of those 4 parameters is added to my.ini Do you think it is useful in this form? So far I tried to avoid "calibration" topics altogether since it is a controversial topic where people never have same opinions. Also I tried to avoid it because I cannot write GUIs . However, this one change is small in and it does not introduce additional dialogs. It makes some important defaults the same as "standard configuration" in existing MySQL installers. I think it was PeterL who already asked for non-default buffer pool size already, also Monty also spoke about it in Lisbon. Comments welcome, also those on the picture above - I'm not sure if wording and use of brackets and quotes is correct. Wlad

5 8

Re: [Maria-developers] dist-hook in top-level Makefile.am
by Kristian Nielsen 06 Apr '11

06 Apr '11

"Vladislav Vaintroub" <wlad(a)montyprogram.com> writes: > Kristian, now that Windows builds in 5.2+ are self-sufficient and can > produce different packages from bzr, do you see any reason to keep this hook > in the top-level Makefile.am? > > If not, I can remove, or you can remove it if you prefer. Will make our > source distribution smaller/cleaner , if nothing else. > > I'm talking about this place in Makefile.am > > # Remove BK's "SCCS" subdirectories from source distribution > # Create initial database files for Windows installations and check them. > dist-hook: > mkdir -p $(distdir)/win > scripts/mysql_install_db --no-defaults --cross-bootstrap \ > --builddir=$(top_builddir) \ > --datadir=$(distdir)/win/data \ > --srcdir=$(top_srcdir) > storage/myisam/myisamchk --silent --fast > $(distdir)/win/data/mysql/*.MYI I do not have time to look deeply into this at the moment, but I did not want you to have to wait any longer for an answer, sorry for taking so long already. It is very good news that Windows builds are now self-sufficient! This need to compile the whole server just to produce a source tarball has been nagging me forever, and I suppose this has also been an annoyance on the Windows side when installing from source. You need to be careful when you remove it. Update any documentation that refers to the old way of installing from source on Windows, making sure existing stuff still works, etc. Well, I guess you are already aware of that. It would be nice to also fix the autotools builds so that `make dist` to produce a source tarball no longer needs to compile the whole server. But then, as we are phasing out autotools, it is probably not worth it. So I suppose just removing the dist-hook is a reasonable step if you make sure it does not break anything. I guess the CMake system already has this done properly, so that a source tarball is produced without needing to build the server (and without generating these obsolete database files for Windows)? - Kristian.

2 1