How much to store in SESSION variable?

[SaranacLake]

Currently I am adding the concept of "entitlements" to my website.

In the past, my "article.php" script would simply look to the URL for which article was being requested and then load it.  However now that I am also adding the concept of "premium content" for "paid members", I need a way to control who sees what.

What I am wondering is - from a security standpoint - how much information I should load into the $_SESSION variable.

For instance, right now when a user logs in, I think I just store the "memberID" and "FirstName" and possibly "Username".

It would be more efficient when a Member logs in to also retrieve their "Membership Plan" and store that in the $_SESSION variable, so that as they browse my website, each page can simply grab $_SESSION['MembershipPlan'] and run that through a function that I need to build and then determine if the user gets to access said page.

However, maybe it would be more secure to have it so when a user lands on page XYZ, I would look at their "memberID" and query the database to get their "MembershipPlan"?

Any thoughts on each approach?

Again, my main concern is *security*, but I also suppose this plays into "performance".

 

 

[Barand]

2 hours ago, SaranacLake said:

However, maybe it would be more secure to have it so when a user lands on page XYZ, I would look at their "memberID" and query the database to get their "MembershipPlan"?

Yes, that one.

[requinix]

If you store data in the session then you won't look it up in the database, and that means you won't know if it changes. If a user was logged in and you stored that they had a deluxe membership plan, what would happen if that plan changed? Or ended? You would continue using the deluxe plan and the user would have access they should not.

[SaranacLake]

10 hours ago, requinix said:

If you store data in the session then you won't look it up in the database, and that means you won't know if it changes. If a user was logged in and you stored that they had a deluxe membership plan, what would happen if that plan changed? Or ended? You would continue using the deluxe plan and the user would have access they should not.

I was more concerned that maybe someone could tamper with the SESSION variable, but you make a good point that I hadn't really thought about.

So how "expensive" is querying MySQL for trivial things like which "Membership Plan" a Member currently has?

My original thinking was that I am sending my script to the database TWICE - 1st to log in the user and 2nd to get their "Membership Plan" - and so maybe that was really inefficient?!

Thoughts?

 

 

[requinix]

1 minute ago, SaranacLake said:

I was more concerned that maybe someone could tamper with the SESSION variable, but you make a good point that I hadn't really thought about.

Unless you have a particularly bad setup, the user cannot tamper with their session data.

1 minute ago, SaranacLake said:

So how "expensive" is querying MySQL for trivial things like which "Membership Plan" a Member currently has?

Any good database system will cache data and queries. As long as you're using good database design and have appropriate indexes, it should not be expensive.

1 minute ago, SaranacLake said:

My original thinking was that I am sending my script to the database TWICE - 1st to log in the user and 2nd to get their "Membership Plan" - and so maybe that was really inefficient?!

Again: good database design and appropriate indexes.

[SaranacLake]

Just now, requinix said:

Unless you have a particularly bad setup, the user cannot tamper with their session data.

Any good database system will cache data and queries. As long as you're using good database design and have appropriate indexes, it should not be expensive.

Again: good database design and appropriate indexes.

What constitutes "good database design"?

I think my databases are always modeled properly from a "data modeling" standpoint (e.g. logical, normalized, etc).

Not claiming to be a wizard on indexing and performance tuning, but since my database should be modeled okay, and since when this goes live if I had 10,000 users I'd feel like a mega star, I'm hoping things are more forgiving for me.

What say you, @requinix?

 

[requinix]

45 minutes ago, SaranacLake said:

What constitutes "good database design"?

Opinion. Preferably backed by benchmarks. Which then leads into things like "standards" and "best practices".

[SaranacLake]

Just now, requinix said:

Opinion. Preferably backed by benchmarks. Which then leads into things like "standards" and "best practices".

I am inferring that for a MySQL database/table with less than say 100,000 records, it isn't that big of a deal to run a query, right?

And so if a certain web page/script needed to make even a handful of calls to the database to get things like: "Membership Plan", "Number of Posts", "User Status", etc then that shouldn't really put much stress on MySQL, right?

 

[gizmola]

If your structure is normalized correctly you should be fine.

Make sure that your tables are using the InnoDB engine.

Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself.  This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index.

