افزايش سرعت insert در ديتابيس mysql - تفاوت موتورهاي InnoDB و MyISAM و عمليات Bulk Insert (اختصاصي)

[دانه کولانه]

افزايش سرعت insert در ديتابيس mysql - تفاوت موتورهاي InnoDB و MyISAM و عمليات Bulk Insert
چه عنوان هيبريدي شد ! .


خلاصه و مفيد :

وقتي يه تيبل ميسازيد هميشه مواظب باشيد كه موتور ذخيره سازي مناسب با كاري كه اون تيبل ميخواد انجام بده رو انتخاب كنيد
اگر با phpmyadmin (حفظ الله من التحريم) كار ميكنيد و ديفالت روي InnoDB هست مواظب باشيد كه چيكار داريد ميكنيد كلي مقاله خوب و خلاصه در نت هست ميتونين بخونين كه كجا بايد از اين استفاده كنيد كجا از MyISAM

حالا منم اينجا يه چندتا فرقش رو ميگم

در MyISAM بحث كليد خارجي يا ترنزكشن وجود نداره كه البته خيلي مهمه اما سرعت كار اون بسيار بيشتر از نوع Innodb هست .
همچنين بر خلاف InnoDB كه قفل كردن اون ركورد بيس هست ايشون تيبل بيس هستند ! يعني براي هر عمليات اينسرت يا اپديت كل تيبل قفل ميشه تا ايشون كارش رو انجام بده بعد دوباره تيبل در دسترس كوري هاي بعدي قرار ميگيره يعني اگر همزمان 10 يوزر بخوان يه چيزي رو در تيبل شما درج كنند بايد برن توي صف تا دونه دونه كارشون انجام بشه در حالي كه در innodb فقط ركورد مربوطه قفل ميشه و اگر اون 10 درخواست مربوط به يك ركورد خاص نباشن هر 10 تا همزمان انجام ميشن

همچنين در MyIsam تعداد ركوردهاي يك جدول ذخيره ميشه و شما در دستور select count(*) كه استفاده ميكنيد اين موتور هر بار ركوردها رو شمارش نميكنه بلكه تعدادش رو ذخيره كرده و به شما پس ميده (خوبه !‌)
اما در انواع ديگر موتور ها هر بار بايد سرشماري بشه :

MyISAM stores row count. Other storage engines like InnoDB do not store row counts and will count all rows each time. – The Scrum Meister

اما تفاوت اصلي بعدي FULLTEXT indexing هست كه در MyISAM ساپورت ميشه اما در Innodb ساپورت نميشه (ميتونيد بخونيد كه فول تكست سرچ چيه ) .


در پست بعدي تجربه خودم رو مينويسم در مورد اين دو
همچنين بالك اينسرت

[دانه کولانه]

تفاوت MyISAM vs INnoDB موتور ديتابيس MYSQL - دستور Bulk insert و خواندن اطلاعات از فايل LOAD DATA INFILE




اول خيلي سريع بگم كه در موتور Innodb بنده اعداد 1 تا 1000 رو توسط php در جدول درج كردم از 30 ثانيه بيشتر طول كشيد و خطاي تايم اوت 30 ثانيه رو داد و حدود 650 تاشو درج كرده بود


در حالي كه با دستور

LOAD DATA IN FILE


100 هزار عدد رو در كمتر از 1 ثانيه به داخل ديتابيس فرستادم ! (در همون innodb).

LOAD DATA INFILE 'c:\\1.txt' INTO TABLE `num`

در ضمن توليد اون 100000 عدد در پي اچ پي حدود 1 دقيقه طول كشيد اما در كمتر از 1 ثانيه ديتابيس تونست اونو لود كنه در همون Innodb كه ميخوايم بگيم نسبت به myisam كندتر هست .

همين تست رو براي Myisam گرفتم حدود 10 برابر سريعتر بود !:

اعداد رو دو برابر كردم يعني 200 هزار عدد رو طي :

199999 rows inserted. ( Query took 0.1062 sec )

به داخل جدولي از نوع myisam فرستادم ! . (يك دهم ثانيه 200 هزار اينسرت انجام داد )




و اين 200 هزار تا با innodb :

199999 rows inserted. ( Query took 1.9816 sec )

