PHP के लिए ट्यूनिंग SQLite

यह आलेख PHP परियोजनाओं के लिए SQLite के उपयोग का आकलन करता है, बहुस्तरीय काम की सुविधाओं और काम को गति देने के तरीकों पर चर्चा करता है। "मिठाई के लिए" - अन्य DBMS (MySQL, PosgreSQL, MS SQL, MongoDB) के साथ एक छोटी तुलना।

शुरुआत

बहुत पहले नहीं, मैं अपनी एक परियोजना के लिए प्रचार कोड की अपनी सेवा बनाना चाहता था। मंच के चुनाव में, मैंने लंबे समय तक संदेह नहीं किया। चूंकि मेरे पास एक तैनात PHP वातावरण के साथ एक सर्वर है, सेवा स्वयं को PHP में सुरक्षित रूप से लिखा जा सकता है (विशेषकर जब से मैं इससे परिचित हूं)। DBMS के रूप में, मैं SQLite (हालांकि MySQL और PostgreSQL सर्वर पर तैनात किया गया है) की कोशिश करना चाहता था। कारण यह है कि "मुकाबला" वातावरण में स्थानांतरित करना आसान है (बस फ़ोल्डर की नकल करें), इसे "नंगे" सर्वर पर तैनात करना आसान है (आपको PHP के अलावा कुछ भी ज़रूरत नहीं है)। इसके अलावा, मैं बैकअप को सरल बनाना चाहता था (मैंने डिस्क स्नैपशॉट का उपयोग करने की योजना बनाई + परिणामस्वरूप "स्लाइस" की सरल प्रतिलिपि)।

लेकिन उपयोग करने से पहले, आपको इस समाधान की प्रयोज्यता और प्रदर्शन की जांच करनी चाहिए।

लोहा

2 वस्तुओं पर परीक्षण हुआ:

• कार्य केंद्र। इंटेल i5। 12 जीबी रैम। SSD + HDD (WD ब्लैक)। विंडोज 8. PHP चलाने के लिए OpenServer द्वारा उपयोग किया जाता है।
• सेलेक्ट में बादल। डेबियन 6. एक सेवा होगी। डिफ़ॉल्ट कॉन्फ़िगरेशन में स्थापित LAMP। जब तक कि अपाचे ने मेमोरी खपत को कम करने के लिए, और भी अधिक कटौती की है।

प्रारंभिक मूल्यांकन

प्रारंभिक प्रदर्शन मूल्यांकन के लिए, एक साधारण स्क्रिप्ट लिखी गई थी:

<?php //   function initDB() { $guid = ''; //      if (function_exists('com_create_guid') === true) { $guid = trim(com_create_guid(), '{}'); } else { $guid = sprintf('%04X%04X-%04X-%04X-%04X-%04X%04X%04X', mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(16384, 20479), mt_rand(32768, 49151), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535)); } //     $file = "_$guid.db"; //   ( ) $db = new SQLite3($file); //   $db->exec('CREATE TABLE foo (i INT, i2 INT)'); return $db; } //    function testDB($db, $count) { for ($i=0; $i<$count; $i++) testIteration($db, $i); } // 1   function testIteration($db, $iteration) { $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } // ----------     ---------- //    $COUNT = 1000.0; if(isset($_REQUEST['COUNT'])) $COUNT = $_REQUEST['COUNT']; //   $db = initDB(); //    $start = microtime(true); //   testDB($db, $COUNT); //   $time = microtime(true) - $start; //   echo "$COUNT inserts per " . number_format($time, 2) . "sec. " . number_format($COUNT / $time, 2) . " operation per second"; ?> 

