Shulou(Shulou.com)06/01 Report--

There is a table in the Mysql library with a data volume of about 1 billion, which is basically meaningless for the operation of this table, so I want to test whether the migration to mongodb's shared schema will improve. So we built a 3-slice mongo environment, and exported the data of this large table in mysql to csv format. Finally, we successfully imported more than 1 billion data into the mongo cluster using mongoimport tools. But after the guide, I checked the information of the corresponding db in mongodb and almost peed. Please take a look:

# # View the db information in mongos and show that the size of the migrated lens_mobapp_ trace library is 1197.416GBmongos > show dbs;admin 0.016GBconfig 0.109GBlens_mobapp_trace 1197.416GBtest 0.031GB

The size of the data file I exported from mysql is only 259GB, and the format is csv, as follows:

# # size $ll-h total 259G root root 259G Mar 25 16:27 NL_MOB_APP_ERROR_TRACE.txt## format: comma-delimited csv files $head-2 NL_MOB_APP_ERROR_TRACE.txt "1551445226", "2015-02-11 00:00:01", "4613", "5969", "2", "19796", "1", "5397", "73", "0", "0", "46000", "4" "0", "499", "2", "4815", "1", "- 370443099", "http://pingma.qq.com/mstat/report","nbfs://2015/2/11/4613/4613.0.txt?2444,2#93b""1551445229","2015-02-11 00:00:01", "4611", "7259", "1", "34", "2", "1038", "71", "48", "4815", "16", "1", "1482" "412161", "2", "903", "1", "1429877488", "http://api.mobile.cosmeapp.cn/cosmeapi","nbfs://2015/2/11/4611/4611.0.txt?38200,750#2c2""

The size of the 259G data in the mysql library is as follows:

# # data size 228GBmysql > select sum (data_length) / 1024 select sum 1024 as size from information_schema.tables where table_name = 'NL_MOB_APP_ERROR_TRACE' and table_schema='lens_mobapp_trace' +-- + | size | +-- + | 227.9790587359032 | +-- + 1 row in set (0.59sec) # # data + total index size 404GBmysql > select sum (data_length+index_length) / 1024 from information_schema.tables where table_name = 'NL_MOB_APP_ERROR_TRACE' and table_schema='lens_mobapp_trace' +-- + | size | +-- + | 404.3463549343156 | +-- + 1 row in set (0.00 sec) # # number of records mysql > select count (*) from NL_MOB_APP_ERROR_TRACE +-+ | count (*) | +-+ | 1079684226 | +-+ 1 row in set (23 min 18.06sec)

When I saw that the amount of data migrated to mongodb became 1197.416GB, I refused at first and suspected that I had added special effects, because it was said that mongodb would pre-allocate large files for storage, so I went to mongos to see the real storage status of the back-end slices.

Mongos > use lens_mobapp_traceswitched to db lens_mobapp_trace## here only one fragment is extracted to illustrate the problem mongos > db.stats (); {"raw": {. "udb-q12c20/10.6.14.38:27017": {"db": "lens_mobapp_trace", "collections": 4, "objects": 352321865, "avgObjSize": 1005.7750069868641, "dataSize": 354356526232, "storageSize": 371299001584, "numExtents": 711, "indexes": 3 "indexSize": 64238570368, "fileSize": 437856239616, "nsSizeMB": 16, "dataFileVersion": {"major": 4, "minor": 5}, "extentFreeList": {"num": 4 "totalSize": 180224}, "ok": 1},.

"objects": 352321865 # # number of records on the slice

"avgObjSize": 1005.7750069868641 # # average size of each record

"dataSize": 354356526232 # # Real data size

"storageSize": 371299001584 # # data size + pre-allocated tablespace (2G)

"indexSize": size of the 64238570368 # # index

"fileSize": 437856239616 # # size of the entire data file

You can see that each record takes up about 1k of space in mongodb, while the average size of each record in mysql is 227bytes, so the data becomes so large after the data is migrated to mongodb. So what is the specific reason? Mongodb automatically adds a unique identification field _ id to each record inserted. The size of this field is 12bytes, and mongodb reserves some replace space for record update operation, and the pre-allocation function also takes up a certain amount of space (the maximum is 2GB). Mongodb and mysql do not manage tablespaces differently, but one thing is certain that data migration from mysql to mongodb is bloated.

