FreeBSD + PostgreSQL: एक डेटाबेस सर्वर को ट्यूनिंग

हाय, हब्र समुदाय!

संभवतः, मेरा लेख अनुभवी सिस्टम प्रशासकों के लिए दिलचस्प नहीं होगा और कॉपी-पेस्ट प्रतीत होगा। लेकिन मैं इसे उन लोगों को संबोधित कर रहा हूं, जो मुझे पसंद करते हैं, केवल एक डेवलपर होने के नाते, पहले एक अत्यधिक लोड किए गए डेटाबेस के कार्यों को हल करते हुए एक सर्वर को प्रशासित करने की आवश्यकता का सामना करना पड़ा। और ताकि Google आपको शाप न दे, मैं डेटाबेस सर्वर को ओवरक्लॉक करने की मूल तकनीकों को एक स्थान पर एकत्रित करने का प्रयास करूँगा जिन्हें मैंने सफलतापूर्वक लागू किया है।

मेरे कार्य का इनपुट इस प्रकार है: एक ड्यूल-प्रोसेसर (Intel Xeon) मशीन, 500GB की 8 हार्ड ड्राइव और 12GB RAM। और पूर्ण, भौतिक सहित, इस अच्छे तक पहुंच। उद्देश्य: FreeBSD और PostgreSQL OS पर आधारित एक तेज़ डेटाबेस सर्वर को व्यवस्थित करना।

1. RAID

हमें ऐसे पोस्टग्रेएसक्यूएल फीचर के लिए छापे के रूप में उपलब्ध पट्टियों के सही विभाजन को टेबलस्पेसिंग के रूप में करना होगा (अधिक नीचे)। मैंने अपनी 8 कठोर जोड़ी को जोड़े में विभाजित किया, इस तरह से व्यवस्थित किया: RAID1 में दो जोड़े और RAID0 में दो जोड़े (सामान्य रूप से, हमारे उद्देश्यों के लिए, हमें कम से कम 6 हार्ड की आवश्यकता है - RAID1 में दो जोड़े को मिलाएं, अन्य 2 को उसी तरह छोड़ दें)। यदि आपके पास बड़ी संख्या में हार्ड हैं, तो आप कुछ अधिक विश्वसनीय, जैसे कि RAID5, RAID10, आदि के साथ आ सकते हैं, लेकिन एक मौका है कि यह कुछ हद तक धीमी गति से काम करेगा। मैं छापे को व्यवस्थित करने के बारे में विवरण में नहीं जाऊंगा, जैसा कि मैं लोहे में मजबूत नहीं हूं, मैं केवल यह कह सकता हूं कि मैंने किसी भी नियंत्रक को नहीं छुआ, क्योंकि BIOS के बाद सर्वर पर, एक उपयोगिता भरी हुई है जो आपको इस प्रोग्राम को करने की अनुमति देती है।

2. ओएस, डेटाबेस सर्वर को स्थापित करना और अपने कर्नेल का उपयोग करना

सबसे पहले, पहले रक 1 पर जंक लगाएं। मैंने सभी फाइलों के साथ फ्रीबीएसडी 8.2 रिलीज एएमडी 64 वितरण स्थापित किया। 64-बिट संस्करण की आवश्यकता है ताकि सिस्टम सभी रैम को "देखता" हो।

अब सबसे दिलचस्प हिस्सा: हमें कर्नेल को क्यों संकलित करना चाहिए और किन मापदंडों को बदलना चाहिए? यह उच्च लोड के लिए आवश्यक के रूप में कई संसाधनों का उपयोग करने के लिए PostgreSQL सर्वर की अनुमति देने के लिए आवश्यक है। तो, डेटाबेस पैरामीटर हमारे लिए क्या रुचि रखते हैं। एलेक्सी वासिलिव की पुस्तक में "पोस्टग्रैसेकल के साथ काम करना"। सेटिंग, स्केलिंग ”, अत्यधिक लोड किए गए डेटाबेस (फ़ाइल postgresql.conf) के लिए निम्न मापदंडों की सिफारिश की जाती है:

• share_buffers = 1/8 RAM या अधिक (लेकिन 1/4 से अधिक नहीं);
• 1/20 रैम में swork_mem;
• 1/4 रैम में smaintain_work_mem;
• डेटाबेस में तालिकाओं की नियोजित संख्या में smax_fsm_relations * 1.5;
• max_fsm_pages में max_fsm_relations * 2000;
• fsync = true;
• wal_sync_method = fdatasync;
• प्रतिबद्ध_देल = 10 से 100;
• प्रतिबद्ध_संबंध = 5 से 10;
• प्रभावी_ कैश = कैश्ड मूल्य का 0.9 =, जो मुफ़्त दिखाता है;
• random_page_cost = तेजी से सीपीयू के लिए 2, धीमी गति से 4 के लिए;
• cpu_tuple_cost = तेजी से सीपीयू के लिए 0.001, धीमी गति से 0.01;
• cpu_index_tuple_cost = तेज सीपीयू के लिए 0.0005, धीमे के लिए 0.005;
• autovacuum = on;
• autovacuum_vacuum_threshold = 1800;
• autovacuum_analyze_threshold = 900;

