API tools faq
paste
Advertisement
mr_felixoid

3 days partitions

mr_felixoid
Aug 7th, 2019
154
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.82 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. table,
  3. count(),
  4. active,
  5. partition,
  6. formatReadableSize(sum(bytes_on_disk)) AS size,
  7. sum(rows) AS rows
  8. FROM system.parts
  9. WHERE table LIKE 'data_3d%' OR table like 'data_month%'
  10. GROUP BY
  11. table,
  12. partition,
  13. active
  14. ORDER BY
  15. partition ASC,
  16. table ASC,
  17. active ASC
  18. FORMAT PrettySpace
  19.  
  20.  
  21. table count() active partition size rows
  22.  
  23. data_month_i256 3 1 201904 6.28 GiB 1523808811
  24. data_3days_i1024 1 1 20190601 246.60 MiB 53274865
  25. data_3days_i256 1 1 20190601 278.37 MiB 53274865
  26. data_3days_i512 1 1 20190601 257.19 MiB 53274865
  27. data_3days_original 1 1 20190601 246.82 MiB 53274865
  28. data_3days_values 1 1 20190601 476.20 MiB 53274865
  29. data_3days_i1024 5 0 20190619 652.33 MiB 160437871
  30. data_3days_i1024 1 1 20190619 643.95 MiB 159936191
  31. data_3days_i256 6 0 20190619 748.14 MiB 159936194
  32. data_3days_i256 1 1 20190619 739.98 MiB 159936191
  33. data_3days_i512 10 0 20190619 691.67 MiB 160437872
  34. data_3days_i512 1 1 20190619 675.94 MiB 159936191
  35. data_3days_original 5 0 20190619 689.42 MiB 160437875
  36. data_3days_original 1 1 20190619 661.03 MiB 159936191
  37. data_3days_values 1 1 20190619 1.29 GiB 159936191
  38. data_3days_i1024 9 0 20190719 4.82 GiB 1823407380
  39. data_3days_i1024 1 1 20190719 4.80 GiB 1823407369
  40. data_3days_i256 6 0 20190719 5.86 GiB 1823407378
  41. data_3days_i256 1 1 20190719 5.83 GiB 1823407369
  42. data_3days_i512 11 0 20190719 5.17 GiB 1823407378
  43. data_3days_i512 1 1 20190719 5.15 GiB 1823407369
  44. data_3days_original 17 0 20190719 6.00 GiB 1824661371
  45. data_3days_original 1 1 20190719 5.80 GiB 1823407369
  46. data_3days_values 1 1 20190719 9.97 GiB 1823407369
  47. data_3days_i1024 14 0 20190803 19.83 GiB 8978975447
  48. data_3days_i1024 1 1 20190803 19.51 GiB 8906402955
  49. data_3days_i256 10 0 20190803 24.83 GiB 9005089265
  50. data_3days_i256 1 1 20190803 24.52 GiB 8906402955
  51. data_3days_i512 10 0 20190803 21.49 GiB 8993041029
  52. data_3days_i512 1 1 20190803 21.18 GiB 8906402955
  53. data_3days_original 13 0 20190803 34.23 GiB 9036540684
  54. data_3days_original 1 1 20190803 38.23 GiB 8906402955
  55. data_3days_values 1 1 20190803 32.34 GiB 8906402955
  56.  
  57. SELECT
  58. table,
  59. name,
  60. formatReadableSize(data_compressed_bytes) AS compressed,
  61. formatReadableSize(data_uncompressed_bytes) AS uncompressed,
  62. floor(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio,
  63. formatReadableSize(marks_bytes) AS marks,
  64. compression_codec AS codec
  65. FROM system.columns
  66. WHERE table LIKE 'data_3days_%'
  67. ORDER BY
  68. name ASC,
  69. table ASC
  70.  
  71. ┌─table───────────────┬─name──────────┬─compressed─┬─uncompressed─┬─ratio─┬─marks──────┬─codec───────────────────┐
  72. │ data_3days_i1024 │ date │ 49.96 MiB │ 20.38 GiB │ 417.7 │ 244.60 MiB │ CODEC(DoubleDelta, LZ4) │
  73. │ data_3days_i256 │ date │ 49.96 MiB │ 20.38 GiB │ 417.7 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  74. │ data_3days_i512 │ date │ 49.96 MiB │ 20.38 GiB │ 417.7 │ 489.19 MiB │ CODEC(DoubleDelta, LZ4) │
  75. │ data_3days_original │ date │ 122.92 MiB │ 20.38 GiB │ 169.7 │ 30.57 MiB │ │
  76. │ data_3days_values │ date │ 49.96 MiB │ 20.38 GiB │ 417.7 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  77. │ data_3days_i1024 │ metric │ 292.25 MiB │ 20.91 GiB │ 73.2 │ 489.19 MiB │ │
  78. │ data_3days_i256 │ metric │ 315.89 MiB │ 21.39 GiB │ 69.3 │ 1.91 GiB │ │
  79. │ data_3days_i512 │ metric │ 305.58 MiB │ 21.08 GiB │ 70.6 │ 978.38 MiB │ │
  80. │ data_3days_original │ metric │ 2.67 GiB │ 588.30 GiB │ 220.1 │ 30.57 MiB │ │
  81. │ data_3days_values │ metric │ 315.89 MiB │ 21.39 GiB │ 69.3 │ 1.91 GiB │ │
  82. │ data_3days_i1024 │ timestamp │ 199.22 MiB │ 40.77 GiB │ 209.5 │ 244.60 MiB │ CODEC(DoubleDelta, LZ4) │
  83. │ data_3days_i256 │ timestamp │ 199.22 MiB │ 40.77 GiB │ 209.5 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  84. │ data_3days_i512 │ timestamp │ 199.22 MiB │ 40.77 GiB │ 209.5 │ 489.19 MiB │ CODEC(DoubleDelta, LZ4) │
  85. │ data_3days_original │ timestamp │ 9.33 GiB │ 40.77 GiB │ 4.3 │ 30.57 MiB │ │
  86. │ data_3days_values │ timestamp │ 199.22 MiB │ 40.77 GiB │ 209.5 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  87. │ data_3days_i1024 │ updated │ 1.53 GiB │ 40.77 GiB │ 26.6 │ 244.60 MiB │ CODEC(DoubleDelta, LZ4) │
  88. │ data_3days_i256 │ updated │ 1.53 GiB │ 40.77 GiB │ 26.6 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  89. │ data_3days_i512 │ updated │ 1.53 GiB │ 40.77 GiB │ 26.6 │ 489.19 MiB │ CODEC(DoubleDelta, LZ4) │
  90. │ data_3days_original │ updated │ 11.51 GiB │ 40.77 GiB │ 3.5 │ 30.57 MiB │ │
  91. │ data_3days_i1024 │ value │ 21.07 GiB │ 81.53 GiB │ 3.8 │ 244.60 MiB │ │
  92. │ data_3days_i256 │ value │ 21.07 GiB │ 81.53 GiB │ 3.8 │ 978.38 MiB │ │
  93. │ data_3days_i512 │ value │ 21.07 GiB │ 81.53 GiB │ 3.8 │ 489.19 MiB │ │
  94. │ data_3days_original │ value │ 21.07 GiB │ 81.53 GiB │ 3.8 │ 30.57 MiB │ │
  95. │ data_3days_values │ value_2delta │ 19.69 GiB │ 81.53 GiB │ 4.1 │ 978.38 MiB │ CODEC(DoubleDelta, LZ4) │
  96. │ data_3days_values │ value_gorilla │ 15.63 GiB │ 81.53 GiB │ 5.2 │ 978.38 MiB │ CODEC(Gorilla, LZ4) │
  97. └─────────────────────┴───────────────┴────────────┴──────────────┴───────┴────────────┴─────────────────────────┘
  98.  
  99. --- How tables are created
  100. CREATE TABLE graphite.data_3days_values
  101. (
  102. `metric` LowCardinality(String),
  103. `value_gorilla` Float64 CODEC(Gorilla, LZ4),
  104. `value_2delta` Float64 CODEC(DoubleDelta, LZ4),
  105. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  106. `date` Date CODEC(DoubleDelta, LZ4)
  107. )
  108. ENGINE = MergeTree
  109. PARTITION BY toYYYYMMDD(toStartOfInterval(date, toIntervalDay(3)))
  110. ORDER BY (metric, timestamp)
  111. SETTINGS index_granularity = 256
  112.  
  113. CREATE TABLE graphite.data_3days_i256
  114. (
  115. `metric` LowCardinality(String),
  116. `value` Float64,
  117. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  118. `date` Date CODEC(DoubleDelta, LZ4),
  119. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  120. )
  121. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  122. PARTITION BY toYYYYMMDD(toStartOfInterval(date, toIntervalDay(3)))
  123. ORDER BY (metric, timestamp)
  124. SETTINGS index_granularity = 256
  125.  
  126. CREATE TABLE graphite.data_3days_i512
  127. (
  128. `metric` LowCardinality(String),
  129. `value` Float64,
  130. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  131. `date` Date CODEC(DoubleDelta, LZ4),
  132. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  133. )
  134. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  135. PARTITION BY toYYYYMMDD(toStartOfInterval(date, toIntervalDay(3)))
  136. ORDER BY (metric, timestamp)
  137. SETTINGS index_granularity = 512
  138.  
  139. CREATE TABLE graphite.data_3days_i1024
  140. (
  141. `metric` LowCardinality(String),
  142. `value` Float64,
  143. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  144. `date` Date CODEC(DoubleDelta, LZ4),
  145. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  146. )
  147. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  148. PARTITION BY toYYYYMMDD(toStartOfInterval(date, toIntervalDay(3)))
  149. ORDER BY (metric, timestamp)
  150. SETTINGS index_granularity = 1024
  151.  
  152. CREATE TABLE graphite.data_3days_original
  153. (
  154. `metric` String,
  155. `value` Float64,
  156. `timestamp` UInt32,
  157. `date` Date,
  158. `updated` UInt32
  159. )
  160. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  161. PARTITION BY toYYYYMMDD(toStartOfInterval(date, toIntervalDay(3)))
  162. ORDER BY (metric, timestamp)
  163. SETTINGS index_granularity = 8192
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!