क्या MySQL अरबों पंक्तियों पर यथोचित प्रश्न कर सकता है?


255

मैं एक MySQL डेटाबेस में मास स्पेक्ट्रोमीटर से स्कैन को स्टोर करने की योजना बना रहा हूं और यह जानना चाहता हूं कि क्या डेटा की इस राशि का भंडारण और विश्लेषण करना दूरस्थ रूप से संभव है।मुझे पता है कि प्रदर्शन पर्यावरण के आधार पर बेतहाशा भिन्न होता है, लेकिन परिमाण के किसी न किसी क्रम के लिए Ilooking: प्रश्न 5 दिन या 5 मिलीसेकंड ले जाएगा?

इनपुट प्रारूप

प्रत्येक इनपुट फ़ाइल में स्पेक्ट्रोमीटर का एक रन होता है;प्रत्येक रन में स्कैन का एक सेट शामिल होता है, और प्रत्येक स्कैन में डाटापॉइंट का एक क्रमबद्ध सरणी होता है।मेटाडेटा का पालन होता है, लेकिन फ़ाइल के अधिकांश हिस्से में 32- या 64-बिट इनट या फ़्लोट्स शामिल होते हैं।

मेजबान प्रणाली

|----------------+-------------------------------|
| OS       | Windows 2008 64-bit      |
| MySQL version | 5.5.24 (x86_64)        |
| CPU      | 2x Xeon E5420 (8 cores total) |
| RAM      | 8GB              |
| SSD filesystem | 500 GiB            |
| HDD RAID    | 12 TiB            |
|----------------+-------------------------------|

सर्वर पर कुछ अन्य सेवाएं चल रही हैं, जो नगण्य प्रक्रिया के उपयोग से चल रही हैं।

फ़ाइल आँकड़े

|------------------+--------------|
| number of files | ~16,000   |
| total size    | 1.3 TiB   |
| min size     | 0 bytes   |
| max size     | 12 GiB    |
| mean       | 800 MiB   |
| median      | 500 MiB   |
| total datapoints | ~200 billion |
|------------------+--------------|

डेटा पॉइंट्स की कुल संख्या a हैबहुतमोटा अनुमान।

प्रस्तावित स्कीमा

मैं चीजों को "सही" करने की योजना बना रहा हूं (यानी पागलों की तरह डेटा को सामान्य करना) andso a होगाrunsटेबल, एspectraएक विदेशी कुंजी के साथ तालिकाruns, और एdatapointsएक विदेशी कुंजी के साथ तालिकाspectra

200 बिलियन डेटापॉइंट सवाल

मैं कई स्पेक्ट्रा और संभवतः यहां तक ​​कि मल्टीप्लायरों का विश्लेषण करने जा रहा हूं, जिसके परिणामस्वरूप लाखों पंक्तियों को छू सकता है।यह मानते हुए कि मैं अनुक्रमण को ठीक से कर रहा हूं (जो एक और प्रश्न के लिए एक विषय है) और नेटवर्क भर में सैकड़ों MiB को टॉस करने की कोशिश नहीं कर रहा हूं, क्या यह MySQLto के लिए दूरस्थ रूप से प्रशंसनीय है?

अतिरिक्त जानकारी

XML- आधारित फाइलों से स्कैन डेटा आ जाएगाmzMLप्रारूप।इस प्रारूप का मांस में हैऐसे तत्व जहां डेटा संग्रहीत किया जाता है।प्रत्येक स्कैन> = 2 का उत्पादन करता हैतत्व, जिन्हें एक साथ लिया जाता है, फॉर्म का 2-आयामी (ormore) सरणी बनाते हैं[[123.456, 234.567, ...], ...]

ये डेटा राइट-एक बार होते हैं, इसलिए प्रदर्शन और लेनदेन की सुरक्षा अपडेट अपडेट नहीं होती है।

एक डेटाबेस स्कीमा के लिए मेरी भोली योजना है:

runsतालिका

| column name | type    |
|-------------+-------------|
| id     | PRIMARY KEY |
| start_time | TIMESTAMP  |
| name    | VARCHAR   |
|-------------+-------------|

spectraतालिका

| column name  | type    |
|----------------+-------------|
| id       | PRIMARY KEY |
| name      | VARCHAR   |
| index     | INT     |
| spectrum_type | INT     |
| representation | INT     |
| run_id     | FOREIGN KEY |
|----------------+-------------|

datapointsतालिका

