{"id":4561,"date":"2011-12-12T09:35:19","date_gmt":"2011-12-12T07:35:19","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4561"},"modified":"2011-12-12T15:06:09","modified_gmt":"2011-12-12T13:06:09","slug":"information_schema-optimizations-still-crashing-my-servers","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/information_schema-optimizations-still-crashing-my-servers","title":{"rendered":"INFORMATION_SCHEMA Optimizations: still crashing my servers"},"content":{"rendered":"

[Update<\/strong>: need to take more breaks: now NOT<\/strong> crashing my servers! See clarifications below]<\/strong><\/p>\n

INFORMATION_SCHEMA Optimizations<\/a> are meant to make your INFORMATION_SCHEMA<\/strong> queries lighter and safer.<\/p>\n

For example, if you’re going to query the COLUMNS<\/strong> table for just the columns of a single table, then the following:<\/p>\n

\n
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='rental'<\/pre>\n<\/blockquote>\n
makes for an optimization: specifying a literal on TABLE_SCHEMA<\/strong> avoid scanning the directories of other schemata. Specifying a literal on TABLE_NAME<\/strong> avoids checking up on other tables. So it’s a one-schema-one-table read operation, as opposed to “first read every single column from all and any single schema and table, then return only those I’m interested in”<\/em>.<\/p>\n
Here’s the execution plan for the above query:<\/p>\n
\n
*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1\r\n\u00a0 select_type: SIMPLE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: COLUMNS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ALL\r\npossible_keys: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: TABLE_SCHEMA,TABLE_NAME\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 key_len: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Open_frm_only; Scanned 0 databases<\/pre>\n<\/blockquote>\n
What I tried to do is to read the entire COLUMNS<\/strong> table, one schema at a time, one table at a time. I’m good with this taking longer time.<\/p>\n
I have a production system on which reads from COLUMNS<\/strong> consistently crash the servers<\/em>. Well, one read at a time can’t do harm, right?<\/p>\n
Unfortunately, as the title of this posts reveals, even sequential read of COLUMNS<\/strong> using INFORMATION_SCHEMA<\/strong> optimization does not help: a minute into the process and the client lost connection. The server crashed.<\/del><\/p>\n
I was expecting that table locks would be released, buffers released etc. One at a time, there wouldn’t be a congestion of locks, reads, table cache suffocation etc.<\/del><\/p>\n
Was actually having high hopes for this to succeed. I have to find a way in which INFORMATION_SCHEMA<\/strong> tables are not dangerous.<\/del><\/p>\n
A few hours later, and I have both conclusions and achievements.<\/p>\n
There are indeed memory issues with querying from INFORMATION_SCHEMA<\/strong> tables. I’ve found that VARCHAR(64)<\/strong> columns can consume 64K<\/strong> each: I’m reading from large tables of more than 1,000<\/strong> columns each, while monitoring MySQL’s memory consumption. By dividing the increase in memory by the number of rows resulting from a query I sent, and which was for one single columns, I got an almost exact 64K<\/strong> value per row.<\/p>\n
So a query on INFORMATION_SCHEMA<\/strong> consumes much more memory than it should. The good news is that this memory is released once the query terminates. So there is no leak into the session memory.<\/p>\n
This is combined with a mistake of mine<\/em> in the way I iterated the tables, such that the problem was amplified: I happened to query much more than I needed, and so got my query’s memory bloated. That is to say, I used the INFORMATION_SCHEMA<\/strong> optimizations only partly right, and so got only part of the savings it could offer me.<\/p>\n
With better pinpointing I’m now actually able to read from COLUMNS,<\/strong> without crashing my servers, consistently<\/em>.<\/p>\n
I will further look into the 64K<\/strong> issue. That in itself still drains a lot of memory: on my mycheckpoint<\/a> schema tables a singe table read means > 64MB<\/strong> of query memory down the drain.<\/p>\n","protected":false},"excerpt":{"rendered":"
[Update: need to take more breaks: now NOT crashing my servers! See clarifications below] INFORMATION_SCHEMA Optimizations are meant to make your INFORMATION_SCHEMA queries lighter and safer. For example, if you’re going to query the COLUMNS table for just the columns of a single table, then the following: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=’sakila’ AND TABLE_NAME=’rental’ […]<\/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":[24],"class_list":["post-4561","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-information_schema"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1bz","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4561","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=4561"}],"version-history":[{"count":5,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4561\/revisions"}],"predecessor-version":[{"id":4565,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4561\/revisions\/4565"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}