657805 - Marks are too big to be handled efficiently by the database

Status: VERIFIED FIXED

(Websites Graveyard :: markup.mozilla.org, defect)

Description

[Fred Wenzel [:wenzel]]

When fixing a problem with a race condition on saving marks, we load-tested the submission and noticed that with 10 concurrent users, dev and stage take between 4 and 20 seconds to save a mark.

About 25% of these submissions fail when the transaction runs into a timeout, because previous submissions are still running.

We believe that this is caused by single marks being between 50 and 150 kilobytes large (base64-encoded).

With the current state of the database (2000 marks), we are using an average of 100KB per mark.


This leads to two problems:
- depending on the success of the project, our database server will inevitably run out of disk space (about 200GB).
- more importantly, MySQL seems unable and not designed to handle such massive amounts of data efficiently.

What do we do? Marks can be stored on the file system, but that touches many parts of the app, so this fix would become quite large. Any other possibilities? Not Base64-encoding the data in the database and instead making the field a BLOB would reduce the size a little, but the marks are still too complex and would still take many kilobytes. Any way to reduce their size to, say, 5KB a piece?

Comment 1

[Fred Wenzel [:wenzel]]

Attachment: JMeter test file

This is the jmeter test file we've been using. For it to work, put the right password into the Auth manager (or change the settings to point at your own server).

Comment 2

[Fred Wenzel [:wenzel]]

Since we can't rearchitect the entire app, will try to reduce the weight of individual submissions. Can we get rid of points_obj and just keep points_obj_simplified around?

Comment 3

[Fred Wenzel [:wenzel]]

We should get individual marks down to 20K or less in the database.

Comment 4

[[:brez]]

I just ran that jmeter test locally and it passed (with 20 simultaneous requests)  bumped it to a hundred and managed to get some 500s - tracking down exactly what they are..

Comment 5

[[:brez]]

Wenzel are you working on this or am I?  Some clarification is needed.

Comment 6

[[:brez]]

Assigning back to you..

Comment 7

[Matt Brandt [:mbrandt]]

Attachment: A 'complex' data heavy mark of an elephant

Just a note from QA. The current JMeter script attached (attachment JMeter test file) to this bug is very simple. A single pen stroke. 

Here's a more complex signature of an elephant :)

Comment 8

[[:brez]]

Just documenting what we're doing for this:

-- Not store raw points in db, but store on a configurable file path using the reference as a file name.  
-- Change the method where if comes out of the backend to look at the file and not the db as well. 
-- Remove base64 encoding from simplified points object.

Comment 9

[Fred Wenzel [:wenzel]]

(In reply to comment #8)
> -- Change the method where if comes out of the backend to look at the file
> and not the db as well. 

I thought the app never uses the raw (unsimplified) data by itself?

Comment 10

[Chris Hinkle]

Fred, brez, I thought the https://markup.allizom.org/en-US/gml/Ru4M/ was using the raw data, but I could be wrong.

Comment 11

[[:brez]]

No - gml is created from simplified - raw is being saved only for posterity at the moment

Comment 12

[[:brez]]

Updated

-- Not store raw points in db, but store on a configurable file path using the reference as a file name.  
-- Remove base64 encoding from simplified points object

Comment 13

[[:brez]]

What should do re. the encode marks currently in the database?  seems like we need take each one out and decode and put back in.. ?   as part of a migration presumably

Comment 14

[Fred Wenzel [:wenzel]]

Yes, can South migrations do something as complex as that? Just make sure it works before committing, so the dev server doesn't hose its DB ;)

Comment 15

[[:brez]]

Yea we can do this with south - will test locally of course..

Comment 16

[[:brez]]

All set

https://github.com/mozilla/markup/commit/28ef93a6ebec565858489f8771343d642c21aa97

Note there is a new parameter in settings.py:

https://github.com/mozilla/markup/commit/7d2484a2b28cfec49f3e9b125cd7dcd9ccf30a14

Comment 17

[Matt Brandt [:mbrandt]]

wenzel:  Can we bump this to verified given that marks are being saved properly to the db, rendered in the ui, and the gml is exportable?

Comment 18

[Fred Wenzel [:wenzel]]

Yes. If there's anything else that needs fixed in this context, we should open a new bug.

Comment 19

[Matt Brandt [:mbrandt]]

Thx Fred :) QA verified as well.