mysql - MyISAM बनाम InnoDB




database performance (18)

InnoDB प्रदान करता है:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

इनो डीबी में टेक्स्ट और बीएलओबी को छोड़कर एक पंक्ति में सभी डेटा अधिकतम 8,000 बाइट्स पर कब्जा कर सकते हैं। InnoDB के लिए कोई पूर्ण टेक्स्ट इंडेक्सिंग उपलब्ध नहीं है। इनओडीबी में COUNT (*) एस (जहां, ग्रुप बाय, या जॉइन का उपयोग नहीं किया जाता है) MyISAM की तुलना में धीमी गति से निष्पादित करें क्योंकि पंक्ति गणना आंतरिक रूप से संग्रहीत नहीं होती है। InnoDB एक फ़ाइल में डेटा और इंडेक्स दोनों स्टोर करता है। इनओडीबी डेटा और इंडेक्स दोनों को कैश करने के लिए एक बफर पूल का उपयोग करता है।

माईसाम ऑफ़र करता है:

fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

माईसाम में टेबल-स्तरीय लॉकिंग है, लेकिन कोई पंक्ति-स्तर लॉकिंग नहीं है। कोई लेनदेन नहीं कोई स्वचालित क्रैश रिकवरी नहीं है, लेकिन यह मरम्मत तालिका कार्यक्षमता प्रदान करता है। कोई विदेशी कुंजी बाधाएं नहीं। InnoDB तालिकाओं की तुलना में MyISAM तालिका आमतौर पर डिस्क पर आकार में अधिक कॉम्पैक्ट होती है। यदि आवश्यक हो तो मायिसैम्प के साथ संपीड़ित करके माईसाम टेबल को आकार में बहुत कम किया जा सकता है, लेकिन केवल पढ़ने के लिए। MyISAM एक फ़ाइल और अन्य डेटा में इंडेक्स स्टोर करता है। MyISAM कैशिंग इंडेक्स के लिए प्रमुख बफर का उपयोग करता है और डेटा कैशिंग प्रबंधन को ऑपरेटिंग सिस्टम में छोड़ देता है।

कुल मिलाकर मैं केवल विशेष प्रयोजनों के लिए इन उद्देश्यों और MyISAM के लिए InnoDB की अनुशंसा करता हूं। InnoDB अब नए MySQL संस्करणों में डिफ़ॉल्ट इंजन है।

मैं उन परियोजनाओं पर काम कर रहा हूं जिनमें बहुत सारे डेटाबेस लिखते हैं, मैं कहूंगा ( 70% आवेषण और 30% पढ़ता है )। इस अनुपात में उन अपडेट्स भी शामिल होंगे जिन्हें मैं एक पढ़ा और एक लिखना मानता हूं। पढ़ना गंदा हो सकता है (उदाहरण के लिए मुझे पढ़ने के समय 100% सटीक जानकारी की आवश्यकता नहीं है)।
प्रश्न में कार्य एक घंटे में 1 मिलियन से अधिक डेटाबेस लेनदेन कर रहा है।

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

क्या किसी के पास इस प्रकार के भार (या उच्चतर) के साथ कोई अनुभव है? क्या माईसाम जाने का रास्ता है?


अधिक लिखने और पढ़ने के साथ लोड के लिए, आपको इनो डीबी से फायदा होगा। चूंकि InnoDB टेबल-लॉकिंग के बजाए पंक्ति-लॉकिंग प्रदान करता है, इसलिए आपके SELECT एक दूसरे के साथ नहीं बल्कि कई INSERT साथ समवर्ती हो सकते हैं। हालांकि, जब तक आप SQL लेनदेन का उपयोग करने का इरादा नहीं रखते हैं, तो InnoDB प्रतिबद्धता को 2 ( innodb_flush_log_at_trx_commit ) पर फ़्लश करें। इससे आपको बहुत सारे कच्चे प्रदर्शन मिलते हैं जिन्हें आप अन्यथा खो देंगे जब MyISAM से InnoDB तक टेबल ले जा रहे हैं।

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