ये विकल्प वास्तव में हमारे लिए उपयुक्त हैं, दो को छोड़कर:

1) कनेक्शन की अधिकतम संख्या

यह विशिष्ट स्थिति पर निर्भर करता है। मेरे पास मुकुट में एक स्क्रिप्ट चल रही है (डेटाबेस से कनेक्ट करें और डेटा दर्ज करें), मुझे लगा कि 256 पर्याप्त होना चाहिए:

• max_connection = 256;

लेकिन डिफ़ॉल्ट FreeBSD कॉन्फ़िगरेशन कनेक्शन की संख्या के लिए यह मान प्रदान नहीं करता है। यदि आप इस मान को सेट करते हैं और पोस्टग्राउंड डेमॉन को शुरू करने का प्रयास करते हैं, तो कुछ भी काम नहीं करेगा। संबंधित सिस्टम मापदंडों को बढ़ाना आवश्यक है। इसके लिए हम अपने मूल को इकट्ठा करेंगे। डिफ़ॉल्ट सामान्य कर्नेल कॉन्फ़िगरेशन लें, KERNEL_MAX_PERF नाम की एक प्रतिलिपि बनाएं, KERNEL_MAX_PERF को निम्नानुसार संपादित करें: डिफ़ॉल्ट विकल्पों में पंक्तियों को जोड़कर सेमाफ़ोर की संख्या बदलें:

options SEMMNI=512
options SEMMNS=1024
options SEMUME=64
options SEMMNU=512


(ये max_connection = 256 के लिए मान हैं)।

2) PostgreSQL रैम की अधिकतम मात्रा ले सकता है (यह थोक प्रश्नों के लिए महत्वपूर्ण है)। Postgresql.conf में शेयर्ड_बफर्स ​​पैरामीटर इसके लिए जिम्मेदार है। इस मात्रा के मूल्य के संबंध में अलग-अलग सिफारिशें हैं। मैं इस नतीजे पर पहुंचा कि यदि यह डेटाबेस के लिए समर्पित सर्वर है, तो आप लगभग पूरी मात्रा में रैम को एक प्रोसेस माइनस में दे सकते हैं कि सिस्टम को अपनी जरूरतों के लिए क्या चाहिए। मैंने allow जीबी १२ आवंटित किया। सिस्टम के लिए हमें शेयर्ड_बफर्स ​​के लिए आवश्यक मूल्य निर्धारित करने की अनुमति देने के लिए, कर्नेल में आपको SHMMAXPGS विकल्प को बदलने की आवश्यकता है, जिसका मूल्य सूत्र द्वारा परिकलित किया गया है:

SHMMAXPGS = share_buffers / PAGE_SIZE

मेरे मामले में share_buffers = 8GB, PAGE_SIZE = 4Kb सभी i386 के लिए, जिसका अर्थ है
SHMMAXPGS = 8 * 1024 * 1024/4 = 2097152); अब हम SHMMAX पैरामीटर लिख सकते हैं (गिरी में गतिशील रूप से गणना की गई)। तो, हम कर्नेल विन्यास में लिखते हैं:

options SHMMAXPGS = 2097152
options SHMMAX = "(SHMMAXPGS*PAGE_SIZE + 1)"

यह कर्नेल को KERNEL_MAX_PERF के साथ संकलित करने के लिए बना हुआ है। कर्नेल संकलन प्रक्रिया अपने आप में सरल है, यहां मैं आपको आधिकारिक मैना का संदर्भ देता हूं।

हम अपने कर्नेल के साथ ओएस को लोड करते हैं, PostgreSQL के नवीनतम संस्करण को स्थापित करते हैं (मेरे पास संस्करण 9.0.4 था), पहले हम सत्यापन के लिए डिफ़ॉल्ट कॉन्फ़िगरेशन के साथ PostgreSQL शुरू करते हैं। यदि सब कुछ ठीक है, तो हम ऊपर निर्दिष्ट उन लोगों के लिए postgresql.conf में पैरामीटर बदलते हैं, हम PostgreSQL को पुनरारंभ करते हैं। यह शुरू हुआ - हम आगे बढ़ते हैं।

नोट: यदि किसी कारण से कर्नेल को निर्धारित मापदंडों के साथ संकलित करना संभव नहीं था, तो आप उन्हें sysctl.conf में लिख सकते हैं:

kern.ipc.shmall=2097152
kern.ipc.shmmax=8589938688
kern.ipc.semmap=256

और डिफ़ॉल्ट सामान्य कर्नेल के साथ जंक चलाएं।

3. टेबलस्पर्श

Tablespacing फ़ाइल सिस्टम में स्थानों को निर्धारित करने के लिए PostgreSQL की क्षमता है जहां डेटाबेस ऑब्जेक्ट्स का प्रतिनिधित्व करने वाली फ़ाइलों को संग्रहीत किया जाएगा। सीधे शब्दों में कहें, अगर हम टेबल, इंडेक्स और लॉग को अलग-अलग डिस्क पर बिखेरते हैं, तो रिकॉर्ड / डेटा को पढ़ने की तुलना में तेज होगा यदि यह सब एक ही डिस्क पर था।

यहां हमें अपने छापे की आवश्यकता होगी। मुझे आपको याद दिलाना है कि हमारे पास चार विभाजन हैं: दो RAID1 और दो RAID0। पहले RAID1 पर हमने ओएस स्थापित किया है और पोस्टग्रेज। दूसरे RAID1 पर हम अपने डेटाबेस की तालिकाओं को संग्रहीत करेंगे। मान लीजिए कि इसे / disk1 के रूप में रखा गया है। पहले RAID0 पर हम अनुक्रमित स्टोर करेंगे। इसे फ़ाइल सिस्टम में / disk2 के रूप में आरोहित होने दें। हम लॉग्स के लिए दूसरे RAID0 को छोड़ देंगे, हम मानते हैं कि इसे / disk3 के रूप में माउंट किया गया है।

निम्नलिखित कदम उठाए जाने चाहिए:

1. टेबल, इंडेक्स और एक लॉग के लिए फ़ोल्डर बनाएँ:
   

   #mkdir -p /disk1/postgresql/tables
#mkdir -p /disk2/postgresql/ind
#mkdir -p /disk3/postgresql/log

2. इन फ़ोल्डरों के लिए पोस्टग्रेटर ओवरर करें, और बाकी सभी अधिकार छीन लें (मैं आपको याद दिलाता हूं कि पोस्टग्रेज वह उपयोगकर्ता है जो पोस्टग्रेएसक्यूएल को स्थापित करते समय शुरू हो जाता है, अगर स्थापना आधिकारिक आदमी के अनुसार मानक तरीके से की जाती है):
   

   #chown -R postgres /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log
#chmod -R go-rwx /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log

   
   
3. पोस्टग्रेज के तहत psql क्लाइंट में जाएं और दो टेबलस्पेस बनाएं:
   

   सृजन के रिक्त स्थान space_table स्थिति '/ disk1 / postgresql / टेबल' ;
   सृजन के रिक्त स्थान space_index स्थिति '/ disk2 / postgresql / ind' ;

   
   
4. यदि आपके डेटाबेस का ऑब्जर पोस्टग्रेज नहीं है, लेकिन, उदाहरण के लिए, माईसर, तो आपको उपयोगकर्ता माईसर को बनाए गए टेबलस्पेस के अधिकार देने की जरूरत है (आप क्लाइंट में भी ऐसा कर सकते हैं):
   

   मास्टर्स को TABLESPACE space_table पर अनुदान बनाएँ ;
   मास्टर्स को TABLESPACE space_index पर अनुदान बनाएँ ;

   
   
5. अब माईसर के तहत आप टेबल और इंडेक्स के लिए टेबलस्पेस को बदल सकते हैं:
   

   माई टेबल सेट करें स्पेस स्पेस में टाइप करें;
   ALTER INDEX mytable SET TABLESPACE space_index;

   
   
6. पोस्टग्रैस डेमॉन को बंद करें, लॉग फ़ोल्डर को स्थानांतरित करें और इसे एक प्रतीकात्मक लिंक बनाएं:
   

   #/usr/local/bin/rc.d/postgres.sh stop
#mv /usr/local/pgsql/data/pg_xlog /disk3/postgresql/log
#cd /usr/local/pgsql/data
#ln -s /disk3/postgresql/log/pg_xlog

   
   पोस्टग्रेज रन करें:
   

   #/usr/local/bin/rc.d/postgres.sh start

   
   यदि सब कुछ सही ढंग से किया जाता है, तो डेमॉन शुरू होना चाहिए।
   

4. विभाजन

विभाजन एक बड़ी तालिका को छोटे भौतिक टुकड़ों में विभाजित करने का तार्किक विभाजन है। यदि तालिका वास्तव में बड़ी है, तो यह क्वेरी निष्पादन समय को काफी तेज़ कर सकती है।