हम क्रमिक रूप से तालिका में 1000 रिकॉर्ड सम्मिलित करते हैं। हम खर्च किए गए समय को मापते हैं। हम प्रति सेकंड संचालन की संख्या की गणना करते हैं।
सिद्धांत रूप में, मोटे तौर पर मूल्यांकन के लिए, अधिक आवश्यक नहीं है।
कृपया ध्यान दें कि मैंने विशेष रूप से लेनदेन का उपयोग नहीं किया है। प्रत्येक ऑपरेशन अपने स्वयं के लेनदेन में जाएगा। ऑपरेशन का यह मोड मल्टीथ्रेडेड मोड में आधार के वास्तविक उपयोग के करीब है।

और अब परिणाम:
एसएसडी के लिए , उन्हें प्रति सेकंड 365 ऑपरेशन का स्कोर मिला। बुरा नहीं है।
एचडीडी के लिए केवल 13 ऑपरेशन मिले। यह दुखद है। लेकिन सर्वर क्या कहेगा?
और सर्वर प्रसन्न हुआ। 210 ऑपरेशन।
सामान्य तौर पर, सर्वर पर प्रदर्शन रेटिंग काफी सभ्य होती है। आप SQLite के साथ PHP का परीक्षण जारी रख सकते हैं। बस तुरंत ध्यान दें कि सर्वर के लिए आपको फास्ट फाइल सबसिस्टम (एसएसडी) का उपयोग करना होगा।
हमारे मामले में, जैसा कि मैंने कहा, बादल का चयन सेलेल में किया जाता है। SSD डिस्क और क्लाउड मशीनों के बीच कैश के रूप में कार्य करता है (किसी भी मामले में, जैसा कि मैं कुख्यात अमरो की कहानियों से समझा था)।

बहुआयामी ऑपरेशन

अब इस सवाल का जवाब देने की कोशिश करते हैं कि क्या आधार मल्टी-थ्रेडेड मोड में काम करने में सक्षम होगा या नहीं।
हमने SQLite के बारे में लेखों की एक अद्भुत श्रृंखला पढ़ी: habrahabr.ru/post/149356
और हम देखते हैं कि बहु-थ्रेडेड मोड के लिए, आपको वाल-लॉग को सक्षम करना होगा। डेटाबेस आरंभीकरण के लिए एक पंक्ति जोड़ें (खोलने के बाद):

 $db->exec('PRAGMA journal_mode=WAL;'); 

और तुरंत परीक्षण चलाएं:
एसएसडी: प्रति सेकंड 2000 लेनदेन। संकेतक 5 गुना से अधिक हो गया है!
HDD: 42 ऑपरेशन। 3 गुना वृद्धि।
सर्वर: 1000 ऑपरेशन। विकास लगभग 5 गुना है।

बहुत अप्रत्याशित परिणाम। लॉग मोड में एक साधारण परिवर्तन 5 गुना वृद्धि देता है।
अगला कदम मल्टीथ्रेडिंग के लिए स्क्रिप्ट को फिर से लिखना है।
हम कई थ्रेड्स (साइटों के वास्तविक संचालन के करीब) में कई बार PHP स्क्रिप्ट के माध्यम से मल्टीथ्रेडिंग प्राप्त करेंगे। ऐसा करने के लिए, उपयोगिता एबी (अपाचे बेंचमार्क) का उपयोग करें।

 ab -n 1000 -c 10 "http://localhost:81/test/benc_sqlite.php" 

