Database design optimization - MariaDB (MySQL)

Correct. That is how I get the totals for the current update, the days total, yesterdays total, week total, etc...
This is done for each user, host and team.
 
Skillz said:
Splitting them up even more, in VMs even, wouldnt really solve anything. Each VM instance will still take just as long to update the database as the current setup.
Click to expand...
May help if the DB engine can't take maximum advantage of available threads. There are two databases in your case but still only one DB engine (the brains) that may or may not be maxing out the available hardware resources. Splitting them into four VMs would ensure that four DB engines are able to work in parallel.
 
Skillz said:
Correct. That is how I get the totals for the current update, the days total, yesterdays total, week total, etc...
This is done for each user, host and team.
Click to expand...
Why are you updating yesterday's total with today's number?
 
igor_kavinski said:
May help if the DB engine can't take maximum advantage of available threads. There are two databases in your case but still only one DB engine (the brains) that may or may not be maxing out the available hardware resources. Splitting them into four VMs would ensure that four DB engines are able to work in parallel.
Click to expand...
Err, fixing the workflow is the right solution.
 
igor_kavinski said:
May help if the DB engine can't take maximum advantage of available threads. There are two databases in your case but still only one DB engine (the brains) that may or may not be maxing out the available hardware resources. Splitting them into four VMs would ensure that four DB engines are able to work in parallel.
Click to expand...

MariaDB (and most other database engines) can already run multiple parallel jobs concurrently without a problem. My dual database design already solves the biggest problem with tables being locked during updates. While that database has its tables locked for updates, the other database is free for the web server to utilize to fetch the data for users visiting the site.

Splitting the database up into separate instances is the fundamental issue I am having here. If I can split the database up into multiple VMs, and it speeds things up, then I can do that with a single instance as well. Which I've already tried splitting the data up into chunks to process concurrently. The problem lies with merging all that data back into a single data set which makes the whole process take 3x longer. If I did this with separate VMs, each with its own database and set of data, all that data will then need to be combined at some point for the overall credits stuff which is the process that takes by far the longest on the setup.

Which means this solution would probably take even longer as it'll have the overhead of multiple VMs, more network traffic for them to communicate with one another, etc...

During normal processing the CPU maxes out at one thread being fully utilized. This is because the actual processing of the data is single threaded. Read what happened when I split this up into multiple chunks. The processor maxed out a little over 4 fully utilized threads during this processing, but the end dropped back down to 1 fully utilized thread to merge all the data.
 
sdifox said:
Why are you updating yesterday's total with today's number?
Click to expand...

I'm not, todays total gets moved to the "yesterdays" column at the end of the day. The only thing that gets updated is the current update. That gets added to the todays total. The historical data doesn't get updated, it just gets moved to the appropriate location. IE: Todays total gets moved to yesterdays, yesterdays gets moved to 2 days ago, 2 days ago gets moved to 3 days ago, etc... for 28 days.

This allows the site to display:
Recent Update total
Todays total
Yesterdays total
2 days ago total
Last 7 days total
Last 28 days total


(By total, i mean the amount of points earned in that time period)
 
Skillz said:
I'm not, todays total gets moved to the "yesterdays" column at the end of the day. The only thing that gets updated is the current update. That gets added to the todays total. The historical data doesn't get updated, it just gets moved to the appropriate location. IE: Todays total gets moved to yesterdays, yesterdays gets moved to 2 days ago, 2 days ago gets moved to 3 days ago, etc... for 28 days.

This allows the site to display:
Recent Update total
Todays total
Yesterdays total
2 days ago total
Last 7 days total
Last 28 days total


(By total, i mean the amount of points earned in that time period)
Click to expand...
Err, maybe you should just have date and score table and generate the view based on today's date?

Let me think about it a bit and come up with a workflow that is a lot lighter than what you are doing.

Is there a global boinc team id?
 
Last edited:
Skillz said:
Splitting the database up into separate instances is the fundamental issue I am having here. If I can split the database up into multiple VMs, and it speeds things up, then I can do that with a single instance as well. Which I've already tried splitting the data up into chunks to process concurrently. The problem lies with merging all that data back into a single data set which makes the whole process take 3x longer.
Click to expand...
How about change the application queries so it knows from user ID which DB instance to send the request to? This way you never have to merge the data.
 
igor_kavinski said:
How about change the application queries so it knows from user ID which DB instance to send the request to? This way you never have to merge the data.
Click to expand...

The merged data is the total BOINC credits across all the projects. Having the application do this, on the fly every time totaling all these will just move the computation to the web server which would then definitely get overwhelmed with the number of bots that have grown exponentially over the last couple years. Even if I block all those bots (which I try to do, they're tricky) the regular user base checking the site on a daily basis will most like result in the same problem.

The overall boinc credits is stored in a table that gets calculated every time a project has an update. This means this calculation only happens once per database update and the web server just queries that total.
 
You must log in or register to reply here.
Become a Patron!
Back
Top