All primary and foreign keys are inherently indexed.  So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition.

This is the single most important detail.  Your queries need to use a relevant index.  An obvious example would be username & password columns in a user table.  Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query.  For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password.  

If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention.  MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation.  If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.

[SaranacLake]

3 minutes ago, gizmola said:

If your structure is normalized correctly you should be fine.

Yes, my database is normalized.

 

3 minutes ago, gizmola said:

Make sure that your tables are using the InnoDB engine.

My project has been on hold for so long, I forget what I used.

How can I tell?  Is there an easy way to tell in phpMyAdmin?

 

 

3 minutes ago, gizmola said:

Any queries that can be satisfied by using the primary key index return all data in the record by reading the index itself.  This is because InnoDB has "clustered indexes" which is a fancy term meaning that the data is actually stored in the primary key index.

You lost me.  All of the record data is stored in the PK index?

 

3 minutes ago, gizmola said:

All primary and foreign keys are inherently indexed.  So the main concern in regards to other required indexes would be queries that have where clauses that aren't keys in the table definition.

This is the single most important detail.  Your queries need to use a relevant index.  An obvious example would be username & password columns in a user table.  Essentially you just need to focus on the WHERE clauses of your queries and make sure that you have an index to handle the query.  For example, if you always query for user by username & password, have an index on username, password, and not individual indexes on username and password.  

Interesting!

 

3 minutes ago, gizmola said:

If you have a monolithic server (webserver, php, mysql running on one box) then you have to plan for contention.  MySQL needs some tweaking to provide a sufficient InnoDB buffer pool allocation.  If it's a small DB as you describe your entire dataset can be in the buffer pool cache the majority of the time.

This is a for a website I am building which is a combination of online newspaper where paid users can post comments and an ecommerce site.

It will sit on a VPS with 8GB RAM, and the whole website and database reside on the VPS.

If I had 10,000 users I would be tickled pink, so I think my website and database will fall on the small side - of course I hope that changes!!

 

[SaranacLake]

P.S.  I have a question about how to implement my entitlements.  Should I ask here or start a new thread?

[gizmola]

In phpMyAdmin, when you look at the list of tables for a database the "type" column shows you the engine.  If you did not explicitly set the engine to use the InnoDB engine you will likely have all tables using the default MyISAM engine.  You'll need to alter that for each table using a SQL statement:

ALTER TABLE TableName ENGINE = InnoDB;

MyISAM is not ACID compliant.  It also will have thrown away any foreign key constraints, so if you had or wanted those you'd need to recreate them individually after you altered the tables to use InnoDB.  

Quote

You lost me.  All of the record data is stored in the PK index?

Correct.  For InnoDB tables.  What that means from a performance standpoint, is that if there's a query that uses the primary key index, the entire data for the row gets read in the same operation that searched the index.  I only bring this up since you asked about performance, and is one of the many reasons that MySQL DBA's are fairly unanimous in favoring the use of InnoDB.

[gizmola]

19 minutes ago, SaranacLake said:

P.S.  I have a question about how to implement my entitlements.  Should I ask here or start a new thread?

New thread would be appropriate.

[SaranacLake]

Just now, gizmola said:

In phpMyAdmin, when you look at the list of tables for a database the "type" column shows you the engine. 

Ah, right.  Yes, "InnoDB" for all tables in my database, so it sounds like I am safe from a performance standpoint.

 

Just now, gizmola said:

Correct.  For InnoDB tables.  What that means from a performance standpoint, is that if there's a query that uses the primary key index, the entire data for the row gets read in the same operation that searched the index.  I only bring this up since you asked about performance, and is one of the many reasons that MySQL DBA's are fairly unanimous in favoring the use of InnoDB.

So InnoDB is fairly efficient, dare I say "competitive", when it comes to indexing and performance as compared to SQL Server and Oracle, or am I dreaming?

 

[Barand]

Years ago I wrote a web page (using VB with SQLServer) which showed all bus stops within 200m of your location (this could be changed by a slider) with their scheduled departure times/destinations in the next hour. This was fine out in the 'burbs where there might only be a handful of stops inside the search area. In the city centre it was a different picture with hundreds of stops and thousands of journeys. If the search radius exceed 50m it was timing out after 30secs.

