مشکل با جدول های بزرگ در دیتابیس

[alik]

سلام به دوستان

این سئوال مربوط به mysql هست و میدانم داخل انجمن خاص خودش باید مطرح بشود ولی چون اکثر php کار ها با mysql کار میکنند فکر کردم اینجا حتما جواب بهتری پیدا میکنم.

mysql دیتابیس قوی هست و شرکت های بزرگی مثل google و flickr و ... از این دیتابیس استفاده میکنند مشکلی هم ندارند مشکل من از وقتی شروع میشه که تعداد رکورد های جدولم از مثلا 100000 صد هزار رکورد بیشتر می شود و دقیقا از این لحظه به بعد سرور کند میشود.

و گاه به گاه timeout می دهد که این timeout ها روی query های mysql هستند... البته سرور مشکلی نداره کلا نه cpu نه ram مشکلی ندارند ...


ساختار جدول:
id, cat , title , text
id= auto increment
cat= index
title , text = fulltext

کوئری هایی که معمولا مشکل ساز هستند کوئری های جستجو در fulltext و count برای صفحه بندی سایت است.
در تمام کوئری ها یک join بین جدول فوق با یک جدول که categori ها هست انجام میشه که اینجا من فیلد cat که عامل join هست را index گذاشتم.

تنظیمات mysql را از نظر بافر و سورت ها تغییر دادم و اندازه اینا ها را تا 512k بالا بردم سرعت بهتر شد ولی هنوز زمانیکه دارم جدول را update , insert میکنم این مشکل پا برجاهست !

تا اونجایی که بررسی کردم زمان insert , update کانکشن مورد نظر lock میکنه جدول را و مثلا کانکشن کوئری select باید صبر کنه تا اون یکی کانکشن lock را بردارد ...


حجم جدول حدود 800 مگابایت و تعداد رکورد حدود 120 هزار رکورد است لطفا پیشنهاد یا تجربه خاصی اگر دارید در اختیارم بزارید متشکرم

 

[Shahed]

repair .. optimize
تمام فیلدهایی هم که ممکن هست با where انتخاب بشن رو index یا unique بزار !! حجم دیتابیست بالاتر میره .. ولی تاثیر خیلی زیادی داره !

 

[Shahed]

کوئری هایی که معمولا مشکل ساز هستند کوئری های جستجو در fulltext و count برای صفحه بندی سایت است.

در این مورد یه ایده دارم ! یه table کش بساز تمام رکوردهایی که نیاز داری رو بشمر بریز اینتو ... هر وقت یه جایی یه چیزی اینزرت شد این رو یکی ببر بالاتر.
اینطوری یه سلکت ساده جای count توی120 هزار رکورد رو انجام میده !

 

[balabala]

database engine چیه؟ Inno یا ISAM؟

 

[Shahed]

database engine چیه؟ Inno یا ISAM؟

اینم تائید میشه !! MyISAM خیلی سریعتر هست !

 

[alik]

تمام فیلدهایی هم که ممکن هست با where انتخاب بشن رو index یا unique بزار !! حجم دیتابیست بالاتر میره .. ولی تاثیر خیلی زیادی داره !

ممنون شاهد جان
کلا دوتا جدول خیلی کارائی دارند که همانطور بالا گفتم عامل join فیلد cat است که index هستش مثل این کوئری:

select news.*,cat.name as 'cat' ,cat.id as 'cid' from `news`,`cat` where news.cat in ('1','2','10') and news.cat=cat.id order by id asc limit $start,$perpage

در این مورد یه ایده دارم ! یه table کش بساز تمام رکوردهایی که نیاز داری رو بشمر بریز اینتو ... هر وقت یه جایی یه چیزی اینزرت شد این رو یکی ببر بالاتر.
اینطوری یه سلکت ساده جای count توی120 هزار رکورد رو انجام میده !

متاسفانه این count برای صفحه بندی استفاده میشه و ثابت نیست در عین حالا بقیه پروژه جوری هستش که مطالب ورودی جدول با یک الگوریتم وارد cat های مختلف می شوند ... و هر cat میتوانه زیر مجموعه های خودش را شامل بشه ...

select count(id) as 'num' from `news` where news.cat in ('1','2','12','13') order by id DESC

database engine چیه؟ Inno یا ISAM؟

ممنون از پاسختان
Myisam هستش

 

[miladmovie]

