{"id":4761,"date":"2012-03-08T20:31:56","date_gmt":"2012-03-08T18:31:56","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4761"},"modified":"2012-03-08T20:32:54","modified_gmt":"2012-03-08T18:32:54","slug":"auto-caching-information_schema-tables-seeking-input","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/auto-caching-information_schema-tables-seeking-input","title":{"rendered":"Auto caching INFORMATION_SCHEMA tables: seeking input"},"content":{"rendered":"

The short version<\/h4>\n

I have it all working. It’s kind of magic. But there are issues, and I’m not sure it should even exist, and am looking for input.<\/p>\n

The long version<\/h4>\n

In Auto caching tables<\/a> I presented with a hack which allows getting cached or fresh results via a simple SELECT queries.<\/p>\n

The drive for the above hack was INFORMATION_SCHEMA<\/strong> tables. There are two major problems with INFORMATION_SCHEMA<\/strong>:<\/p>\n

    \n
  1. Queries on schema-oriented tables such as TABLES<\/strong>, COLUMNS<\/strong>, STATISTICS<\/strong>, etc. are heavyweight. How heavyweight? Enough to make a lockdown of your database. Enough to crash down your database in some cases.<\/li>\n
  2. The data is always generated on-the-fly, as you request it. Query the COLUMNS<\/strong> table twice, and risk two lockdowns of your database.<\/li>\n<\/ol>\n

    The auto-cache mechanism solves issue #2<\/strong>. I have it working, time based. I have an auto-cache table for each of the INFORMATION_SCHEMA<\/strong> heavyweight tables. Say, every 30<\/strong> minutes the cache is invalidated. Throughout those 30<\/strong> minutes, you get a free pass!<\/p>\n

    The auto-cache mechanism also paves the road to solving issue #1<\/strong>: since it works by invoking a stored routine, I have better control of the way I read INFORMATION_SCHEMA<\/strong>. This, I can take advantage of INFORMATION_SCHEMA optimization<\/a>. It’s tedious, but not complicated.<\/p>\n

    For example, if I wanted to cache the TABLES<\/strong> table, I don’t necessarily read the entire TABLES<\/strong> data in one read. Instead, I can iterate the schemata, get a list of table names per schema, then read full row data for these, table by table. The result? Many many more SELECT<\/strong>s, but more optimized, and no one-big-lock-it-all query.<\/p>\n

    And the problem is…<\/h4>\n

    I have two burning problems.<\/p>\n

      \n
    1. INFORMATION_SCHEMA<\/strong> optimization only works that much<\/em>. It sometimes does not work. In particular, I’ve noticed that if you have a view which relies on another view (possibly relying on yet another view), things get out of hand. I author a monitoring tool for MySQL called mycheckpoint<\/a>. It uses some fancy techniques for generating aggregated data, HTML and charts, by means of nested views. There are a few views there I can never query for in COLUMNS<\/strong>. It just crashes down my server. Repeatedly. And it’s a good machine with good configuration. Make that 5<\/strong> machines. They all crash, repeatedly. I just can’t trust INFORMATION_SCHEMA<\/strong>!<\/li>\n
    2. Replication: any caching table is bound to replicate. Does it make any sense to replicate cache for internal metadata? Does it make sense to query for the cached table on slave, to have it answer for master’<\/em>s data? With plain old INFORMATION_SCHEMA<\/strong>, every server is on its own. Caching kinda works against this. Or is it fair enough, since we would usually expect master\/slaves to reflect same schema structure?<\/li>\n<\/ol>\n

      I would feel much better if I could read SHOW<\/strong> statements with a SELECT<\/strong> query. Though I’ve found this nice hack<\/a>, it can’t work from a stored function, only via stored procedure. So it can’t be used from within a SELECT<\/strong> query. I’ve been banging my head for months now, I think I gave up on this one.<\/p>\n

      Any insights are welcome!<\/p>\n","protected":false},"excerpt":{"rendered":"

      The short version I have it all working. It’s kind of magic. But there are issues, and I’m not sure it should even exist, and am looking for input. The long version In Auto caching tables I presented with a hack which allows getting cached or fresh results via a simple SELECT queries. The drive […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[74,24,8],"class_list":["post-4761","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-hack","tag-information_schema","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1eN","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4761"}],"version-history":[{"count":7,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4761\/revisions"}],"predecessor-version":[{"id":4768,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4761\/revisions\/4768"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}