1

this is a interview question

a telecom billing system, there is a internet flow table (phone_number,start_time,stop_time,time_lasting,fare),500 million records per month(2G),every record size is 300K, insert and update when people use 4G to surf the internet,and update their account balance every time(166 write/s),and people will query their internet details from 5 * 6 = 30 hundred million (save half year data)

question1:how to use mysql to handle these data?
quesiton2:how to optimize insert and update?
question3:how to optimize query ?

my opion (mybe not correct) 1.insert and update is a transaction so use InnoDB engine 2.partition table every day 3.do I need redis or anything to help speed up insert and update ? 4.I don't know how to deal with this query optimize

thank you very much

3

Plan A: ENGINE=InnoDB, with these specific suggestions:

Since you are purging after 6 months, I recommend PARTITION BY RANGE (TO_DAYS(...)) with weekly partitions. Every week, drop the oldest and REORGANIZE "future" into "next week" and "future". See Details.

Partitioning by day leads to so many partitions that it is inefficient. And, dropping a week's worth of data is just as easy as a day's worth.

a_vlad is right that partitioning does not necessarily help with queries. But it is excellent for that time-based purge.

200M * 300K is more like 2T. Where is the math error? Perhaps 300B? Are you implying a fixed-length row? Don't.

300KB for one record seems terribly big -- what are you keeping in it? Seriously consider normalizing out any repetitive columns.

Provide a tentative SHOW CREATE TABLE and the main queries (insert/update/delete). It is unclear that there need to be any updates or deletes for your application.

166 writes/sec is pushing the limits of a spinning drive. Either use hardware RAID-5 with a Battery Backed Write Cache or use SSDs.

I suggest you adapt the suggestions in my High speed ingestion blog. That discusses a technique for getting well past 166 writes/sec. (Keep in mind that you will have peak loads that are much higher than 166.)

Plan B: NDB Cluster. (Sorry, I don't have specific suggestions. But NDB was designed for Telecom.)

Redis? That adds another moving part -- more things to go wrong. And, assuming that your traffic is reasonably consistent, there is little to be gained by any buffering scheme.

The MySQL box should be separate from the Client box(es). You should design the client boxes (web servers?) to be scalable -- any number of them, add more as needed.

2

there are many variants.

  1. partitioning - not help from bad queries and can make situation more bad
  2. split operations - write on master, all read (especial users) - on slave
  3. on master optimise structure for insert update - only necessary indexes, which really need for improve updates
  4. On slave(s) - indexes for select operations
  5. query must reduce number of rows as much as possible - depend from logic, but it user_id, date, location and etc - need analyse queries and indexes cardinality
  6. then good idea to ask - for what reason in table with very simple structure:

    (phone_number,start_time,stop_time,time_lasting,fare)

each records take 300kb? what also stored in this table. Good idea to split table for 2

it of course only top layer, always need more details about application and environment.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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