Hosting Server questions

[psweb]

Putting this in the Coffee Lounge as it is un related to Wappler, suffice to say, if you are having issues with scripts, and thinking it might be a Wappler issue, after reading this you might consider testing the same thing on a different server before spending 2 weeks like me thinking your script is wrong.

For the last 2 weeks I have been in a battle with APIs, namely the Google Gmail API and OAuth, half the issue I have been having is getting the script to run even after the user has closed their browser window or left it open for that matter.
No matter how I tried I could not get the script to run from start to finish, considering the script could run for up to 4 hours or even more depending upon how much mail the user has in their gmail mail account.

I have altered timeouts in config files, and changed half my WHM / cPanel settings around and spent countless hours researching and testing, adjusting php.ini files, .user.ini, .htaccess files, honestly I think I have tried it all, including changing php versions and database versions.

I decided to rather do some very simple testing and here are some very strange results in my opinion, maybe someone here who is a little more server administrator savvy could advise.
Using the same server I have been fighting the API on I setup a very simple server action in Wappler

Steps
Settings : Script Timeout=7200
Database Connection
Database Insert to insert a single record with a value of 0 on script start
Set Value : Name=base1 : Value={{NOW.dateAdd("minutes", 5)}}
While : Condition={{NOW <= base1}}
While Steps : Condition={{NOW == base1}}
Condition Steps : Database Insert to insert a single value of 1

Here is a screenshot of the test described above

All the script does is insert a value of 0 into a database row and after 5 minutes it inserts another database row with a value of 1

For my real world test, I duplicated the steps 15 times so I land up with something like this

My expected result should produce 16 database row inserts with values from 0-15 spanning a 75 minute period, and should give the same results regardless of if the user keeps the browser window open or if they close it, if they do keep the window open it does give an error after exactly 15 minutes.
The Database table has 3 columns ID, Loop Number, Date Added, the ID is a normal auto incrementing ID, the Loop Number is where I am inserting my value of 0 through 15 depending on which step runs, and the Date Added field is an ON UPDATE CURRENT TIMESTAMP so I can see that the times are exactly what I expected.

Actual Results
On first run it ran for 35 minutes (8 database entries)
On second run it ran for 30 minutes (7 database entries)
On third run it ran for 55 minutes (12 database entries)
On fourth run it ran for 65 minutes (14 database entries)

Obviously even though the results never ran the entire script even once, the stranger thing was that with no alteration to the script or browser used or any other environment variable, each test produced a different result, which led me to believe there is something terribly wrong with my dedicated server or ISP.

I assume considering the test does not rely on my machine, nor browser, nor internet connection after it has been initialised, and considering it does not rely on outside sources like a possibly expiring API Token, that this script should really be a fairly simple thing for my server to run, and if it failed, it should have at least produced the exact same results on each test.

Just a note, because the server is faster at writing a database record than 1 second, most of the database inserts with a value of say 1 or 2 or 3 etc. inserted anywhere from 30 to 300 duplicate rows, which I also found slightly strange, the severe disparity of what the MySQL server is capable of writing in a single second, I would have expected a slight difference based on server load at that exact second in time, but not a difference of 270 rows.

At this point I decided to replicate this entire test procedure on a second dedicated server I own with an entirely different ISP in an entirely different country. Considering my server producing the strange results are in a datacenter in the wonderful third world Africa, where electricity is unstable and telecommunications even more so, although to be honest I feel that even if internet connectivity dropped at the ISP level it should not have impacted this test as it was self contained after initialisation.
Note: The African and American servers are on the same versions of MySQL, PHP, WHM and cPanel, centos 7.7 v86.0.18

Anyway the second test server is with InMotion Hosting in Washington DC and I figured it was worth a try. I copied the same script and created the same database test table, and did not adjust a single server server setting, nor add any of my php.ini, .htaccess, .user.ini modifications at all.
Ran the test file, and it ran from start to finish for the full 75 minutes, i re-ran the test 5 times and each time it ran the entire time frame without error.

The only strange thing I noted even on the new server which is running the full script is the database inserted rows per second disparity, this server writes between 1 and 260 rows per second, considering the server spec is far higher with 48 x 2.3ghz processors vs. the African 6 x 2.8ghz processor bad server which can’t even complete a script, I would have expected more rows per second than the lower spec server.

So I suppose my question at the end is should I just dump this African server and get another American one or could this be due to some server misconfiguration that is possibly fixable, and has anyone else noted such odd behaviour on a server before.

[George]

Well having a highly reliable server is obviously the most important thing and will save you lots of time chasing weird random errors obviously.

I would suggest to get a good VPS and install whatever control panel you like to manage its hosting easily.

A good options is to use digitalocean but here in holland we like more TransIP as it gives you much more bang for the bucks. Also great snapshotting, backup and vps monitoring.

But of course there are many reliable VPS providers like Hertzner, upcloud

[psweb]

Thanks George, I rekon I will try out Digital Ocean or one that has Docker capabilities so I can fiddle with that more too.
I honestly never realised my African server was that useless till now, lol.

[Hyperbytes]

Would you not be better running this as a cron job, lots of small “pulses” rather than a long single task

[psweb]