| column name | type    |
|-------------+-------------|
| id     | PRIMARY KEY |
| spectrum_id | FOREIGN KEY |
| mz     | DOUBLE   |
| num_counts | DOUBLE   |
| index    | INT     |
|-------------+-------------|

क्या यह उचित है?


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

यहाँ मैं जिस तरह के डेटा के साथ एक सिंगल स्पेक्ट्रम (स्कैन) की साजिश कर रहा हूँ:

Viewer screenshot

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

+4

[फेसबुक MySQL के साथ स्केलिंग पर] (http://gigaom.com/cloud/facebook-shares-some-secrets-on-making-mysql-scale/) 03 jul. 122012-07-03 18:35:39

+6

चूंकि यह कच्चा ए/डी पोलिंग मास स्पेक्ट्रोमीटर डेटा है, इसलिए डेटाबेस में इसे संग्रहीत करना वास्तव में गूंगा लगता है।मैं अपना कच्चा डेटा ले जाऊंगा, उसे डंप करूंगा, उसे प्रोसेस करूंगा और प्रोसेस किए गए नतीजों को एक डेटाबेस में स्टोर करूंगा।परिणाम (ए) तरंगों को प्रति तरंग एक पंक्ति में संग्रहीत किया जाएगा, (बी) अन्य तरंगों से जुड़े अन्य डेटा जैसे अंशांकन घटता, और (सी) डेटाबेस में पंक्तियों को परिणामित करता है।यह आपके डिज़ाइन से अरबों ब्लोट की पंक्तियों को काट देगा।जब आप एक प्रारंभिक विश्लेषण को फिर से चलाना चाहते हैं, तो आप प्रभावी रूप से कुछ मापदंडों का संपादन कर रहे होंगे, एक विशाल गणना ऑपरेशन चला रहे होंगे, और db में नए परिणामों को संग्रहीत करेंगे। 03 jul. 122012-07-03 21:58:45

101

मैं आपकी आवश्यकताओं से बहुत परिचित नहीं हूं, लेकिन शायद डेटाबेस में प्रत्येक डेटा बिंदु को संग्रहीत करना थोड़ा अधिक है।यह लगभग प्रत्येक पिक्सेल को एक संबंधपरक डेटाबेस में एक अलग रिकॉर्ड के रूप में संग्रहीत करके एक छवि पुस्तकालय के दृष्टिकोण को लेने जैसा लगता है।

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

एक अन्य दृष्टिकोण आपके डेटा पॉइंट्स (और शायद स्पेक्ट्रा) डेटा के लिए एक दस्तावेज़-आधारित भंडारण प्रणाली का उपयोग करना होगा, और रन के लिए MySQL का उपयोग करना होगा (या शायद दूसरों के समान ही डीबी में रन डालना)।

+5

किसी डेटाबेस में बाइनरी डेटा को स्टोर करना गलत क्यों माना जाता है?(आंशिक रूप से पूछ रहा हूं क्योंकि मैं उत्सुक हूं, बल्कि इसलिए भी कि मैं इसके लिए उपयोग के मामले के बारे में सोच सकता हूं।) 03 jul. 122012-07-03 16:44:05

+13

यदि बाइनरी डेटा का व्यक्तिगत रूप से कोई मूल्य नहीं है, तो इसे एक अद्वितीय पंक्ति के रूप में संग्रहीत नहीं किया जाना चाहिए।एक छवि पर पिक्सेल 500x325 अप्रासंगिक है। 03 jul. 122012-07-03 17:39:50

+1

यह बहुत अच्छी बात है।अगर हम सामान को बाद में फिर से बाहर निकालना चाहते हैं तो हमें शायद कच्ची फाइलों को इधर-उधर रखना चाहिए, लेकिन छवियों को संचय करने की उपमा एक बेहतरीन है।हमें कभी भी प्रत्येक डेटा पॉइंट तक पहुंच की आवश्यकता नहीं होगी (जब तक कि हम शिखर निष्कर्षण को फिर से नहीं कर रहे हैं), इसलिए केवल निकाले गए सांख्यिकीय जानकारी को संग्रहीत करना बहुत बेहतर होगा। 04 jul. 122012-07-04 16:16:25


103

मैंने एक बार एक बहुत बड़े (टेराबाइट +) MySQL डेटाबेस के साथ काम किया।हमारे पास सबसे बड़ी तालिका सचमुच एक बिलियन पंक्तियों से अधिक थी।यह MySQL 5.0 का उपयोग कर रहा था, इसलिए यह संभव है कि चीजें बेहतर हुई हों।

इसने काम कर दिया।MySQL ने अधिकांश समय डेटा को सही ढंग से संसाधित किया।हालांकि यह बहुत ही अनिर्दिष्ट था।(यदि आप डेटा के टेराबाइट के साथ छह सिग्मा-स्तरीय उपलब्धता चाहते हैं, तो MySQL का उपयोग न करें। हम एक स्टार्टअप थे जिसमें डीबीए और सीमित धन नहीं था।)

बस डेटा का बैकअप लेना और स्टोर करना एक चुनौती थी।अगर हमें जरूरत पड़ती है तो मेज को बहाल करने में दिन लगेंगे।

हमारे पास 10-100 मिलियन रो रेंज में कई टेबल थे।तालिकाओं में किसी भी महत्वपूर्ण जुड़ने में बहुत समय लगता था और हमेशा के लिए लग जाएगा।इसलिए हमने संग्रहीत प्रक्रियाओं को टेबल पर 'चलने' के लिए लिखा और प्रक्रिया 'आईडी' की श्रेणियों के खिलाफ जुड़ती है।इस तरह से हम एक बार में डेटा 10-100,000 पंक्तियों को संसाधित करेंगे (आईडी के 1-100,000 के खिलाफ फिर 100,001-200,000, आदि से जुड़ें)।यह पूरी तालिका के खिलाफ शामिल होने की तुलना में काफी तेज था।

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

इसका जाल यह है कि बहुत बड़ी तालिकाओं (1-200 मिलियन से अधिक पंक्तियों) के लिए तालिकाओं के विरुद्ध अनुक्रमण अधिक प्रतिबंधित है।आपको कम, सरल इंडेक्स चाहिए।और सरल चयन कथन भी करना जो सीधे एक सूचकांक पर नहीं हैं, कभी वापस नहीं आ सकते हैं।जहां क्लॉस होता हैजरूरहिट इंडेक्स या इसके बारे में भूल जाओ।

लेकिन यह सब कहा जा रहा है, चीजें वास्तव में काम करती थीं।हम इन बहुत बड़ी तालिकाओं के साथ MySQL का उपयोग करने और गणना करने और उत्तर पाने में सक्षम थे जो सही थे।

डेटा की 200 बिलियन पंक्तियों पर विश्लेषण करने की कोशिश करने के लिए बहुत उच्च-अंत हार्डवेयर और बहुत अधिक हाथ-पकड़ और धैर्य की आवश्यकता होती है।बस एक प्रारूप में डेटा का बैकअप रखना जिससे आप बहाल कर सकते हैं एक महत्वपूर्ण काम होगा।

मैं सहमत हूंsrini.venigalla's answerउसपागलों की तरह डेटा को सामान्ययहाँ एक अच्छा विचार नहीं हो सकता है।उस डेटा के साथ कई तालिकाओं में जुड़ना आपको जोखिम के लिए खोल देगाफ़ाइल प्रकारजिसका अर्थ यह हो सकता है कि आपके कुछ प्रश्न कभी वापस नहीं आएंगे।सरल, पूर्णांक कुंजियों के साथ मना करने से आपको सफलता का बेहतर अवसर मिलेगा।

हमारे पास सब कुछ InnoDB था।MyISAM बनाम InnoDB के बारे में: मुख्य बात यह है कि दोनों का मिश्रण नहीं होगा।जिस तरह से MySQL कैश कुंजी और अन्य डेटा के कारण आप वास्तव में दोनों के लिए एक सर्वर का अनुकूलन नहीं कर सकते हैं।यदि आप कर सकते हैं तो एक सर्वर में सभी तालिकाओं के लिए एक या दूसरे को चुनें।MyISAM कुछ गति मुद्दों के साथ मदद कर सकता है, लेकिन यह समग्र DBA कार्य के साथ मदद नहीं कर सकता है जिसे करने की आवश्यकता है - जो कि हत्यारा हो सकता है।

  0

5.0 के बाद से MySQL ने इंडेक्स (...) डिपार्टमेंट में बहुत सुधार किया।यह देखना दिलचस्प होगा कि अब यह कैसा व्यवहार करता है। 07 oct. 172017-10-07 08:01:19


69

पागलों की तरह डेटा को सामान्य

पागलों की तरह डेटा को सामान्य करना इस मामले में सही रणनीति नहीं हो सकती है।डेटा को सामान्यीकृत रूप में संग्रहीत करके और भौतिकीकृत विचारों के रूप में भी अपने आवेदन के अनुकूल होने के द्वारा अपने विकल्पों को खुला रखें।इस प्रकार के अनुप्रयोगों में कुंजी एडहॉक क्वेरी नहीं लिख रही है।डेटा मॉडलिंग की तुलना में क्वेरी मॉडलिंग अधिक महत्वपूर्ण है।अपने लक्ष्य प्रश्नों से शुरुआत करें और इष्टतम डेटा मॉडल की दिशा में काम करें।

Is this reasonable?

मैं सभी डेटा के साथ एक अतिरिक्त फ्लैट टेबल भी बनाऊंगा।

run_id | spectrum_id | data_id |  |

मैं इस तालिका को सभी प्रश्नों के प्राथमिक स्रोत के रूप में उपयोग करूंगा।इसका कारण किसी भी जॉइन को करने से बचना है।इंडेक्सिंग के बिना शामिल होने से आपका सिस्टम बहुत ही बेकार हो जाएगा, और इतनी बड़ी फ़ाइलों पर इंडेक्स होना भी उतना ही भयानक होगा।

रणनीति, उपरोक्त तालिका में क्वेरी पहले, परिणामों को एक अस्थायी तालिका में डंप करें और रन और स्पेक्ट्रम के लुक अप तालिकाओं के साथ अस्थायी तालिका में शामिल हों और इच्छित डेटा प्राप्त करें।


क्या आपने अपनी लिखी जरूरतों बनाम पढ़ने की जरूरतों का विश्लेषण किया है?यह एसक्यूएल को खोदने और गैर-मानक डेटा भंडारण तंत्र पर जाने के लिए बहुत लुभावना होगा।मेरे विचार में, यह अंतिम उपाय होना चाहिए।

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

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html


32

संक्षिप्त उत्तर एक योग्य हां है - जैसा कि पंक्तियों की संख्या सटीक स्कीमा, डेटाटिप्स और आपके द्वारा चुने गए संचालन को बढ़ती है।

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

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

मेरे द्वारा अब तक का सबसे बड़ा MySQL ~ 100 मिलियन पंक्तियाँ प्रबंधित किया गया था।इस आकार में आप keep your rows and thus your fields fixed-size -- this allows MySQL to efficiently calculate the position of any row in the table प्रत्येक पंक्ति के निश्चित आकार को गुणा करके (पॉइंटर अंकगणितीय) गुणा करना चाहते हैं - हालांकि सटीक विवरण निर्भर करता है कि आप किस संग्रहण इंजन का उपयोग करने की योजना बना रहे हैं।MyISAM का उपयोग करें यदि आप इसके साथ दूर हो सकते हैं, तो इसे विश्वसनीयता में कमी है जो इसे गति के लिए बनाता है, और आपकी स्थिति में यह पर्याप्त होगा।चर-आकार के क्षेत्रों जैसे VARCHAR को CHAR (n) से बदलें और अपने पढ़ें प्रश्नों पर RTRIM() का उपयोग करें।

एक बार आपकी तालिका पंक्तियाँ निश्चित हो जाने के बाद, आप MySQL के integer datatypes (जिनमें से कुछ गैर-मानक हैं) का सावधानीपूर्वक मूल्यांकन करके बाइट्स की संख्या को कम कर सकते हैं।प्रत्येक 1-बाइट बचत आप 4-बाइट INT को 3-बाइट मेडियम में परिवर्तित करके निकाल सकते हैं, जो आपको ~ 1MB प्रति मिलियन पंक्तियों में बचाता है - जिसका अर्थ है कम डिस्क I/O और अधिक प्रभावी कैशिंग।smallest possible datatypes that you can get away with उपयोग करें।फ़्लोटिंग पॉइंट प्रकारों का सावधानीपूर्वक मूल्यांकन करें और देखें कि क्या आप 8-बाइट DOUBLEs को 4-बाइट FLOATs या यहां तक ​​कि <8 बाइट fixed-point NUMERICs ।यह सुनिश्चित करने के लिए परीक्षण चलाएं कि जो भी आप चुनते हैं वह आपको बाद में काटता नहीं है।

आपके डेटासेट के अपेक्षित गुणों के आधार पर और आपके आवश्यक संचालन में आपके मानों के अधिक असामान्य एन्कोडिंग में आगे की बचत हो सकती है (अपेक्षित पैटर्न/दोहराव जो सूचकांक के रूप में एन्कोड किए जा सकते हैं मूल्यों के एक सेट में, कच्चे डेटा जो केवल सार्थक योगदान कर सकते हैं मेटाडेटा और खारिज किया जा सकता है, आदि) - हालांकि विदेशी, अनपेक्षित, विनाशकारी अनुकूलन केवल तभी सार्थक हैं जब हर दूसरे विकल्प की कोशिश की गई हो।

सबसे महत्वपूर्ण बात, कोई फर्क नहीं पड़ता कि आप क्या कर रहे हैं, यह मत मानिए कि आपने एकदम सही स्कीमा चुना है और फिर आँख बंद करके 10 लाख रिकॉर्ड बनाने की शुरुआत करते हैं। अच्छा डिजाइन विकसित होने में समय लगता है।परीक्षण डेटा का एक बड़ा लेकिन प्रबंधनीय (कहते हैं, 1-5%) सेट करें और अपने स्कीमा की शुद्धता और प्रदर्शन को सत्यापित करें।देखें कि कैसे अलग-अलग ऑपरेशन करते हैं (http://dev.mysql.com/doc/refman/5.0/en/use-explain.html) और यह सुनिश्चित करें कि आप सबसे लगातार संचालन के पक्ष में स्कीमा को संतुलित करते हैं।

क्या मैंने छोटा कहा था?ओह।वैसे भी, गुड लक!


12

हम्म ... मैं दो कारणों से देखता हूं कि आप इस तरह की डेटा संरचना का चयन क्यों करेंगे:

  • तुम सच में किसी भी datapoint बनाम किसी भी datapoint प्रश्नों को करने की जरूरत है
  • आप एसक्यूएल में अपने सभी तर्क प्रदर्शन करने का इरादा रखते हैं

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

पुनश्च: ध्यान रखें कि आपको प्रति डेटा बिंदु पर कम से कम 36 + 5 बाइट्स की आवश्यकता होगी, इसलिए 200B डेटापॉइंट के साथ जो आपको कम से कम 8.2 टीबी आवश्यक स्थान प्रदान करना चाहिए।

PPS: आपको datapoints टेबल में id कॉलम की जरूरत नहीं है, एक PRIMARY KEY (spectrum_id, index) शायद पर्याप्त है (सिर्फ सावधान रहें कि index आरक्षित शब्द हो सकता है)


6

डाटा किस तरह की मशीन पर स्टोर होने वाला है?क्या यह एक साझा भंडारण उपकरण है?

अंतिम कारक जो आपके क्वेरी समय को निर्धारित करेगा, वह आपकी हार्डड्राइव होने जा रहा है।डेटाबेस और उनके क्वेरी ऑप्टिमाइज़र को यथासंभव डिस्क I/Os की संख्या को कम करने के लिए डिज़ाइन किया गया है।यह देखते हुए कि आपके पास केवल 3 टेबल हैं, यह बहुत मज़बूती से किया जाएगा।

हार्डड्राइव की रीड/राइट स्पीड मेमोरी स्पीड की तुलना में 200-300 गुना धीमी होती है।बहुत तेज़ विलंबता और तेज़ पढ़ने और लिखने की गति के साथ हार्डड्राइव देखें।यदि यह सभी डेटा एक 2-टीबी ड्राइव पर है, तो आप शायद प्रश्नों के समाप्त होने के लिए एक लंबे समय का इंतजार करेंगे।हार्डड्राइव लेटेंसी ~ 10-15मिलिसेकंड है जबकि मेमोरी लेटेंसी 10nanoseconds से कम है।मेमोरी विलंबता की तुलना में हार्डड्राइव लेटेंसी 1000-2000x धीमी हो सकती है।हार्डड्राइव पर मेकेनिकल आर्म का हिलना इस पूरे सिस्टम में SLOWEST चीज है।

आपके पास कितना रैम है?16 GB?कहते हैं कि आपको 32 रिकॉर्ड रखने की सुविधा देता है।आपके पास 16000 फाइलें हैं।यदि आप सभी डाटापॉइंट्स को रेखीय स्कैन करने जा रहे हैं, तो आप अकेले समय की तलाश में 5-10 सेकंड के साथ आसानी से समाप्त हो सकते हैं।फिर ट्रांसफर रेट 50mb/s में फैक्टर?लगभग 7 घंटे।इसके अतिरिक्त, किसी भी अस्थायी रूप से सहेजे गए डेटा को हार्डडिरेव पर संग्रहीत किया जाना चाहिए ताकि नए डेटा को पढ़ा जा सके।

यदि आप एक साझा संग्रहण डिवाइस का उपयोग कर रहे हैं जो अन्य उपयोगकर्ताओं द्वारा सक्रिय रूप से उपयोग किया जा रहा है ... आपका सबसे अच्छा दांव रात में सब कुछ चलाने वाला है।

नेस्टेड प्रश्नों की संख्या को कम करने में भी मदद मिलती है।नेस्टेड क्वेरी में अस्थायी टेबल्स होती हैं जो आपके हार्डड्राइव को और भी अधिक रोमांचित करेंगी।मुझे उम्मीद है कि आपके हार्डड्राइव पर आपके पास खाली जगह होगी।

क्वेरी ऑप्टिमाइज़ेशन केवल एक बार में 1 क्वेरी देख सकता है।इसलिए नेस्टेड सेलेक्ट स्टेटमेंट को ऑप्टिमाइज़ नहीं किया जा सकता है।यदि आप जानते हैं कि एक विशिष्ट नेस्टेड क्वेरी के परिणामस्वरूप एक छोटा डेटासेट लौटाया जा रहा है, तो इसे रखें।क्वेरी ऑप्टिमाइज़ेशन हिस्टोग्राम और रफ मान्यताओं का उपयोग करता है, यदि आप डेटा और क्वेरी के बारे में कुछ जानते हैं तो आगे बढ़ें और इसे करें।

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

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

जहां तक ​​तालिका को निरूपित करने की टिप्पणियों की बात है।मुझे लगता है कि केवल बड़े समूहों (शायद स्पेक्ट्रा) में डेटा पॉइंट्स को स्टोर करना सबसे अच्छा हो सकता है और फिर डेटा विश्लेषण को अजगर या एक भाषा में डेटाबेस के साथ इंटरैक्ट करता है।जब तक आपका एसक्यूएल-विजार्ड न हो।

+3

आप हार्ड ड्राइव बनाम मेमोरी लेटेंसी में भारी अंतर पर जोर देते हैं लेकिन आपकी संख्या 1000 के एक कारक से दूर होती है। यदि हार्ड ड्राइव में लगभग 10ms की लेटेंसी है, और मेमोरी 10ns है, तो लेटेंसी 1,000 के कारक से भिन्न नहीं होती है लेकिन एक कारक है 1,000,000! 03 jul. 122012-07-03 22:28:39


11

संपादित करें:

एक डिस्क पर डेटा के साथ MYSQL में यह मत करो।सिर्फ एक माध्यम से डेटा की मात्रा को पढ़ने में घंटों लगेंगे।आपको SCALE OUT चाहिए, UP नहीं।

और यदि आप प्रभावी डेटा विश्लेषण करना चाहते हैं तो आपको अपने डेटा को अलग करना होगा।आप यहां ऑनलाइन सिस्टम नहीं बना रहे हैं।आप संख्याओं को क्रंच करना चाहते हैं, तदनुसार डिजाइन करें।

मूल उत्तर पंक्ति के नीचे।


आपके प्रश्नों के आधार पर उत्तर अलग-अलग होंगे, MySQL इस नौकरी के लिए सबसे अच्छा साधन नहीं हो सकता है।आप समाधान को देखना चाहते हैं जिसे आप "आउट" कर सकते हैं और "अप" नहीं कर सकते।यदि आप कुछ प्रयास करने के लिए तैयार हैं, तो शायद आपको Hadoop जैसे Map Reduce solution को देखना चाहिए।

यदि आप अधिक तदर्थ प्रश्न करना चाहते हैं तो Google's BigQuery समाधान आपके लिए अच्छा हो सकता है।Google I/O 2012 से प्रासंगिक प्रस्तुति: Crunching Big Data with BigQuery

तो, समाधान इस बात पर निर्भर करेगा कि क्या यह एक-शॉट वाली चीज है और यदि आप तदर्थ प्रश्नों का यथोचित समर्थन करना चाहते हैं।


22

ऐसा लगता है कि एक्सएमएल से डेटा बिंदु डेटा को बाहर निकालने का एकमात्र कारण (जैसा कि समय और प्रकार के मेटाडेटा के विपरीत) और डेटाबेस फॉर्म में है, जब आप ऐरे में स्पेक्ट्रा का विश्लेषण कर रहे हैं - यानी शायद सभी को ढूंढना एक निश्चित हस्ताक्षर के साथ चलता है।अभी आप केवल अपने समस्या डोमेन को जानते हैं, लेकिन यह प्रति पंक्ति 1 नमूना के साथ 96kHz पर संग्रहीत संगीत के समान हो सकता है।मुझे यकीन नहीं है कि आकार डेटा का उपयोग करने से अधिक समस्या है।बीटल्स द्वारा सभी गीतों में गीत में 2 मिनट के सापेक्ष आयाम पूछने के लिए डेटा भर में क्वेरी करना बराबर होगा।यदि आप जानते हैं कि किस प्रकार के विश्लेषण किए जा सकते हैं, तो यह बहुत संभव है कि इन संकेतों पर प्रदर्शन करना और रन के बारे में मेटाडेटा में संग्रहीत करना अधिक समझ में आता है।

मुझे भी यकीन नहीं है कि अगर आपका स्रोत डेटा विरल है।यह पूरी तरह से संभव है कि डेटाबेस में एक स्पेक्ट्रम में केवल गैर-शून्य प्रविष्टियाँ शामिल होनी चाहिए जबकि मूल XML में शून्य-प्रविष्टियाँ शामिल हैं, और इसलिए आपकी कुल पंक्तियाँ स्रोत डेटा की तुलना में बहुत कम हो सकती हैं।

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


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


16

मैं लगभग 50 डेटाबेस सर्वरों के साथ एक वेब एनालिटिक्स सेवा चलाता हूं, जिनमें से प्रत्येक में 100 मिलियन से अधिक पंक्तियों वाले कई टेबल हैं, और कई जो एक अरब पंक्तियों से अधिक होते हैं, कभी-कभी दो बिलियन (प्रत्येक सर्वर पर) होते हैं।

यहां प्रदर्शन ठीक है।यह बहुत सामान्यीकृत डेटा है।हालाँकि - इसे पढ़ने के साथ मेरी मुख्य चिंता यह है कि आप इन तालिकाओं के लिए 4.2 बिलियन पंक्ति के निशान से बेहतर होंगे (शायद "रन नहीं" लेकिन शायद अन्य दो), जिसका अर्थ है कि आपको INT के बजाय BIGINT का उपयोग करना होगा प्राथमिक/विदेशी कुंजी।

इंडेक्स में BIGINT फ़ील्ड के साथ MySQL का प्रदर्शन INT की तुलना में हास्यास्पद रूप से भयानक है ।मैंने ऐसा करने की गलती एक बार एक मेज के साथ की थी जो मुझे लगा कि इस आकार में बढ़ सकता है, और एक बार कुछ सौ मिलियन पंक्तियों को हिट करने के बाद प्रदर्शन बस संक्षिप्त था।मेरे पास कच्चे नंबर नहीं हैं, लेकिन जब मैं बुरा कहता हूं, तो मेरा मतलब है कि विंडोज एमई खराब है।

यह कॉलम प्राथमिक कुंजी थी।हमने इसे केवल एक INT और presto magico होने के लिए वापस बदल दिया, प्रदर्शन फिर से अच्छा था।

उस समय हमारे सभी सर्वर डेबियन 5 और MySQL 5.0 के साथ थे।हमने डेबियन 6 और पेरकोना MySQL 5.5 में अपग्रेड किया है, इसलिए तब से चीजें बेहतर हो सकती हैं।लेकिन मेरे अनुभव के आधार पर, नहीं, मुझे नहीं लगता कि यह बहुत अच्छा काम करेगा।


5

मेरे लिए यह एक उपयोग परिदृश्य की तरह लगता है जहाँ आप "रिलेशनल कॉलम स्टोर" as described here जैसा कुछ चाहते हैं।

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

सरणियों को प्राप्त करते समय, न केवल आपको अपने सामान्यीकरण के परिणामस्वरूप किसी अन्य तालिका के साथ जुड़ने की आवश्यकता नहीं हो सकती है, लेकिन आप श्रृंखला को हैश के बजाय एक सरणी के रूप में पुनः प्राप्त कर सकते हैं।

मैं वास्तव में समस्या को गलत समझ सकता हूं, और मैं एक विशिष्ट समाधान का सुझाव भी नहीं दे रहा हूं।

Here's another talk प्रासंगिक हो सकता है, भले ही यह वास्तव में एक वर्तमान या तैनाती योग्य समाधान न हो।


4

मेरा सुझाव है कि आप अपनी तालिका को विभाजित करके देखें।हमारे पास एक ही तालिका (स्टॉक मार्केट डेटा) में 80 से अधिक सैन्य पंक्तियां हैं और इसे जल्दी से एक्सेस करने में कोई परेशानी नहीं है।

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

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

http://www.slideshare.net/datacharmer/mysql-partitions-tutorial


7

किसी ने उल्लेख नहीं किया, इस प्रकार मेरा सुझाव।पर एक नज़र डालेंबड़े पैमाने पर MySQL तेजसमाधान की।उदाहरण के लिए, यह अत्यधिक माना जाता हैtumblr presentation

अवधारणा है:

  • एक अतिरिक्त बड़े डेटाबेस के बजाय
  • मूल डेटा के कुछ हिस्सों को पकड़े हुए कई छोटे का प्रयोग करें

इस प्रकार आप ऊर्ध्वाधर प्रदर्शन को बेहतर बनाने की कोशिश करने के बजाय, क्षैतिज पैमाने पर कर सकते हैं।गूगल कीBigTableतथाGFSसस्ते क्षैतिज स्केलेबल नोड्स का उपयोग करके डेटा के पेटाबाइट्स को स्टोर और क्वेरी करने के लिए उपयोग कर रहे हैं।

हालाँकि, यदि आपको अलग-अलग शर्ड पर क्वेरीज़ चलाने की आवश्यकता है, तो परेशानी होगी।


अगर किसी को दिलचस्पी है, तो मैंने कुछ समय पहले एक हैलो-वर्ल्ड शार्किंग एप्लिकेशन बनाया।इसकी चर्चा हैhere एक ब्लॉग पोस्ट में।मैंने रेवेनडीबी और सी # का उपयोग किया लेकिन विवरण अप्रासंगिक हैं और विचार समान है।


16

यह काम करता है या नहीं, आप हमेशा एक ही अखंड भंडारण माध्यम के साथ एक ही समस्या में चले जा रहे हैं: डिस्क धीमी हैं।100 एमबी/एस (मीडिया को स्पिन करने के लिए बहुत अच्छा) में सिर्फ 3 घंटे लगते हैंपढ़नाएक 1TB तालिका;कि कोई विश्लेषण या मांग या अन्य देरी आपको धीमा कर रही है।

यही कारण है कि लगभग हर "बड़ा डेटा" इंस्टॉलेशन कुछ प्रकार के वितरित डेटा स्टोर का उपयोग करता है।आप अपना DB चलाने के लिए एक सुपर अद्भुत कंप्यूटर के निर्माण में 8 गुना ज्यादा पैसा खर्च कर सकते हैं, लेकिन अगर आपके पास बहुत सारे डेटा हैं जिन्हें समानांतर में स्कैन किया जा सकता है, तो आप लगभग 8 सस्ते कंप्यूटरों में लोड को वितरित करने से लगभग हमेशा बेहतर होंगे।

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


4

हाँ लेकिन...

मैंने उन तालिकाओं के साथ काम किया है जिनमें 2 बिलियन पंक्तियाँ थीं।हालाँकि केवल PK का उपयोग करने वाले प्रश्नों के तेज़ होने की उम्मीद थी।

सबसे महत्वपूर्ण बात, हार्डवेयर में मेमोरी में पूरी तालिकाओं को फिट करने के लिए पर्याप्त रैम थी।जब यह एक मुद्दा बन गया (उस समय 96GB पर अधिकतम हो गया), ऊर्ध्वाधर विभाजन के लिए चला गया, प्रत्येक मशीन पर टेबल सेट का आकार काफी छोटा रखने के लिए अभी भी स्मृति में फिट है।इसके अलावा, मशीनें 10Gb फाइबर के माध्यम से जुड़ी हुई थीं, इसलिए नेटवर्क थ्रूपुट एक मुद्दा नहीं था।

Btw।आपकी स्कीमा कुछ ऐसी दिखती है, जो NoSQL समाधान में फिट हो सकती है, जिसमें run_id स्पेक्ट्रा के लिए हैशिंग कुंजी और spectrum_id रूप में हैशिंग कुंजी के रूप में डेटा बिंदुओं का उपयोग किया जा सकता है।


3

मैंने अपने ब्लॉग पर इस विषय पर लिखा है: http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-MySQL.html

कुछ प्रमुख बिंदुओं को दोहराने के लिए:

  • बी-पेड़ नीचा दिखाते हैं क्योंकि वे बड़े हो जाते हैं और स्मृति में फिट नहीं होते (MySQL यहाँ अकेले नहीं है)।
  • InnoDB में कुछ प्रदर्शन को बनाए रखने में मदद करने के लिए कुछ विशेषताएं हैं (बफ़रिंग बदलें; पहले जिसे 'बफर' कहा जाता है)।
  • विभाजन भी मदद कर सकता है।

मेरी पोस्ट टिम कैलाघन की टिप्पणियों में इस से जुड़ी: http://www.tokutek.com/resources/benchmark-results/benchmarks-vs-innodb-hdds/#iiBench

जो आईबेंच बेंचमार्क का उपयोग करके 1 बिलियन पंक्तियों को सम्मिलित करता है।