0

Question

Which optimizations should I employ to make this DB manageable? I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are suitable for my setup.

DB Info

I loaded about 90 mil rows from flatfile csvs into a mysql database (aws RDS, if that matters). There are 60 columns and each row is a click on a website.

Datatypes: All datatypes are text because of limitations with the mysqlimport utility (specifically, its treatment of null vs. 0 values in numerical fields, which i could be wrong about)

Each column falls into one of these categories:

  1. Visit info: time, referrer, etc. Many pages log millions of visits (front page, for ex.), some log only a few.

  2. User info: city/state, etc. Users log anywhere from 1 to thousands of visits, with the distribution skewing right.

  3. Page info: url, content flags, etc.

IO - The database only has 4 users, so will not have a lot of i/o, mostly just queries to populate dataframes in our python data analysis environment.

indexes - none besides the default generated index. Candidates for indexing are user ID and page view timestamp (potentially a multi column index?)

Options

I welcome any other ideas, but this is what I've come up w/ with some searching. Interested to hear which steps are worthwhile and which aren't.

  1. right-size the datatype of each field -- does this offer performance boost? most fields are text or boolean flags.

  2. break table into at least 2 others, e.g., visit info and page info.

  3. Keep it in one table and partition it. From what I've read, I think partitioning on the most frequently queried field is the optimal choice.

Improve this question
2
  • There is no such thing as an optimal design without taking into account how you're going to use the data. Indexes and all that are there to speed up specific queries. Think about what queries you're going to write, then optimize your schema.
    – Mat
    Oct 26, 2015 at 6:11
  • Now you are just using the DB as a CSV storage, with no benefits from all relational features it offers. You should design an actual normalized schema for the data you import and then transform the data you have from a one big table to the normalized structure (write a script for it).
    – jkavalik
    Oct 26, 2015 at 7:48

1 Answer 1

Reset to default
0

OPTIMIZE TABLE is almost always not worth doing.

INDEXes are your friend -- they can (in many cases) make queries run orders of magnitude faster. Provide a Slow query, plus SHOW CREATE TABLE, and we can help you. Or study my cookbook. As that says, picking the 'right' index (perhaps a composite index) requires that you first have some clues of the SELECTs you will be doing.

"Rightsizing" datatypes is a good idea, especially when first creating the tables. Smaller --> more cacheable --> less I/O --> faster.

Two tables (visit info and page info)? If they are 1:1, usually 1 table is 'right'; if they are 1:many, then two tables is a must.

Improve this answer
1
  • thanks-- turns out something like 80% of visits were to a fairly small # of unique URLs, so the two-table scheme was def. a must
    – Brandon
    Nov 6, 2015 at 5:17

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.