The version we used for the above test is mongodb2.6.9. After mongodb3.0 made improvements to the underlying storage, using WiredTiger as the built-in storage engine, it is officially said that this move can bring document-level lock concurrency control and efficient document compression to MongoDB, as well as a significant improvement in its own performance, improving MongoDB's write performance by 7-10 times, reducing storage footprint by 80%, reducing operation overhead by up to 95%, supporting up to 50 replica sets, and curiosity drivers. So a simple test is done, and the process is as follows: mongodb starts with the WiredTiger engine, then imports 5G data, and finally looks at the size of the imported data.

# # WiredTiger engine is used in mongodb3.0.1 Startup service mongod-dbpath=/data/db/-logpath=/data/db/logs/mongodb.log-logappend-port=27017-storageEngine wiredTiger-fork > / dev/null## data sample size [root@manager opt] # ll-h xaa-rw-r--r-- 1 root root 5.0G Apr 22 16:57 xaa## data sample records [root@manager opt] # wc-l xaa21300000 xaa## data sample format [root@manager opt] # head-2 xaa "1551445226" "2015-02-11 00:00:01", "4613", "5969", "2", "19796", "1", "5397", "73", "0", "0", "46000", "4", "0", "499", "2", "903", "1", "- 370443099", "http://pingma.qq.com/mstat/report","nbfs://2015/2/11/4613/4613.0.txt?2444,2#93b""1551445229"," "2015-02-11 00:00:01", "4611", "7259", "1", "34", "2", "1038", "71", "48", "4815", "16", "1", "1482", "412161", "2", "903", "1", "1429877488", "http://api.mobile.cosmeapp.cn/cosmeapi","nbfs://2015/2/11/4611/4611.0.txt?38200,750#2c2""

Import mongodb as mongoimport, and then look up the size and number of records in the library

# # View > show dbs;lens_mobapp_trace 2.847GBlocal 0.000GB > use lens_mobapp_traceswitched to db lens_mobapp_trace > show collections;NL_MOB_APP_ERROR_TRACE > db.NL_MOB_APP_ERROR_TRACE.count () in the library 21300000directory # View the size of the data directory in the file system [root@manager db] # ll-h total 2.9G Apr rwkashi-1 mongodb mongodb 16K Apr 22 22:28 collection-0-38624736807305835.wwanthanthine-1 root root 2.7g Apr 22 23:03 collection-2-386247368073035.wtMushashi-1 mongodb mongodb 16K Apr 22 22:28 index-1-38624147368073058wtMelafe 1 root root 185m Apr 22 23:03 index-3-3862414736807305835.wtdrwxr-xr-x 2 mongodb mongodb 4.0K Apr 22 23:03 journaldrwxr-xr-x 2 mongodb mongodb 4.0K Apr 22 22:26 logs-rw-r--r-- 1 mongodb mongodb 32K Apr 22 22:31 _ mdb_catalog.wt-rwxr-xr-x 1 mongodb mongodb 5 Apr 22 22:27 mongod.lock-rw-r--r-- 1 mongodb mongodb 36K Apr 22 23:05 sizeStorer.wt-rw-r-- Mongodb mongodb-1 mongodb mongodb 95 Apr 22 22:27 storage.bson-rw-r--r-- 1 mongodb mongodb 46 Apr 22 22:27 WiredTiger-rw-r--r-- 1 mongodb mongodb 495 Apr 22 22:27 WiredTiger.basecfg-rw-r--r-- 1 mongodb mongodb 21 Apr 22 22:27 WiredTiger.lock-rw-r--r-- 1 root root 886 Apr 22 23:06 WiredTiger.turtle-rw-r--r-- 1 mongodb mongodb 44K Apr 22 23:06 WiredTiger.wt

It is gratifying to see this result. It seems that after mongodb3.0, it has indeed improved a lot. The compression ratio of 5GB data is about 57%. If a larger amount of data is imported, then the compression ratio should be improved. It seems that it is not much different from the official figure of 80%. Does this test result rekindle your confidence in mongodb? Haha, it seems that it is necessary to learn the new version of 3.0. new technologies emerge in endlessly. It is drifting in the sea, only day day up.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.