परिणामी स्क्रिप्ट पाठ:

 <?php function openDB() { //     $file = "_TEMP.db"; //   ( ) $db = new SQLite3($file); //   $db->busyTimeout(5000); $db->exec('PRAGMA journal_mode=WAL;'); return $db; } //   function initDB() { $db = openDB(); //   $db->exec('CREATE TABLE foo (i INT, i2 INT)'); return $db; } //    function testDB($db, $count) { return testIteration($db, mt_rand(0, 1000)); } // 1   function testIteration($db, $iteration) { return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } // ----------     ---------- //   if(isset($_REQUEST['init'])) $db = initDB(); else $db = openDB(); //   if(testDB($db, 1)) echo 'OK'; else echo 'FAIL'; ?> 

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

 $db->busyTimeout(5000); 

यह आवश्यक है ताकि जब मल्टीथ्रेड रिकॉर्डिंग की कोशिश की जाए, तो प्रक्रिया अपनी कतार में थोड़ी देर (मैं 5 सेकंड सेट करता हूं) इंतजार कर रहा हूं, और इस तथ्य के साथ तुरंत गिर न जाए कि "SQLITE_BUSY"।

परिणाम:
SSD: 970 ऑपरेशन प्रति सेकंड। पर्याप्त से अधिक।
HDD: 35 ऑपरेशन।
सर्वर: स्थानीय मशीन से परीक्षण करते समय 90 ऑपरेशन और सर्वर से एबी शुरू करते समय 210। एक एसएसडी पर चलने के साथ एक बहुत बड़ा अंतर। जाहिरा तौर पर, यह सिस्टम के विन्यास में अंतर के कारण है (अपाचे सर्वर पर, यह न्यूनतम संभव में कट जाता है)।
इसके अलावा, यह ध्यान दिया जाना चाहिए कि स्थानीय संकेतक बहुत अधिक तैरते हैं, सर्वर का बादल प्रभावित होता है। मुझसे सर्वर तकरीबन डेढ़ हजार किलोमीटर।
किसी भी मामले में, यहां तक ​​कि अगर आप प्रति सेकंड न्यूनतम 90 ऑपरेशन करते हैं, तो यह मेरी राय में, पर्याप्त से अधिक है। उदाहरण के लिए, हैबर के पास प्रति सेकंड औसतन 30 विचार हैं (ज़ाहिर है, दोपहर में मजबूत चोटियों, लेकिन ग्रैब से पहले भी बढ़ते और बढ़ते हैं)।

भविष्य में, एसएसडी और एचडीडी के लिए संकेतक स्थानीय परीक्षण के दौरान लिए जाते हैं। और सर्वर के लिए - एक दूरस्थ मशीन से।
सर्वर और एसएसडी परीक्षण 10 थ्रेड्स में 1000 अनुरोधों को चलाकर किया जाता है। एचडीडी के लिए - 10 थ्रेड्स में 100 अनुरोध।

उलझन

आपको याद दिला दूं कि परीक्षण में 1 सरल INSERT अनुरोध था। सिद्धांत रूप में, एक सेवा करना अच्छा है जो आधार पर 1 ऑपरेशन करता है। लेकिन यह वास्तविकता से बहुत दूर है। हम तीन प्रश्नों (1 चयन, 1 अद्यतन और 1 अंक) को जटिल करते हैं।
सारांश समारोह परीक्षण:

  function testIteration($db, $iteration) { $rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration); if($rez > 0) { $db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration); } return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } 

यह स्पष्ट है कि आप एक SELECT क्वेरी के बिना कर सकते हैं, लेकिन फिर भी, हमारे पास एक परीक्षण है, एक अनुकूलन प्रतियोगिता नहीं है।

संकेतक:
SSD: 420 अनुरोधों से। रन से रन करने के लिए, दर गिरता है।
HDD: 20 अनुरोधों (और क्रैश) से।
सर्वर: 65 अनुरोधों (और दुर्घटनाओं) से।

DB अनुकूलन

मुझे प्रदर्शन में गिरावट पसंद नहीं थी। ठीक है, चलो इस समस्या को ठीक करने का प्रयास करें। विशेष रूप से, हम सूचकांक जोड़ते हैं। हम केवल एक फ़ील्ड खोजते हैं, इसलिए इसे इंडेक्स में जोड़ें।

आरंभीकरण में, रेखा जोड़ें

 $db->exec('CREATE INDEX "idx_foo" ON "foo" ("i");'); 

