Old 02-18-2014, 05:57 PM   #1
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Website/database question

Basic question but I am curious as to the responses:

On a website, if you have a large database in the backend, and a profile page that populates with data from the backend, is it generally the case that the profile page must requery the database every single time the page is loaded?

What if the database is huge? Won't this result in lots of overhead per page refresh?
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 06:20 PM   #2
noname219
FFR Wiki Admin
Wiki Administrator
FFR Wiki StaffFFR Veteran
 
noname219's Avatar
 
Join Date: May 2007
Location: Quebec, Canada
Age: 29
Posts: 1,675
Send a message via Skype™ to noname219
Default Re: Website/database question

Well, there are some websites that manage to do a few things to avoid overloads.
One example is RYM and their charts. Basically, every 1-2 week, a snapshot is taken of every single album in the database, they use that data to produce a ranking with an algorhythm that weights user ratings. That's all I know.
noname219 is offline   Reply With Quote
Old 02-18-2014, 06:27 PM   #3
Izzy
Frau Bow
FFR Simfile AuthorFFR Veteran
 
Izzy's Avatar
 
Join Date: Jan 2003
Location: Kansas
Age: 29
Posts: 9,197
Send a message via AIM to Izzy Send a message via MSN to Izzy Send a message via Skype™ to Izzy
Default Re: Website/database question

You should ask Houkounchi how we manage smo queries because the database is getting huge at this point. Something with indexing fields that get queried a lot and caching.

Random article about database optimizing.
http://www.techfounder.net/2011/03/2...neric-version/
__________________
Join SMO IRC. irc.rizon.net#smo
Izzy is offline   Reply With Quote
Old 02-18-2014, 06:32 PM   #4
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

A database's whole job is to perform well despite the size of its data. Indexing key fields helps queries speed up without row count or data size hindering much, etc. A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-18-2014, 06:46 PM   #5
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

Quote:
Originally Posted by arcnmx View Post
A database's whole job is to perform well despite the size of its data. Indexing key fields helps queries speed up without row count or data size hindering much, etc. A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed.

This is what I had in mind -- I figured that ideally, indexing should be making the queries fast anyway.

Also do you have examples or more to elaborate on by "A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed."?

Doesn't this mean you are double-storing data in some form?
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 06:47 PM   #6
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

It often means you cache the resulting html for commonly-requested pages in ram so you can just shove it right out instead of running php/queries/etc. to generate it again. Look up things like memcached.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-18-2014, 06:53 PM   #7
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

Quote:
Originally Posted by arcnmx View Post
It often means you cache the resulting html for commonly-requested pages in ram so you can just shove it right out instead of running php/queries/etc. to generate it again. Look up things like memcached.
Does this generally result in a lot of storage / is this sort of thing commonly used?

Or do most people just try to intelligently index their databases and just perform repeated queries from the relevant pages?
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 06:59 PM   #8
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

It's very common, yes. Whether it results in a lot of memory usage is up to you, these things are often configurable to cap at a certain amount and start discarding old data. Some solutions are completely automatic and cache entire pages, some like memcached let you decide how and when you want to cache data.

In general it's a great idea for things that get requested often but don't change often. Take FFR's homepage for example, caching the news posts instead of querying each time would benefit the site, the latest posts list, the video db list, things like that.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-18-2014, 07:09 PM   #9
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 07:32 PM   #10
choof
youtu.be/sM5hlrkaPSo
FFR Simfile AuthorD7 Elite KeysmasherFFR Veteran
 
choof's Avatar
 
Join Date: Nov 2013
Posts: 7,767
Default Re: Website/database question

i just want to say that i'm glad you guys enjoy databases because i sure as fuck don't
choof is offline   Reply With Quote
Old 02-18-2014, 07:51 PM   #11
TheSaxRunner05
The Doctor
FFR Veteran
 
TheSaxRunner05's Avatar
 
Join Date: Apr 2006
Age: 31
Posts: 6,004
Default Re: Website/database question

I'd love there to be a separate raw score Database so that raw scores would actually mean something. I know there is a raw score high scores page, but those aren't supplanted by raw score improvements.
__________________


TheSaxRunner05 is offline   Reply With Quote
Old 02-18-2014, 08:02 PM   #12
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?
Let's just say the entire thing could be done better. Due to poor design, counting scores is slow which is why the site caches your AAA/FC/etc. counts on your profile and the levelrank page updates it.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-18-2014, 08:10 PM   #13
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

