API tools faq
paste
Advertisement
mr_felixoid

Graphite data table experiments

mr_felixoid
Aug 6th, 2019
103
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.07 KB | None | 0 0
raw download clone embed print report
  1. Graphite data experiments:
  2.  
  3. SELECT
  4. table,
  5. count(),
  6. active,
  7. partition,
  8. formatReadableSize(sum(bytes_on_disk)) AS size
  9. FROM system.parts
  10. WHERE (database = 'graphite') AND (table LIKE 'data_daily_%')
  11. GROUP BY
  12. table,
  13. partition,
  14. active
  15. ORDER BY
  16. partition ASC,
  17. table ASC
  18. -- Before OPTIMIZE FINAL
  19. ┌─table───────────────┬─count()─┬─active─┬─partition─┬─size───────┐
  20. │ data_daily_i1024 │ 3 │ 1 │ 20190601 │ 64.87 MiB │
  21. │ data_daily_i256 │ 3 │ 1 │ 20190601 │ 72.80 MiB │
  22. │ data_daily_i512 │ 3 │ 1 │ 20190601 │ 67.51 MiB │
  23. │ data_daily_orifinal │ 3 │ 1 │ 20190601 │ 67.18 MiB │
  24. │ data_daily_i1024 │ 3 │ 1 │ 20190615 │ 65.57 MiB │
  25. │ data_daily_i256 │ 3 │ 1 │ 20190615 │ 73.59 MiB │
  26. │ data_daily_i512 │ 3 │ 1 │ 20190615 │ 68.24 MiB │
  27. │ data_daily_orifinal │ 3 │ 1 │ 20190615 │ 68.02 MiB │
  28. │ data_daily_i1024 │ 12 │ 1 │ 20190702 │ 438.09 MiB │
  29. │ data_daily_i256 │ 1 │ 1 │ 20190702 │ 528.49 MiB │
  30. │ data_daily_i512 │ 12 │ 1 │ 20190702 │ 468.26 MiB │
  31. │ data_daily_orifinal │ 12 │ 1 │ 20190702 │ 506.63 MiB │
  32. │ data_daily_i1024 │ 9 │ 1 │ 20190720 │ 431.24 MiB │
  33. │ data_daily_i256 │ 4 │ 1 │ 20190720 │ 521.51 MiB │
  34. │ data_daily_i512 │ 12 │ 1 │ 20190720 │ 461.38 MiB │
  35. │ data_daily_orifinal │ 7 │ 1 │ 20190720 │ 510.53 MiB │
  36. │ data_daily_i1024 │ 8 │ 1 │ 20190804 │ 1.70 GiB │
  37. │ data_daily_i256 │ 14 │ 1 │ 20190804 │ 2.14 GiB │
  38. │ data_daily_i512 │ 5 │ 1 │ 20190804 │ 1.84 GiB │
  39. │ data_daily_orifinal │ 10 │ 1 │ 20190804 │ 2.33 GiB │
  40. └─────────────────────┴─────────┴────────┴───────────┴────────────┘
  41. -- After OPTIMIZE vefore cleanup
  42. ┌─table───────────────┬─count()─┬─active─┬─partition─┬─size───────┐
  43. │ data_daily_i1024 │ 3 │ 0 │ 20190601 │ 64.87 MiB │
  44. │ data_daily_i1024 │ 1 │ 1 │ 20190601 │ 64.86 MiB │
  45. │ data_daily_i256 │ 1 │ 1 │ 20190601 │ 72.79 MiB │
  46. │ data_daily_i256 │ 3 │ 0 │ 20190601 │ 72.80 MiB │
  47. │ data_daily_i512 │ 1 │ 1 │ 20190601 │ 67.50 MiB │
  48. │ data_daily_i512 │ 3 │ 0 │ 20190601 │ 67.51 MiB │
  49. │ data_daily_orifinal │ 1 │ 1 │ 20190601 │ 67.17 MiB │
  50. │ data_daily_orifinal │ 3 │ 0 │ 20190601 │ 67.18 MiB │
  51. │ data_daily_i1024 │ 3 │ 0 │ 20190615 │ 65.57 MiB │
  52. │ data_daily_i1024 │ 1 │ 1 │ 20190615 │ 65.56 MiB │
  53. │ data_daily_i256 │ 1 │ 1 │ 20190615 │ 73.58 MiB │
  54. │ data_daily_i256 │ 3 │ 0 │ 20190615 │ 73.59 MiB │
  55. │ data_daily_i512 │ 3 │ 0 │ 20190615 │ 68.24 MiB │
  56. │ data_daily_i512 │ 1 │ 1 │ 20190615 │ 68.23 MiB │
  57. │ data_daily_orifinal │ 3 │ 0 │ 20190615 │ 68.02 MiB │
  58. │ data_daily_orifinal │ 1 │ 1 │ 20190615 │ 68.02 MiB │
  59. │ data_daily_i1024 │ 1 │ 1 │ 20190702 │ 438.00 MiB │
  60. │ data_daily_i1024 │ 12 │ 0 │ 20190702 │ 438.09 MiB │
  61. │ data_daily_i256 │ 1 │ 1 │ 20190702 │ 528.49 MiB │
  62. │ data_daily_i256 │ 1 │ 0 │ 20190702 │ 528.49 MiB │
  63. │ data_daily_i512 │ 12 │ 0 │ 20190702 │ 468.26 MiB │
  64. │ data_daily_i512 │ 1 │ 1 │ 20190702 │ 468.20 MiB │
  65. │ data_daily_orifinal │ 12 │ 0 │ 20190702 │ 506.63 MiB │
  66. │ data_daily_orifinal │ 1 │ 1 │ 20190702 │ 506.42 MiB │
  67. │ data_daily_i1024 │ 9 │ 0 │ 20190720 │ 431.24 MiB │
  68. │ data_daily_i1024 │ 1 │ 1 │ 20190720 │ 431.21 MiB │
  69. │ data_daily_i256 │ 4 │ 0 │ 20190720 │ 521.51 MiB │
  70. │ data_daily_i256 │ 1 │ 1 │ 20190720 │ 521.49 MiB │
  71. │ data_daily_i512 │ 12 │ 0 │ 20190720 │ 461.38 MiB │
  72. │ data_daily_i512 │ 1 │ 1 │ 20190720 │ 461.34 MiB │
  73. │ data_daily_orifinal │ 7 │ 0 │ 20190720 │ 510.53 MiB │
  74. │ data_daily_orifinal │ 1 │ 1 │ 20190720 │ 502.00 MiB │
  75. │ data_daily_i1024 │ 1 │ 1 │ 20190804 │ 1.62 GiB │
  76. │ data_daily_i1024 │ 8 │ 0 │ 20190804 │ 1.70 GiB │
  77. │ data_daily_i256 │ 14 │ 0 │ 20190804 │ 2.14 GiB │
  78. │ data_daily_i256 │ 1 │ 1 │ 20190804 │ 2.04 GiB │
  79. │ data_daily_i512 │ 1 │ 1 │ 20190804 │ 1.77 GiB │
  80. │ data_daily_i512 │ 5 │ 0 │ 20190804 │ 1.84 GiB │
  81. │ data_daily_orifinal │ 10 │ 0 │ 20190804 │ 2.33 GiB │
  82. │ data_daily_orifinal │ 1 │ 1 │ 20190804 │ 2.42 GiB │
  83. └─────────────────────┴─────────┴────────┴───────────┴────────────┘
  84. -- After cleanup
  85. ┌─table───────────────┬─count()─┬─active─┬─partition─┬─size───────┐
  86. │ data_daily_i1024 │ 1 │ 1 │ 20190601 │ 64.86 MiB │
  87. │ data_daily_i256 │ 1 │ 1 │ 20190601 │ 72.79 MiB │
  88. │ data_daily_i512 │ 1 │ 1 │ 20190601 │ 67.50 MiB │
  89. │ data_daily_orifinal │ 1 │ 1 │ 20190601 │ 67.17 MiB │
  90. │ data_daily_i1024 │ 1 │ 1 │ 20190615 │ 65.56 MiB │
  91. │ data_daily_i256 │ 1 │ 1 │ 20190615 │ 73.58 MiB │
  92. │ data_daily_i512 │ 1 │ 1 │ 20190615 │ 68.23 MiB │
  93. │ data_daily_orifinal │ 1 │ 1 │ 20190615 │ 68.02 MiB │
  94. │ data_daily_i1024 │ 1 │ 1 │ 20190702 │ 438.00 MiB │
  95. │ data_daily_i256 │ 1 │ 1 │ 20190702 │ 528.49 MiB │
  96. │ data_daily_i512 │ 1 │ 1 │ 20190702 │ 468.20 MiB │
  97. │ data_daily_orifinal │ 1 │ 1 │ 20190702 │ 506.42 MiB │
  98. │ data_daily_i1024 │ 1 │ 1 │ 20190720 │ 431.21 MiB │
  99. │ data_daily_i256 │ 1 │ 1 │ 20190720 │ 521.49 MiB │
  100. │ data_daily_i512 │ 1 │ 1 │ 20190720 │ 461.34 MiB │
  101. │ data_daily_orifinal │ 1 │ 1 │ 20190720 │ 502.00 MiB │
  102. │ data_daily_i1024 │ 1 │ 1 │ 20190804 │ 1.62 GiB │
  103. │ data_daily_i256 │ 1 │ 1 │ 20190804 │ 2.04 GiB │
  104. │ data_daily_i512 │ 1 │ 1 │ 20190804 │ 1.77 GiB │
  105. │ data_daily_orifinal │ 1 │ 1 │ 20190804 │ 2.42 GiB │
  106. └─────────────────────┴─────────┴────────┴───────────┴────────────┘
  107.  
  108. > cat /var/lib/clickhouse/metadata/graphite/data_daily_*
  109. ATTACH TABLE data_daily_i1024
  110. (
  111. `metric` LowCardinality(String),
  112. `value` Float64,
  113. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  114. `date` Date CODEC(DoubleDelta, LZ4),
  115. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  116. )
  117. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  118. PARTITION BY toYYYYMMDD(date)
  119. ORDER BY (metric, timestamp)
  120. SETTINGS index_granularity = 1024
  121. ATTACH TABLE data_daily_i256
  122. (
  123. `metric` LowCardinality(String),
  124. `value` Float64,
  125. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  126. `date` Date CODEC(DoubleDelta, LZ4),
  127. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  128. )
  129. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  130. PARTITION BY toYYYYMMDD(date)
  131. ORDER BY (metric, timestamp)
  132. SETTINGS index_granularity = 256
  133. ATTACH TABLE data_daily_i512
  134. (
  135. `metric` LowCardinality(String),
  136. `value` Float64,
  137. `timestamp` UInt32 CODEC(DoubleDelta, LZ4),
  138. `date` Date CODEC(DoubleDelta, LZ4),
  139. `updated` UInt32 CODEC(DoubleDelta, LZ4)
  140. )
  141. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  142. PARTITION BY toYYYYMMDD(date)
  143. ORDER BY (metric, timestamp)
  144. SETTINGS index_granularity = 512
  145. ATTACH TABLE data_daily_orifinal
  146. (
  147. `metric` String,
  148. `value` Float64,
  149. `timestamp` UInt32,
  150. `date` Date,
  151. `updated` UInt32
  152. )
  153. ENGINE = GraphiteMergeTree('graphite_ig_rollup')
  154. PARTITION BY toYYYYMMDD(date)
  155. ORDER BY (metric, timestamp)
  156. 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!