آژانس هواپیمایی
pop up

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

شروع موضوع توسط alik ‏8 ژانویه 2008 در انجمن PHP

  1. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    سلام به دوستان :)

    این سئوال مربوط به 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 هزار رکورد است لطفا پیشنهاد یا تجربه خاصی اگر دارید در اختیارم بزارید متشکرم :)
     
  2. Shahed

    Shahed Registered User

    تاریخ عضویت:
    ‏30 ژوئن 2003
    نوشته ها:
    7,175
    تشکر شده:
    21
    محل سکونت:
    mt.cgi
    repair .. optimize
    تمام فیلدهایی هم که ممکن هست با where انتخاب بشن رو index یا unique بزار !! حجم دیتابیست بالاتر میره .. ولی تاثیر خیلی زیادی داره !
     
  3. Shahed

    Shahed Registered User

    تاریخ عضویت:
    ‏30 ژوئن 2003
    نوشته ها:
    7,175
    تشکر شده:
    21
    محل سکونت:
    mt.cgi
    در این مورد یه ایده دارم ! یه table کش بساز تمام رکوردهایی که نیاز داری رو بشمر بریز اینتو ... هر وقت یه جایی یه چیزی اینزرت شد این رو یکی ببر بالاتر.
    اینطوری یه سلکت ساده جای count توی120 هزار رکورد رو انجام میده !
     
  4. balabala

    balabala کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏22 می 2005
    نوشته ها:
    7,348
    تشکر شده:
    1,321
    محل سکونت:
    یه خورده اونورتر
    database engine چیه؟ Inno یا ISAM؟
     
  5. Shahed

    Shahed Registered User

    تاریخ عضویت:
    ‏30 ژوئن 2003
    نوشته ها:
    7,175
    تشکر شده:
    21
    محل سکونت:
    mt.cgi
    اینم تائید میشه !! MyISAM خیلی سریعتر هست !
     
  6. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    ممنون شاهد جان
    کلا دوتا جدول خیلی کارائی دارند که همانطور بالا گفتم عامل join فیلد cat است که index هستش مثل این کوئری:
    PHP:
    select news.*,cat.name as 'cat' ,cat.id as 'cid' from `news`,`catwhere news.cat in ('1','2','10') and news.cat=cat.id order by id asc limit $start,$perpage

    متاسفانه این count برای صفحه بندی استفاده میشه و ثابت نیست در عین حالا بقیه پروژه جوری هستش که مطالب ورودی جدول با یک الگوریتم وارد cat های مختلف می شوند ... و هر cat میتوانه زیر مجموعه های خودش را شامل بشه ...
    PHP:
    select count(id) as 'num' from `newswhere news.cat in ('1','2','12','13'order by id DESC
    ممنون از پاسختان
    Myisam هستش
     
  7. boxilink
  8. miladmovie

    miladmovie مدیر بازنشسته کاربر فعال

    تاریخ عضویت:
    ‏25 دسامبر 2002
    نوشته ها:
    1,936
    تشکر شده:
    2
    برو دنبال این : partition mysql database
    نمی دونم چه طوری کمکت می کنه ! ولی وقتی توی کانال mysql پرسیدم با دیتابیس های بزرگ چی کار کنیم این جواب رو دادند
    راستی بررسی اش کردی یادت نره بیایی اینجا بگی چی فهمیدی :p
     
  9. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    ممنون واقعا جالب بود :) فعلا فقط در موردش خواندم :) انجام ندادم روی دیتابیس خودم که نتیجه عملی را بگم :) ولی همین تئوری و مثال های تئوری هم خیلی امیدوار کننده بودن


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

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

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


    رایج ترین نوعش همان دسته بندی رکورد ها هستش که برای دسته بندی چند تا گزینه دارید:
    HTML:
    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 مصرف میشه !! ولی با پارتیشن بندی هر پارتیشن را میتوانید جداگانه دسترسی داشته باشید و ....
     
  10. miladmovie

    miladmovie مدیر بازنشسته کاربر فعال

    تاریخ عضویت:
    ‏25 دسامبر 2002
    نوشته ها:
    1,936
    تشکر شده:
    2
    اوو چه جالب بود ! من اون بار رفتم خوندمش دیدم نوشته عمودی افقی ولش کردم !

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

    Shahed Registered User

    تاریخ عضویت:
    ‏30 ژوئن 2003
    نوشته ها:
    7,175
    تشکر شده:
    21
    محل سکونت:
    mt.cgi
    آقا یه جوری بگید ما هم سر در بیاریم :D
     
  12. Mehdi

    Mehdi مدیر بازنشسته

    تاریخ عضویت:
    ‏1 آگوست 2004
    نوشته ها:
    5,569
    تشکر شده:
    48
    محل سکونت:
    Anywhere
    خوب الان که پارتیشن بندی شده , ازش استفاده کن ,
    ولی قدیما که این نبود , میومدیم از چند تا تیبل استفاده میکردیم , مثلا برا هر سال یه تیبل میساختیم [​IMG]
    سرعت خیلی بالا میرفت , حجم هر تیبل هم کمتر میشد ![​IMG]
     
  13. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    نه اصلا این پارتیشن محدود به نوع فیلد نیست :) من برای مثال گفتم date
    و این شرط که الان دیدم به اینجا اضافه میکنم:
    کلا با اون 5 نوع روش میتوانید پارتیشن ها را ایجاد کنید یکی از روش ها محدوده روی یک فیلد است .

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

    کلا هرجوری دوست دارید میتوانید تیکه تیکه اش کنید.
     
  14. avajang.com .leftavajang.com.right
  15. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    پارتیشن همین کارها را میکنه با همین دوتا خصوصیت ولی هر دوتاش پیشرفت کردن یعنی هم سرعت بیشتر هست و هم اینکه دینامیکه :) دیگه لازم نیست چندتا جدول بسازی خود mysql همان جدول را داخل خودش تیکه تیکه میکنه :) دسترسی به اطلاعات را به همین صورت مدیریت میکنه و اعمال دیگر را بصورت پارتیشن به پارتیشن میتوانی اعمال کنی ...
    دیگه لازم نیست برنامه را تغییر بدی به چندتا جدول دسترسی پیدا کنه :) همان جدول با همان دستورات قبلی داخلش این اتفاقات میوفته...
     
  16. balabala

    balabala کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏22 می 2005
    نوشته ها:
    7,348
    تشکر شده:
    1,321
    محل سکونت:
    یه خورده اونورتر
    جالبه. MSSQL هم همچین چیزی داره؟ البته فکر نکنم MSSQL مشکلی داشته باشه [​IMG]
     
  17. alik

    alik کاربر قدیمی پرشین تولز

    تاریخ عضویت:
    ‏23 سپتامبر 2005
    نوشته ها:
    2,147
    تشکر شده:
    1,226
    اطلاعی ندارم از Mssql فقط داشتم در مورد پارتیشن mysql میخواندم یکجا دیدم یک بنده خدایی برای یک کاری این قابلیت mysql را با مشابه همان عملکرد روی mssql مقایسه کرده بود و نتایج یکسانی گرفته بود .

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

    hossein_asp کاربر تازه وارد

    تاریخ عضویت:
    ‏31 مارس 2005
    نوشته ها:
    637
    تشکر شده:
    0
    محل سکونت:
    جایی که خدا نباشد
    من یه نگاه انداختم به لینکی که داده بودن به نظر جالب میومد.شما که داری کاربردیش میکنی.یه آموزش هم اگه بزاری ازش عالی میشه.
     
  19. Mehdi

    Mehdi مدیر بازنشسته

    تاریخ عضویت:
    ‏1 آگوست 2004
    نوشته ها:
    5,569
    تشکر شده:
    48
    محل سکونت:
    Anywhere
    گفتم که , روش قدیمی اون بود !!
    تو همون لینکی که میلاد داد , اولین گزینه آموزش هم داره ,

    البته اگه کسی آموزش بزاره که فبحال [​IMG]
     
  20. amin_lili

    amin_lili کاربر تازه وارد

    تاریخ عضویت:
    ‏24 آپریل 2007
    نوشته ها:
    528
    تشکر شده:
    2
    محل سکونت:
    ای کاش آنجا ...
    توی MSSQL هم این امکان وجود داره . در این آدرس توضیحات کاملی داده .