----------------------------
يك نكته : تا اونجايي كه من اطلاع دارم Bulk insert با insert معمولي متفاوت هست حداقل در اس كيو ال سرور مايكروسافت خود من بارها از دستور بالك كپي و BCP.exe
براي خواندن اطلاعات از فايلها استفاده كرده ام


براي مثال دستور در MS SQL SERVER (لااقل بعد از 2005 ) استفاده ميشه (BCP.exe هم فوق العاده س )

BULK INSERT myDatabase.MyTable FROM 'C:\MyTextFile.txt' WITH  FIELDTERMINATOR = ','

اما ايشون در اينجا بالك اينسرت رو با اينسرت معمولي يكي گرفته !
و تازه بر خلاف ادعاي اوليه خودش كه لود از فايل در ماي اس كيو ال بسيار (اكسترملي) سريع هست در بنچماركش اين نتيجه رو گرفته كه فرقي نكرده اند چندان و حتي اينسرت معمولي براش بهتر بوده .


با اين حال براي خود بنده اين دو خيلي متفاوت بود




داكيومنت MYSQL :

11.3. Bulk Insert




The logic behind bulk insert optimization is simple.
Instead of writing each key value to B-tree (that is, to the key cache, although the bulk insert code doesn't know about the key cache), we store keys in a balanced binary (red-black) tree, in memory. When this tree reaches its memory limit, we write all keys to disk (to key cache, that is). But since the key stream coming from the binary tree is already sorted, inserting goes much faster, all the necessary pages are already in cache, disk access is minimized, and so forth

براي بالا بردن ظرفيت bulk insert mysql :

Go ahead and add this to my.cnf

[mysqld]
bulk_inset_buffer_size=256M




You could also set it just for your session before launching extended INSERTs

SET bulk_insert_buffer_size= 1024 * 1024 * 256;

استفاده از بالك اينسرت و خواندن اطلاعات از فايل در ديتابيس MYSQL وآدرس دهي فايل مورد نظر در دستور LOAD DATA INFILE

LOAD DATA INFILE 'C:/DataSet.csv'
INTO TABLE test.LargeDataSet S
TERMINATED BY '\r\n';
FIELDS TERMINATED BY ',' LINE

حتما حواستون باشه كه ادرس فايل رو وقتي وارد ميكنيد بايد از / استفاده كنيد و نه \ (مطابق مثال بالا)

تيبلتون رو بايد مناسب محتويات فايل بسازيد دستور بالا رو نگاه كنيد متوجه ميشيد .

[دانه کولانه]

تفاوت innodb و myisam و insert و LOAD DATA INFILE




خلاصه : اون 1000 عددي كه توسط php در يك جدول از نوع INNODB وارد كردم و 600 تاش رو در 30 ثانيه وارد كرد به دو طريق ميشه سريعتر وارد كرد
اول اينكه موتور جدول رو به MYISAM عوض كرد ( تقريبا زير 1 ثانيه طول كشيد تا 1000 عدد رو وارد كنه )
يا اينكه اعداد رو در داخل فايل بريزيد و با دستور لود از فايل با همون موتور قبلي innodb اونو وارد كنيد (بازم زير 1 ثانيه طول كشيد )



--------------------

How to change your table Engine type from MyISAM to Innodb?

You do so by simply issuing the "ALTER TABLE" DDL statement:

ALTER TABLE <table-name> ENGINE=INNODB;

Log into the PHPMyAdmin utility through your control panel. If you are unsure how, please see Working with a MySQL database for instruction on how to login to PHPMyAdmin.
Select the database which contains the Table-Name
Click on the SQL tab
Paste in the query provided above. Be sure to replace table-name with the correct name of your table
Click the GO button.

• Note: A MyISAM table that is using FULL TEXT Indexing can not be converted to an Innodb Table Engine type.

=======================


MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons




The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

• InnoDB is newer while MyISAM is older.
• InnoDB is more complex while MyISAM is simpler.
• InnoDB is more strict in data integrity while MyISAM is loose.
• InnoDB implements row-level lock for inserting and updating while MyISAM implementstable-level lock.
• InnoDB has transactions while MyISAM does not.
• InnoDB has foreign keys and relationship contraints while MyISAM does not.
• InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
• MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.
Advantages of InnoDB

• InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
• Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

• Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
• Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
• No full-text indexing.

Advantages of MyISAM

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
• Full-text indexing.
• Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

• No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
• Doesn’t support transactions which is essential in critical data applications such as that of banking.
• Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.