The first I thought about was what max_allowed_packet actually controls. Here is what I found:
According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory---a cap
on the size of the packet, which this
option accomplishes.
The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().
This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.
Given that definition of max_allowed_packet, I then discovered something else from ServerFault: innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones
Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start
This will accommodate any big blobs you may have in your data.
PROLOGUE
As I mentioned earlier in Adding new tables -- memory usage increases, adding new tables increases memory usage. That being the case, tables that are not in active use would still consume memory in the INFORMATION_SCHEMA.
Determining what is active is an arbitrary process. If you need to see when was the last time a table had written changes, you have to find out based on the Storage Engine of each table.
MyISAM
Learning the last time a MyISAM table had written changes is straightforward.
Suppose you need to see all MyISAM tables that have not been written to in over a month. Simply run this query again INFORMATION_SCHEMA.tables:
SELECT table_schema,table_name,update_time FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND engine='MyISAM' and update_time > (NOW() - INTERVAL 1 MONTH);
Note: update_time for a MyISAM table is the timestamp of the .MYD file. If you run ANALYZE TABLE mydb.mytable;, the index statistics are written to the .MYI file. The .MYI's file timestamp is not reflected in the INFORMATION_SCHEMA.
InnoDB
Getting InnoDB timestamps is quite a challenge because InnoDB does not store update_time in the INFORMATION_SCHEMA. If innodb_file_per_table is disabled, everything InnoDB and its grandmother is encased in the system tablespace file ibdata1. Gettting the timestamp of ibdata1 gives you the last time any write was received. That could be anything
- INSERT, UPDATE, DELETE (altering data and index pages)
- ANALYZE TABLE (altering index pages)
- SELECT (data snapshots via MVCC)
Therefore, with innodb_file_per_table disabled, there is not way to know.
On the other hand, if innodb_file_per_table is enabled, the question remains: HOW DO YOU FIND OUT THE LAST TIME AN INNODB TABLE WAS WRITTEN ?
You must check the file timestamp of the .ibd.
Rather than reinvent the wheel, please read my posts on how to get that info
EPILOGUE
The main point to keep in mind is to find out timestamp of the table and subtract it from NOW() (or UNIX_TIMESTAMP(NOW()) from the OS point-of-view). You have to arbitrarily determine how old is too old for an inactive table.
Best Answer
Create a text file with all the view definitions:
You edit AllMyViews.sql from there. Then, Drop the Views
After editing AllMyViews.sql reload them
Give it a Try !!!