[0.5.2] Installation hangs
29 views
Skip to first unread message
Paolo
Apr 27, 2011, 10:16:00 AM4/27/11
to secu...@googlegroups.com
Hi!
I'm installing securich 0.5.2 on a mysql server 5.1.54 but it's hangs
when execute the following insert:
I'm installing securich 0.5.2 on a mysql server 5.1.54 but it's hangs
when execute the following insert:
insert into sec_db_tb (DB_ID,TB_ID)
select DB_ID,TB_ID
from
(
select db.ID as DB_ID, tb.ID as TB_ID
from sec_databases db, sec_tables tb join (
select TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_schema <> 'information_schema'
) nms
where nms.TABLE_SCHEMA = db.DATABASENAME and
nms.TABLE_NAME = tb.TABLENAME
UNION ALL
select DB_ID,TB_ID
from sec_db_tb
) tmp
group by DB_ID, TB_ID
having count(*) = 1;
A select count(*) on information_schema.tables return this:
mysql> select count(*)
-> from information_schema.tables
-> where table_schema <> 'information_schema';
- count(*)
- 15139
and I've a mysqld's process that are using 97% of cpu from two hours now
(Intel(R) Xeon(R) CPU X5460 @ 3.16GHz)
Is there an optimization to run that query faster?
Thank you.
Paolo.
Darren
Apr 27, 2011, 11:33:46 AM4/27/11
to secu...@googlegroups.com
Hi Paolo,
Performance tuning securich is the next target, and there is definitely room for that. I will be releasing 0.5.4 which will include some general bug fixes including some for 5.5 but in the meantime can you run the following two commands on your setup please?
explain extended select DB_ID,TB_ID
from
(
select db.ID as DB_ID, tb.ID as TB_ID
from sec_databases db, sec_tables tb join (
select TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_schema <> 'information_schema'
) nms
where nms.TABLE_SCHEMA = db.DATABASENAME and
nms.TABLE_NAME = tb.TABLENAME
UNION ALL
select DB_ID,TB_ID
from sec_db_tb
) tmp
group by DB_ID, TB_ID
having count(*) = 1;
show warnings;
cheers,
Darren
Performance tuning securich is the next target, and there is definitely room for that. I will be releasing 0.5.4 which will include some general bug fixes including some for 5.5 but in the meantime can you run the following two commands on your setup please?
explain extended select DB_ID,TB_ID
from
(
select db.ID as DB_ID, tb.ID as TB_ID
from sec_databases db, sec_tables tb join (
select TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_schema <> 'information_schema'
) nms
where nms.TABLE_SCHEMA = db.DATABASENAME and
nms.TABLE_NAME = tb.TABLENAME
UNION ALL
select DB_ID,TB_ID
from sec_db_tb
) tmp
group by DB_ID, TB_ID
having count(*) = 1;
cheers,
Darren
goliardico
Apr 28, 2011, 4:26:32 AM4/28/11
to Securich
Thank you for your reply, here are the results:
mysql> explain extended select DB_ID,TB_ID
-> from
-> (
-> select db.ID as DB_ID, tb.ID as TB_ID
-> from sec_databases db, sec_tables tb join (
-> select TABLE_SCHEMA, TABLE_NAME
-> where nms.TABLE_SCHEMA = db.DATABASENAME and
-> nms.TABLE_NAME = tb.TABLENAME
-> UNION ALL
-> select DB_ID,TB_ID
-> from sec_db_tb
-> ) tmp
-> group by DB_ID, TB_ID
-> having count(*) = 1;
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | filtered |
Extra |
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL |
NULL | NULL | 0 | 0.00 | const row not
found |
| 2 | DERIVED | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | no matching row in const
table |
| 3 | DERIVED | tables | ALL | NULL | NULL |
NULL | NULL | NULL | NULL | Using where; Skip_open_table;
Scanned all databases |
| 4 | UNION | sec_db_tb | index | NULL | PRIMARY |
12 | NULL | 1 | 100.00 | Using
index |
| NULL | UNION RESULT | <union2,4> | ALL | NULL | NULL
| NULL | NULL | NULL | NULL
| |
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
| Level | Code |
Message
| Note | 1003 | select NULL AS `DB_ID`,NULL AS `TB_ID` from (select
`securich`.`db`.`ID` AS `DB_ID`,`securich`.`tb`.`ID` AS `TB_ID` from
`securich`.`sec_databases` `db` join `securich`.`sec_tables` `tb` join
(select `tables`.`TABLE_SCHEMA` AS
`TABLE_SCHEMA`,`tables`.`TABLE_NAME` AS `TABLE_NAME` from
`information_schema`.`tables` where (`tables`.`TABLE_SCHEMA` <>
'information_schema')) `nms` where ((NULL =
convert(`securich`.`db`.`DATABASENAME` using utf8)) and (NULL =
convert(`securich`.`tb`.`TABLENAME` using utf8))) union all select
`securich`.`sec_db_tb`.`DB_ID` AS
`DB_ID`,`securich`.`sec_db_tb`.`TB_ID` AS `TB_ID` from
`securich`.`sec_db_tb`) `tmp` group by NULL,NULL having (count(0) = 1)
|
1 row in set (0.00 sec)
cheers,
Paolo.
mysql> explain extended select DB_ID,TB_ID
-> from
-> (
-> select db.ID as DB_ID, tb.ID as TB_ID
-> from sec_databases db, sec_tables tb join (
-> select TABLE_SCHEMA, TABLE_NAME
-> from information_schema.tables
-> where table_schema <> 'information_schema'
-> ) nms
-> where table_schema <> 'information_schema'
-> where nms.TABLE_SCHEMA = db.DATABASENAME and
-> nms.TABLE_NAME = tb.TABLENAME
-> UNION ALL
-> select DB_ID,TB_ID
-> from sec_db_tb
-> ) tmp
-> group by DB_ID, TB_ID
-> having count(*) = 1;
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | filtered |
Extra |
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL |
NULL | NULL | 0 | 0.00 | const row not
found |
| 2 | DERIVED | NULL | NULL | NULL | NULL |
NULL | NULL | NULL | NULL | no matching row in const
table |
| 3 | DERIVED | tables | ALL | NULL | NULL |
NULL | NULL | NULL | NULL | Using where; Skip_open_table;
Scanned all databases |
| 4 | UNION | sec_db_tb | index | NULL | PRIMARY |
12 | NULL | 1 | 100.00 | Using
index |
| NULL | UNION RESULT | <union2,4> | ALL | NULL | NULL
| NULL | NULL | NULL | NULL
| |
+----+--------------+------------+--------+---------------+---------
+---------+------+------+----------
+-----------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
| Level | Code |
Message
| Note | 1003 | select NULL AS `DB_ID`,NULL AS `TB_ID` from (select
`securich`.`db`.`ID` AS `DB_ID`,`securich`.`tb`.`ID` AS `TB_ID` from
`securich`.`sec_databases` `db` join `securich`.`sec_tables` `tb` join
(select `tables`.`TABLE_SCHEMA` AS
`TABLE_SCHEMA`,`tables`.`TABLE_NAME` AS `TABLE_NAME` from
`information_schema`.`tables` where (`tables`.`TABLE_SCHEMA` <>
'information_schema')) `nms` where ((NULL =
convert(`securich`.`db`.`DATABASENAME` using utf8)) and (NULL =
convert(`securich`.`tb`.`TABLENAME` using utf8))) union all select
`securich`.`sec_db_tb`.`DB_ID` AS
`DB_ID`,`securich`.`sec_db_tb`.`TB_ID` AS `TB_ID` from
`securich`.`sec_db_tb`) `tmp` group by NULL,NULL having (count(0) = 1)
|
1 row in set (0.00 sec)
cheers,
Paolo.
Darren
Apr 28, 2011, 5:33:21 PM4/28/11
to Securich
Hi Paolo,
Download and compile
http://securich.googlecode.com/svn/trunk/procedures/update_databases_tables_storedprocedures_list.sql
I tested it with 10000 tables on my laptop and it went down from
4minutes 30seconds to 1.7seconds. It is still not good enough and I
plan to work on it to make it better, but should help till I optimize
it further.
Regards,
Darren
Download and compile
http://securich.googlecode.com/svn/trunk/procedures/update_databases_tables_storedprocedures_list.sql
I tested it with 10000 tables on my laptop and it went down from
4minutes 30seconds to 1.7seconds. It is still not good enough and I
plan to work on it to make it better, but should help till I optimize
it further.
Regards,
Darren
goliardico
Apr 29, 2011, 9:40:33 AM4/29/11
to Securich
Well done! Now the installer moved to the next step in a few minutes
but slow down again during "Reconciling privileges between MySQL db
and Securich db started":
===============
Building Securich database ... done
Loading Securich db with data ... done
Loading securich stored procedures ... done
Reconciling privileges between MySQL db and Securich db started
Progress: 1% done in 57 min 5 sec
================
There are 3 or 4 thread running and the more often running query I see
is:
SELECT MIN(System) AS System, GRANTEE, TABLE_SCHEMA, TABLE_NAME,
PRIVILEGE, TYPE
FROM
(
SELECT 'MySQL' AS System,
inf_grantee_privileges.GRANTEE, inf_grantee_privileges.TABLE_SCHEMA,
inf_grantee_privileges.TABLE_NAME, inf_grantee_privileges.PRIVILEGE,
inf_grantee_privileges.TYPE
FROM inf_grantee_privileges
UNION ALL
SELECT 'Securich' AS System,
sec_grantee_privileges.GRANTEE, sec_grantee_privileges.TABLE_SCHEMA,
sec_grantee_privileges.TABLE_NAME, sec_grantee_privileges.PRIVILEGE,
sec_grantee_privileges.TYPE
FROM sec_grantee_privileges
) tmp
GROUP BY GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE,
TYPE
HAVING COUNT(*) = 1
ORDER BY GRANTEE;
Here are some other info on my system:
mysql> select count(*) from mysql.tables_priv;
+----------+
| count(*) |
+----------+
| 19219 |
+----------+
+----------+
| count(*) |
+----------+
| 0 |
+----------+
+----------+
| count(*) |
+----------+
| 11605 |
+----------+
back Monday, please ask me any question to help your work.
Thanks.
Paolo.
> Download and compilehttp://securich.googlecode.com/svn/trunk/procedures/update_databases_...
but slow down again during "Reconciling privileges between MySQL db
and Securich db started":
===============
Building Securich database ... done
Loading Securich db with data ... done
Loading securich stored procedures ... done
Reconciling privileges between MySQL db and Securich db started
Progress: 1% done in 57 min 5 sec
================
There are 3 or 4 thread running and the more often running query I see
is:
SELECT MIN(System) AS System, GRANTEE, TABLE_SCHEMA, TABLE_NAME,
PRIVILEGE, TYPE
FROM
(
SELECT 'MySQL' AS System,
inf_grantee_privileges.GRANTEE, inf_grantee_privileges.TABLE_SCHEMA,
inf_grantee_privileges.TABLE_NAME, inf_grantee_privileges.PRIVILEGE,
inf_grantee_privileges.TYPE
FROM inf_grantee_privileges
UNION ALL
SELECT 'Securich' AS System,
sec_grantee_privileges.GRANTEE, sec_grantee_privileges.TABLE_SCHEMA,
sec_grantee_privileges.TABLE_NAME, sec_grantee_privileges.PRIVILEGE,
sec_grantee_privileges.TYPE
FROM sec_grantee_privileges
) tmp
GROUP BY GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE,
TYPE
HAVING COUNT(*) = 1
ORDER BY GRANTEE;
Here are some other info on my system:
mysql> select count(*) from mysql.tables_priv;
+----------+
| count(*) |
+----------+
| 19219 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from mysql.columns_priv;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from securich.sec_db_tb;
+----------+
| count(*) |
+----------+
| 11605 |
+----------+
1 row in set (0.00 sec)
Now I must leave my office and I'm stopping the installation. I'll be
back Monday, please ask me any question to help your work.
Thanks.
Paolo.
> Download and compilehttp://securich.googlecode.com/svn/trunk/procedures/update_databases_...
Darren Cassar
Apr 29, 2011, 10:12:08 AM4/29/11
to secu...@googlegroups.com
Hi Paolo,
There are a couple of things we can do in order to increase the speed.
The installation script runs a `call reconciliation('list')` every few seconds to check how much work is left to do (that is where the percentage value is obtained from). In your case you might need to run it every 10minutes not every 5seconds so I will supply you with different version of the installer which enables this feature.
The second is to optimize all the procedures, which if you bear with me would be my pleasure to do since you have such a big setup and can give me better input for larger scale installations of securich.
Can you give me some statistics about your server?
How many users, how many databases, how many tables in each database, what kind of privileges do the users have (all privileges, a few privileges, just read)?
That will help me setup a similar scenario and test from my side too.
Regards,
Darren
There are a couple of things we can do in order to increase the speed.
The installation script runs a `call reconciliation('list')` every few seconds to check how much work is left to do (that is where the percentage value is obtained from). In your case you might need to run it every 10minutes not every 5seconds so I will supply you with different version of the installer which enables this feature.
The second is to optimize all the procedures, which if you bear with me would be my pleasure to do since you have such a big setup and can give me better input for larger scale installations of securich.
Can you give me some statistics about your server?
How many users, how many databases, how many tables in each database, what kind of privileges do the users have (all privileges, a few privileges, just read)?
That will help me setup a similar scenario and test from my side too.
Regards,
Darren
Darren
May 1, 2011, 10:27:48 PM5/1/11
to Securich
Hi Paulo,
The reconciliation has been optimized greatly reducing time spent on
10000 tables from 4minutes 3 seconds to 0.08 second after the first
run:
preacher securich > select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
| 10105 |
+----------+
1 row in set (1.41 sec)
preacher securich > call reconciliation('list');
Empty set (1.94 sec)
Query OK, 0 rows affected (1.94 sec)
preacher securich > call reconciliation('list');
Empty set (0.08 sec)
Query OK, 0 rows affected (0.08 sec)
preacher securich > call reconciliation('list');
Empty set (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
preacher securich > grant select on test.* to 'newuser'@'%';
Query OK, 0 rows affected (0.00 sec)
preacher securich > call reconciliation('list');
+--------+---------------+--------------+------------+-----------
+------+
| System | GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE |
TYPE |
+--------+---------------+--------------+------------+-----------
+------+
| MySQL | 'newuser'@'%' | test | NULL | SELECT |
t |
+--------+---------------+--------------+------------+-----------
+------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.07 sec)
preacher securich > call reconciliation('sync');
Query OK, 0 rows affected, 1 warning (0.42 sec)
preacher securich > call reconciliation('list');
Empty set (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
Please retry running both the initial installation and I should think
that this time the reconciliation will be finished in a decent time.
Hopefully with the amount of tables you have in about 30minutes or so.
Please provide feedback as I am curious about how it will perform.
Regards,
Darren
The reconciliation has been optimized greatly reducing time spent on
10000 tables from 4minutes 3 seconds to 0.08 second after the first
run:
preacher securich > select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
| 10105 |
+----------+
1 row in set (1.41 sec)
preacher securich > call reconciliation('list');
Empty set (1.94 sec)
Query OK, 0 rows affected (1.94 sec)
preacher securich > call reconciliation('list');
Empty set (0.08 sec)
Query OK, 0 rows affected (0.08 sec)
preacher securich > call reconciliation('list');
Empty set (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
preacher securich > grant select on test.* to 'newuser'@'%';
Query OK, 0 rows affected (0.00 sec)
preacher securich > call reconciliation('list');
+--------+---------------+--------------+------------+-----------
+------+
| System | GRANTEE | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE |
TYPE |
+--------+---------------+--------------+------------+-----------
+------+
| MySQL | 'newuser'@'%' | test | NULL | SELECT |
t |
+--------+---------------+--------------+------------+-----------
+------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.07 sec)
preacher securich > call reconciliation('sync');
Query OK, 0 rows affected, 1 warning (0.42 sec)
preacher securich > call reconciliation('list');
Empty set (0.09 sec)
Query OK, 0 rows affected (0.09 sec)
Please retry running both the initial installation and I should think
that this time the reconciliation will be finished in a decent time.
Hopefully with the amount of tables you have in about 30minutes or so.
Please provide feedback as I am curious about how it will perform.
Regards,
Darren
Darren
May 1, 2011, 10:29:39 PM5/1/11
to Securich
Sorry I forgot to add the link to the new binaries.
wget http://securemydb.com/securich_install_plus_securich.0.6.2.tar
wget http://securemydb.com/securich_install_plus_securich.0.6.2.tar
goliardico
May 2, 2011, 11:03:08 AM5/2/11
to Securich
Doesn't work, sorry...
There's logs of my last try, I've stopped with CTRL+Z after 152min.
-----
[mysql51@rlldbtest03 securich_install]$ time ./securich_install.sh
[...]
Building Securich database ... done
Loading Securich db with data ... done
Loading securich stored procedures ... done
Computing work to be done ... done
1331429" to server ...
Error occurred - terminating - MySQL to Securich reconciliation failed
[1]+ Stopped ./securich_install.sh
real 152m49.022s
user 0m0.001s
sys 0m0.000s
----
Next I run "call reconciliation('list');":
...
146573 rows in set (20.79 sec)
Query OK, 0 rows affected, 8 warnings (38.11 sec)
Our MySQL has:
select count(distinct db) from mysql.db; -> 245
select count(distinct user) from mysql.user; -> 160
select count(*) from information_schema.tables; -> 15239
select count(*) from information_schema.schema_privileges; -> 5885
select count(*) from information_schema.table_privileges; -> 69790
The biggest db has 6791 tables.
we create all users with ALL PRIVILEGES on their db.
I hope this helps.
Paolo.
On 2 Mag, 04:29, Darren <photomalta....@gmail.com> wrote:
> Sorry I forgot to add the link to the new binaries.
>
> wgethttp://securemydb.com/securich_install_plus_securich.0.6.2.tar
> > > > > > select...
>
> leggi tutto
There's logs of my last try, I've stopped with CTRL+Z after 152min.
-----
[mysql51@rlldbtest03 securich_install]$ time ./securich_install.sh
[...]
Building Securich database ... done
Loading Securich db with data ... done
Loading securich stored procedures ... done
Reconciling privileges between MySQL db and Securich db started
Progress: 2% done in 32 min 4 secCtrl-C -- sending "KILL QUERY
1331429" to server ...
Error occurred - terminating - MySQL to Securich reconciliation failed
[1]+ Stopped ./securich_install.sh
real 152m49.022s
user 0m0.001s
sys 0m0.000s
----
Next I run "call reconciliation('list');":
...
146573 rows in set (20.79 sec)
Query OK, 0 rows affected, 8 warnings (38.11 sec)
Our MySQL has:
select count(distinct db) from mysql.db; -> 245
select count(distinct user) from mysql.user; -> 160
select count(*) from information_schema.tables; -> 15239
select count(*) from information_schema.schema_privileges; -> 5885
select count(*) from information_schema.table_privileges; -> 69790
The biggest db has 6791 tables.
we create all users with ALL PRIVILEGES on their db.
I hope this helps.
Paolo.
On 2 Mag, 04:29, Darren <photomalta....@gmail.com> wrote:
> Sorry I forgot to add the link to the new binaries.
>
>
> leggi tutto
Darren
May 6, 2011, 9:00:56 PM5/6/11
to Securich
Hi Paulo,
Sorry I have been a bit busy lately but I'll be trying to optimize
other procedures called during reverse reconciliation which depending
on the optimization should make the reconciliation faster.
Darren
> ...
>
> read more »
Sorry I have been a bit busy lately but I'll be trying to optimize
other procedures called during reverse reconciliation which depending
on the optimization should make the reconciliation faster.
Darren
>
> read more »
Reply all
Reply to author
Forward
0 new messages