Merge lp:~guillaume-simon/mysql-utilities/mysql-utilities into lp:mysql-utilities

Proposed by Guillaume Simon
Status: Needs review
Proposed branch: lp:~guillaume-simon/mysql-utilities/mysql-utilities
Merge into: lp:mysql-utilities
Diff against target: 58 lines (+30/-18)
1 file modified
mysql/utilities/common/database.py (+30/-18)
To merge this branch: bzr merge lp:~guillaume-simon/mysql-utilities/mysql-utilities
Reviewer Review Type Date Requested Status
Oracle/MySQL Engineering Pending
Review via email: mp+204179@code.launchpad.net

Description of the change

When using mysqldiff or mysqlindexcheck with DB users having read access to many DBs objects (e.g tables), mysqldiff and others takes ages to execute (30+ secs). It can lead to DB timeouts issue when the value of interactive_timeout is too low, such "crashing" the execution of mysqldiff/mysqlindexcheck.

The issue comes in database.py/get_db_objects() where useless but resources intensive LEFT JOINS are performed when the method is executed with obj_type=_TABLE and columns="names" arguments.

The proposed patch just skipped those LEFT JOINS in this case, leading to a normal execution of mysqldiff even with many DB objects (back to a few seconds).

To post a comment you must log in.

Unmerged revisions

402. By Guillaume Simon

Optimization fix for users having accesses to many DBs and Tables

401. By Nelson Goncalves <email address hidden>

Release-1.3.6 testing

This patch fixes the result files for the following MUT tests:
rpl_admin_gtid_demote_master_repo_file,
rpl_admin_gtid_demote_master_repo_file_req_pass and
rpl_admin_gtid_demote_master_repo_table

400. By Israel Gomez

Release-1.3.6 preparation

Added changes to CHANGES.txt file.

399. By Nelson Goncalves <email address hidden>

Release-1.3.6 testing

This patch fixes an issue with mysqlfrm on Windows and it also fixes
the frm_reader_default MUT test.

398. By Israel Gomez

Release-1.3.6 testing

More changes to mac OSX package name, this patch removes the python
version from the name.

397. By Nelson Goncalves <email address hidden>

Release-1.3.6 testing

This patch fixes the following MUT tests:
audit_log_admin, audit_log_grep, check_rpl_parameters, check_gtid_version
check_rpl_errors, export_gtid, audit_log_admin_errors, clone_server_errors
connection_values, copy_db_errors and frm_reader_errors.

396. By Israel Gomez

Release-1.3.6 testing

This patch fixes the mac OSX package name, which was missing the
platform name and platform version. Also removes the '-1' from the
commercial msi package name.

v2

395. By Paulo Jesus

BUG#14725390 : multithreaded copy, export, import

This patch fixes regression issues introduced in failover and rpl_admin by the
previous patch for this bug. More specifically, error handling for replication
commands has been corrected and a commit is issued before enabling/disabling
the binary logging in order to close any active transaction (that would lead
to an error when executing the command). Other small adjustments were also
made to few rpl_admin test to run properly.

394. By Paulo Jesus

BUG#14725390 : multithreaded copy, export, import

The mysqldbexport, mysqldbimport and mysqldbcopy utilities are single
threaded, not taking advantages of concurrent executions to export, import and
copy multiple databases or tables (on multi-core systems). This patch adds
multiprocessing capabilities to those utilities in order to improve their
performance. Other optimizations were also made to avoid repeated processing
(e.g., remove duplicated privileges check) and reuse created object, reducing
the waste of CPU time.

Follows the summary of multiprocessing changes and performance improvement for
each utility.

mysqldbexport:

- Multiprocessing by database for non POSIX systems (Windows) and by table for
  POSIX systems, using the new --multiprocess option.
- New --output-file option was added to store the export results instead of
  using the standard output which can be very slow (up to 10x slower). Now,
  export results are always sent to a file (even if temporary) and only sent
  to the stdout at the end if needed.

mysqldbimport:

- Multiprocessing by file (independently of the operating systems), using the
  new --multiprocess option.
- Perform only a single COMMIT at the end of importing each file, by default.
  A new --autocommit option was added to allow each statement to be committed
  upon its execution.
- Improve the bulk insert feature (support SQL statements). If the
  --bulk-insert option is enabled, then all INSERT statements are parsed and
  their data aggregated (if possible) in order to be executed as a single
  query.
- A new --max-bulk-insert option was added to control the maximum size of the
  bulk insert, in order to avoid broken pipe errors. When the max number of
  insert is reached a new bulk is created.

mysqldbcopy:
- Multiprocessing by database for non POSIX systems (Windows) and by table for
  POSIX systems, using the new --multiprocess option.
- Remove/disable previous thread feature.

Some additional fixes and improvements were also made to the code. For
example: fix position of database lock for export; improve output of database
export with the --file-per-table option, fix execution of SET
FOREIGN_KEY_CHECKS to import data ensuring that statements are executed in the
same order they appear in the imported files; improve method exec_query() and
call method with appropriate options to avoid unnecessary commits; move
get_copy_lock() to dbexport module to remove circular import issue.

MUT tests were added and existing one changed for regression.

393. By Israel Gomez

BUG#17634676 : Add missing option to show license type on Utilities

This patch adds a new option to each MySQL Utility to show the license
type. The parameter '--license' can now be used to display the release
version, license type and full copyright message. In addition, the
'--version' option has been modified to display the release version
and the license type, without the copyright. The '--help' option will
also display the license type.

Tests were updated.

v2

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'mysql/utilities/common/database.py'
2--- mysql/utilities/common/database.py 2013-11-22 23:04:19 +0000
3+++ mysql/utilities/common/database.py 2014-01-31 10:14:13 +0000
4@@ -1275,24 +1275,36 @@
5 KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
6 """
7 minimal_pos_to_quote = (0, 1, 4, 11, 12, 13, 16, 17, 18, 19)
8- _OBJECT_QUERY = """
9- FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
10- TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
11- TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
12- LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON
13- TABLES.TABLE_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
14- AND
15- TABLES.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME
16- LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
17- TABLES.TABLE_SCHEMA = KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
18- AND
19- TABLES.TABLE_NAME = KEY_COLUMN_USAGE.TABLE_NAME
20- WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
21- GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
22- COLUMNS.ORDINAL_POSITION
23- ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
24- COLUMNS.ORDINAL_POSITION
25- """
26+ if columns == "names":
27+ _OBJECT_QUERY = """
28+ FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
29+ TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
30+ TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
31+ WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
32+ GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
33+ COLUMNS.ORDINAL_POSITION
34+ ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
35+ COLUMNS.ORDINAL_POSITION
36+ """
37+ else:
38+ _OBJECT_QUERY = """
39+ FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS ON
40+ TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA AND
41+ TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
42+ LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON
43+ TABLES.TABLE_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
44+ AND
45+ TABLES.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME
46+ LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
47+ TABLES.TABLE_SCHEMA = KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
48+ AND
49+ TABLES.TABLE_NAME = KEY_COLUMN_USAGE.TABLE_NAME
50+ WHERE TABLES.TABLE_SCHEMA = '%s' AND TABLE_TYPE <> 'VIEW' %s
51+ GROUP BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
52+ COLUMNS.ORDINAL_POSITION
53+ ORDER BY TABLES.TABLE_SCHEMA, TABLES.TABLE_NAME,
54+ COLUMNS.ORDINAL_POSITION
55+ """
56 exclude_param = "TABLES.TABLE_NAME"
57
58 elif obj_type == _VIEW:

Subscribers

People subscribed via source and target branches