परिणाम:
SSD: 671 अनुरोध। बहुत अच्छा।
HDD: 25 से 12 अनुरोधों से। मुझे नहीं पता कि समस्या क्या है, लेकिन परिणाम सांकेतिक नहीं है। शायद इस तथ्य के कारण कि सर्वर और एसएसडी I 10 थ्रेड्स में 1000 अनुरोधों का परीक्षण करता है, और एचडीडी - 10 थ्रेड्स में 100 अनुरोध। परिमाण के एक क्रम से कम डेटा है। लेकिन इस तरह के प्रसार को प्रभावित नहीं किया जाना चाहिए था।
सर्वर: 60 अनुरोध।

खैर, परिणाम मुझे काफी भाते हैं। लेकिन हमारे पास पहले से ही 2 आधार परिवर्तन परिचालन हैं, मेरा लेनदेन कहां है? इसे जोड़ें:

  function testIteration($db, $iteration) { $rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration); $db->exec('BEGIN;'); if($rez > 0) { $db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration); } $rez = $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); $db->exec('COMMIT;'); return $rez; } 

और फिर से परिणाम:
SSD: 720 अनुरोधों से।
HDD: 30 अनुरोधों से। और इस बार सूचक काफी स्थिर है।
सर्वर: 70 अनुरोधों से।

डेटाबेस विफलता अनुकूलन

अपाचे बेंचमार्क, क्वेरी निष्पादन के अलावा, अनुरोधों की सफलता की निगरानी करता है। प्रतिक्रिया की लंबाई ली गई है और सभी प्रतिक्रियाओं को मूल से अधिक या कम त्रुटियों माना जाता है। यह दिलचस्प है कि जब इसी तरह की समस्याओं का परीक्षण लंबे समय तक दिखाई देने लगा। प्रति 1000 अनुरोधों पर केवल एक दर्जन दें, लेकिन फिर भी।
लॉग में, संदेश "SQLite3 :: निष्पादन (): डेटाबेस बंद है"। अजीब बात है, वाल मोड, लेनदेन, और अभी भी "डेटाबेस बंद है"। समाधान SQLite मेलिंग सूची में पाया गया था: http://www.mail-archive.com/sqlite-users@sqlite.org/msg05525.html
मल्टीथ्रेडेड मोड में, आपको "BEGIN" नहीं, बल्कि "BEGIN IMMEDIATE" या "BEGIN EXCLUSIVE" का उपयोग करना चाहिए। बहु-थ्रेडेड कार्य के लिए विशेष लेनदेन। "BEGIN EXCLUSIVE" मोड ने मुझे कभी-कभी यह समस्या दी, लेकिन "BEGIN IMMEDIATE" मोड ने इसे पूरी तरह से हल कर दिया।

और अब अंतिम परीक्षण:
SSD: 700 अनुरोधों से।
HDD: 30 अनुरोधों से। इसके अलावा, सूचक काफी स्थिर है।
सर्वर: 53 अनुरोधों से। अजीब बात है, मुझे लगभग 70 की उम्मीद थी। लेकिन यह एक बादल है, हम स्थिरता के बारे में बात नहीं कर सकते।

वैसे, समय के साथ अनुरोधों की संख्या का संकेतक थोड़ा कम हो जाता है। स्वच्छ आधार पर, SSD 1000 अनुरोध जारी करता है। "गंदे" पर - 350 तक। एक ही समय में, लगभग 50,000 प्रविष्टियाँ डेटाबेस में जमा हो गई हैं।

यह जोड़ने योग्य है कि यदि एकल-थ्रेडेड परीक्षण जटिल है, जैसा कि हमने बहु-थ्रेडेड (1 चयन, 1 अद्यतन और 1 INSERT क्वेरी + लेनदेन + अनुक्रमित) को जटिल किया है, तो परिणाम एसएसडी पर प्रति सेकंड 1,500 पुनरावृत्तियों है। जहां प्रयास करना है। उसी समय, जैसे-जैसे डेटाबेस बढ़ता है, प्रदर्शन बहु थ्रेडेड मोड की तुलना में लगभग 2 गुना धीमा हो जाता है।
बार-बार किए गए परीक्षण में मल्टी-थ्रेडेड के सापेक्ष SQLite के एकल-थ्रेडेड मोड के महत्वपूर्ण लाभों का पता नहीं चला।