अंत में, विभिन्न टेबल लोड के बारे में पता होना चाहिए। आपके पास सभी तालिकाओं पर एक ही पढ़ने / लिखने का अनुपात नहीं होगा। लगभग 100% पढ़ने के साथ कुछ छोटी टेबल माईसैम रह सकती हैं। इसी तरह, यदि आपके पास कुछ टेबल हैं जो 100% लिखने के करीब हैं, तो आपको INSERT DELAYED से लाभ हो सकता है, लेकिन यह केवल MyISAM में समर्थित है ( DELAYED क्लॉज को इनो डीबी तालिका के लिए अनदेखा किया जाता है)।

लेकिन बेंचमार्क सुनिश्चित करने के लिए।


थोड़ा ऑफ-विषय, लेकिन दस्तावेज़ीकरण उद्देश्यों और पूर्णता के लिए, मैं निम्नलिखित जोड़ना चाहता हूं।

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

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

इसके अलावा, सामान्य मामलों में, इनओडीबी कई कारणों से बहुत बेहतर प्रदर्शन करेगा, उनमें से एक टेबल-स्तरीय लॉकिंग के विपरीत रिकॉर्ड स्तर लॉकिंग का उपयोग करने में सक्षम है। न केवल ऐसी परिस्थिति में जहां लेखन पढ़ने से अधिक बार-बार होते हैं, साथ ही बड़ी डेटासेट पर जटिल जुड़ने वाली स्थितियों में भी। हमने बहुत बड़े जुड़ने (कई मिनट लेते हुए) के लिए माईसाम टेबल पर इनो डीबी टेबल का उपयोग करके 3 गुना प्रदर्शन वृद्धि देखी है।

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

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


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

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

मैंने नीचे बेंचमार्किंग का थोड़ा सा काम किया है - मैंने लूपिंग के लिए पायथन और टाइमिंग तुलना के लिए टाइमिट लाइब्रेरी का उपयोग किया है। ब्याज के लिए मैंने मेमोरी इंजन भी शामिल किया है, यह बोर्ड में सबसे अच्छा प्रदर्शन देता है हालांकि यह केवल छोटे टेबल के लिए उपयुक्त है (आप लगातार सामना करते हैं जब आप MySQL मेमोरी सीमा से अधिक हो जाते हैं तो The table 'tbl' is full जाती है)। चार प्रकार के चयन मैं देखता हूं:

  1. वेनिला चयन करता है
  2. मायने रखता है
  3. सशर्त चयन
  4. अनुक्रमित और गैर अनुक्रमित उप-चयन

सबसे पहले, मैंने निम्नलिखित SQL का उपयोग करके तीन तालिकाओं का निर्माण किया

CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

'माईसाम' के साथ दूसरी और तीसरी तालिकाओं में 'इनो डीबी' और 'मेमोरी' के लिए प्रतिस्थापित किया गया।

1) वेनिला का चयन करता है

प्रश्न: SELECT * FROM tbl WHERE index_col = xx

परिणाम: ड्रा

इनकी गति मोटे तौर पर वही है, और उम्मीद की जा रही कॉलम की संख्या में रैखिक है। InnoDB MyISAM से थोड़ा तेज लगता है लेकिन यह वास्तव में मामूली है।

कोड:

import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

2) मायने रखता है

प्रश्न: SELECT count(*) FROM tbl

परिणाम: माईसाम जीता

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

कोड:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

3) सशर्त चयन करता है

प्रश्न: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

परिणाम: माईसाम जीता

यहां, माईसाम और मेमोरी लगभग समान प्रदर्शन करती है, और बड़ी तालिकाओं के लिए इनो डीबी को लगभग 50% तक हराती है। यह ऐसी क्वेरी है जिसके लिए माईसाम के लाभों को अधिकतम किया जाना प्रतीत होता है।

कोड:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

4) उप-चयन

परिणाम: InnoDB जीतता है

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

CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

जहां एक बार फिर, 'माईसाम' को दूसरी तालिका में 'इनो डीबी' के लिए प्रतिस्थापित किया जाता है।

