Recap on the problem:<\/p>\n
I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I\/O at that time, which then means other queries can have their chance to execute.<\/p>\n
I present two approaches:<\/p>\n
We use a simple, single-table scan. No aggregates (which complicate the solution considerably).<\/p>\n
\nSELECT\r\n rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days\r\nFROM\r\n sakila.rental\r\n;<\/pre>\n<\/blockquote>\nThe naive solution<\/h4>\n
We need to know every 1,000<\/strong> rows. So we need to count the rows. We do that by using a counter, as follows:<\/p>\n
\nSELECT\r\n rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days,\r\n @row_counter := @row_counter + 1\r\nFROM\r\n sakila.rental,\r\n (SELECT @row_counter := 0) sel_row_counter\r\n;<\/pre>\n<\/blockquote>\nA thing that bothers me, is that I wasn’t asking for an additional column. I would like the result set to remain as it were; same result structure. We also want to sleep for 1<\/strong> second for each 1,000<\/strong> rows. So we merge the two together along with one of the existing columns, like this:<\/p>\n
\nSELECT\r\n rental_id +\r\n IF(\r\n (@row_counter := @row_counter + 1) % 1000 = 0,\r\n SLEEP(1), 0\r\n ) AS rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days\r\nFROM\r\n sakila.rental,\r\n (SELECT @row_counter := 0) sel_row_counter\r\n;<\/pre>\n<\/blockquote>\nTo remain faithful to my slides<\/a>, I rewrite as follows, and this is the naive solution<\/em>:<\/p>\n
\nSELECT\r\n rental_id +\r\n CASE\r\n WHEN (@row_counter := @row_counter + 1) % 1000 = 0<\/strong> THEN SLEEP(1)<\/strong>\r\n ELSE 0<\/strong>\r\n END AS rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days\r\nFROM\r\n sakila.rental,\r\n (SELECT @row_counter := 0) sel_row_counter\r\n;<\/pre>\n<\/blockquote>\nThe WHEN<\/strong> clause always returns 0<\/strong>, so it does not affect the value of rental_id<\/strong>.<\/p>\n
The factor approach<\/h4>\n
In the factor approach we wish to keep record of query execution, every 1,000<\/strong> rows. I introduce a nested WHEN<\/strong> statement which updates time records. I rely on SYSDATE()<\/strong> to return the true time, and on NOW()<\/strong> to return query execution start time.<\/p>\n
\nSELECT\r\n rental_id +\r\n CASE\r\n WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL\r\n WHEN @row_counter % 1000 = 0<\/strong> THEN\r\n CASE\r\n WHEN (@time_now := SYSDATE()<\/strong>) IS NULL THEN NULL\r\n WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now)<\/strong>)) IS NULL THEN NULL\r\n WHEN SLEEP(@time_diff)<\/strong> IS NULL THEN NULL\r\n WHEN (@chunk_start_time := SYSDATE()<\/strong>) IS NULL THEN NULL\r\n ELSE 0\r\n END\r\n ELSE 0\r\n END AS rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days\r\nFROM\r\n sakila.rental,\r\n (SELECT @row_counter := 0) sel_row_counter,\r\n (SELECT @chunk_start_time := NOW()) sel_chunk_start_time\r\n;<\/pre>\n<\/blockquote>\nProof<\/h4>\n
How can we prove that the queries do indeed work?<\/p>\n
We can see if the total runtime sums up to the number of sleep calls, in seconds; but how do we know that sleeps do occur at the correct times?<\/p>\n
A solution I offer is to use a stored routines which logs to a MyISAM table (a non transactional table) the exact time (using SYSDATE()<\/strong>) and value per row. The following constructs are introduced:<\/p>\n
\nCREATE TABLE<\/strong> test.proof(\r\n id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\r\n dt DATETIME NOT NULL,\r\n msg VARCHAR(255)\r\n) ENGINE=MyISAM<\/strong>;\r\n\r\nDELIMITER $$\r\nCREATE FUNCTION<\/strong> test.prove_it(message VARCHAR(255)) RETURNS TINYINT\r\nDETERMINISTIC\r\nMODIFIES SQL DATA\r\nBEGIN\r\n INSERT INTO test.proof (dt, msg) VALUES (SYSDATE(), message); RETURN 0;<\/strong>\r\nEND $$\r\nDELIMITER ;<\/pre>\n<\/blockquote>\nThe prove_it()<\/strong> function records the immediate time and a message into the MyISAM table, which immediately accepts the write, being non-transactional. It returns with 0<\/strong>, so we will now embed it within the query. Of course, the function itself incurs some overhead, but it will nevertheless convince you that SLEEP()<\/strong>s do occur at the right time!<\/p>\n
\nSELECT\r\n rental_id +\r\n CASE\r\n WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL\r\n WHEN @row_counter % 1000 = 0 THEN\r\n CASE\r\n WHEN (@time_now := SYSDATE()) IS NULL THEN NULL\r\n WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now))) IS NULL THEN NULL\r\n WHEN SLEEP(@time_diff) + test.prove_it(CONCAT('will sleep for ', @time_diff, ' seconds'))<\/strong> IS NULL THEN NULL\r\n WHEN (@chunk_start_time := SYSDATE()) IS NULL THEN NULL\r\n ELSE 0\r\n END\r\n ELSE 0\r\n END AS rental_id,\r\n TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days\r\nFROM\r\n sakila.rental,\r\n (SELECT @row_counter := 0) sel_row_counter,\r\n (SELECT @chunk_start_time := NOW()) sel_chunk_start_time\r\n;\r\n\r\nmysql> SELECT * FROM test.proof;\r\n+----+---------------------+--------------------------+\r\n| id | dt | msg |\r\n+----+---------------------+--------------------------+\r\n| 1 | 2011-11-01 09:22:36 | will sleep for 1 seconds |\r\n| 2 | 2011-11-01 09:22:36 | will sleep for 0 seconds |\r\n| 3 | 2011-11-01 09:22:36 | will sleep for 0 seconds |\r\n| 4 | 2011-11-01 09:22:36 | will sleep for 0 seconds |\r\n| 5 | 2011-11-01 09:22:36 | will sleep for 0 seconds |\r\n| 6 | 2011-11-01 09:22:36 | will sleep for 0 seconds |\r\n| 7 | 2011-11-01 09:22:38 | will sleep for 1 seconds |\r\n| 8 | 2011-11-01 09:22:38 | will sleep for 0 seconds |\r\n| 9 | 2011-11-01 09:22:38 | will sleep for 0 seconds |\r\n| 10 | 2011-11-01 09:22:38 | will sleep for 0 seconds |\r\n| 11 | 2011-11-01 09:22:38 | will sleep for 0 seconds |\r\n| 12 | 2011-11-01 09:22:40 | will sleep for 1 seconds |\r\n| 13 | 2011-11-01 09:22:40 | will sleep for 0 seconds |\r\n| 14 | 2011-11-01 09:22:40 | will sleep for 0 seconds |\r\n| 15 | 2011-11-01 09:22:40 | will sleep for 0 seconds |\r\n+----+---------------------+--------------------------+<\/pre>\n<\/blockquote>\nThe above query is actually very fast. Try adding BENCHMARK(1000,ENCODE(‘hello’,’goodbye’))<\/strong> to rental_id so as to make it slower, or just use it on a really large table, see what happens (this is what I actually used to make the query run for several seconds in the example above).<\/p>\n
Observant reads will note that the “will sleep…”<\/strong> message actually gets written after<\/em> the SLEEP()<\/strong> call. I leave this as it is.<\/p>\n
Another very nice treat of the code is that you don’t need sub-second resolution for it to work. If you look at the above, we don’t actually go to sleep every 1,000<\/strong> rows (1,000<\/strong> is just too quick in the query — perhaps I should have used 10,000<\/strong> seconds). But we do<\/em> make it once a second has elapsed<\/em>. Which means it works correctly on average<\/em>. Of course, the entire discussion is only of interest when a query executes for a substantial<\/em> number of seconds, so this is just an anecdote.<\/p>\n
And the winner is…<\/h4>\n
Wow, this contest<\/a> was anything but popular. Marc Alff<\/a><\/strong> is the obvious winner: he is the only<\/em> one to suggest a solution \ud83d\ude42<\/p>\n
But Marc uses a very nice trick: he reads the PERFORMANCE_SCHEMA<\/strong>. Now, I’m not sure how the\u00a0PERFORMANCE_SCHEMA<\/strong> gets updated. I know that the INFORMATION_SCHEMA.GLOBAL_STATUS<\/strong> table does not get updated by a query until the query completes (so you cannot expect a change in innodb_rows_read<\/strong> throughout the execution of the query). I just didn’t test it (homework, anyone?). If it does get updated, then we can throttle the query based on InnoDB page reads using a simple query. Otherwise, an access to \/proc\/diskstats<\/strong> is possible, assuming no apparmor<\/em> or SELinux<\/em> are blocking us.<\/p>\n
Marc also uses a stored function, which is the clean<\/em> way of doing it; however I distrust the overhead incurred by s stored routine and prefer my solution (which is, admittedly, not a pretty SQL sight!).<\/p>\n
Happy throttling!<\/p>\n","protected":false},"excerpt":{"rendered":"
Recap on the problem: A query takes a long time to complete. During this time it makes for a lot of I\/O. Query’s I\/O overloads the db, making for other queries run slow. I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the […]<\/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,13,52,21,59],"class_list":["post-4294","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-hack","tag-myisam","tag-performance","tag-sql","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-17g","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4294","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=4294"}],"version-history":[{"count":32,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4294\/revisions"}],"predecessor-version":[{"id":4325,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4294\/revisions\/4325"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}