सिद्धांत रूप में, परीक्षण ने काफी अच्छे परिणाम दिखाए। इसे स्थानों और विरोधाभासी, विवादास्पद और पर्याप्त रूप से सीखा नहीं जाने दें। लेकिन फिर भी, मेरे लिए व्यक्तिगत रूप से, SQLite और PHP के एक गुच्छा का प्रदर्शन मूल्यांकन काफी दिखाया गया है।

अन्य DBMS के साथ तुलना

SQLite हमने देखा। लेकिन अन्य DBMS इन स्थितियों में कैसे व्यवहार करते हैं?
यह परीक्षण केवल स्थानीय मशीन पर और केवल एसएसडी पर होगा। कारण यह है कि स्थानीय मशीन पर सर्वर की तुलना में अधिक DBMS है, और यह मोड मेरे सर्वर के संचालन के करीब है।
हाथ में थे: SQLite (v3.7.7.1), PostgreSQL (v9.2.1), MS SQL सर्वर (v11.0.3000.0 (2012, डेवलपर संस्करण)), MySQL (v5.5.28), MongoDB (v2.4.4) ।
PHP ने SQLite और MongoDB के लिए मानक ड्राइवर का उपयोग किया। PostgreSQL, MySQL और MS SQL ने PDO के माध्यम से काम किया।
इसके अतिरिक्त, मैंने परीक्षण को थोड़ा बदलने का फैसला किया: 10 थ्रेड्स में 10,000 अनुरोध निष्पादित किए जाते हैं। प्रयोग की शुद्धता के लिए, आधार हर बार खाली था।
डेटाबेस इनिशियलाइज़ेशन मैनुअल मोड में होता है। इनिशियलाइज़ेशन में शामिल हैं: फ़ील्ड द्वारा तालिका और इंडेक्स बनाना।



परिणाम:
SQLite: प्रति सेकंड 993 प्रश्न। यह अपेक्षित परिणाम है।
PostgreSQL: प्रति सेकंड 178,202 प्रश्न। बहुत कम। जाहिर है, समस्या डिफ़ॉल्ट सेटिंग में है।
MS SQL: प्रति सेकंड 1400 क्वेरीज़। बुरा नहीं है, बहुत बुरा है।
MySQL: प्रति सेकंड 1490 प्रश्न। यहां आपके पास डिफ़ॉल्ट सेटिंग्स :) हैं। यह ध्यान देने योग्य है कि शुरू में प्रति सेकंड 9 अनुरोध थे। 127.0.0.1 के साथ लोकलहोस्ट को कॉल को बदलने में मदद मिली। जहां तक ​​मुझे पता है, यह सिस्टम में सक्रिय आईपीवी 6 से जुड़ा हुआ है।
MongoDB: प्रति सेकंड 3173 प्रश्न। शानदार परिणाम। हालाँकि मुझे NoSQL से ज्यादा उम्मीद थी।

सच कहूँ तो, तुलना सबसे ईमानदार नहीं है। परीक्षण डिफ़ॉल्ट सेटिंग्स पर हुआ। मेरे पास DBMS सेटिंग्स संरेखित करने के लिए आवश्यक प्रशासनिक कौशल नहीं है।
MongoDB मुख्य रूप से गति उन्मुख है। SQLIte विश्वसनीयता पर केंद्रित है। और डिफ़ॉल्ट कॉन्फ़िगरेशन में PostgeSQL न्यूनतम संसाधन खपत पर केंद्रित है। शायद यही असफलता का कारण है।

---