इस क्वेरी में, मैं चयन तालिका का आकार 1000000 पर छोड़ देता हूं और इसके बजाय उप-चयनित कॉलम के आकार को बदलता हूं।

यहां InnoDB आसानी से जीतता है। एक उचित आकार तालिका प्राप्त करने के बाद दोनों इंजन उप-चयन के आकार के साथ रैखिक रूप से स्केल करते हैं। सूचकांक MyISAM कमांड को गति देता है लेकिन दिलचस्प रूप से इनो डीबी गति पर थोड़ा प्रभाव डालता है। subSelect.png

कोड:

myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

मुझे लगता है कि इन सब का लेक-होम संदेश यह है कि यदि आप वास्तव में गति के बारे में चिंतित हैं, तो आपको किसी भी धारणा के मुकाबले कौन से इंजन अधिक उपयुक्त होंगे, इसके बारे में कोई भी विचार करने के बजाय आपको उन प्रश्नों को बेंचमार्क करना होगा।


मैंने MySQL का उपयोग करके एक उच्च-मात्रा प्रणाली पर काम किया है और मैंने MyISAM और InnoDB दोनों को आजमाया है।

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

अंत में मैंने डेटा को खंडित करके विवाद समस्या को हल किया जैसे कि आवेषण "गर्म" तालिका में चला गया और कभी भी गर्म तालिका से पूछताछ नहीं करता है।

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

बेशक मुझे नहीं पता कि आपका आवेदन क्या है लेकिन उम्मीद है कि यह आपको माईसाम और इनो डीबी के साथ कुछ मुद्दों में कुछ अंतर्दृष्टि प्रदान करता है।


मैंने इस प्रश्न पर एक टेबल में संक्षेप में discussed ताकि आप यह निष्कर्ष निकाल सकें कि इनो डीबी या माईसाम के साथ जाना है या नहीं।

यहां एक छोटा सा अवलोकन है कि किस डीबी स्टोरेज इंजन का उपयोग आप किस स्थिति में करना चाहिए:

                                                 MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                                      Yes
----------------------------------------------------------------
Frequent select queries                          Yes      
----------------------------------------------------------------
Frequent insert, update, delete                           Yes
----------------------------------------------------------------
Row locking (multi processing on single table)            Yes
----------------------------------------------------------------
Relational base design                                    Yes

संक्षेप में:

Frequent reading, almost no writing   => MyISAM
Full-text search in MySQL <= 5.5      => MyISAM

अन्य सभी परिस्थितियों में, इनो डीबी आमतौर पर जाने का सबसे अच्छा तरीका है।


लोग अक्सर प्रदर्शन के बारे में बात करते हैं, बनाम लिखते हैं, विदेशी कुंजी इत्यादि पढ़ते हैं, लेकिन मेरी राय में एक स्टोरेज इंजन के लिए एक अन्य विशेषता है: परमाणु अद्यतन।

इसे इस्तेमाल करे:

  1. अपने MyISAM तालिका के विरुद्ध एक अद्यतन जारी करें जिसमें 5 सेकंड लगते हैं।
  2. जबकि अद्यतन प्रगति पर है, 2.5 सेकंड में कहें, इसे बाधित करने के लिए Ctrl-C दबाएं।
  3. तालिका पर प्रभाव का निरीक्षण करें। कितनी पंक्तियां अपडेट की गईं? कितने अपडेट नहीं किए गए थे? क्या टेबल भी पठनीय है, या जब आप Ctrl-C दबाते हैं तो यह दूषित हो गया था?
  4. एक InnoDB तालिका के विरुद्ध अद्यतन के साथ एक ही प्रयोग का प्रयास करें, क्वेरी को प्रगति पर बाधित करें।
  5. InnoDB तालिका का निरीक्षण करें। शून्य पंक्तियों को अद्यतन किया गया था। इनो डीबी ने आश्वासन दिया है कि आपके पास परमाणु अपडेट हैं, और यदि पूरा अपडेट नहीं किया जा सका, तो यह पूरे बदलाव को वापस ले जाता है। इसके अलावा, तालिका दूषित नहीं है। यह तब भी काम करता है जब आप क्रैश अनुकरण करने के लिए killall -9 mysqld का उपयोग करते हैं।

