Skip to content

Instantly share code, notes, and snippets.

@castor4bit

castor4bit/generate_dummydata.rb

Created March 20, 2013 20:18
Show Gist options
  • Save castor4bit/5208065 to your computer and use it in GitHub Desktop.
Save castor4bit/5208065 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL5.6.10でのSTART TRANSACTION READ ONLY効果検証
Raw
generate_dummydata.rb
require 'faker'
require 'date'
now = Time.now
100000.times do |i|
line = [
i+1,
Faker::Name.name,
Faker::Internet.email,
Faker::PhoneNumber.phone_number,
rand(2),
(now - rand(86400*1000)).strftime("%Y-%m-%d %H:%M:%S")
]
puts line.join("\t")
end
Raw
readonly_test.sql
CREATE TABLE `rotest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`email` varchar(256) NOT NULL,
`phone_number` varchar(32) NOT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Raw
reslut2.txt
+----------+------------+---------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------+
| 1 | 0.14251975 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 2 | 0.19904075 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 3 | 0.18981750 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 4 | 0.13855350 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 5 | 0.18145475 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 6 | 0.22038575 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 7 | 0.18477925 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 8 | 0.12132375 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 9 | 0.18848100 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 10 | 0.18735250 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
+----------+------------+---------------------------------------------------------------------------------------------------------+
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Status | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| starting | 0.000109 | 0.000094 | 0.000117 | 0.000097 | 0.000100 | 0.000203 | 0.000122 | 0.000098 | 0.000115 | 0.000111 |
| checking permissions | 0.000017 | 0.000012 | 0.000013 | 0.000010 | 0.000011 | 0.000017 | 0.000012 | 0.000011 | 0.000013 | 0.000014 |
| Opening tables | 0.000049 | 0.000032 | 0.000035 | 0.000030 | 0.000033 | 0.000040 | 0.000034 | 0.000030 | 0.000034 | 0.000038 |
| init | 0.000046 | 0.000047 | 0.000048 | 0.000043 | 0.000046 | 0.000058 | 0.000050 | 0.000042 | 0.000048 | 0.000055 |
| System lock | 0.000014 | 0.000015 | 0.000017 | 0.000014 | 0.000015 | 0.000022 | 0.000017 | 0.000014 | 0.000017 | 0.000020 |
| optimizing | 0.014358 | 0.000015 | 0.000018 | 0.000016 | 0.000015 | 0.000022 | 0.000018 | 0.000032 | 0.000017 | 0.000019 |
| statistics | 0.000237 | 0.000219 | 0.000128 | 0.000117 | 0.000126 | 0.000131 | 0.000139 | 0.000114 | 0.000184 | 0.000144 |
| preparing | 0.000023 | 0.000084 | 0.000073 | 0.000020 | 0.000022 | 0.000023 | 0.000028 | 0.000019 | 0.000023 | 0.000025 |
| executing | 0.000004 | 0.000007 | 0.000006 | 0.000017 | 0.000005 | 0.000004 | 0.000006 | 0.000003 | 0.000005 | 0.000006 |
| Sending data | 0.127553 | 0.198391 | 0.189237 | 0.138095 | 0.180962 | 0.219714 | 0.184226 | 0.120855 | 0.187853 | 0.186798 |
| end | 0.000019 | 0.000020 | 0.000023 | 0.000020 | 0.000020 | 0.000026 | 0.000022 | 0.000020 | 0.000024 | 0.000022 |
| query end | 0.000015 | 0.000017 | 0.000017 | 0.000015 | 0.000017 | 0.000019 | 0.000018 | 0.000015 | 0.000060 | 0.000017 |
| closing tables | 0.000022 | 0.000024 | 0.000026 | 0.000023 | 0.000024 | 0.000028 | 0.000026 | 0.000023 | 0.000029 | 0.000027 |
| freeing items | 0.000036 | 0.000043 | 0.000038 | 0.000021 | 0.000037 | 0.000054 | 0.000039 | 0.000030 | 0.000038 | 0.000038 |
| cleaning up | 0.000019 | 0.000022 | 0.000025 | 0.000017 | 0.000022 | 0.000027 | 0.000022 | 0.000019 | 0.000023 | 0.000021 |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
Raw
result1.txt
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.01972350 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 2 | 0.00044300 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 3 | 0.00066000 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 4 | 0.00056350 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 5 | 0.00052850 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 6 | 0.00051225 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 7 | 0.00052150 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 8 | 0.00066600 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 9 | 0.00049925 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 10 | 0.00053900 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
+----------+------------+----------------------------------------------------+
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Status | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| starting | 0.000146 | 0.000106 | 0.000108 | 0.000117 | 0.000117 | 0.000100 | 0.000114 | 0.000091 | 0.000092 | 0.000117 |
| checking permissions | 0.000013 | 0.000011 | 0.000016 | 0.000014 | 0.000013 | 0.000012 | 0.000016 | 0.000010 | 0.000011 | 0.000017 |
| Opening tables | 0.000050 | 0.000033 | 0.000040 | 0.000035 | 0.000034 | 0.000035 | 0.000041 | 0.000034 | 0.000033 | 0.000041 |
| init | 0.000056 | 0.000043 | 0.000080 | 0.000047 | 0.000047 | 0.000049 | 0.000069 | 0.000045 | 0.000045 | 0.000049 |
| System lock | 0.000022 | 0.000018 | 0.000038 | 0.000017 | 0.000016 | 0.000016 | 0.000017 | 0.000015 | 0.000015 | 0.000018 |
| optimizing | 0.000025 | 0.000020 | 0.000023 | 0.000019 | 0.000018 | 0.000018 | 0.000019 | 0.000018 | 0.000018 | 0.000022 |
| statistics | 0.019248 | 0.000102 | 0.000127 | 0.000134 | 0.000110 | 0.000101 | 0.000103 | 0.000169 | 0.000099 | 0.000127 |
| preparing | 0.000029 | 0.000016 | 0.000021 | 0.000019 | 0.000021 | 0.000017 | 0.000016 | 0.000020 | 0.000019 | 0.000024 |
| executing | 0.000004 | 0.000003 | 0.000006 | 0.000004 | 0.000006 | 0.000005 | 0.000004 | 0.000006 | 0.000051 | 0.000007 |
| Sending data | 0.000031 | 0.000024 | 0.000033 | 0.000077 | 0.000050 | 0.000029 | 0.000028 | 0.000032 | 0.000029 | 0.000033 |
| end | 0.000008 | 0.000006 | 0.000009 | 0.000009 | 0.000009 | 0.000053 | 0.000009 | 0.000009 | 0.000007 | 0.000008 |
| query end | 0.000009 | 0.000006 | 0.000011 | 0.000008 | 0.000011 | 0.000010 | 0.000009 | 0.000011 | 0.000009 | 0.000008 |
| closing tables | 0.000018 | 0.000011 | 0.000016 | 0.000013 | 0.000014 | 0.000013 | 0.000015 | 0.000015 | 0.000012 | 0.000013 |
| freeing items | 0.000046 | 0.000032 | 0.000107 | 0.000033 | 0.000043 | 0.000037 | 0.000038 | 0.000167 | 0.000042 | 0.000037 |
| cleaning up | 0.000020 | 0.000017 | 0.000026 | 0.000019 | 0.000021 | 0.000019 | 0.000025 | 0.000027 | 0.000019 | 0.000019 |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
Raw
result1_readonly.txt
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.00014400 | START TRANSACTION READ ONLY |
| 2 | 0.00047175 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 3 | 0.00042500 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 4 | 0.00039875 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 5 | 0.00046775 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 6 | 0.00042525 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 7 | 0.00045150 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 8 | 0.00055375 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 9 | 0.00045350 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 10 | 0.00048050 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
| 11 | 0.00041900 | SELECT SQL_NO_CACHE * FROM rotest WHERE id = 50000 |
+----------+------------+----------------------------------------------------+
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Status | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| starting | 0.000093 | 0.000095 | 0.000090 | 0.000090 | 0.000101 | 0.000108 | 0.000099 | 0.000107 | 0.000091 | 0.000087 |
| checking permissions | 0.000011 | 0.000011 | 0.000009 | 0.000011 | 0.000014 | 0.000010 | 0.000012 | 0.000018 | 0.000013 | 0.000011 |
| Opening tables | 0.000048 | 0.000029 | 0.000026 | 0.000032 | 0.000031 | 0.000027 | 0.000032 | 0.000032 | 0.000059 | 0.000029 |
| init | 0.000045 | 0.000046 | 0.000042 | 0.000052 | 0.000043 | 0.000043 | 0.000049 | 0.000046 | 0.000054 | 0.000044 |
| System lock | 0.000016 | 0.000016 | 0.000014 | 0.000018 | 0.000014 | 0.000016 | 0.000018 | 0.000015 | 0.000020 | 0.000016 |
| optimizing | 0.000017 | 0.000018 | 0.000017 | 0.000046 | 0.000017 | 0.000020 | 0.000050 | 0.000017 | 0.000019 | 0.000015 |
| statistics | 0.000111 | 0.000083 | 0.000091 | 0.000085 | 0.000079 | 0.000096 | 0.000162 | 0.000083 | 0.000080 | 0.000072 |
| preparing | 0.000017 | 0.000017 | 0.000014 | 0.000016 | 0.000014 | 0.000017 | 0.000018 | 0.000018 | 0.000027 | 0.000015 |
| executing | 0.000005 | 0.000004 | 0.000003 | 0.000005 | 0.000003 | 0.000003 | 0.000004 | 0.000004 | 0.000004 | 0.000005 |
| Sending data | 0.000028 | 0.000027 | 0.000023 | 0.000028 | 0.000023 | 0.000023 | 0.000028 | 0.000023 | 0.000029 | 0.000039 |
| end | 0.000007 | 0.000007 | 0.000006 | 0.000007 | 0.000006 | 0.000006 | 0.000009 | 0.000028 | 0.000008 | 0.000007 |
| query end | 0.000010 | 0.000010 | 0.000008 | 0.000011 | 0.000009 | 0.000028 | 0.000012 | 0.000010 | 0.000011 | 0.000011 |
| closing tables | 0.000009 | 0.000009 | 0.000007 | 0.000012 | 0.000007 | 0.000009 | 0.000010 | 0.000008 | 0.000011 | 0.000009 |
| freeing items | 0.000031 | 0.000036 | 0.000035 | 0.000035 | 0.000031 | 0.000030 | 0.000034 | 0.000032 | 0.000036 | 0.000040 |
| cleaning up | 0.000024 | 0.000019 | 0.000016 | 0.000019 | 0.000034 | 0.000016 | 0.000019 | 0.000016 | 0.000020 | 0.000020 |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
Raw
result2_readonly.txt
+----------+------------+---------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------+
| 1 | 0.00014475 | START TRANSACTION READ ONLY |
| 2 | 0.19859700 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 3 | 0.20582900 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 4 | 0.19069950 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 5 | 0.18376575 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 6 | 0.20775525 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 7 | 0.18807900 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 8 | 0.18891075 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 9 | 0.14573700 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 10 | 0.20078100 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
| 11 | 0.12474075 | SELECT SQL_NO_CACHE * FROM rotest WHERE created BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59' |
+----------+------------+---------------------------------------------------------------------------------------------------------+
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Status | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration | Duration |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| starting | 0.000094 | 0.000096 | 0.000111 | 0.000097 | 0.000108 | 0.000097 | 0.000126 | 0.000124 | 0.000099 | 0.000178 |
| checking permissions | 0.000010 | 0.000011 | 0.000011 | 0.000015 | 0.000012 | 0.000011 | 0.000017 | 0.000016 | 0.000011 | 0.000011 |
| Opening tables | 0.000047 | 0.000058 | 0.000029 | 0.000034 | 0.000028 | 0.000028 | 0.000037 | 0.000042 | 0.000029 | 0.000025 |
| init | 0.000048 | 0.000046 | 0.000045 | 0.000048 | 0.000048 | 0.000096 | 0.000058 | 0.000049 | 0.000049 | 0.000043 |
| System lock | 0.000021 | 0.000016 | 0.000016 | 0.000016 | 0.000017 | 0.000018 | 0.000021 | 0.000020 | 0.000016 | 0.000014 |
| optimizing | 0.000019 | 0.000016 | 0.000016 | 0.000015 | 0.000017 | 0.000017 | 0.000021 | 0.000019 | 0.000017 | 0.000014 |
| statistics | 0.000126 | 0.000223 | 0.000176 | 0.000176 | 0.000177 | 0.000124 | 0.000154 | 0.000203 | 0.000127 | 0.000131 |
| preparing | 0.000023 | 0.000025 | 0.000024 | 0.000024 | 0.000024 | 0.000023 | 0.000029 | 0.000029 | 0.000027 | 0.000019 |
| executing | 0.000005 | 0.000005 | 0.000004 | 0.000005 | 0.000004 | 0.000005 | 0.000007 | 0.000005 | 0.000000 | 0.000003 |
| Sending data | 0.198040 | 0.205229 | 0.190156 | 0.183201 | 0.207197 | 0.187548 | 0.188314 | 0.145080 | 0.200280 | 0.124206 |
| end | 0.000021 | 0.000023 | 0.000021 | 0.000021 | 0.000022 | 0.000021 | 0.000021 | 0.000022 | 0.000023 | 0.000019 |
| query end | 0.000012 | 0.000013 | 0.000012 | 0.000035 | 0.000013 | 0.000012 | 0.000012 | 0.000012 | 0.000013 | 0.000010 |
| closing tables | 0.000062 | 0.000020 | 0.000019 | 0.000021 | 0.000021 | 0.000020 | 0.000019 | 0.000020 | 0.000021 | 0.000018 |
| freeing items | 0.000044 | 0.000025 | 0.000041 | 0.000038 | 0.000042 | 0.000040 | 0.000053 | 0.000071 | 0.000047 | 0.000030 |
| cleaning up | 0.000027 | 0.000026 | 0.000022 | 0.000023 | 0.000028 | 0.000022 | 0.000025 | 0.000024 | 0.000023 | 0.000019 |
+----------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment