PDA

View Full Version : مشکلperformance در distributed Database



golnaz_a
چهارشنبه 06 خرداد 1394, 13:06 عصر
سلام
من یک جدول با 40 ستون و نزدیک به 30 میلیون داده در اون دارم. می خواهم هر query که از روی این جدول زدم کمتر از 1 ثانیه به جواب برسم.برای اینکار اول اومدم جدولم رو پارتیشن کردم بر اساس تاریخ به صورت ماهیانه بطوریکه هر ماه توی یک FileGroup قرار بگیرد و به غیر از ID جدول که cluster index بود فیلد هایی که در شرط فیلتر query میومد مثل همین تاریخ و... رو Non_clusterIndex کردم سرعت کمی بهبود پیدا کرد ولی هنوز خیلی کم بود.
اینبار اومدم با استفاده از linked server دیتابیسم رو روی 6 تا کامپیوتر توزیع کردم هر کامپیوتر 3 یا 4 سال از داده های من رو در خودش داره و جدولم در همه کامپیوتر ها پارتیشن شده و همونطور که در بالا گفتم index گذاری هم شده ولی باز سرعت مناسب نیست
سوالم اینه که اولا اصلا میشه با sql این حجم از داده را مدیریت کرد و اگر آره چه طوری؟ کجای کار من اشتباه هست که به نتیجه نرسیدم؟:ناراحت:
کسی اگر تجربه ای در خصوص کار با large data داره لطفا راهنمایی کنه

golbafan
چهارشنبه 06 خرداد 1394, 13:54 عصر
چی بگم والا؟

اول که فکر میکنم استفاده از چهل ستون! کار درستی نیست
دویُم ، شما از سرور استفاده میکنید یا نه ؟
سیُم ، دقیقا بگید کوئری چی هست و ایندکس ها چی هستند و چند ثانیه زمان مصرف میشه؟
چارم ، ستون ها رو هم پارتیشن بندی کنید.
پنجم: از oracle یا mysql استفاده کنید (اگر نیاز به دیتابیسهای ریلیشن دارید) در غیر اینصورت از mongodb یا cassandra

مهدی نان شکری
چهارشنبه 06 خرداد 1394, 14:05 عصر
با سلام
بدون Plan نمی توان در مورد Performance یک کوئری صحبت دقیقی کرد و شبیه راه رفتن در تاریکی است.

golnaz_a
چهارشنبه 06 خرداد 1394, 14:25 عصر
چی بگم والا؟

اول که فکر میکنم استفاده از چهل ستون! کار درستی نیست
دویُم ، شما از سرور استفاده میکنید یا نه ؟
سیُم ، دقیقا بگید کوئری چی هست و ایندکس ها چی هستند و چند ثانیه زمان مصرف میشه؟
چارم ، ستون ها رو هم پارتیشن بندی کنید.
پنجم: از oracle یا mysql استفاده کنید (اگر نیاز به دیتابیسهای ریلیشن دارید) در غیر اینصورت از mongodb یا cassandra

ممنون از پاسخگویی برای توضیح بیشتر باید بگم:
1-این جدول تستی هست برای اینکه بفهمم اگر database ام رشد کرد چه طور باهاش برخورد کنم
2-بله از سرور استفاده میکنم اما فعلا در حالت تست روی یک شبکه database رو توزیع کردم
3-کوئری یک select ساده هست :
select count (id) from table where Date >@date
4- آیا sql جوابگو نیست یعنی حتما باید switch کنم روی اوراکل؟ چون شنیدم sql هم میتونه large data رو مدیریت کنه لطفا توضیح بدید؟

مهدی نان شکری
چهارشنبه 06 خرداد 1394, 14:36 عصر
MS SQL از لحاظ Performance برای تعداد رکورد هایی که فرمودید (البته می توانید آن عدد را در 40 نیز ضرب نمایید) با اوراکل در شرایطی که از RAC استفاده نکنید تقریبا برابر هستند.
البته این تعداد رو خودم در محیط تجاری تجربه کردم.
برای برطرف کردن مشکلات Performance nv در سطح کوچک معمولا از تغیر سکو استفاده نمی کنند.

منظور شما از توزیع شدن دیتابیس آیا RAC می باشد؟

golnaz_a
چهارشنبه 06 خرداد 1394, 14:43 عصر
با سلام
بدون Plan نمی توان در مورد Performance یک کوئری صحبت دقیقی کرد و شبیه راه رفتن در تاریکی است.

این هم plan کوئری ام :
در حالت اول که هنوز database رو توزیع نکرده بودم فقط پارتیشن و index داره:
131707
در حالت دوم این کوئری رو برای پیدا کردن count زدم نمی دونم بهینه هست یا نه :


select sum(id) from(select count (id) as id from Table where StartDate >@date
union
select count (id) as id from server1.test.dbo.Table where StartDate >@date
union
select count (id) as id from server3.test.dbo.Table where StartDate >@date
union
select count (id) as id from server4.test.dbo.Table where StartDate >@date
union
select count (id) as id from server5.test.dbo.Table where StartDate >@date
union
select count (id) as id from server6.test.dbo.Table where StartDate >@date)t

golnaz_a
چهارشنبه 06 خرداد 1394, 14:44 عصر
non_cluster index روی date زدم

golnaz_a
چهارشنبه 06 خرداد 1394, 14:52 عصر
MS SQL از لحاظ Performance برای تعداد رکورد هایی که فرمودید (البته می توانید آن عدد را در 40 نیز ضرب نمایید) با اوراکل در شرایطی که از RAC استفاده نکنید تقریبا برابر هستند.
البته این تعداد رو خودم در محیط تجاری تجربه کردم.
برای برطرف کردن مشکلات Performance nv در سطح کوچک معمولا از تغیر سکو استفاده نمی کنند.

منظور شما از توزیع شدن دیتابیس آیا RAC می باشد؟

من فقط روی سرور های دیگم داده ها رو به این صورت تقسیم کردم که هر سرور 3 سال را در بر بگیره مثلا از 2002 تا 2005 روی یک سرور بعد بر اساس ماه پارتیشن کردم و با linked server هم به داده ها دسترسی دارم
منظورتون از Rac رو متوجه نشدم اطلاعاتی در این زمینه ندارم میشه لطفا توضیح بدید؟

مهدی نان شکری
چهارشنبه 06 خرداد 1394, 15:06 عصر
لطفا پلن هر دو کوئری را Save کنید و ارسال نمایید.
ممنون

golbafan
چهارشنبه 06 خرداد 1394, 21:01 عصر
MS SQL از لحاظ Performance برای تعداد رکورد هایی که فرمودید (البته می توانید آن عدد را در 40 نیز ضرب نمایید) با اوراکل در شرایطی که از RAC استفاده نکنید تقریبا برابر هستند.
البته این تعداد رو خودم در محیط تجاری تجربه کردم.
برای برطرف کردن مشکلات Performance nv در سطح کوچک معمولا از تغیر سکو استفاده نمی کنند.
منظور شما از توزیع شدن دیتابیس آیا RAC می باشد؟

سلام مهندس عزیز
ما در تستهایی که روی داده های مخابراتی داشتیم متاسفانه نتونستیم از sqlserver یک پرفورمنس قابل قبول بگیریم (در بهینه ترین حالت)
تعداد رکورد ها بالغ بر 100 میلیارده که روی سرورهای بلید ذخبره میشن
درحالی که اوراکل و MyIsam از mysql تونستند داده ها رو در زمان مناسبی آنالیز کنن و آمارش رو بتونیم مشاهده کنیم

البته من هم فکر میکنم 30 میلیون هنوز داده زیادی بشمار نمیره ولی چون تعداد ستونهای ایشون بهینه نیست فکر کنم دلیل اصلی مشکل ایشون باشه

soheileee
پنج شنبه 07 خرداد 1394, 00:55 صبح
سلام
من یک جدول با 40 ستون و نزدیک به 30 میلیون داده در اون دارم. می خواهم هر query که از روی این جدول زدم کمتر از 1 ثانیه به جواب برسم.

انتظار شما برای پاسخ دهی روی یک جدول با 30 میلیون رکورد در کمتر از یک ثانیه کمی بالاست.
حتی زمانی که جدول رو پارتیشن بندی می کنی.
زمانی که شما داری از یک شبکه دارای سرور صحبت می کنی، توان اون سرور و تجهیزات شبکه شما در Performance بسیار مؤثره.
مثلاً سؤال من اینه، روی سرور شما چند تا کارت شبکه موجوده و سرعت انتقال داده شما چقدره؟

یک پاسخ معمولی برای یک شبکه کوچک دارای یک سرور معمولی:

سرور من یک کارت شبکه گیگابیت داره.

خوب یعنی اگر این کارت شبکه 100% پهنای باند خودش رو به انتقال داده های شما تخصیص بده، که حتماً اینطور نخواهد بود، در هر ثانیه می تونه 125 مگابایت داده رو برات منتقل کنه.
در حالی که اگه فرض کنیم در بهترین شرایط تمام ستونهای جدول شما Data type شون Int باشه و با توجه به اینکه Storage size برای INT برابر 4 بایت هست، برای 30 میلیون رکورد حجم جدول شما حدود 4.47 گیگابایت می شه.
پس در بهترین حالت، با یک کارت شبکه گیگابیتی، حدود 37 ثانیه زمان لازمه تا تمام جدول لود بشه.

بماند که شما باید یک دیتابیس با طراحی خوب داشته باشی که بتونی Performance خوبی بگیری.
مثلاً در رابطه با داشتن 40 ستون در یک جدول، اگه دیتابیس شما Operational هست خوب واقعاً 40 ستون زیاده که در یک جدول داشته باشید، اما اگه یه Data Warehouse طراحی کردید و در یک Dimension چهل تا ستون دارید، خوب این طبیعیه.
البته در این صورت باید بگم که داشتن 30 میلیون رکورد در یک Dimension دوباره یه چیز غیرطبیعی هست و نشون می ده که طراحی شما ایراداتی داره.

از این مورد که بگذریم، بحث ایندکسینگ روی جداول با تعداد زیادی داده خیلی مهمه.
مثلاً ممکنه لازم باشه تعدادی Nonclustered Index تعریف کنی روی ستونهایی که بیشتر صدا زده می شن و تعدادی از ستونها رو که کمتر مورد استفاده کوئری ها قرار گرفتن رو Include تعریف کنی در همون ایندکس.
اینجا این بحث مطرح میشه که شما از چه SQL Server Edition استفاده می کنی؟
چون اگر از SQL Server 2012 Enterprise Edition استفاده کنی امکان تعریف Columnstore Index روی جدول وجود داره. البته از این نوع ایندکس زمانی باید استفاده کنی که جدول شما خیلی به ندرت Update بشه، چون Columnstore Index بهت اجازه نمی ده دیتا رو آپدیت کنی. پس اول باید ایندکس رو غیرفعال کنی، Update رو انجام بدی و بعد دوباره ایندکس رو فعال کنی.

البته این محدودیت برای Clustered Columnstore Index در SQL Server 2014 برطرف شده و در SQL Server 2016 کلاً امکان Update وجود داره بدون نیاز به دست زدن به ایندکس.
اما اگه شرایط استفاده از این نوع ایندکس رو داشته باشی، Performance به طرز محسوسی بهتر می شه.


برای اینکار اول اومدم جدولم رو پارتیشن کردم بر اساس تاریخ به صورت ماهیانه بطوریکه هر ماه توی یک FileGroup قرار بگیرد
این مورد دوباره بر میگرده به اینکه شما Rerouceهایی که روی سرورت نصب کردی چقدر باشه.
مثلاً روی سرور چندتا CPU داری و هر CPU چندتا Core داره؟
چون اگه مثلاً یه سرور با یک CPU داشته باشی که 4 هسته داشته باشه، ماکسیمم درجه Parallelism شما 4 خواهد بود. یعنی در بهترین حالت کوئری شما از تمام 4 هسته CPU استفاده می کنه و زمان پاسخ دهی خیلی كمتر از حالتى خواهد بود كه يه سى پى يوى دو هسته اى دارى. پس اگه مثلاً دو تا سى پى يوى هشت هسته اى داشته باشى خوب مسلماً سرعت بالاترى دارى.
مورد دیگه ای اینجا مطرح هست دوباره بحث SQL Server Edition شماست. چون ویرایشهای مختلف SQL Server محدودیتهای متفاوتی روی تعداد هسته های CPU دارن.

از طرفی، تعریف Link Server خودش می تونه باعث کندی سیستم بشه.
این خیلی مهمه که کوئری شما بتونه روی سروری که بهش Link شدی از Statistics استفاده کنه و بتونه پروسس رو در سرور مقصد انجام بده و فقط نتیجه رو برای شما لود کنه.
برای اینکه بتونه باید Login ی که باهاش به Linked Server وصل شدی روی سرور مقصد دارای یکی از Roleهای زیر باشه تا بتونه از Statistics استفاده کنه:

Sysadmin
db_owner
db_ddladmin

و اگر اینطور نیست یعنی شما از امکانات موجود روی سرور مقصد بهره مند نمی شی و معلومه بعدش چه اتفاقی میفته.

البته شما می تونی کوئری رو با OPENQUERY فورس کنی که روی سرور مقصد اجرا بشه.

یا مثلاً نوع Functionهایی که استفاده می کنی در یک کوئری در Performance کوئریهایی که با Linked Server کار می کنن تاثیر داره. مثلاً Getdate روی سرور مقصد پروسس نمی شه.
پس برای رفتن سراغ Linked Server باید خیلی خوب روش شناخت داشته باشی.

کلاً به این کاری که شما می خوای انجام بدی می گن DPV یا Distributed Partitioned Views.
پیشنهاد می کنم در موردش تحقیق کنی و دقیقاً بدونی که قدم به قدم باید چکار کنی.

در مجموع واسه پیاده سازی DPV لازمه که سه کار انجام بدی:

ایجاد Linked Serverها
ایجاد Partitioned Tableها
ایجاد Partitioned Viewها


جرف آخر: من فکر می کنم که برای فقط 30 میلیون رکورد واقعاً نیازی به این چیزا نیست، چون SQL Server خصوصاً SQL SERVER Enterprise Edition مثل آب خوردن داده های بالای 100-200 میلیون رکورد در جداول رو پشتیبانی می کنه.

پس به نظر من:

شناخت امکانات موجود
داشتن سطح انتظارات معقول با توجه به امکانات موجود
دانش کافی برای استفاده از تکنولوژی هایی که کاندیدا کردی
استفاده درست و بهینه از امکانات موجود
طراحی خوب سیستم

موارد اساسی هستن که باید بهشون توجه بشه.


امیدوارم کمکی کرده باشم

golnaz_a
پنج شنبه 07 خرداد 1394, 18:53 عصر
البته من هم فکر میکنم 30 میلیون هنوز داده زیادی بشمار نمیره ولی چون تعداد ستونهای ایشون بهینه نیست فکر کنم دلیل اصلی مشکل ایشون باشه

سلام
قبول دارم که تعداد زیاد فیلدها روی Performance تاثیر داره فقط سوالی که دارم این هست که با تعداد فیلد های کمتر (نصف این مقدار) Performance قابل قبولی می تونم داشته باشم طوری که نخواهم از sql برم سراغ اوراکل؟
راستش خیلی بیشتر از این ها به sql ایمان دارم و احساس می کنم یه جای کار خودم داره میلنگه

golbafan
پنج شنبه 07 خرداد 1394, 18:57 عصر
سلام
قطعا سرعت بهتر میشه

اما حالا کار به ایمان شما ندارم ولی count(*) رو هم جایگزین کنید با count(id) بهتر میشه

برای دو سه میلیارد رکورد من هم میتونم اعتماد کنم ولی نه برای 200 میلیارد

golnaz_a
پنج شنبه 07 خرداد 1394, 19:33 عصر
اما حالا کار به ایمان شما ندارم ولی count(*) رو هم جایگزین کنید با count(id) بهتر میشه


(count (1 یا ( count(*) , count(id تاثیری در افزایش performance ندارند در تمام این ها sql کل جدول رو اسکن میکنه

tooraj_azizi_1035
پنج شنبه 07 خرداد 1394, 21:16 عصر
سلام
با توجه به کوئری شما سیاست پیشنهادی من اسکن تنها یک پارتیشن است و آن اولین پارتیشنی است که مقداری بزرگتر از @Date دارد.
کاری که می تونید بکنید این هست که پس از پارتیشن بندی جدول بر اساس StartDate بیایید مقدار @Date رو چک کنید و ببینید این مقدار در کدوم پارتیشن قرار داره. فرضاً اگر در پارتیشن 5 قرار دارد (یعنی این اولین پارتیشنی است که مقادیری بزرگتر از @Dtate دارد و بنابراین مطمئن هستیم که پارتیشن های بعدی هم این شرط را ارضاء می کنند و نیازی نیست روی آنها هم کوئری اجرا شود) شما کافیست تعداد رکورد های 5 پارتیشن بعدی رو بدست بیاری و جمع کنی چون 5 پارتیشن بعدی (6 تا 10) هم شامل جواب هستند و نیازی به اجرای کوئری روی اونها نیست.
بدست آوردن رکوردهای هر پارتیشن:
http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx


--paritioned table and index details
SELECT
OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
--non-partitioned table/indexes
SELECT
OBJECT_NAME(p.object_id) AS ObjectName,
i.name AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds
ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE
OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
ObjectName,
IndexID,
PartitionNumber;


لینکی برای پیدا کردن پارتیشنی که رکورد مربوطه در آن قرار دارد:
http://sqlity.net/en/2483/partition-boundaries/

بعد از اینکه مقدار پارتیشن رو بدست آوردی باید ببینی مقداری بزرگتر از @Date در اون هست یا نه اگه بود کوئری باید روی این پارتیشن بخوره وگرنه این کوئری در پارتیشن بعدی باید اجرا بشه. بعد از اون می تونی براحتی پارتیشن های بعدی تا آخرین پارتیشن رو با کوئری بالا تعداد رکورد هاش رو بدست بیاری و با مقدار بدست اومده جمع کنی.

امیدوارم منظورم رو متوجه شده باشی.

مهدی نان شکری
جمعه 08 خرداد 1394, 11:18 صبح
با سلام
MS SQL می تواند هر workload و Scale را مدیریت نماید. البته به شرطی که از دیتابیس OLTP انتظار DW را نداشته باشیم. شما با خیال راحت سیستم خود را پیاده سازی نمایید.