प्रदर्शन निश्चित रूप से वांछनीय है, लेकिन डेटा खोने से उसे टंप करना चाहिए।


The Question and most of the Answers are out of date .

Yes, it is an old wives' tale that MyISAM is faster than InnoDB. notice the Question's date: 2008; it is now almost a decade later. InnoDB has made significant performance strides since then.

The dramatic graph was for the one case where MyISAM wins: COUNT(*) without a WHERE clause. But is that really what you spend your time doing?

If you run concurrency test, InnoDB is very likely to win, even against MEMORY .

If you do any writes while benchmarking SELECTs , MyISAM and MEMORY are likely to lose because of table-level locking.

In fact, Oracle is so sure that InnoDB is better that they removed MyISAM from 8.0!

The Question was written early in the days of 5.1. Since then, these major versions were marked "General Availability":

  • 2010: 5.5 (.8 in Dec.)
  • 2013: 5.6 (.10 in Feb.)
  • 2015: 5.7 (.9 in Oct.)
  • [TBD, maybe 2018], 8.0

Bottom line: Don't use MyISAM


MyISAM

The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking .

FlashMAX and FlashMAX Connect: Leading the Flash Platform Transformation Download Now Unless you need transactions, there are few databases and applications that cannot effectively be stored using the MyISAM engine. However, very high-performance applications where there are large numbers of data inserts/updates compared to the number of reads can cause performance proboelsm for the MyISAM engine. It was originally designed with the idea that more than 90% of the database access to a MyISAM table would be reads, rather than writes.

With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

MyISAM Summary

Name -MyISAM

Introduced -v3.23

Default install -Yes

Data limitations -None

Index limitations -64 indexes per table (32 pre 4.1.2); Max 16 columns per index

Transaction support -No

Locking level -Table

InnoDB

The InnoDB Engine is provided by Innobase Oy and supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.

The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk. This enables very fast recovery, and works even on very large data sets. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

As with MyISAM , there are few data types that cannot effectively be stored in an InnoDB database. In fact, there are no significant reasons why you shouldn't always use an InnoDB database. The management overhead for InnoDB is slightly more onerous, and getting the optimization right for the sizes of in-memory and on disk caches and database files can be complex at first. However, it also means that you get more flexibility over these values and once set, the performance benefits can easily outweigh the initial time spent. Alternatively, you can let MySQL manage this automatically for you.

If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.

InnoDB Summary

Name -InnoDB

Introduced -v3.23 (source only), v4.0 (source and binary)

Default install -No

Data limitations -None

Index limitations -None

Transaction support -Yes (ACID compliant)

Locking level -Row


Please note that my formal education and experience is with Oracle, while my work with MySQL has been entirely personal and on my own time, so if I say things that are true for Oracle but are not true for MySQL, I apologize. While the two systems share a lot, the relational theory/algebra is the same, and relational databases are still relational databases, there are still plenty of differences!!

I particularly like (as well as row-level locking) that InnoDB is transaction-based, meaning that you may be updating/inserting/creating/altering/dropping/etc several times for one "operation" of your web application. The problem that arises is that if only some of those changes/operations end up being committed, but others do not, you will most times (depending on the specific design of the database) end up with a database with conflicting data/structure.

Note: With Oracle, create/alter/drop statements are called "DDL" (Data Definition) statements, and implicitly trigger a commit. Insert/update/delete statements, called "DML" (Data Manipulation), are not committed automatically, but only when a DDL, commit, or exit/quit is performed (or if you set your session to "auto-commit", or if your client auto-commits). It's imperative to be aware of that when working with Oracle, but I am not sure how MySQL handles the two types of statements. Because of this, I want to make it clear that I'm not sure of this when it comes to MySQL; only with Oracle.

An example of when transaction-based engines excel:

Let's say that I or you are on a web-page to sign up to attend a free event, and one of the main purposes of the system is to only allow up to 100 people to sign up, since that is the limit of the seating for the event. Once 100 sign-ups are reached, the system would disable further signups, at least until others cancel.

