9

I am designing a scraping system that will scrape large set of data from websites. The system is expected to collect data from websites and store it to database.

I am storing structured data parsed from HTML tags.

For example: If it's scraping a hotel listing, the data would contain the hotel id, hotel name, location, price, reviews, ratings, URL, etc.

This database is expected to have over 20-50 million records. The data set can contain information in different languages. The data will be populated periodically (more or less a million every week).

The database needs to be optimized for filtering and search. Also it should be capable of managing the other language characters (for example Chinese). I need fast execution and retrieval of data along with character support.

I was considering using MySQL, but I don't know if there are any better options. If MySQL is the best option, which storage engine, collation, and charset should be used?

Edited

I have a DB design plan to store data for each week store in separate tables, which could be later combined (if needed) as per the requirements.

Does Chinese characters cause any troubles while JOINing the queries?

Does storing the data in multiple tables create any performance issue while retrieval. (I planned of multiple tables since an indexed big table could make data insertion slower)

7
  • 6
    MySQL is a poor choice as far as data integrity is concerned. PostgreSQL may be practical enough for you. Mar 7, 2014 at 6:43
  • 7
    20 million records is nothing for any decent modern database; and they all support unicode these days. Your issue is not what database to use, but how to identify, scrape (that includes code page conversions to unicode) and structure your data - and subsequently how to efficiently extract information from the database.
    – user416
    Mar 7, 2014 at 10:31
  • what about using a NoSQL approach in this case.I am having experience only with MySQL and I have heard a lot on databases like mongo db.Will using this could make a substantial improvement in the performance.? Mar 8, 2014 at 16:55
  • 2
    mysql will be fine for your task. if you decide to go with an alternative, you risk the frustration of a learning curve, which would delay your project. Also, depending on your technology you decide to extract and display those results, you have to make sure that the technology is compliant to use it. Mar 16, 2014 at 12:59
  • @JoshuaAnderson OK thanks. I'm using PHP to scrape the data and with the PDO extension it is compliant to major DB's . Earlier while processing a DB with about 1 million entries with MySQl, I had faced different constraints like queries taking more time thereby casing timeout issues.So I am in a confusion to MySQL in handling such a big database. Mar 17, 2014 at 4:53

3 Answers 3

4

Postgres JSONB

If you are scraping different kinds of data from different sources to be stored all together but varied enough or changing often enough that you don't want to define specific tables for each source, and the structured data you derive is simple enough to be represented as JSON, then I suggest considering Postgres.

The latest versions of Postgres have a new built-in data type called JSONB, where the B means binary. When you submit a JSON value, Postgres parses it into the various fields and stores those parts intelligently in an internally-defined binary format. This process gives Postgres the ability to index the JSON fields. That means wicked fast performance that can out-perform the various “NO-SQL” systems. And unlike those other systems, you get ACID-compliant reliability and data-integrity with Postgres.

This JSONB type supplants Postgres original JSON support as well as its older HSTORE type.

For more info, see:

Chinese text

Postgres can handle Chinese text, with full support of Unicode. Be sure when creating the database/catalog to specify UTF-8 encoding.

Of course, your database driver and client app must also support UTF-8.

Data volume

Postgres can easily handle tens of millions of rows, and inserts or updates of a million a week.

If loading many rows at once, look at alternatives to calling INSERT one row at a time, such as the COPY command.

Postgres by default is configured conservatively to not negatively impact the host machine. If you know you have additional memory and CPU available, explore the various Postgres settings.

1
  • Great answer, thanks for taking the time to offer your input May 31 at 21:13
1

Any modern database system with unicode support will be able to handle this easily, performance gains will be made in the optimization of indexes and a proper database schema. Breaking things into multiple tables/databases using natural clusters will also help with performance.

Its not just about response time requesting data but also updating. For a mass load of multiple items, I generaly found it much better to disable indexing until finished loading, then re-index..and even better to go to smaller sub-tables with logical clusters.

Try your hardest to avoid using strings/varchar/guid values as a key in an index. Use an int or int64. I know that uuid's are "statistically unique" and seem like a great thing to use for a unique id...but collisions happen, and you have to be prepared for them and the cost of checking for one. If you need to index on something common, better to put the common thing in a separate table and link to the record using an integer foreign key, and index the foreign key field.

2
  • Here in my case,I could not find any integer to be used as primary key.There is a single parameter which could be treated as unique and its a page URL. Mar 27, 2014 at 4:28
  • Then use a hash algorithm to reduce the string into a fairly unique integer. You will get some collisions, but the index will find matches MUCH faster than the string comparisons, then you have to loop through the result set looking for an exact match.
    – skamradt
    Apr 2, 2014 at 15:27
1

Why not a NOSQL database?

I think that there may be better response times on a document-based no-sql database like MongoDB. It allows to free-form (imagine that there are hotels that haven't all the fields you look for, for example) the documents and replication is really easy, which adds an integrity layer, being possible to have replica sets in different servers that are transparent for the programmer.

Said so, you have to be really careful with the data structure, because there are no structure checks, nor PKs or FKs.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.