برو دنبال این : partition mysql database
نمی دونم چه طوری کمکت می کنه ! ولی وقتی توی کانال mysql پرسیدم با دیتابیس های بزرگ چی کار کنیم این جواب رو دادند
راستی بررسی اش کردی یادت نره بیایی اینجا بگی چی فهمیدی

 

[alik]

برو دنبال این : partition mysql database
نمی دونم چه طوری کمکت می کنه ! ولی وقتی توی کانال mysql پرسیدم با دیتابیس های بزرگ چی کار کنیم این جواب رو دادند
راستی بررسی اش کردی یادت نره بیایی اینجا بگی چی فهمیدی

ممنون واقعا جالب بود فعلا فقط در موردش خواندم انجام ندادم روی دیتابیس خودم که نتیجه عملی را بگم ولی همین تئوری و مثال های تئوری هم خیلی امیدوار کننده بودن


و اما این مطلب چی هست ؟
از نسخه 5.1 به بعد یک امکان به اسم پارتیشن اضافه شده که بصورت خلاصه شما میتوانید اطلاعات یک جدول را چند تیکه کنید و این چند تیکه کردن در دو قالب انجام میشه
1- قالب عمودی
2- قالب افقی

در قالب عمودی شما سطر های یک جدول را تیکه تیکه می کنید یعنی همان رکورد ها را دسته بندی می کنید مثلا اگر یکسری اطلاعات دارید که تاریخ گذاری شدند شما میتوانید مثلا اطلاعات هر 5 سال را در یک تیکه قرار بدید.

درقالب افقی هم شما ستون های جدول را هر کدوم یا هر چندتاش را میزارید داخل یک تیکه


رایج ترین نوعش همان دسته بندی رکورد ها هستش که برای دسته بندی چند تا گزینه دارید:

Range - this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980's, 1990's, and everything beyond and including the year 2000. 
Hash - this partitioning mode allows a DBA to separate data based on a computed hash key that is defined on one or more table columns, with the end goal being an equal distribution of values among partitions. For example, a DBA may create a partitioned table that has ten partitions that are based on the table's primary key. 
Key - a special form of Hash where MySQL guarantees even distribution of data through a system-generated hash key. 
List - this partitioning mode allows a DBA to segment data based on a pre-defined list of values that the DBA specifies. For example, a DBA may create a partitioned table that contains three partitions based on the years 2004, 2005, and 2006. 
Composite - this final partitioning mode allows a DBA to perform sub-partitioning where a table is initially partitioned by, for example range partitioning, but then each partition is segmented even further by another method (for example, hash).

این گزینه ها هستند و عینا" نقل قول کردم اولیش همان محدوده هستش که مثلا شما محدوده سال 2005 را یک پارتیشن میکنید و محدوده سال 2005 تا 2007 را یک پارتیشن دیگه

حالا مزیت این کارها چیه ؟ اگر مثلا همان کوئری count که من باهاش مشکل داشتم بخواد اجرا بشه و این جدول پارتیشن بندی نباشه و شرط count من مثلا تعداد رکورد های سال 2005 باشه !! حدود مثلا 38 ثانیه طول میکشه تا جواب بدست بیاد ولی اگر پارتیشن بندی کرده باشم حدود 3 ثانیه طول میکشه !!!!!


اگر پارتیشن بندی را درست انتخاب کنیم و برنامه (کوئری) ها را جوری تنظیم کنیم که با پارتیشن بندی بیشترین سازگاری را داشته باشند سرعت در جدول های بزرگ به شکل قابل توجهی افزایش پیدا می کند.

علاوه بر سرعت ؛ مدیریت اطلاعات هم راحت تر خواهد بود چون تمام اعمالی که روی یک جدول میشود انجام داد و میدانید در یک جدول بزرگ هر عمل مثل repair کلی طول میکشه و کلی هارد و cpu مصرف میشه !! ولی با پارتیشن بندی هر پارتیشن را میتوانید جداگانه دسترسی داشته باشید و ....

 

[miladmovie]

اوو چه جالب بود ! من اون بار رفتم خوندمش دیدم نوشته عمودی افقی ولش کردم !

می گم حتما باید یک فیلد با نوع Date داشته باشیم تا بشه این کار رو روش کرد ؟ یعنی یک جوری می شه با شماره رکورد این کار رو کرد ؟

 

[Shahed]