In this case, there may be a table for guests (name, phone, email, etc.), and a second table which tracks the number of guests that have signed up. We thus have two operations for one "transaction". Now suppose that after the guest info is added to the GUESTS table, there is a connection loss, or an error with the same impact. The GUESTS table was updated (inserted into), but the connection was lost before the "available seats" could be updated.

Now we have a guest added to the guest table, but the number of available seats is now incorrect (for example, value is 85 when it's actually 84).

Of course there are many ways to handle this, such as tracking available seats with "100 minus number of rows in guests table," or some code that checks that the info is consistent, etc.... But with a transaction-based database engine such as InnoDB, either ALL of the operations are committed, or NONE of them are. This can be helpful in many cases, but like I said, it's not the ONLY way to be safe, no (a nice way, however, handled by the database, not the programmer/script-writer).

That's all "transaction-based" essentially means in this context, unless I'm missing something -- that either the whole transaction succeeds as it should, or nothing is changed, since making only partial changes could make a minor to SEVERE mess of the database, perhaps even corrupting it...

But I'll say it one more time, it's not the only way to avoid making a mess. But it is one of the methods that the engine itself handles, leaving you to code/script with only needing to worry about "was the transaction successful or not, and what do I do if not (such as retry)," instead of manually writing code to check it "manually" from outside of the database, and doing a lot more work for such events.

Lastly, a note about table-locking vs row-locking:

DISCLAIMER: I may be wrong in all that follows in regard to MySQL, and the hypothetical/example situations are things to look into, but I may be wrong in what exactly is possible to cause corruption with MySQL. The examples are however very real in general programming, even if MySQL has more mechanisms to avoid such things...

Anyway, I am fairly confident in agreeing with those who have argued that how many connections are allowed at a time does not work around a locked table. In fact, multiple connections are the entire point of locking a table!! So that other processes/users/apps are not able to corrupt the database by making changes at the same time.

How would two or more connections working on the same row make a REALLY BAD DAY for you?? Suppose there are two processes both want/need to update the same value in the same row, let's say because the row is a record of a bus tour, and each of the two processes simultaneously want to update the "riders" or "available_seats" field as "the current value plus 1."

Let's do this hypothetically, step by step:

  1. Process one reads the current value, let's say it's empty, thus '0' so far.
  2. Process two reads the current value as well, which is still 0.
  3. Process one writes (current + 1) which is 1.
  4. Process two should be writing 2, but since it read the current value before process one write the new value, it too writes 1 to the table.

I'm not certain that two connections could intermingle like that, both reading before the first one writes... But if not, then I would still see a problem with:

  1. Process one reads the current value, which is 0.
  2. Process one writes (current + 1), which is 1.
  3. Process two reads the current value now. But while process one DID write (update), it has not committed the data, thus only that same process can read the new value that it updated, while all others see the older value, until there is a commit.

Also, at least with Oracle databases, there are isolation levels, which I will not waste our time trying to paraphrase. Here is a good article on that subject, and each isolation level having it's pros and cons, which would go along with how important transaction-based engines may be in a database...

Lastly, there may likely be different safeguards in place within MyISAM, instead of foreign-keys and transaction-based interaction. Well, for one, there is the fact that an entire table is locked, which makes it less likely that transactions/FKs are needed .

And alas, if you are aware of these concurrency issues, yes you can play it less safe and just write your applications, set up your systems so that such errors are not possible (your code is then responsible, rather than the database itself). However, in my opinion, I would say that it is always best to use as many safeguards as possible, programming defensively, and always being aware that human error is impossible to completely avoid. It happens to everyone, and anyone who says they are immune to it must be lying, or hasn't done more than write a "Hello World" application/script. ;-)

I hope that SOME of that is helpful to some one, and even more-so, I hope that I have not just now been a culprit of assumptions and being a human in error!! My apologies if so, but the examples are good to think about, research the risk of, and so on, even if they are not potential in this specific context.

Feel free to correct me, edit this "answer," even vote it down. Just please try to improve, rather than correcting a bad assumption of mine with another. ;-)

