Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Improve performance of LOAD MYSQL SERVERS TO RUNTIME #829

Closed
renecannao opened this issue Dec 8, 2016 · 6 comments
Closed

Improve performance of LOAD MYSQL SERVERS TO RUNTIME #829

renecannao opened this issue Dec 8, 2016 · 6 comments
Assignees
@renecannao
Labels
ADMIN CONNECTION POOL cxx_dbx enhancement
Milestone
v1.4.0

Comments

@renecannao
Copy link
Contributor

For really large mysql_servers table, LOAD MYSQL SERVERS TO RUNTIME is time consuming.
This affects only extremely large setups.
Here is an example with 16k entries in mysql_servers:

mysql> select count(*) FROM mysql_servers;
+----------+
| count(*) |
+----------+
| 16000    |
+----------+
1 row in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (4.77 sec)

Query OK, 0 rows affected (4.84 sec)

Query OK, 0 rows affected (4.93 sec)

Query OK, 0 rows affected (5.00 sec)

Query OK, 0 rows affected (4.99 sec)
@renecannao renecannao added this to the v1.4.0 milestone Dec 8, 2016
@renecannao renecannao self-assigned this Dec 8, 2016
renecannao added a commit that referenced this issue Dec 9, 2016
@renecannao
Speed up LOAD MYSQL SERVERS TO RUNTIME #829
ba7e370 
This commit is able to speed up LOAD MYSQL SERVERS TO RUNTIME up to 10x for very large mysql_servers table with tens of thousands of servers.
Main improvements:
* removed 1 unnecessary call to `generate_mysql_servers_table()`
* replaced 1 call to `generate_mysql_servers_table()` with UPDATE statements when necessary
* implemented new function `servers_add()` that replaces `server_add()` to perform bulk operation
* replaced calls to `sqlite3_exec()` with proper parameters bindings
* use of multirows INSERT statements (32 rows at the time)

Miror improvements:
* added new variable mysql-hostgroup_manager_verbose , 1 by default for backward compatibility and debugging
* in not debug mode, access to HGM db has no shared cache
* in not debug mode, tables in HGM have checks disabled

Further note:
* Once issue #797 is completed, `mysql_servers` in MyHGM can be simplified further
@renecannao
Copy link
Contributor Author

The performance of LOAD MYSQL SERVERS TO RUNTIME are now very good:

mysql> select count(*) FROM mysql_servers;
+----------+
| count(*) |
+----------+
| 20000    |
+----------+
1 row in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.71 sec)

Query OK, 0 rows affected (0.77 sec)

Query OK, 0 rows affected (0.65 sec)

Query OK, 0 rows affected (0.66 sec)

Query OK, 0 rows affected (0.64 sec)

Query OK, 0 rows affected (0.64 sec)

Query OK, 0 rows affected (0.66 sec)

Query OK, 0 rows affected (0.65 sec)

@renecannao
Copy link
Contributor Author

Performance of SAVE MYSQL SERVERS TO MEMORY (or equivalent) still need some care:

mysql> select count(*) FROM mysql_servers;
+----------+
| count(*) |
+----------+
| 20000    |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) FROM runtime_mysql_servers;
+----------+
| count(*) |
+----------+
| 20000    |
+----------+
1 row in set (2.21 sec)

renecannao added a commit that referenced this issue Dec 9, 2016
@renecannao
Speed up SAVE MYSQL SERVERS TO MEMORY #829
cf7e64d 
This is achieved using multirows INSERTs and sqlite prepare
@renecannao
Copy link
Contributor Author

With mysql-hostgroup_manager_verbose disabled:

mysql> set mysql-hostgroup_manager_verbose=0; load mysql variables to runtime;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) FROM mysql_servers; 
+----------+
| count(*) |
+----------+
| 20000    |
+----------+
1 row in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.63 sec)

mysql> SAVE MYSQL SERVERS TO MEMORY;
Query OK, 0 rows affected (0.64 sec)

mysql> select count(*) FROM runtime_mysql_servers;
+----------+
| count(*) |
+----------+
| 20000    |
+----------+
1 row in set (0.62 sec)

renecannao added a commit that referenced this issue Dec 9, 2016
@renecannao
Speed up LOAD/SAVE mysql_servers #829
53d24f4 
A further optimization in `MySQL_HostGroups_Manager::generate_mysql_servers_table()` makes `LOAD MYSQL SERVERS TO RUNTIME` around 35 times faster than original implementation.
`SAVE MYSQL SERVERS TO MEMORY` is now over 10 times faster than the original implementation.
@renecannao
Copy link
Contributor Author

Performance keeps improve drastically:

mysql> select count(*) FROM mysql_servers;
+----------+
| count(*) |
+----------+
| 16000    |
+----------+
1 row in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
 Query OK, 0 rows affected (0.49 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.13 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.15 sec)

mysql> select count(*) FROM runtime_mysql_servers; 
+----------+
| count(*) |
+----------+
| 16000    |
+----------+
1 row in set (0.13 sec)

mysql> SAVE MYSQL SERVERS TO MEMORY;
Query OK, 0 rows affected (0.13 sec)

@skirmis
Copy link

skirmis commented Dec 9, 2016

👍 good job, thank you

@renecannao renecannao mentioned this issue Dec 23, 2016
Closed
@renecannao
Copy link
Contributor Author

Implemented in 1.4.0. Closing

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@renecannao renecannao

Labels
ADMIN CONNECTION POOL cxx_dbx enhancement
Projects
None yet
Milestone
v1.4.0
Development

No branches or pull requests
2 participants
@skirmis @renecannao