سریعترین دیتابیس برای کارهای تحلیلی که این روزها برای خیلی از فعالان حوزه بیگ دیتا شناخته شده است. همواره استفاده از هر منبع ذخیره سازی اطلاعات برای محیط عملیاتی نیاز جدی و مبرمی به مانیتورینگ و پایش مداوم درخواست ها یا کوئری های در حال پردازش دارد. چگونگی استفاده کاربران و میزان استفاده آنها از منابع سرویس دهنده (در کلاستر یا کلاسترهای سازمان) بایستی به طور منظم مورد بررسی قرار بگیرد تا بهینه سازی و یا گاها آموزش های لازم در دستور کار دپارتمان داده قرار بگیرد.
علاوه برای خیل ابزارهای گوناگون مانیتورینگ در جامعه سورس باز کلیک هاوس، میتوانیم از بسترهای مصورسازی (Visulization) در سازمان مانند Metabase, Superset و حتی PowerBI با کمک نمونه کد های زیر استفاده کنیم.
به طور کلی کار را بر اساس دو شاخصه زیر می توانیم دسته بندی کنیم:
با استفاده از کوئری زیر میتوانیم چگونگی اطلاعات کمی و کیفی جداول درون دیتابیس را مشاهده کنیم تا فراوانی و در نمای دیگر اشتباهات سهوی در ساخت و طراحی جداول را شناسایی کنیم.
SELECT table, sum(rows) AS rows, max(modification_time) AS latest_modification, formatReadableSize(sum(bytes)) AS data_size, formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size, any(engine) AS engine, sum(bytes) AS bytes_size FROM clusterAllReplicas(default, system.parts) WHERE active GROUP BY database, table ORDER BY bytes_size DESC
پر هزینه ترین کوئری ها در کلیک هاوس
در گام بعدی با استفاده یک کوئری تلاش میکنیم تا کوئری هایی که برای اجرا به سمت کلیک هاوس ارسال
شده اند را پس از مرتب سازی بر اساس شاخصی مانند مدت اجرا/پردازش کوئری مشاهده کنیم که در مورد استفاده شما میتواند تغییر پیدا کند.
SELECT
type,
query_start_time,
formatReadableTimeDelta(query_duration_ms) AS query_duration,
query_id,
query_kind,
is_initial_query,
query,
concat(toString(read_rows), ' rows / ', formatReadableSize(read_bytes)) AS read,
concat(toString(result_rows), ' rows / ', formatReadableSize(result_bytes)) AS result,
formatReadableSize(memory_usage) AS `memory usage`,
exception,
concat('\n', stack_trace) AS stack_trace,
user,
initial_user,
multiIf(empty(client_name), http_user_agent, concat(client_name, ' ', toString(client_version_major), '.', toString(client_version_minor), '.', toString(client_version_patch))) AS client,
client_hostname,
databases,
tables,
columns,
used_aggregate_functions,
used_dictionaries,
used_formats,
used_functions,
used_table_functions,
ProfileEvents.Names,
ProfileEvents.Values,
Settings.Names,
Settings.Values
FROM system.query_log
WHERE (type != 'QueryStart') AND (query_kind = 'Select') AND (event_date >= (today() - 1)) AND (event_time >= (now() - toIntervalDay(1)))
ORDER BY query_duration_ms DESC
LIMIT 10
در این نمونه برای سازمان تلاش کردم از ابزار متابیس برای مصور سازی این اطلاعات استفاده کرده ام و برای مشاهده کوئری های اجرا شده از طریق این ابزار و کاربران سازمان ستونی را برای شناسایی و مشاهده اطلاعات کاربر مرتبط نیز اضافه کرده ام تا برای بررسی دقیق تر مشکل با آنها بتوانم ارتباط بگیرم.
SELECT
type,
query_start_time,
query_duration_ms,
formatReadableTimeDelta(query_duration_ms/1000) AS query_duration,
query_id,
query_kind,
is_initial_query,
query,
case when extract(query, 'userID: (\\d+)')!='' then concat('https://***.com/admin/people/',extract(query, 'userID: (\\d+)'),'/edit') else 'N/A' end AS MetabaseUserID,
concat(toString(read_rows), ' rows / ', formatReadableSize(read_bytes)) AS read,
concat(toString(result_rows), ' rows / ', formatReadableSize(result_bytes)) AS result,
formatReadableSize(memory_usage) AS `memory usage`,
exception,
concat('\n', stack_trace) AS stack_trace,
user,
initial_user,
multiIf(empty(client_name), http_user_agent, concat(client_name, ' ', toString(client_version_major), '.', toString(client_version_minor), '.', toString(client_version_patch))) AS client,
client_hostname,
databases,
tables,
columns,
used_aggregate_functions,
used_dictionaries,
used_formats,
used_functions,
used_table_functions,
FROM system.query_log
WHERE (type != 'QueryStart') AND (query_kind = 'Select') AND (event_date >= (today() - 1)) AND (event_time >= (now() - toIntervalDay(14)))
order by query_duration_ms desc
خروجی این کار که برای مدیر سرویس کلیک هاوس نیز سفارشی سازی شده است مانند تصویر زیر خواهد بود.
نمونه جزییات قابل مشاهده در هر یک از سطرهای اطلاعاتی نیز به شکل دیالوگ نیز قابل مشاهده و بررسی خواهد بود.
نکته! بدلیل یکسان بودن بستر مانیتورینگ و پایش داده های کلیک هاوس به آسانی با کلیک بروی لینک سفارشی شده میتوان از هویت کاربران نیز آگاه شد تا با آنها مذاکره یا Action مورد نیاز را در دستور کار قرار داد.
در بررسی جزییات نتیجه بدست آمده از کوئری میزان حجم اطلاعات خوانده شده از دیسک، حافظه جانبی یا RAM و مدت کوئری را میتوان مورد بررسی قرار داد.
بر این اساس و نیاز مندی کوئری زیر میتواند به شما امکان مقایسه مدت اجرا یا هر متریک دیگری که در مقایسه کوئری ها نیاز دارید با جای گزاری و بازنویسی بخش یا ستون مربوطه در کوئری زیر صورت پذیرد.
SELECT query_id, query, formatReadableTimeDelta(query_duration_ms) AS query_duration FROM clusterAllReplicas(default, system.query_log) WHERE (type != 'QueryStart') AND (query_kind = 'Select') AND (event_time >= (now() - toIntervalHour(1))) ORDER BY event_time DESC LIMIT 10
در ادامه دو نسخه از یک کوئری را میتوان با شناسایی کوئری آی دی آنها از system.querylog با استفاده کوئری زیر مقایسه کرد.
WITH query_id = '...query_id_old_version...' AS first, query_id = '...query_id_new_version...' AS second SELECT PE.Names AS metric, anyIf(PE.Values, first) AS v1, anyIf(PE.Values, second) AS v2 FROM clusterAllReplicas(default, system.query_log) ARRAY JOIN ProfileEvents AS PE WHERE (first OR second) AND (event_date = today()) AND (type = 2) GROUP BY metric HAVING v1 != v2 ORDER BY (v2 - v1) / (v1 + v2) ASC, v2 ASC, metric ASC
د ر نمونه کوئری و نمودار زیر می توانید تعداد و مدت صرف شده "بصورت میانگین" برای اجرای کوئری های پردازش در کلیک هاوس را مشاهده کنید تا رفتارهای نامتعارف و بحران های در حال اتفاق را مشاهده و گاها پیش بینی نمایید.
SELECT
toStartOfHour(event_time) AS event_time_h,
count() AS count_m,
avg(query_duration_ms) AS avg_duration
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_kind = 'Select') AND (type != 'QueryStart') AND (event_time > (now() - toIntervalDay(3)))
GROUP BY event_time_h
ORDER BY event_time_h ASC
کوئری زیر میتواند به شما امکان مشاهده و محاسبه تعداد کوئری ها بر اساس نام کلاینت را ارائه کند تا کانال های ارتباطی بهره برداران و میزان کارهای دریافتی از سمت آنها را شناسایی نموده و برای مدیریت یا بهبود آنها اقدام مناسبی را اتخاذ نمایید.
SELECT
toStartOfMinute(event_time) AS event_time_m,
if(empty(client_name), 'unknow_or_http', client_name) AS client_name,
count(),
query_kind
FROM clusterAllReplicas(default, system.query_log)
WHERE (type = 'QueryStart') AND (event_time > (now() - toIntervalMinute(10))) AND (query_kind = 'Select')
GROUP BY
event_time_m,
client_name,
query_kind
ORDER BY
event_time_m DESC,
count() ASC
LIMIT 100
در واقع امکان مشاهده و پایش تمام سرویس ها و اتفاقات به صورت چشمی توسط انسان کاری نشدنی یا به عبارتی دیگر نامطمئن است. بنابراین پیشنهاد میکنم تا با پیاده سازی کد و یا اسکریپت های مطلوب اقدام به بررسی های برنامه ریزی شده و سیستماتیک نمایید و به هنگام کشف موارد قابل رسیدگی و پیشگیری به کارشناسان و مدیران سرویس اعلان "notification" های مناسب ارسال نمایید و در صورت نبود بستر مورد نیاز از ایمیل استفاده شود.