मेरे पास काफी विशिष्ट स्थिति है: एक स्क्रिप्ट मुकुट में काम करती है, एक निश्चित आयाम पर आंकड़े एकत्र करती है। वेब इंटरफेस द्वारा, उपयोगकर्ता इन आंकड़ों को देख सकता है। प्रति सप्ताह लगभग 10 मिलियन पंक्तियों को तालिका में डाला जाता है। यदि आप एक तालिका में सब कुछ लिखते हैं, तो आप शापित होंगे। यह सब बहुत धीरे-धीरे काम करेगा।

आइए इस तालिका को टुकड़ों में तोड़ने की कोशिश करें, ब्रेकिंग के लिए मानदंड के रूप में समय ले रहे हैं। इस मामले में, जब उपयोगकर्ता आंकड़े देखना चाहता है, और आप इसे केवल एक निश्चित समय अवधि के लिए देख सकते हैं, डेटाबेस सर्वर को अनुरोध करने पर पूरी बड़ी तालिका को ऊन नहीं करना होगा, लेकिन कई छोटे लोग जो चयनित समय अवधि में आते हैं।

दुर्भाग्य से, PostgreSQL में, विभाजन को डेटाबेस स्तर पर लागू नहीं किया जाता है, इसलिए आपको इसे तालिका विरासत संपत्ति का उपयोग करके मैन्युअल रूप से करना होगा।

इसलिए, हमारे पास एक मेज माप_डाटा_मास्टर है जहां हम अपने माप लिखते हैं। मान लीजिए, समय अंतराल के रूप में, एक सप्ताह का सूट। आइए जाने:

1. माप_दत्ता_मास्टर मास्टर तालिका के लिए, किसी भी चेक अखंडता की कमी न करें और इंडेक्स न बनाएं
2. postgresql.conf config में, विकल्प को संपादित करें:
   

   constraint_exclusion = on

   
   
3. फार्म के वंशज तालिकाएँ बनाएँ:
   

   बनाएँ तालिका माप_दत्ता_y2011m06d06 ( CHECK ( माप_ समय = दिनांक '2011-06-06' और माप_ समय '2011-06-13' )
   ) प्रवेश ( माप_दत्ता_मास्टर ) ;

   
   
4. चाइल्ड टेबल के लिए इंडेक्स बनाएं:
   

   CREATE INDEX मेज़र_डेट_y2011m06d06_key नाप_data_y2011m06d06 ( माप_टाइम ) पर ;

   
   
5. यह आवश्यक है कि एक नई पंक्ति सम्मिलित करते समय, इसे वांछित वंशज तालिका में लिखा जाए। आइए इसके लिए एक ट्रिगर फंक्शन बनाएं:
   

   क्रिएट या रिप्लेसमेंट फंक्शन नाप_इन्टर_ट्रिगर ( )
   $ $ के रूप में रिटर्न्स ट्राइगर
   शुरू
   IF ( NEW । मेजर_टाइम > = DATE '2011-06-06' और
   नई। उपाय_ समय < डेट '2011-06-13' )
   INSERT INTO उपाय_दत्ता_y2011m06d06 VALUES ( NEW । * ) ;
   ELSIF ( NEW । मापन_टाइम > = दिनांक '2011-06-13' और
   नई। उपाय_ समय < दिनांक '2011-06-20' ) तब
   INSERT INTO उपाय_दत्ता_y2011m06d13 VALUES ( NEW । * ) ;
   .....................................
   ELSIF ( NEW । मापन_टाइम > = दिनांक '2011-12-19' और
   नई। उपाय_समय < DATE '2011-12-26' ) तब
   INSERT INTO उपाय_दत्ता_y2011m12d19 VALUES NEW । * ) ;
   वरना
   वृद्धि की सीमा 'सीमा से बाहर की तारीख। नापना_insert_trigger () फ़ंक्शन के लिए!' ;
   END IF ;
   RETURN NULL ;
   अंत;
   $$
   भाषा plpgsql;

   
   
6. खैर, ट्रिगर ही है, जो फ़ंक्शन को कॉल करेगा:
   

   सृजित ट्राइगर डालें_मास_टाइगर
   पहले से नापें_माटा_मास्टर पर डालें
   प्रत्येक के लिए आवश्यक प्रक्रिया की माप करें_insert_trigger ( ) ;

   
   

बेशक, ऐसे बड़े प्रश्नों को लिखना असुविधाजनक है। मैंने एक php स्क्रिप्ट लिखी है जो तालिकाओं और उन सभी को बनाती है जो उन्हें आगे पूरे एक वर्ष के लिए चाहिए।

यह, शायद, वह सब है जो मैं बताना चाहता था। यदि आप इस क्षेत्र से अपना अनुभव साझा करते हैं, तो मैं बहुत आभारी रहूंगा।