This was at a time when I had started using PHP but my bosses were against it (cos it wasn't Micro$oft). I migrated the required database tables so that I had a MySQL version and rewrote the VB code in PHP. Absolutely no problem in the city centre until I wound up the search radius to 2 Km. That was the last time I used VB.

[gizmola]

4 minutes ago, SaranacLake said:

Ah, right.  Yes, "InnoDB" for all tables in my database, so it sounds like I am safe from a performance standpoint.

 

So InnoDB is fairly efficient, dare I say "competitive", when it comes to indexing and performance as compared to SQL Server and Oracle, or am I dreaming?

 

MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used.  All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index.  

FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL.  MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona).  PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years.  Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL.  Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005.

The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL.  Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB.  This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.

 

[SaranacLake]

Just now, gizmola said:

MySQL/InnoDB is highly performant which is one of the reasons (along with simplicity) it continues to be used.  All RDBMS's depend on indexing, and the fundamental index type they all provide and implement is the Btree index.  

So, in broad terms, do you think you can tune a small-to-medium MySQL database and have it compete with SQL Server or Oracle?

Or do those enterprise RDBMS have superior internals including how they index?

In the early 2000s, MySL bragging rights were that it was used by companies like Facebook (?), Wikipedia, and some other big ones.

 

Just now, gizmola said:

FWIW, I have extensive experience with Sybase/Sql Server, Oracle and MySQL.  MySQL is unique in that it has alternative engines, as well as forks (MariaDB, Percona). 

If you were starting a business, would you stick with MySQL or go with MariaDB?  (Aside from fears of Oracle doing something stupid, it seems like MySQL is a safer way to go...)

 

Just now, gizmola said:

PostgreSQL is often preferred by people coming from Oracle, as it is very similar down to database objects, datatypes and the stored procedure/trigger language (PL/SQL) that are foundations of Oracle use through the years.  Sybase/SQL Server were also popularized through use of the stored proc/trigger language T-SQL.  Comparatively, MySQL didn't even have Stored Procedures/Triggers until version 5.x beta'd in 2005.

The bigger question in the open source world is not MySQL vs the commercial DB's but rather MySQL vs PostgreSQL.  Even more recently many RDBMS alternatives have come into the mainstream under the NoSQL or Document DB monikers, most notably MongoDB and CouchDB.  This has to do with concerns about scalability, which you don't have reasonable expections will be an issue for you that can't be solved with a bigger server.

Back in early 2000s, PostgreSQL was supposed to be the up-and-coming database.  In the last 10 years t seems to have fizzled.

Last I heard, PostgreSQL was way more mature than MySQL, but that was light-years ago in IT terms.

I would like to switch t PostgreSQL because I figure it might be a way to learn more entreprise-esque database design and coding, but then there is the fear that online forums like PHPFreaks have all but dried up on the Internet, and so you'd be screwd if you needed any kind of support.