آقا یه جوری بگید ما هم سر در بیاریم

 

[Mehdi]

خوب الان که پارتیشن بندی شده , ازش استفاده کن ,
ولی قدیما که این نبود , میومدیم از چند تا تیبل استفاده میکردیم , مثلا برا هر سال یه تیبل میساختیم

سرعت خیلی بالا میرفت , حجم هر تیبل هم کمتر میشد !

 

[alik]

نه اصلا این پارتیشن محدود به نوع فیلد نیست من برای مثال گفتم date
و این شرط که الان دیدم به اینجا اضافه میکنم:

Data type of partitioning key. A partitioning key must be either an integer column or an expression that resolves to an integer. The column or expression value may also be NULL

کلا با اون 5 نوع روش میتوانید پارتیشن ها را ایجاد کنید یکی از روش ها محدوده روی یک فیلد است .

Range - this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980's, 1990's, and everything beyond and including the year 2000.
Hash - this partitioning mode allows a DBA to separate data based on a computed hash key that is defined on one or more table columns, with the end goal being an equal distribution of values among partitions. For example, a DBA may create a partitioned table that has ten partitions that are based on the table's primary key.
Key - a special form of Hash where MySQL guarantees even distribution of data through a system-generated hash key.
List - this partitioning mode allows a DBA to segment data based on a pre-defined list of values that the DBA specifies. For example, a DBA may create a partitioned table that contains three partitions based on the years 2004, 2005, and 2006.
Composite - this final partitioning mode allows a DBA to perform sub-partitioning where a table is initially partitioned by, for example range partitioning, but then each partition is segmented even further by another method (for example, hash).

نوع پنجمش composite این اجازه را میده که شما مثلا پارتیشن های قبلی که ایجاد کردید را مجدد پارتیشن بندی کنید یعنی یک پارتیشن خودش دوباره میتونه دسته بندی بشه و زیر پارتیشن براش درست بشه...

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

 

[alik]

خوب الان که پارتیشن بندی شده , ازش استفاده کن ,
ولی قدیما که این نبود , میومدیم از چند تا تیبل استفاده میکردیم , مثلا برا هر سال یه تیبل میساختیم

سرعت خیلی بالا میرفت , حجم هر تیبل هم کمتر میشد !

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

 

[balabala]

جالبه. MSSQL هم همچین چیزی داره؟ البته فکر نکنم MSSQL مشکلی داشته باشه

 

[alik]

جالبه. MSSQL هم همچین چیزی داره؟ البته فکر نکنم MSSQL مشکلی داشته باشه

اطلاعی ندارم از Mssql فقط داشتم در مورد پارتیشن mysql میخواندم یکجا دیدم یک بنده خدایی برای یک کاری این قابلیت mysql را با مشابه همان عملکرد روی mssql مقایسه کرده بود و نتایج یکسانی گرفته بود .

صحبت از cluster index در mssql هم کرده بود که بیشتر از این یادم نمیاد ! لینک هم که ندارم چون وقتی سرچ میکنیم از 100 جا رد میشم یادم نمیمونه کجا خوندم

 

[hossein_asp]

اطلاعی ندارم از Mssql فقط داشتم در مورد پارتیشن mysql میخواندم یکجا دیدم یک بنده خدایی برای یک کاری این قابلیت mysql را با مشابه همان عملکرد روی mssql مقایسه کرده بود و نتایج یکسانی گرفته بود .

صحبت از cluster index در mssql هم کرده بود که بیشتر از این یادم نمیاد ! لینک هم که ندارم چون وقتی سرچ میکنیم از 100 جا رد میشم یادم نمیمونه کجا خوندم

من یه نگاه انداختم به لینکی که داده بودن به نظر جالب میومد.شما که داری کاربردیش میکنی.یه آموزش هم اگه بزاری ازش عالی میشه.

 

[Mehdi]

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

گفتم که , روش قدیمی اون بود !!

من یه نگاه انداختم به لینکی که داده بودن به نظر جالب میومد.شما که داری کاربردیش میکنی.یه آموزش هم اگه بزاری ازش عالی میشه.

تو همون لینکی که میلاد داد , اولین گزینه آموزش هم داره ,

البته اگه کسی آموزش بزاره که فبحال

 

[amin_lili]

توی MSSQL هم این امکان وجود داره . در این آدرس توضیحات کاملی داده .