SaranacLake
-
Posts
648 -
Joined
-
Last visited
Posts posted by SaranacLake
-
-
19 hours ago, requinix said:
Okay. How about you write it, then come back and say what it is? Because like I said, depending on what it is, you may not need the subquery.
While I am doing that, care to give me a hint what the alternative might be? 🙂
-
On 2/20/2020 at 11:27 PM, kicken said:
The issue I think requinix is trying to point to is that if the article doesn't even exist, you don't want to show a 'This is a premium article.' error, you want to display a proper 404/article not found error. If you just display 'This is a premium article' in both cases, that will lead to confusion and frustration for your users.
If the article doesn't exist there's no need to do any extra work for determining if the user has a matching entitlement to go with that article so you can cut all that processing out and not waste time on it.
Don't nitpick your query count. One vs two queries will make absolutely zero noticible difference in the runtime of your code. If two queries make the program flow nicer, use two queries.
So can I run a quick query to see if the Article exists before moving forward?
For example...
SELECT id FROM article AS a WHERE a.slug = $articleSlug;
Then if the Article exists, I can do a second check to see if the user has permissions to view the Article.
Sound good?
-
1 minute ago, requinix said:
You might not need a subquery. Depends what that "too complicated" query was. So just let us see it.
It involves joining 3-4 tables - some with composites keys. I haven't written it yet.
Either way, query #1 will return a result set that might be anywhere from 1 to 15 "Category" values. (Since the results will vary, that I why I was thinking query #1 would be in the WHERE part of query #2.
Like...
WHERE a.category_id IN (query #1 results)
-
3 minutes ago, requinix said:
Eh, it's BBCode parsing. IPB doesn't want people doing that anymore. But I'll see if maybe something happened during one of our upgrades - some configuration getting screwed up, something like that.
I figured it was a parsing issue.
Since I am big on quoting people to give context, that one is pretty important - and even surpasses "Post #"! 🙂
3 minutes ago, requinix said:That aside, please don't use quotes for code. You don't need to emphasize what line something else. Just say it. The little bit you gain from pointing out a particular line isn't enough to lose out on the monospace and syntax highlighting.
Okay
-
I think I need a Subquery to solve my latest issue, but am unsure. (Boy am i rusty on SQL!!)
Simplified example...
Let's say that I have query #1 that joins a bunch of tables (too complicated to post here for now) and it's end result is the following...
Quote+---------+------+---------------+
| Plan | Ver | Category |
+---------+------+---------------+
| Basic | v2 | News |
| Basic | v2 | Articles |
| Basic | v2 | Op-Ed |
+---------+------+---------------+Now for query #2 - the one I need help with - I am trying to do something like this...
SELECT *
FROM article AS a
WHERE (a.slug = $urlSlug) AND {a.category_id is equal to one of the Category values returned in Query #1)
-
Here is a screenshot after I submitted the above post.
Notice how the text that I had added AFTER the [ quote ] tags got slurped up into the quote?
That is the bug, and it really FUBAR'd my response to you last night since I had multiple quotes from your post then my responses
-
17 hours ago, requinix said:
Screenshot of the post submit textbox thing before you submit it?
Above is a [ quote ] from using the "Quote" button.
My response: No, I didn't take a screenshot, but let me see if I can recreate it now....
Use-case
- Do this
- Do that
- Then this...
- End of use-case
This text should be outside of the [ quote ] above...
-
Thanks for the response.
Last night I sat down and started drawing things out more on paper, and is often the case, I discovered this is more complicated than what I had originally thought. (I have a bad habit of building gold-plated toilet seat ND toilets AND bathrooms!)
That being said, I'm not sure what Requinix and I were arguing about is relevant anymore, but I will go back and make sure I don't have the issue that you pointed out, because - in general - I agree that error-messages need to be SPECIFIC - and thus accurate.
I am posting a question in the MySQL forum on how to do new query that I think I will need.
To be continued, and thanks again!
-
This is a bug I have encountered before and is rather infuriating as it trashed lots of typing and formatting.
If you quote someone (or add your own quote) and then add some text outside the quote, everything gets slurped up into the first quote which creates a real FUBAR at times.
I added a quote tag around my use-case so i could format key parts using color which I cannot do using the [ code ] tags.
The text I added below this got slurped up into the quoted use-case.
And prior to that it slurped up quotes from Requinix and then my responses created a couple layers of nested quotes which is NOT what I told it to do.
Had to delete my entire post and strat over, and as you can see at the end of the link above, even my 2nd attempt got FUBAR'd as well.
Infuriating.
You have been warned...
-
42 minutes ago, requinix said:
But what if the article doesn't even exist? How can you tell the user it's a premium article when you haven't actually looked for the article?
My use-case above clearly addresses this issue.
Here it is again...
Quote- User clicks on an Article link...
- "article.php" launches.
- Script grabs "Article-Slug" from URL.
- Script checks to see is user is logged in.
- IF ($loggedIn)
THEN
- Query for member's "membershipPlanID"
- Set $membershipPlanIDELSE
- Set $membershipPlanID = SomeFreeCode
ENDIF// This checks for the Article's existence AND for entitlement.
- Run this query to see if user has proper Article Entitlement...
SELECT e.id
FROM article_entitlement AS e
INNER JOIN membership_plan AS mp
ON mp.id = e.plan_id
INNER JOIN article AS a
ON a.id = e.article_id
WHERE (mp.id = $membershipPlanID AND a.slug = $urlArticleSlug)// This decides if the Article is retrieved and ultimately displayed, OR if an error message is displayed.
- IF Article is found
THEN
Display ArticleELSE
Display error "This is a premium article..."
ENDIFIn summary...
1.) Check for logged in User
2.) Determine "Membership Plan"
3.) Check for Article Entitlement
4.) Display Article or Error
What's dumb about that?
-
51 minutes ago, requinix said:
You shouldn't have to run a query just to see if the article is free: didn't you already run a query to look it up in the first place?
When a user clicks on an Article link, I am trying ascertain whether or not they have rights to see the Article BEFORE I go grab the Article from the database. (Why waste time and resources grabbing an Article is the user is not allowed to see it? Unless I wanted to show the first paragraph as a teaser maybe...)
51 minutes ago, requinix said:The solution about setting up some situation with entitlements and something about outer joins?
Let me turn that around on you: what's wrong with my solution?
Maybe you missed what I said here...
https://forums.phpfreaks.com/topic/310084-article-entitlements-for-free-articles/?do=findComment&comment=1574685 (Yet another need for a Post #) 😉
(No outer join - that was an earlier post and a bad idea.)
-
11 minutes ago, requinix said:
Thoughts? My thoughts are why you're making this so complicated. If the article is free then the article is free. Entitlements has nothing to do with it. Completely and totally irrelevant.
Actually, I think you are over-simplifying things...
1.) My design is NOT binary. A "Platinum" Member will have access to Articles that a "Gold" Member might not have access to.
2.) There is also the issue that if a Member is not logged in, then I don't know what Membership Plan they have, so I have to treat them as an unregistered Guest.
3.) Some articles might be viewable by everyone (i.e. Guest, Silver, Gold, Platinum) and other Articles will be viewable by other combinations (e.g. Silver, Gold, Platinum or Platinum-only).
4.) Also is the the fact that i am trying to be efficient with my code and not query the database TWICE - once to see if the article is free and a second time to see which entitlements apply.
What was wrong with the solution I posted above?
(I thought it was consistent with what you said, while tying loose ends together.)
-
How about this...
1.) I eliminate my MEMBERSHIP_PLAN_VERSION table because that is overkill for setting Entitlements for Articles. (This would make more sense when I give Webpage Entitlements.)
2.) Take this general coding approach...
Use-Case --------- - User clicks on an Article link... - "article.php" launches. - Script grabs "Article-Slug" from URL. - Script checks to see is user is logged in. - IF ($loggedIn) THEN - Query for member's "membershipPlanID" - Set $membershipPlanID ELSE - Set $membershipPlanID = SomeFreeCode ENDIF - Run this query to see if user has proper Article Entitlement... SELECT e.id FROM article_entitlement AS e INNER JOIN membership_plan AS mp ON mp.id = e.plan_id INNER JOIN article AS a ON a.id = e.article_id WHERE (mp.id = $membershipPlanID AND a.slug = $urlArticleSlug) - IF Article is found THEN Display Article ELSE Display error "This is a premium article..." ENDIF
Similar to what @requinix said above, then I would then have to assign all "free" Articles to a "Membership Plan" called "FREE".
Thoughts?
-
Like most things, there are probably a couple of different ways to solve this problem.
I am trying to write some pseudo-code now, but am also thinking that an outer-join might be helpful...
-
Maybe some sample data would help better explain where I am stuck...
MEMBERSHIP_PLAN +------+-----------+ | ID | Name | +------+-----------+ | 1 | Silver | | 2 | Gold | | 3 | Platinum | +------+-----------+ MEMBERSHIP_PLAN_VERSION +----+---------+------------+------------+------------+ | ID | plan_id | version_no | start_date | end_date | +----+---------+------------+------------+------------+ | 1 | 1 | 1 | 2020-01-01 | 2020-02-29 | | 2 | 2 | 1 | 2020-01-01 | 2020-02-29 | | 3 | 3 | 1 | 2020-01-01 | 2020-02-29 | | 4 | 1 | 2 | 2020-03-01 | 2020-06-30 | | 5 | 2 | 2 | 2020-03-01 | 2020-06-30 | | 6 | 2 | 3 | 2020-07-01 | 2020-12-31 | +----+---------+------------+------------+------------+ ARTICLE_ENTITLEMENT +----+---------+------------+------------+ | ID | plan_id | version_no | article_id | +----+---------+------------+------------+ | 1 | 1 | 1 | 71 | | 2 | 2 | 1 | 71 | | 3 | 3 | 1 | 71 | | 4 | 1 | 2 | 71 | | 5 | 2 | 2 | 71 | | 6 | 2 | 3 | 71 | | 7 | 2 | 3 | 74 | +----+---------+------------+------------+ ARTICLE +-----+-------------------------------+-----------------------------+ | ID | slug | Title | +-----+-------------------------------+-----------------------------+ | 71 | using-indexes-to-tune-mysql | Using Indexes to Tune MySQL | | 72 | working-with-arrays-in-php | Working with Arrays in PHP | | 73 | preventing-sql-injection | Preventing SQL Injection | | 74 | file-handling-ing-php | File Handling in PHP | | 75 | using-var-dump-to-debug | Using var_dump to Debug | +-----+-------------------------------+-----------------------------+
Let's say that I want Article (id=73) to be available to everyone, including Guests, Paid Members that aren't logged in (aka Guests), and Paid members who are logged in regardless of which "Membership Plan" they have.
In my other thread (and above to a lesser degree), I explained how I would take the $memberID and see if a user has an "entitlement" for a given Article.
But since Article (id=73) is free, there are no entitlements.
So how would I change my table structure or my PHP code to handle this? (Again, I was planning on having a function at the top of my "article.php" script that always looks for the $memberID and then calls my checkArticleEntitlement function.
Not sure if my question is making sense?
-
48 minutes ago, requinix said:
ALTER TABLE ARTICLE ADD COLUMN is_free TINYINT(1) NOT NULL DEFAULT 0;
I don't see how that answers my question....
Maybe I should have posted in the PHP forum?
I went into greater detail in my last thread, but nobody responded, so I tried summarizing here.
-
Earlier this week i created a table layout to incorporate "entitlements" into my website, which looks like this...
MEMBER >|-------||- MEMBERSHIP_PLAN -||-------|< MEMBESHIP_PLAN_VERSION -||-------|< ENTTLEMENT >0-------||- ARTICLE
MEMBERSHIP_PLAN
- id (PK)
-name
MEMBERSHIP_PLAN_VERSION
- id (PK)
- plan_id (UK1)(FK)
_ version_no (UK1)
- start_date
- end_date
ARTICLE_ENTITLEMENT
- id (PK)
- plan_id (UK1)(FK1)
- version_no (UK1)(FK1)
- article_ID (UK1)(FK2)
ARTICLE
- id (PK)
- slug (UK)
- title
- body
In order to determine if a Member can view "premium" content, I have decided to....
1.) Take the logged in Member's $memberID and run a query (function) to get their "Membership Plan" (plan_id)
2.) Call another query/function like below to determine if the are entitled to view the "premium" Article...
SELECT e.id FROM membership_plan_version AS mpv INNER JOIN article_entitlement AS e ON (mpv.plan_id = e.plan_id) AND (mpv.version_no = e.version_no) INNER JOIN article AS a ON (a.id = e.article_id) WHERE ((CURDATE( ) BETWEEN mpv.start_date AND mpv.end_date) AND ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug)));
Now onto my problem...
How can I handle cases where an Article is "free" and therefore everyone is entitled to view it regardless of their "Membership Plan" or even if they are just a Guest??
My ARTICLE table obviously has to have *every* Article in it, since that is where the content resides!
But if my PHP script called a function/query like above, then for most of my Articles, there will NOT be a corresponding "Membership Plan" on the left to match to, and so free Articles would never appear.
Should I create a dummy MEMBERSHIP_PLAN record called "Free", and also make a dummy MEMBERSHIP_PLAN_VERSION record called "Free v1"?
Or something else?
I would say that 60% of my Articles are free and therefore should be accessible to everyone (i.e. Members and Guests), but the other 40% is restricted to various levels of "Membership Plans".
Hopefully my design above is still solid and just needs to be tweaked, but I'm not really sure how to tackle this issue.
Thanks i advance!
-
I responded to your very long post with some equally long responded.
No comments or answers to my questions?
-
-
Just now, requinix said:
Without going into details, the owners don't own PHP Freaks for the revenue. It generating any revenue at all is more of a nice to have - especially if it's enough to cover the hardware and hosting costs. As demonstrated by how long it took for us to put up ads - ads which appear to me to not be working again...
If they did, it would be no sin.
Sorry my comment came across the wrong way.
-
1 hour ago, requinix said:
Then I must have misread your post, because I was sure it said something like "if it makes the owner $$$, that is all he cares about". Or maybe I'm mistaken on the meaning of the word "greedy" and it does not, in fact, mean "having or showing an intense and selfish desire for something, especially wealth or power".
You read too much into what I said, and I could have worded it better.
Put another way, if you run a business your goal is to have a working system and make $$, which apparently your forum software accomplishes. That doesn't mean it is perfect - which it isn't - or that it will satisfy every user's need (e.g. Post #).
That's all.
At least you guys took into consideration my request for a Post #.
Thanks.
-
Just now, requinix said:
With enough people, time, effort, and skill, yes.
Our current owner is a significant improvement over our past owners. Kindly retract your implication that they are greedy, as it will have the added benefit of not making you look like an ass.
I *NEVER* said anything about anyone being greedy!
I was saying that if the software used on PHPFreaks helps the owner(s) to make $$$, then it is likely "good enough" from their perspective, even if it doesn't have a "Post #".
Nothing wrong said or implied there...
-
45 minutes ago, gizmola said:
What's missing from your list of tables is the table that describes the relationship between a member/user and the plan version.
Nope, it's there. See below...
MEMBER >|-------||- MEMBERSHIP_PLAN -||-------|< MEMBESHIP_PLAN_VERSION -||-------|< ENTTLEMENT >0-------||- ARTICLE
At this time, I just have a single field that stores whatever the Member's "Membership Plan" is at any given moment. (Although I am thinking it might be nice to have a history table of sorts so that I can easily keep track of what different Membership_Plans a Member has over time, but that is immaterial to this discussion.)
A "lookup" table table is always modeled as a One-to-Many where the"One" comes from the lookup table, and the "Many" side is on the parent table (e.g. "Member").
And that was my point above, that - as i recall - it makes it hard to have one query where you join "MEMBER" to "MEMBERSIP_PLAN" plus the other tables.
45 minutes ago, gizmola said:Barand is a SQL expert bar none.
Yes, he is very helpful!
45 minutes ago, gizmola said:Do what he said with the only caveat being, that the table relating a member to a plan version would be required to determine if a user should be able to access an article.
How about this...
When a Member is logged in, I always retain the "memberID" in my SESSION.
So I could have a PHP function called: checkArticleEntitlement($memberID){ }
I would pass in the $memberID which is always known for logged in users, then my query (pseudocode) might look like this...
SELECT e.id FROM membership_plan_version AS mpv INNER JOIN entitlement AS e ON (mpv.plan_id = e.plan_id) AND (mpv.version_no = e.version_no) INNER JOIN article AS a ON (a.id = e.article_id) WHERE ((mpv.plan_id = $memberID) AND (a.slug = $articleSlug))
Then, if COUNT>0 then that means the Member is "entitled" to view the Article, otherwise I display a gracious message and encourage them to upgrade!! 😄
If they are eligible, then i would query the ARTICLE table for the record where article.slug = $artcileSlug (which comes from the URL)
45 minutes ago, gizmola said:That 100% should be done with a single query that involves JOINS. As we discussed previously, with an adequate InnoDB buffer pool, repeated queries will come from the buffer pool cache, so there would be essentially no reading of data by the MySQL server.
(This is where a Post # would have been nice...)
So to my question above, I asked this...
2 hours ago, SaranacLake said:Let's say you have a database table with 10 columns and 10,000 rows/records. And let's say you need two pieces of inf from that table.
Scenario #1: You query the table to get row 8519, and grab both pieces of data at once.
Scenario #2: You query the table to get row 8519, and grab one piece of data. Then after confirming some condition is true. You go back and grab the second piece of data for row 8519.
On average, which scenario puts more effort/strain on your database?
(It's a contrived example, but I think very relevant to what I described above...)
Put another way, which impacts the database server more.... "Going wide" (i.e. returning lots of fields in a record OR "Going deep" (i.e. having to search a table for a record twice)??
For example, in my SQL above, I chose NOT to return the entire Article record UNTIL I know that the user is eligible to view it, so there I splits things up into two queries.
Thoughts on this, @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...
![:-\](https://forums.phpfreaks.com/uploads/emoticons/default_uncertain.gif ":-\")
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?! **
Article Entitlements for Free Articles
in MySQL Help
Posted
@kicken,
If you had to guess, do you think there is more overhead in running a query or in the data you retrieve?
In this case, articles will be from maybe 3 - 10 pages.