There is also the fact that MySQL (and now mariaDB) is the preferred choice of nearly every webhost out there.  (My webhost will help me to some degree with MySQL issues, but I think I'd be hard-pressed to get them to help me with PostgreSQL questions, let alone installing it on my VPS?!

In summary, I am thinking MySQL is my safest way to go, and IF I ever outgrow it, at that point maybe I'll have the $$$ ot resources to choose something more sophisticated?

 

 

[gizmola]

All the DB's I listed use the same basic Indexing technology.  Indexing = query performance. 

In general, no traditional business would do what you plan to do, even if that is very common for small businesses running internet sites.  Monolithic servers just aren't done for any of the commercial DB's.  The RDBMS world assumes that the RDBMS will run on a dedicated server, with attached storage or perhaps a NAS or SAN.  The majority of available server RAM is allocated to the DB, and nothing else runs on it.  You do not want to experience a scenario where the database engine itself might be swapped to disk so that some other set of programs can run.  In your monolithic (one server LAMP?) setup, you will not be insulated whatsoever from that happening without careful tuning and configuration.  Most probably it will happen at some point.  

The other rule of thumb for system tuning is that initially, your scalability issues will bottleneck at application server RAM.  With PHP that is either just Apache/Mod_php, or php-fpm.  In other words, the client connections/php threads will bottleneck before the DB queries will.  This is typically why you want a cluster of application/web servers and a dedicated DB server.  If you find you need to add application server#2 does your application even work?  How will load be balanced?  This effects a number of application level configuration and code decisions you might make.  For example, to start with, how will you scale sessions?  It is much easier to have a basic architecture that has a degree of scalability to begin with, than it is to try and figure out how to fix things when your business no longer works, or is inaccessible due to load that's crashing it, and your only option is to try and move things to a larger server with more resources.  

Often business system design has historically depended on moving some portion of business rules to the database as trigger/sproc code.  Oracle and Sybase/MS Sql Server (Sql Server is a licensed fork of Sybase) built there reputations on the capability and performance of sprocs & triggers.  Since you haven't mentioned those this seems like a non-factor.

One of the other things important to you, since your codebase is in php, is client library support in PHP.  MySQL connections are very lightweight and performant when compared to something like Oracle.  This works very well with typical PHP models where connection pools are not important.  The Oracle connection process is not lightweight.  Once an Oracle connection is made, typically one or more sessions are used, so it's a very different paradigm that doesn't match PHP very well.  This is why you usually see Oracle paired with enterprise java server technology where a pool of db connections will be maintained in the java application server(s).  

I don't think your assessment of PostgreSQL is correct.  It is certainly very active and growing.  ISP's prefer MySQL because of the simplicity of installation and small footprint.  This is the same reason it was used by Facebook and Wikipedia initially.   With that said, I'm by no means suggesting you should switch to PostgreSQL without a strong driving reason to do so.   I would stay with MySQL, in whatever flavor you would like.  The 3 that people are using (Oracle MySQL, MariaDB or Percona) will all work for you.  If it's the latest/greatest of each there isn't a huge advantage right now for one over the other, from a performance or technology standpoint. 

The other things that often concern businesses are backup/recovery and fault tolerance.    Another important reason to use InnoDB involves recovery from crashes.  What happens when MySQL with MyISAM crashes?  How do you get things back up and running if it does?  I recommend scanning the answers to this dba.stackexchange question.   The key fundamental to understand is the transaction log: something that Oracle, SqlServer, PostgreSQL and InnoDB share.    How much data loss can your business tolerate?  Hosting companies often provide you limited fault tolerance.  I have had for example, an entire AWS EBS volume die with no recovery possible.  Over the holiday weekend, a Linode VPS had it's storage die and the entire server had to be migrated.   If this was important business data, there could very well have been data loss.  

From what you've alluded to, you have a SAAS business of some sort, with access to features via paid memberships.   I personally would not advocate launching this on a monolithic server.  Instead, I would build it on a small cluster. You could start with the the smallest 2 machine cluster available.  Rather than running on one "large" server with 8gb you could instead start with 1 medium server allocated to mysql and 1 medium application server.  You can start with DNS round robin for load balancing to additional application servers as needed and migrate to load balancing in the future.  Monolithic servers are ok for hobby or marketing sites, blogs and other non-transactional sites that are 99% read only.  If you have transactional systems with a mix of read/write transactions you are starting out with essentially a non-scalable architecture.  The only way to know for sure how much capacity you have or how your system will break is to load test, but rarely is this task performed.

[SaranacLake]

@gizmola,

Wow!  Your long response makes me teary-eyed for the days of past when people actually talked and had conversations of substance on the Internet?!  (Excuse me while I get a tissue...)

 

 

12 minutes ago, gizmola said:

All the DB's I listed use the same basic Indexing technology.  Indexing = query performance. 

Okay.

 

12 minutes ago, gizmola said:

In general, no traditional business would do what you plan to do, even if that is very common for small businesses running internet sites.

What am I doing?  

 

12 minutes ago, gizmola said:

Monolithic servers just aren't done for any of the commercial DB's.  The RDBMS world assumes that the RDBMS will run on a dedicated server, with attached storage or perhaps a NAS or SAN.

I hope to get there someday soon, but I cannot justify that, nor do I need it now.

 

12 minutes ago, gizmola said:

The majority of available server RAM is allocated to the DB, and nothing else runs on it.  You do not want to experience a scenario where the database engine itself might be swapped to disk so that some other set of programs can run.  In your monolithic (one server LAMP?) setup, you will not be insulated whatsoever from that happening without careful tuning and configuration.  Most probably it will happen at some point.  

Interesting tangent that I have been wondering about...

If I had to guess, based on the website that I am building, I would have said that 85% of my resources will go to serving up HTML/PHP web pages and the images on them, and that database resources will be minimal until if/when I have a really rocking website.  After all, my database only hold things like user credentials, member profiles and preferences, and my articles and other content.  (Images are relegated to a directory.)

Of course I have no real metrics to go off of yet, but I stand by my guesstimates above.

Can you explain to my why you feel/know that the paradigm is flipped, and that database resources are much more intense than webserver resources?

 

 

12 minutes ago, gizmola said:

The other rule of thumb for system tuning is that initially, your scalability issues will bottleneck at application server RAM.

Right, that is what I said above and am guessing will be the case.

 

 

12 minutes ago, gizmola said:

With PHP that is either just Apache/Mod_php, or php-fpm.  In other words, the client connections/php threads will bottleneck before the DB queries will.  This is typically why you want a cluster of application/web servers and a dedicated DB server.

Well, if I wanted to spend the $$$, my webhost will gladly sell me a configuration with multiple dedicated webservers and a dedicated database server.

 

 

12 minutes ago, gizmola said:

If you find you need to add application server#2 does your application even work?

I have no clue how to answer that....

My webhost offers load balancers and does all of the configuration.  (Not sure if they offer that for VPS's - I'm guessing you need to buy dedicated servers for that.

 

 

12 minutes ago, gizmola said:

How will load be balanced?

Either a shared or dedicated load balancer if I upgraded.  I am banking on 8GB of RAM being enough on my VPS for now.  (And IF I could just ever finish getting this stupid website coded, THAT would be the true miracle?!)  

 

 

12 minutes ago, gizmola said:

This effects a number of application level configuration and code decisions you might make.  For example, to start with, how will you scale sessions?  It is much easier to have a basic architecture that has a degree of scalability to begin with, than it is to try and figure out how to fix things when your business no longer works, or is inaccessible due to load that's crashing it, and your only option is to try and move things to a larger server with more resources.  

As an Analyst by day, I agree 110%.  The problem that I face - and always have - is that I can't seem to keep up with technology, let alone the world.  (Maybe all of your gurus here can, but I find it nearly impossible to master HTML, CSS, PHP, responsive design, MySQL, SQL, database design, website design, everything related to web hosting, security, and a bunch of other things.

Most people think I am nuts trying to be CEO and janitor, but to me that is the only way to be a true entrepreneur and a true business/technical guru.  (But every year the world goes faster and faster, and it's getting nearly impossible to keep up?!  

And now you pose some VERY good questions, for which I have no knowledge or solutions.  (I hope this isn't another, "Well, go back to studying new topic XYZ, and in 2-3 years you will have the basics down!"  If I don't get my cod-base wrapped up and my website up by say summer 2020, then I should just hang up my hat and close shop because it's taking too long.

One reason it's taking me so long is I love to get into these intellectual challenges/thought-games about how to build a better mouse-trap, but I was kind of oping I have done enough of that and if I can just finish the e-commerce module for my website, then I will have enough for v1.0

You are starting to make it sound like that isn't the case...  

 

 

12 minutes ago, gizmola said:

Often business system design has historically depended on moving some portion of business rules to the database as trigger/sproc code.  Oracle and Sybase/MS Sql Server (Sql Server is a licensed fork of Sybase) built there reputations on the capability and performance of sprocs & triggers.  Since you haven't mentioned those this seems like a non-factor.

I'm not really sure what that means, but I have often wondered for v2.0 - assuming you don't crush me in this thread?! - that maybe i should learn how to do more advanced database work in MySQL for example...

Currently I would write a PHP script that reads like a linear use-case because that is my procedural background - not modern, but I'd argue it still works just fine.  And in a script, say "article.php" like we have been discussing, i would have a long block of code where my PHP sets up and runs a MySQL query and then takes the results and does something (e.g. display an article, determine if a member is eligible to see the article, etc.)

I *think* the way a high-priced oracle developer would approach things is to store the "business logic" in either a Trigger or a Stored Procedure (written in PL/SQL) and then when so event happens the Trigger would fire or maybe you'd just pass arguments to the Stored Procedure and it would run on the database and return just the results set.

Is that sorta what you mean?

To me, the benefit of that is again 'component architecture" where you "write once, and run many times".  (I know that I am a serial offender for writing code/queries in my PHP 100 times instead of maybe creating a PHP function or a database object (e.g. trigger, stored procedure) and reusing them.)

But I wouldn't be here asking for help if I was that smart?! 

 

 

12 minutes ago, gizmola said:

One of the other things important to you, since your codebase is in php, is client library support in PHP.  MySQL connections are very lightweight and performant when compared to something like Oracle.  This works very well with typical PHP models where connection pools are not important.  The Oracle connection process is not lightweight.  Once an Oracle connection is made, typically one or more sessions are used, so it's a very different paradigm that doesn't match PHP very well.  This is why you usually see Oracle paired with enterprise java server technology where a pool of db connections will be maintained in the java application server(s).  

Yeah, that is way out of my league!!

 

12 minutes ago, gizmola said:

I don't think your assessment of PostgreSQL is correct.  It is certainly very active and growing.  ISP's prefer MySQL because of the simplicity of installation and small footprint.  This is the same reason it was used by Facebook and Wikipedia initially.

Well, I know that the Internet - as I knew it - is dying/dead.  (If I wanted to sell my soul and get on Facebook/Twitter/Slack then it might be better, but I refuse.  Plus you will NEVER see people have long, drawn-out, dare I say "intelligent", conversations on social media like on a good OLD-FASHIONED user-forum like PHPFreaks!!!

That being said, I would be really *nervous* about switching to PostgreSQL and getting stranded - although I do see you guys have a forum here.

As far as the product, yes, I think PostgreSQL is still growing.

 

 

12 minutes ago, gizmola said:

 With that said, I'm by no means suggesting you should switch to PostgreSQL without a strong driving reason to do so.   I would stay with MySQL, in whatever flavor you would like.  The 3 that people are using (Oracle MySQL, MariaDB or Percona) will all work for you.  If it's the latest/greatest of each there isn't a huge advantage right now for one over the other, from a performance or technology standpoint.

Unless you or other experts here show me differently, I am MUCH more concerned about the webserver aspect of my webhosting situation, versus what you say about a potential need for a dedicated database server early on.

Over Christmas i was working on a photo website to share Holiday photos, and I quickly learned how things like photos can kick your *SS!!!  (I think I am much more concerned about re-tooling my website to use something like AMP versus running off to buy a dedicated database server.) 

If I ever "go live", and my v1.0 survives, then what I use for a database in v2.0 will largely be due to which environment lets me do fairy robust database concepts like Check Constraints, Triggers, Stored Procedures, etc.

 

 

12 minutes ago, gizmola said:

The other things that often concern businesses are backup/recovery and fault tolerance.    Another important reason to use InnoDB involves recovery from crashes.  What happens when MySQL with MyISAM crashes?  How do you get things back up and running if it does?

Now you see why I feel like I will never get this business going...    How can one person manage all of this stuff?!

I have been looking into backups, but I really have no clue how you would do a DATABASE recovery on a bus website.  (My simpleton solution is to have WHM/cPanel do a sh*tload of backups (e.g. hourly) and hope that that along with running RAID is enough to cover your butt.

But if it was Black Friday and I have 500 concurrent people checking out and my database and/or website went down....  I perish the thought?!

 

 

12 minutes ago, gizmola said:

I recommend scanning the answers to this dba.stackexchange question.   The key fundamental to understand is the transaction log: something that Oracle, SqlServer, PostgreSQL and InnoDB share.

This is awesome advice, but honestly getting to a point where it's asking a Honda to tow a semi-trailer.

A lot of the things you are mentioning here - while all VERY valid - are things that I watch entire teams and departments at my client's sites at work struggle to get down, and they have 5, 10, 50, 100 people working on all of these things.

There is no way little ol' me can do all of these things any time soon.

About the best I can do is 1.) Design solid, scalable, flexible solutions to the best of my ability, and 2.) work iteratively like we do using Scrum/Agile.

 

 

12 minutes ago, gizmola said:

 How much data loss can your business tolerate?  Hosting companies often provide you limited fault tolerance.  I have had for example, an entire AWS EBS volume die with no recovery possible.  Over the holiday weekend, a Linode VPS had it's storage die and the entire server had to be migrated.   If this was important business data, there could very well have been data loss.

Tough question.

Again, my biggest challenge is trying to finish this website which has taken me years...  (I certainly have bitten off more than i can chew with my subscription and e-commerce modules, but IF I can get them done, that is how I can really start making some $$$.)

My web host is not only pretty big, but here in the U.S. and always there 24/7 to help.  They offer off server backups on my VPS now, and if I had a dedicated server(s), then I'm sure they offer as much as I can afford.  (Which is limited until I get my site up and hopefully start making some money?!)

I think I will be okay with back and data recovery solutions - I think they challenge is how quickly can "I" recover my website or database if it died on a busy day/night.  And based on things you said above, I probably do NOT have robust enough solutions to prevent someone who is registering or checking out from when my website goes down to not suffer from a broken transaction (figuratively).

Again, one step at a time....

 

12 minutes ago, gizmola said:

 From what you've alluded to, you have a SAAS business of some sort, with access to features via paid memberships.

The simplest way to describe what I am doing - and without showing too much of my cards - is a combination of the NY Times and a modest e-commerce site.  There will be a fair amount of free content, but like the NYT or Wall Street journal, the good stuff requires a paid subscription.  In addition, there is lots of other content (e.g. books, guides, etc) that you can buy in my online stores - most of it online content, and probably some of it printed.

 

12 minutes ago, gizmola said:

 I personally would not advocate launching this on a monolithic server.

Good advice, but I have no budget for that.  More importantly, there is no business justification.  For example, let's say i took and was able to implement *everything* you advise here, what is to say that other than a few crickets, anyone would even come to my website?  (I once spent a month building a website for people I knew to check out some cool videos and photos from the area, and I had like 3 people even visit the website.  After that, I have been humbled and now see that what is "cool" to me the rest of the world might very likely not give a flip about?!)

 

 

12 minutes ago, gizmola said:

Instead, I would build it on a small cluster. You could start with the the smallest 2 machine cluster available.  Rather than running on one "large" server with 8gb you could instead start with 1 medium server allocated to mysql and 1 medium application server.

I am definitely making mental notes here, and I REALLY appreciate all of your advice - you definitely sound like you know your stuff!

I am still think that i will need more upfront as far as two webservers than help on the backend, but I could be totally wrong.

How much do you think going from a VPS with 8GB of RAM to a dedicated server with say 8GB RAM changes things?  (I was told buy a seemingly knowledgeable tech to not go above 8GB on my VPS, but if I needed more power to switch over to a dedicated server at that point.

Of course I would love to have 2 webservers and 1 dedicated database server, but that'd likely run me $500/month and not even having a website online, let alone customers, it's hard to think that far ahead.

 

12 minutes ago, gizmola said:

You can start with DNS round robin for load balancing to additional application servers as needed and migrate to load balancing in the future.

If I can afford 2 dedicated servers, I think they have a plan where their shared hardware load-balancer isnt that much more.

 

12 minutes ago, gizmola said:

Monolithic servers are ok for hobby or marketing sites, blogs and other non-transactional sites that are 99% read only.  If you have transactional systems with a mix of read/write transactions you are starting out with essentially a non-scalable architecture.  The only way to know for sure how much capacity you have or how your system will break is to load test, but rarely is this task performed.

I don't know, what kind of site would you say i have?  Better yet, what are some examples of "transactional" sites?  (Is PHPFreaks a "transactional" website?)

I described earlier how and where I use MySQL.  Since my website uses no javascript, and have no SH*T on it (e.g. WordPress, add-ons, advertising, marketing pop-up crap, etc), my hope is that the only bottle-neck is serving up images.  (And I think AMP can solve most of that.)

How much energy can logging into your account or serving up a 4 page article be on my database?  (Serving up four 300KB photos could cause issues, though.)

 

** Here is hoping this mega-long reply doesn't break your forum software?!  **

 

[SaranacLake]

@gizmola,

I responded to your very long post with some equally long responded.

No comments or answers to my questions?