InnoDB and optimize table

Hello.

I use Piwik 1.8.2 and a separate database server (Percona Server 5.5, 6Gb Ram, DB - 12Gb). Every night after cleaning old data maintenance script runs command “optimize table”. I use InnoDB and InnoDB does not support optimize table. This command is executed for a long time and the server hangs.
How can I disable optimize table?

I guess we should not run the OPTIMIZE when we detect that you are running Innodb tables?

I created a ticket: Do not run OPTIMIZE queries on innodb tables · Issue #3289 · matomo-org/matomo · GitHub

if you can submit a patch it would be great :slight_smile:

No!

OPTIMIZE TABLE works for MyISAM, InnoDB, and ARCHIVE tables.

MySQL 5.5 Manual

Same with Percona Server, optimize is supported!

Ok
But I can’t wait 6 hour while executing optimize table.
piwik hangs in this time.

Matt, I just wont to disable optimize table.

Perhaps i can help you:
What is your max available RAM Size of your MySQL Server?
send me your mysql setting for innodb_buffer_pool_size (should be 80% of available RAM if it is a mysql dedicated VM/Host)

VM. 2 CPU(XEON X5650), 6Gb RAM
config http://www.text-upload.com/read,3939887075254

Looks quite good.

But your innodb_log_file_size is way to small. (i would say increase it to 128M (16777216 bytes))

Thanks. I’ll try

I saw reports on the internet that OPTIMIZE is slow for Innodb tables. That’s why I suggested disabling it, but i’m not sure yet what is the best course of action?

It helped. Now, downtime was reduced to 30 min. But it will still not good. Disk IO is very high at the moment of optimization.
I disable “delete old visitor logs and report”, because this is not normal

You will use disk where memory is not greater than disk. For large table of course.
At my work (server administration), to prevent this problem I use:

  • Modern kernel (3.2+)
  • Ext4 + barrier=0 into options of /etc/fstab
  • ssd in some case can be very usefull
  • by top command you can check is swap is in activity (change of value)
  • munin can help
    To not perturbe the trafic of the reste of the server, I put tmpfs as ramdisk on frequently written folder (session & tmp)
    Else professional diagnostic is needed.

Thanks for advice.
But I just want to disable optimize table. I don’t need this db load every night.

P.S. I didn’t want to buy ssd or give more memory for piwik. My websites can handle over 200 000 visitor per day with smaller db server. In this case simple to don’t use piwik at all

To disable the OPTIMIZE statements please apply this patch:


Index: core/PluginsFunctions/Sql.php
===================================================================
--- core/PluginsFunctions/Sql.php	(revision 6642)
+++ core/PluginsFunctions/Sql.php	(working copy)
@@ -150,6 +150,7 @@
 	 */
 	static public function optimizeTables( $tables )
 	{
+		return;
 		if (!is_array($tables))
 		{
 			$tables = array($tables);

but you’ll have to apply it after every update until we fix the ticket Do not run OPTIMIZE queries on innodb tables · Issue #3289 · matomo-org/matomo · GitHub

Thanks Matt

that problem must be somewhere else
i have a database which tracker tables size is about 6GB and report tables size is about 500MB
i tried to debug this problem so i made dump of archive tables, imported it into my pc and ran optimize and it was done under 3 minutes.
when i ran the same command on my website with about 1000 active users (at the same time) it freezes whole database and i must kill mysql

because innodb optimize behaves very differently from myisam
it creates tmp table and inserts optimized data into it and then drops old table
so i think this can be problem on live websites which tries to insert and select from table which is dumped into tmp table at the same time

based on this i think solution can be setting piwik into maintence mode at the start of archive script (or only in optimize part) and turning it off at the end of this script

here’s that optimize command


OPTIMIZE TABLE archive_numeric_2011_01,archive_numeric_2011_05,archive_numeric_2011_06,archive_numeric_2011_07,archive_numeric_2011_08,archive_numeric_2011_09,archive_numeric_2011_10,archive_numeric_2011_11,archive_numeric_2011_12,archive_numeric_2012_01,archive_numeric_2012_02,archive_numeric_2012_03,archive_numeric_2012_04,archive_numeric_2012_05,archive_numeric_2012_06,archive_numeric_2012_07,archive_numeric_2012_08,archive_blob_2011_01,archive_blob_2011_05,archive_blob_2011_06,archive_blob_2011_07,archive_blob_2011_08,archive_blob_2011_09,archive_blob_2011_10,archive_blob_2011_11,archive_blob_2011_12,archive_blob_2012_01,archive_blob_2012_02,archive_blob_2012_03,archive_blob_2012_04,archive_blob_2012_05,archive_blob_2012_06,archive_blob_2012_07,archive_blob_2012_08;

I would appreciate a config setting which disabled optimize too.
I’m using a patch similar to the one from matt for a year now for every release to just skip optimize table calls for my innodb piwik install.