What makes the design poor? (I am trying to get at the difference between poor vs. good design when it comes to something involving massive amounts of data such as the levelranks)
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 08:15 PM   #14
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

A lack of indexes, mainly, and a design that prevents easily switching to them. Dunno how much I'm allowed to disclose so I'm just going to leave it at that >.>
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-18-2014, 08:52 PM   #15
Velocity
Back to Retired.
Resident Owl
AdministratorRetired StaffDeveloperFFR Simfile Author
 
Velocity's Avatar
 
Join Date: Jul 2007
Posts: 1,751
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?
Each of FFR's levels are stored as a separate level in the database, so when doing levelranks, it grabs the entire song list, then loops every level getting your score from each table. So it ends up doing a massive amount of small queries. This is also why that page is cached for 15 minutes each time it's load.

It's terribly slow.
Velocity is offline   Reply With Quote
Old 02-18-2014, 09:16 PM   #16
Izzy
Frau Bow
FFR Simfile AuthorFFR Veteran
 
Izzy's Avatar
 
Join Date: Jan 2003
Location: Kansas
Age: 29
Posts: 9,197
Send a message via AIM to Izzy Send a message via MSN to Izzy Send a message via Skype™ to Izzy
Default Re: Website/database question

Maybe you could do some kind of massive join of all the levels tables and then query it all at once.
__________________
Join SMO IRC. irc.rizon.net#smo
Izzy is offline   Reply With Quote
Old 02-18-2014, 09:29 PM   #17
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

Quote:
Originally Posted by Velocity View Post
Each of FFR's levels are stored as a separate level in the database, so when doing levelranks, it grabs the entire song list, then loops every level getting your score from each table. So it ends up doing a massive amount of small queries. This is also why that page is cached for 15 minutes each time it's load.

It's terribly slow.
What's the smart way to do this? When you say it's a "separate level" in the database, do you mean a separate table? Like Level1 table, Level2 table, etc.

I thought having lots of small queries was technically a good thing with respect to datatable normalization?
Reincarnate is offline   Reply With Quote
Old 02-18-2014, 09:32 PM   #18
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
What's the smart way to do this? When you say it's a "separate level" in the database, do you mean a separate table? Like Level1 table, Level2 table, etc.

I thought having lots of small queries was technically a good thing with respect to datatable normalization?
The smart way is to not do this, just have one single table, and set up an index on the levelid/whatever to allow queries to work as well as if they were in separate tables, without the retardedness that comes with actually using separate tables.

Quote:
Originally Posted by Izzy View Post
Maybe you could do some kind of massive join of all the levels tables and then query it all at once.
This won't improve anything due to the lack of an index on the element you're querying (in levelranks case, userid). It would probably also max out memory usage and fail anyway.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge

Last edited by arcnmx; 02-18-2014 at 09:38 PM..
arcnmx is offline   Reply With Quote
Old 02-18-2014, 09:36 PM   #19
Velocity
Back to Retired.
Resident Owl
AdministratorRetired StaffDeveloperFFR Simfile Author
 
Velocity's Avatar
 
Join Date: Jul 2007
Posts: 1,751
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
What's the smart way to do this? When you say it's a "separate level" in the database, do you mean a separate table? Like Level1 table, Level2 table, etc.
Level1, Level2, Level3, .... Level2145

The table layout has changed over the years so indexes on older levels don't match the new levels.

Quote:
Originally Posted by Reincarnate View Post
I thought having lots of small queries was technically a good thing with respect to datatable normalization?
It's generally bad.

Also since I've only learned this stuff working on FFR, I have no idea whats better. I just change the queries and time the results and pick the consistently fast one.

Last edited by Velocity; 02-18-2014 at 09:41 PM..
Velocity is offline   Reply With Quote
Old 02-18-2014, 10:16 PM   #20
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

I find that interesting -- so you're saying it's actually better to have it all in one table with well-chosen indexes?

How do you then decide what to put into another table and what to lump together?

Although I guess it makes sense: If you have a table called "level_score_data" or something, it would just contain a key identifier (song ID or something) with the userid (person who played it) and the corresponding score. Then you could just query that to calculate the levelranks, yes?
Reincarnate is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off

Forum Jump



All times are GMT -5. The time now is 05:44 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright FlashFlashRevolution