I am not really sure, never really made cron jobs except for on the server itself, but never tried generating a cron job from a php script.
Half the issue with what I am doing is that I need to do a single call to the Google Auth servers, then pull mail messages for 1 hour until the auth expires, and then re-auth and continue getting more messages.
The Google API console has quotas so I do my best to do as few calls as possible or it would kind of be easier to refresh the access token more often but I do not want to make more Api calls than totaly required.

[Dave]

Have you (or the Client) considered the G-Suite commercial provisions? Am sure they offer more flexibility and are much faster than the basic (free) service offerings? Also some VPS providers may not like the extended script calls and may throttle the responses? At some point it is worth considering a dedicated server for such usage and pass the cost on to the Client. Or alternatively consider Amazon SES services which work extremely well with Google and has a great API, is super fast and integration is well documented…? Not an answer to your issues but as a company that supply hosting its just some ideas that are worth thinking about…

[Hyperbytes]

Sorry, obviously not clear in what i meant. Wasn’t meaning create a cron job from a php script, was suggesting rather than run your long job, do it as a scheduled repeat of a smaller job, say every minute rather than one long job. Follow?

[psweb]

Thanks Dave, I thought I was pretty well covered by already paying a pretty penny for the dedicated server I have, although obviously then this came up, and I realised the dedicated server I have is not too great afterall.
I am currently testing on my secondary solution which is a VPS through inmotion hosting and that is running pretty well so far during initial testing.
I will certainly take a look at the Amazon SES idea just to see how that would all work and fit together.

The client already has a Google For Business account with about 50 users on it, and are looking to try trim it down a little as each user is costing around 7 pounds per month per user, so the costs are building up over the years, add a smtp signature provider per user account too and then each user is starting to cost around 10 pounds per month. Realistically they probably only need about 20 monthly users, the rest are staff that have come and gone over the years and are kept purely for record keeping purposes.

Ahh that makes more sense Brian, I have been trying to work out the easiest solution for this.
The way the gmail api works is that i can get a listing of 500 “message LIST” items per call, so 50k messages is going to need 100 calls to get the message list and place that into my database. The list only really has the message ID.
The second call to get the actual message parts I want have 2 solutions, I am using the one at a time approach in a repeat, but am about to look into passing a list of IDs to the API to get multiple results which will possibly make things substantially better.

[Dave]

@psweb

Realistically they probably only need about 20 monthly users, the rest are staff that have come and gone over the years and are kept purely for record keeping purposes.

I’d do one scrape of the old messages and dump them to the database for record keeping. Then remove all the old accounts from G-Suite leaving only those that are required. Will cut your job by two thirds at least by the sounds of it, and cut your Clients costs by about the same. Logically it makes no sense what you’re doing if the data you are pulling is mostly redundant. Sometimes its easy to fall in to the trap of reinventing the wheel. I know I’ve done it many times myself. There are less complicated and far less resource hungry methods to do what you require. We did a similar thing using WHM’s API’s. We simply set forwarders on the GMAIL accounts that were active to new accounts hosted in WHM accounts. Then used the API to create the inbox, outbox, sent items directories. Was quick and dirty and worked like a charm. Didn’t cost anything aside from a couple of hours learning the API WHM side…

[psweb]

Yeah, I think what started out as a pretty straight forward thing, of course landed up growing. I may just look at going with your idea, however this has also become one of those “I can’t lose” style issues, where I had something in my head that I felt should work and now because it is not I am being a stubborn bugger wanting it to work, but what you are saying makes sense.

I have run into this issue all too often with so many clients though, i thought this solution could become a one size fits all, but it landed up being a bit more painful than anticipated.

Out of interest in it’s simplest form, if you needed to take, example 5 email accounts, that over many years had been subscribed to similar mailing lists and had up to 20% duplicate mail in each account, and concatenate them into only one that the user could access in a single Gmail interface, free from duplication, and sorted by gmail labels, how would you go about it.

My standard way of installing 5 accounts in thunderbird using IMAP and once all 5 accounts were fully synced, using a duplicate checker, and removing all but one copy in the master account, and then manually dragging email into folders on the users master email account was just too time consuming considering the smallest of the 5 accounts has 22gb of email and the largest has 47gb of mail.
Just the download and sync of a single gmail account took 3 days. The second then took a further 1 day to sync. Then when i clicked my Thunderbird plugin to perform the duplicate check I was met with a lovely error saying the plugin was no longer reliable in the newer thunderbird and not recommended. So 4 days later I was back to the drawing board.

[Dave]

@psweb

Out of interest in it’s simplest form, if you needed to take, example 5 email accounts, that over many years had been subscribed to similar mailing lists and had up to 20% duplicate mail in each account, and concatenate them into only one that the user could access in a single Gmail interface, free from duplication, and sorted by gmail labels, how would you go about it.

I’d dump the accounts/messages to CSV import to a local MySQL DB and run the comparisons there away from the live environment. Clean up and sanitize the accounts/messages and then reverse the process and import back in to GMAIL afresh.

Quick search reveals the following:

How to Import an Old Email Account Into Gmail

[Dave]

@psweb

“I can’t lose”

Oh man I feel your pain!

[psweb]

Amazing how we all come up with so many different ways to achieve the same results, never even imagined csv to be very honest, but i suppose that does make sense.
Can the csv even hold all the email attachments in place etc?
Never tried that so quite interested