[Maria-developers] INFORMATION_SCHEMA.SCHEMATA and INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
Hi, I'm newbie, I have problem with INFORMATION_SCHEMA. There is mariadb-10.0 with around 100000 databases. Disks are slow, but most users have one database, a few have more than one, but it is not a big number. Queries like SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA bring to knees whole server. The idea to resolve it is: in find_files(...) instead of the for loop execute query: SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT'; There are databases with names with _ in them and \_ in GRANTS. This is theory, but I don't know how to do it. That's mean how to freeze the current query, how to run new query, read results, and fill files in the find_files function and resume the current query. Could you help? Or do you have ideas how to solve it? -- Witold Filipczyk
Hi, Witold! On Feb 21, Witold Filipczyk wrote:
Hi, I'm newbie, I have problem with INFORMATION_SCHEMA. There is mariadb-10.0 with around 100000 databases. Disks are slow, but most users have one database, a few have more than one, but it is not a big number. Queries like SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA bring to knees whole server. The idea to resolve it is: in find_files(...) instead of the for loop execute query: SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT'; There are databases with names with _ in them and \_ in GRANTS.
That's a very interesting idea. Basically, you're saying that if a user has no probal privileges and database privileges on only one database and no wildcards in the database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly doable. It's even not very difficult to do, I'd think. (*) this can be extended to privileges on few (more than one) databases and on wildcards in the database names, but let's start from something simple.
This is theory, but I don't know how to do it. That's mean how to freeze the current query, how to run new query, read results, and fill files in the find_files function and resume the current query.
Kind of. Privileges are stored in memory in lists and hashes. You'd need to traverse them to see what databases a user has grants for. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
On Tue, Feb 21, 2017 at 09:41:07PM +0100, Sergei Golubchik wrote:
Hi, Witold!
On Feb 21, Witold Filipczyk wrote:
Hi, I'm newbie, I have problem with INFORMATION_SCHEMA. There is mariadb-10.0 with around 100000 databases. Disks are slow, but most users have one database, a few have more than one, but it is not a big number. Queries like SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA bring to knees whole server. The idea to resolve it is: in find_files(...) instead of the for loop execute query: SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT'; There are databases with names with _ in them and \_ in GRANTS.
That's a very interesting idea.
Basically, you're saying that if a user has no probal privileges and database privileges on only one database and no wildcards in the database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly doable. It's even not very difficult to do, I'd think.
(*) this can be extended to privileges on few (more than one) databases and on wildcards in the database names, but let's start from something simple.
This is theory, but I don't know how to do it. That's mean how to freeze the current query, how to run new query, read results, and fill files in the find_files function and resume the current query.
Kind of. Privileges are stored in memory in lists and hashes. You'd need to traverse them to see what databases a user has grants for.
I guess, I did not understand. I did something like this: /* sql_show.cc */ static find_files_result find_files(THD *thd, Dynamic_array<LEX_STRING*> *files, LEX_STRING *db, const char *path, const LEX_STRING *wild) { MY_DIR *dirp; Discovered_table_list tl(thd, files, wild); DBUG_ENTER("find_files"); if (!(dirp = my_dir(path, MY_THREAD_SPECIFIC | (db ? 0 : MY_WANT_STAT)))) { if (my_errno == ENOENT) my_error(ER_BAD_DB_ERROR, MYF(ME_BELL | ME_WAITTANG), db->str); else my_error(ER_CANT_READ_DIR, MYF(ME_BELL | ME_WAITTANG), path, my_errno); DBUG_RETURN(FIND_FILES_DIR); } if (!db) /* Return databases */ { #ifndef NO_EMBEDDED_ACCESS_CHECKS char bufor[201]; char *current; uint counter, i, n; ACL_DB *acl_db; char *curr_host= thd->security_ctx->priv_host_name(); mysql_mutex_lock(&acl_cache->lock); for (counter=0 ; counter < acl_dbs.elements ; counter++) { const char *user, *host; acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*); user= safe_str(acl_db->user); host= safe_str(acl_db->host.hostname); if ((strcmp(thd->security_ctx->priv_user, user) || my_strcasecmp(system_charset_info, curr_host, host))) continue; if (is_in_ignore_db_dirs_list(acl_db->db)) continue; for (i = 0, current = bufor; i < 200; ++i) { if (acl_db->db[i] == '\\') continue; if (acl_db->db[i] == '\0') break; *current++ = acl_db->db[i]; } *current = '\0'; if (tl.add_file(bufor)) goto err; } mysql_mutex_unlock(&acl_cache->lock); tl.sort(); #endif } else { if (ha_discover_table_names(thd, db, dirp, &tl, false)) goto err2; } DBUG_PRINT("info",("found: %zu files", files->elements())); my_dirend(dirp); DBUG_RETURN(FIND_FILES_OK); err: mysql_mutex_unlock(&acl_cache->lock); err2: my_dirend(dirp); DBUG_RETURN(FIND_FILES_OOM); } And turned on INFORMATION_SCHEMA in phpmyadmin and it is slow, very slow. What I'm doing wrong here? How to speed up?
Hi, Witold! On Mar 05, Witold Filipczyk wrote:
The idea to resolve it is: in find_files(...) instead of the for loop execute query: SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT'; There are databases with names with _ in them and \_ in GRANTS.
That's a very interesting idea.
Basically, you're saying that if a user has no probal privileges and database privileges on only one database and no wildcards in the database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly doable. It's even not very difficult to do, I'd think.
I guess, I did not understand. I did something like this: ... And turned on INFORMATION_SCHEMA in phpmyadmin and it is slow, very slow. What I'm doing wrong here? How to speed up?
I cannot answer it like that, sorry. The idea looks good and promising. Your implementation looks fine too, I didn't spot anything obviously wrong. Try to profile your code, see where it spends the time. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (2)
-
Sergei Golubchik -
Witold Filipczyk