Comments on: Auto caching INFORMATION_SCHEMA tables: seeking input

By: shlomi

shlomi — Sun, 18 Mar 2012 15:14:05 +0000

@Sheeri,
With MyISAM tables, there’s the UPDATE_TIME column in TABLES which notifies when .frm was last modified. But this does not apply to InnoDB.

Since I wish to cache any data within the TABLES, COLUMNS, etc. tables, I can’t specifically rely on this and that columns to change or not to change (very bad phrasing, but I hope you get my meaning).

By: Sheeri Cabral

Sheeri Cabral — Mon, 12 Mar 2012 15:51:06 +0000

Could you make caching longer? I know it’s hard to see outside of the database, but the .frm file changes when the table structure changes. If the .frm file doesn’t change, does any of the metadata you’re referring to change? (that’s a serious question, because the answer might be “yes” if you’re looking at data/index size or the TABLE_ROWS field of the TABLES table, for example)

If you only are referring to the structure of the table, and not the nature of the data in it, then I bet you could come up with some kind of hack that writes to a common file when a .frm file has changed, and only update the cache for the tables/indexes/columns that have changed when they change, instead of re-reading all the definitions.

By: shlomi

shlomi — Sat, 10 Mar 2012 05:49:44 +0000

Peter, with regard to binlog filters: indeed. I’m wondering how popular this is. In my years as consultant I’ve seen little to no use of this. I believe the vast majority of replication users do not do replication filtering.

By: shlomi

shlomi — Sat, 10 Mar 2012 05:34:00 +0000

Peter,
With regards to the view – they are working just great, no trouble given. It’s just the information_schema querying on views. I don’t know that should mean I was looking for trouble.

By: Peter Laursen

Peter Laursen — Fri, 09 Mar 2012 19:21:35 +0000

1)
Q: “Does it make sense to query for the cached table on slave, to have it answer for master’s data?.. Or is it fair enough, since we would usually expect master/slaves to reflect same schema structure?” A: I don’t think so if some binlog|replicate_do|ignore_db setting is used on master or slave.

2)
Jannes was faster than me here, but building a system that depends on VIEWs defined on VIEWS (defined on .. (.. on ..)) 🙂 with MySQL is a synonym for asking for problems. The limitations with VIEWS in MySQL are well-known and anyone who has read (any edition of) ‘High Performance MySQL’ would foresee the problems arise.

By: shlomi

shlomi — Fri, 09 Mar 2012 18:18:13 +0000

@Jannes,
Yes, it’s just the views.
The deeper the nesting of views, the more memory is consumed, and the more danger for crash.
Just before the crash, I see about 1GB of memory getting drained. I can guess that the recursive read from I_S is the root of the problem.

By: Jannes

Jannes — Fri, 09 Mar 2012 17:56:36 +0000

Do just getting the VIEWs from the INFORMATION_SCHEMA make your server crash or only in combination with the caching? In other words, are your VIEWs already dangerous for anyone to use (risking runnning an INFORMATION_SCHEMA query on the one day)?

Maybe it would be worth it to work out what exactly in the VIEWs is triggering the crash?

By: Jonathan Levin

Jonathan Levin — Fri, 09 Mar 2012 07:03:40 +0000

Hi Shlomi,

As I understand it, Oracle/MySQL query the information_schema quite a bit with their Query Analyzer product.

I hope I am directing you to the right person, but maybe http://www.markleith.co.uk can help with that.

By: Log Buffer #262, A Carnival of the Vanities for DBAs | The Pythian Blog

Log Buffer #262, A Carnival of the Vanities for DBAs | The Pythian Blog — Fri, 09 Mar 2012 06:02:05 +0000

[…] Shlomi Noach is seeking input regarding Auto caching INFORMATION_SCHEMA tables. […]

By: shlomi

shlomi — Thu, 08 Mar 2012 20:56:13 +0000

@Rob,
Thanks. Not on VIEWs, unfortunately.