UPD: उन्होंने मुझे एक "बड़े रहस्य" में बताया कि PostgreSQL को फिर से जोड़ना पसंद नहीं है। इसलिए, मैंने प्रत्येक DBMS के लिए एकल-थ्रेडेड परीक्षण किए।
परीक्षण एकल-प्रेस मोड में ऑपरेटिंग समय को मापता है। 10,000 ऑपरेशन, एक स्वच्छ डेटाबेस। लेन-देन में प्रपत्र 1 का चयन, 1 अद्यतन और 1 INSERT का संचालन। डेटाबेस से जुड़े बिना स्क्रिप्ट में गति का मापन (पहली परीक्षा में)।
परिणाम इस प्रकार हैं:
PostgreSQL: 1403. विकास 7 बार। यह पुन: कनेक्ट करने के लिए PostgreSQL के नापसंद होने की पुष्टि करता है।
PostgreSQL (fsync = off): 1766. डिफ़ॉल्ट मोड के सापेक्ष एक चौथाई की वृद्धि। मल्टीथ्रेडेड मोड में, वृद्धि दिखाई नहीं दी।
SQLite: 938. अजीब, 1500 से पहले था। टेस्ट में शायद कोई गलती थी।
MS SQL: 1993. विकास है, लेकिन महत्वपूर्ण नहीं है। कहीं 40% (1.4 गुना वृद्धि)।
MySQL: 2514. विकास, 1.7 गुना है।
MongoDB: 7603. वृद्धि लगभग 2.5 गुना है।

---

परिणाम काफी दिलचस्प हैं। इतना ही मैंने सर्वर पर विभिन्न DBMS के प्रदर्शन की तुलना करने का निर्णय लिया। आपको याद दिलाता हूं कि डेबियन 6 का उपयोग किया जाता है। सर्वर में: SQLite (v3.7.3), PostgreSQL (v9.0.4), MySQL (v5.1.66) है। डिफ़ॉल्ट सेटिंग में सब कुछ है। AB सर्वर पर स्थानीय रूप से शुरू हुआ। 10 धागे में 10,000 अनुरोध।
परिणाम:
SQLite: 174 क्वेरी प्रति सेकंड।
PostgreSQL: प्रति सेकंड 104 प्रश्न।
MySQL: 167 क्वेरी प्रति सेकंड।

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

परिणाम

नतीजतन, हम कह सकते हैं कि लो-लोडेड प्रोजेक्ट के लिए PHP और SQLite का संयोजन काफी कार्यात्मक है। यह कुछ सरल नियमों का पालन करने के लिए पर्याप्त है:

1. हम वाल-लॉगिंग मोड का उपयोग करते हैं।
2. व्यस्त Timeout सेट करने के लिए मत भूलना।
3. रिकॉर्ड एक लेनदेन में संयुक्त है। और सरल नहीं है, लेकिन "BEGIN IMMEDIATE"।

यदि परियोजना को "छोटे" लोगों के लिए जिम्मेदार नहीं ठहराया जा सकता है, तो डेटाबेस के लिए एसएसडी का उपयोग करना यहां अच्छी तरह से मदद कर सकता है।

अगर हम हाय-लोड के बारे में बात करते हैं, तो ऐसा करने वाले लोग जानते हैं कि मेरी सलाह के बिना क्या करना है।

यदि आप अन्य DBMS के साथ SQLite की तुलना करते हैं, तो वे डिफ़ॉल्ट सेटिंग्स पर तुलनीय प्रदर्शन करते हैं। वैसे भी, SSD ड्राइव का उपयोग करते समय।
इसी समय, सेटअप में आसानी और प्रशासन (कुछ भी कॉन्फ़िगर और प्रशासित करने की आवश्यकता नहीं है), दूसरे सर्वर पर स्थानांतरण में आसानी (एक साधारण कॉपी-पेस्ट के साथ) SQLite के लिए कुछ लाभ प्रदान करती है। मुख्य रूप से डेवलपर्स के लिए। PHP के साथ SQLite के साथ काम करने के लिए, कुछ भी नहीं, लेकिन PHP की आवश्यकता है।