Debian Linux 7.8 Wheezy, 32-bit system as VMWare ESX 3.5 guest, official MariaDB repository

Hi,

Lets start with the slow log since it's already there.
Please try to grep it for 'Time: ', it will give an idea when most records were created.
If there is a significant growth after the upgrade, lets look at the typical queries that were recorded and see what they are.

Hi Elena,

Unfortunately, I had to delete the huge slow error log file, without taking a backup first. My bad, sorry. At this server, I restarted mysqld and it seems to work ok for now.

In another server, the slow log file is small, but it contains huge query times (>2 mins or so) for rather simple operations. I have attached it here for your consideration. As I recall, I did not reload mysqld here (I have to confirm though).

Let me know if you need anything else.

So, there is indeed a very slow DROP DATABASE statement (~2.5 min). Assuming it contained a considerable number of InnoDB tables, there is a known problem with performance of DROP DATABASE, it was recently filed as MDEV-8069, but it's not a fresh regression, it should have worked the same way in MariaDB 5.5.42.

If you are going to downgrade to 5.5.42, could you please run it on both versions (with the same amount of data in the schema) and confirm there is a noticeable difference in performance?

Further, there are a few slow queries from INFORMATION_SCHEMA.
I don't see any difference between 5.5.42 and 5.5.43 on these queries so far, but it might depend on the data and the configuration.
Could you please

• attach or paste your cnf file(s)?
• run the same queries from a client and paste results? The queries are:

  SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;

  SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';

  SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';

  # And also

  SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql');

  Again, when/if you downgrade, it would be very interesting to run these queries on both versions (assuming they have the same data), and check the execution time.

Elena,

The dkan database had 117 tables (a Drupal installation with more modules). The database size was not big (somewhat 6.5-7MB was the dump file). I've never encountered any MariaDB installation that took minutes to drop a database, so there must be something wrong with this version (and that's what we're trying to figure out here). I will attach my.cnf from this server.

I will try to downgrade, but it's not very trivial. I found this link, but it is not very clear how to downgrade, I get apt-get errors.

In any case, I will try to downgrade and re-run these queries with the same dataset against both .42 and .43 versions.

Downgrade:

Assuming you have the following packages:

elenst@debian:~$ dpkg -l | grep -i maria

ii  libmariadbclient18                   5.5.43+maria-1~wheezy         i386         MariaDB database client library

ii  libmysqlclient18                     5.5.43+maria-1~wheezy         i386         Virtual package to satisfy external depends

ii  mariadb-client                       5.5.43+maria-1~wheezy         all          MariaDB database client (metapackage depending on the latest version)

ii  mariadb-client-5.5                   5.5.43+maria-1~wheezy         i386         MariaDB database client binaries

ii  mariadb-client-core-5.5              5.5.43+maria-1~wheezy         i386         MariaDB database core client binaries

ii  mariadb-common                       5.5.43+maria-1~wheezy         all          MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)

ii  mariadb-server                       5.5.43+maria-1~wheezy         all          MariaDB database server (metapackage depending on the latest version)

ii  mariadb-server-5.5                   5.5.43+maria-1~wheezy         i386         MariaDB database server binaries

ii  mariadb-server-core-5.5              5.5.43+maria-1~wheezy         i386         MariaDB database core server

and this line in apt sources:

deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/debian wheezy main

• temporary set the repo to

  deb http://ftp.osuosl.org/pub/mariadb/mariadb-5.5.42/repo/debian wheezy main

  (modify or comment out the old one)

• run apt-get update
• run

  sudo apt-get install libmariadbclient18=5.5.42+maria-1~wheezy libmysqlclient18=5.5.42+maria-1~wheezy mariadb-client=5.5.42+maria-1~wheezy mariadb-client-5.5=5.5.42+maria-1~wheezy mariadb-client-core-5.5=5.5.42+maria-1~wheezy mariadb-common=5.5.42+maria-1~wheezy mariadb-server=5.5.42+maria-1~wheezy mariadb-server-5.5=5.5.42+maria-1~wheezy mariadb-server-core-5.5=5.5.42+maria-1~wheezy

  (the command is excessive, but it should be certainly enough)
  After that, you should get

  $ dpkg -l | grep -i maria

  ii libmariadbclient18 5.5.42+maria-1~wheezy i386 MariaDB database client library

  ii libmysqlclient18 5.5.42+maria-1~wheezy i386 Virtual package to satisfy external depends

  ii mariadb-client 5.5.42+maria-1~wheezy all MariaDB database client (metapackage depending on the latest version)

  ii mariadb-client-5.5 5.5.42+maria-1~wheezy i386 MariaDB database client binaries

  ii mariadb-client-core-5.5 5.5.42+maria-1~wheezy i386 MariaDB database core client binaries

  ii mariadb-common 5.5.42+maria-1~wheezy all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)

  ii mariadb-server 5.5.42+maria-1~wheezy all MariaDB database server (metapackage depending on the latest version)

  ii mariadb-server-5.5 5.5.42+maria-1~wheezy i386 MariaDB database server binaries

  ii mariadb-server-core-5.5 5.5.42+maria-1~wheezy i386 MariaDB database core server files

Checked on Wheezy 32 bit.
You can use your usual repo instead of osuosl, just check that 5.5.42 actually exists there in the given locaition.

After code review changes between 5.5.42 and 5.5.43, I could not find anything clear that would have caused drop database to be slower. Do we have slow query log from 5.5.42 when doing the same operations ?

After some time now, I have yet to encounter the same behaviour as initially reported in this issue. So, I assume it was something of a very rare condition or something else that I cannot reproduce at this point. I haven't tried to downgrade to 5.5.42, because all the servers seem to work fine now.

Feel free to close this issue as invalid. Sorry for the noise.

Closing for now as "Cannot reproduce".
Please comment to re-open the issue if you encounter the problem again.