I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array of integers as text(varchar couldn't become large enough).

A simple

SELECT row FROM tablename LIMIT 200,000;

takes 30 seconds to complete... I need to select all 30 million rows and go through some algorithms in at most 10 seconds to be ideal.

I'm not used to managing such a large database. Is it normal for a queries to take this long? I've heard about indexing, but can't index the text data type column.

What are some ways you suggest I can improve performance? I can't find too many ways to improve performance other than:

  • Indexes
  • More RAM
  • SSD
  • Partitioning tables(sound complicated?)

Is there anything I'm not thinking of?

I appreciate any help! :)

closed as too broad by Michael Green, Mat, Julien Vavasseur, mustaccio, Paul White Mar 5 '16 at 17:16

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Sounds like you need to completely rethink your design, possibly including the selection a relational database. (280G/30mil rows is not huge these days by any stretch. Large array of stuff in a column is a very big smell though.) – Mat Mar 5 '16 at 8:51
  • Are you suggesting a non-relational database? A NoSQL like mongodb? – God Usopp Mar 5 '16 at 9:27
  • 1
    I'm not suggesting anything, you haven't described your problem in enough detail for that. Just that your current design and constraints just don't fit. – Mat Mar 5 '16 at 9:31
  • Windows or Linux? If Linux, output of vmstat, iostat and mpstat during one of these runs (preferably two or three readings). What is your current disk config? What on earth do you use integers in a text field for? – Vérace Mar 5 '16 at 9:35
  • I couldn't figure out a way to store integer arrays in any other data type. TEXT was the only solution I found. I'm storing a HUGE array of integers. I'll get the vmstat, iostat, and mpstat output, but it won't be for a few hours. – God Usopp Mar 5 '16 at 9:45

An index provides a path to rapidly identify the rows that match a given predicate. They help most when you need to reference a small fraction of a table at a time. Since you need all rows this is unlikely to be helpful. They also enforce a sequence. This may be helpful if your algorithm would benefit from ordering, though you do not mention it.

The DBMS copies data from disk into memory before processing the rows. So more memory helps because more data can be held in memory, with corresponding fewer IO waits. Since you need to process 280GB the last 4GB will be in memory at the end. During the next cycle that data will be evicted and fresh data read. Unless you are able to install over 280GB more memory is unlikely to help.

Partitioning is more of an archiving thing. Sharding may help, though. That would mean spreading the data across several servers. Four servers would, approximately, quarter the time to process. You'll need a custom process to stitch the answers together, though, which may not be simple.

The simple fact is that if you need to process 300M rows you need to read 300M rows. This is likely limited by storage bandwidth. An SSD will obviously help here.

However, is there a way that you wouldn't have to process all rows each time? Could you pre-calculate these values, cache intermediate results or only process deltas between queries?

Edit Are you reading all rows only to parse some values out of the text column, but you actually do not use the majority of rows in any particular query? LIKE is not going to help eliminate IO since the majority of values will be somewhere in the middle of the text. An index will not help here. The best you can achieve is to push filtering onto the DB server eliminating network time.

A full text search may help find labels within the large text column. Perhaps redefining the column as JSON and adding a secondary index would help. But really these are kludgy workarounds for a sub-optimal design. Relational databases are built to process relations i.e. tables. Structure the data properly as a table and you will have the best chance of getting good performance. In this case that means separating the key-value pairs embeded in the text column onto separate rows, one per value. An index on the "key" column (and whatever other columns are in the WHERE clause) will be a great start. Yes, the DB may become a little larger, but the software's designed for this. There are many, many systems around with terabytes of data in billions of rows. It will be OK.

  • There might be a way for me to only select a few rows using LIKE... but i'll have to play around with it. Thank you. You gave me an idea. :) – God Usopp Mar 5 '16 at 10:24

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