This is my first response, so please forgive the length due to all the disclaimers, etc... I just don't want to sound arrogant when I am not absolutely certain!


Also check out some drop-in replacements for MySQL itself:

MariaDB

http://mariadb.org/

MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements including alternate storage engines, server optimizations, and patches.

Percona Server

https://launchpad.net/percona-server

An enhanced drop-in replacement for MySQL, with better performance, improved diagnostics, and added features.


Every application has it's own performance profile for using a database, and chances are it will change over time.

The best thing you can do is to test your options. Switching between MyISAM and InnoDB is trivial, so load some test data and fire jmeter against your site and see what happens.


I know this won't be popular but here goes:

myISAM lacks support for database essentials like transactions and referential integrity which often results in glitchy / buggy applications. You cannot not learn proper database design fundamentals if they are not even supported by your db engine.

Not using referential integrity or transactions in the database world is like not using object oriented programming in the software world.

InnoDB exists now, use that instead! Even MySQL developers have finally conceded to change this to the default engine in newer versions, despite myISAM being the original engine that was the default in all legacy systems.

No it does not matter if you are reading or writing or what performance considerations you have, using myISAM can result in a variety of problems, such as this one I just ran into: I was performing a database sync and at the same time someone else accessed an application that accessed a table set to myISAM. Due to the lack of transaction support and the generally poor reliability of this engine, this crashed the entire database and I had to manually restart mysql!

Over the past 15 years of development I have used many databases and engines. myISAM crashed on me about a dozen times during this period, other databases, only once! And that was a microsoft SQL database where some developer wrote faulty CLR code (common language runtime - basically C# code that executes inside the database) by the way, it was not the database engine's fault exactly.

I agree with the other answers here that say that quality high-availability, high-performance applications should not use myISAM as it will not work, it is not robust or stable enough to result in a frustration-free experience. See Bill Karwin's answer for more details.

PS Gotta love it when myISAM fanboys downvote but can't tell you which part of this answer is incorrect.



I've figure out that even though Myisam has locking contention, it's still faster than InnoDb in most scenarios because of the rapid lock acquisition scheme it uses. I've tried several times Innodb and always fall back to MyIsam for one reason or the other. Also InnoDB can be very CPU intensive in huge write loads.


If it is 70% inserts and 30% reads then it is more like on the InnoDB side.


In short, InnoDB is good if you are working on something that needs a reliable database that can handles a lot of INSERT and UPDATE instructions.

and, MyISAM is good if you needs a database that will mostly be taking a lot of read (SELECT) instructions rather than write (INSERT and UPDATES), considering its drawback on the table-lock thing.

you may want to check out;
Pros and Cons of InnoDB
Pros and Cons of MyISAM


bottomline: if you are working offline with selects on large chunks of data, MyISAM will probably give you better (much better) speeds.

there are some situations when MyISAM is infinitely more efficient than InnoDB: when manipulating large data dumps offline (because of table lock).

example: I was converting a csv file (15M records) from NOAA which uses VARCHAR fields as keys. InnoDB was taking forever, even with large chunks of memory available.

this an example of the csv (first and third fields are keys).

USC00178998,20130101,TMAX,-22,,,7,0700
USC00178998,20130101,TMIN,-117,,,7,0700
USC00178998,20130101,TOBS,-28,,,7,0700
USC00178998,20130101,PRCP,0,T,,7,0700
USC00178998,20130101,SNOW,0,T,,7,

चूंकि मुझे जो करना है, वह बैच ऑफ़लाइन मौसम घटना के ऑफ़लाइन अपडेट को चलाता है, मैं डेटा प्राप्त करने के लिए माईसाम टेबल का उपयोग करता हूं और चाबियों पर जॉइन चलाता हूं ताकि मैं आने वाली फ़ाइल को साफ़ कर सकूं और VARCHAR फ़ील्ड्स को INT कुंजी के साथ प्रतिस्थापित कर सकूं (जो संबंधित हैं बाहरी सारणी जहां मूल VARCHAR मान संग्रहीत हैं)।





myisam