★ VirMach ★ Fashionably Late Black Friday & Cyber Week 2020 ★ Flash Deals ★ View inside ★ $7/Yr 512M

Daniel15

@WSCallum said: I think you'd have more joy out of more CPU power per core rather than throwing cores/threads at it, all depends on setup I guess

Since PHP requests are single-threaded, PHP apps usually peform better if you have more cores (even if slightly less powerful) rather than fewer, more powerful cores, as it directly correlates to more concurrent requests and you can run more PHP-FPM workers concurrently.

AlwaysSkint

@VirMach
Gonna run tuning-primer.sh, for shits & giggles; I'd be interested to know these stats:

Current join_buffer_size = 24.00 M
You have had 289 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"

TheWizard

@VirMach said:
Okay, we have to look into clustering. In the meantime we'll try to actually keep the website up for you guys for non flash sale purposes.

So that "better than half off" deal will not see the light of day then?

Also, a couple of people mentioned this, but I missed it if you've answered... Is downgrading to earlier version of WHMCS totally out of the picture?

Daniel15

@VirMach said:

@Daniel15 said: Shouldn't most of the data be in the cache though? Assuming the server has a lot of RAM.

Unfortunately not how it works. We already have the caching way too high and it just won't work great with this many people.

I meant the regular Linux disk cache, not any app-specific cache.

corbpie

sudo apt install php8.0-common php8.0-cli -y

Unbelievable

https://docs.whmcs.com/Maximising_Performance

Unbelievable

https://support.reliablesite.net/kb/a280/optimizing-the-whmcs-database-for-performance.aspx

ALTER TABLE tblproductconfigoptionssub DROP INDEX configid, ADD INDEX configid (configid ASC, hidden ASC, sortorder ASC, id ASC);
ALTER TABLE tblpricing ADD INDEX relid (relid ASC, type ASC, currency ASC);
ALTER TABLE tblproductconfiglinks ADD UNIQUE INDEX pid_gid (pid ASC, gid ASC);
ALTER TABLE tblproductconfigoptions DROP INDEX productid, ADD INDEX productid (gid ASC, hidden ASC);

WSCallum

@Daniel15 said:

@WSCallum said: I think you'd have more joy out of more CPU power per core rather than throwing cores/threads at it, all depends on setup I guess

Since PHP requests are single-threaded, PHP apps usually peform better if you have more cores (even if slightly less powerful) rather than fewer, more powerful cores, as it directly correlates to more concurrent requests and you can run more PHP-FPM workers concurrently.

Indeed, but more powerful cores will process each request more quickly and will probably therefore improve performance overall side by side if you had a 16 core CPU @ 2.3GHz vs a 16 Core at 3.8GHz, for example, but in Virmachs case you aren't going to find a CPU of this sort of performance with 48 cores so probably wouldn't be better off, unless they went the cluster route with 2/3 servers at say 16 cores each, at 3GHz+

SirFoxy

damn virmach really shit the bed this black friday

corbpie

@SirFoxy said: damn virmach really shit the bed this black friday

All he has to do is drop us a juicy link in here

AlwaysSkint

@Unbelievable said: .. ADD INDEX ..

Hmm, wonders if this has been seen.

merlinvn

@corbpie said:

@SirFoxy said: damn virmach really shit the bed this black friday

All he has to do is drop us a juicy link in here

I missed all the flash sales of Racknerd by this method

brueggus

@AlwaysSkint said:

@Unbelievable said: .. ADD INDEX ..

Hmm, wonders if this has been seen.

Maybe someone could provide us with a mysqldump --no-data of a WHMCS 8 database.

...just for the lulz. Any probably some ideas for improvement.

VirMach

@Unbelievable said:
https://support.reliablesite.net/kb/a280/optimizing-the-whmcs-database-for-performance.aspx

ALTER TABLE tblproductconfigoptionssub DROP INDEX configid, ADD INDEX configid (configid ASC, hidden ASC, sortorder ASC, id ASC);
ALTER TABLE tblpricing ADD INDEX relid (relid ASC, type ASC, currency ASC);
ALTER TABLE tblproductconfiglinks ADD UNIQUE INDEX pid_gid (pid ASC, gid ASC);
ALTER TABLE tblproductconfigoptions DROP INDEX productid, ADD INDEX productid (gid ASC, hidden ASC);

These wouldn't work for the queries that are being done, that article is from 2019. I'm setting up some indexing now for the new query.

VirMach

Let's try again with the indexing done.

VirMach

Okay, much better.

benj0x

Bad gateway. (HTTP 502)

DianTama

@VirMach said:
Okay, much better.

Yep, can see it now, its much better.
i hope it can stay like that

VirMach

@benj0x said:
Bad gateway. (HTTP 502)

Just refresh, a few of these will appear here and then.

VirMach

Alright time to catch up on more tickets, see you guys later. Oh and I owe you guys 5 manual deals so I'll do that now.

merlinvn

Got the first one :d

chorong

Yay, managed to grab it Was pretty much all the way through checking out on the $7/y KVM-SPECIAL-512 while the panel was stable and got alerted from FAT32's tracker. Cloudflare popped up pretty much each step but managed to go through successfully

tuc

@VirMach said:
Alright time to catch up on more tickets, see you guys later. Oh and I owe you guys 5 manual deals so I'll do that now.

Thank you @VirMach! Your hard work is greatly appreciated.

brueggus

So, if anyone wants to dig into it - you can download beta versions of WHMCS without registration: https://download.whmcs.com/#beta . The PHP files are ioncubed, but the SQL files to create the database schema are unencrypted.

@Daniel15 said:

@VirMach said: Here's a sample, it's not actually the real syntax because Cloudflare won't let me post it.

select tblusers.*, tblusers_clients.client_id as pivot_client_id, tblusers_clients.auth_user_id as pivot_auth_user_id, tblusers_clients.created_at as pivot_created_at, tblusers_clients.updated_at as pivot_updated_at, tblusers_clients.owner as pivot_owner, tblusers_clients.permissions as pivot_permissions, tblusers_clients.last_login as pivot_last_login from tblusers inner join tblusers_clients on tblusers.id = tblusers_clients.auth_user_id where tblusers_clients.client_id = ? and owner = ? order by tblusers.id asc limit 1

This happens every time a client is mentioned.

That query doesn't look too bad, as long as there's indexes on client_id and owner.

Guess what I couldn't find in the SQL files. But they seem to have run into a similar issue earlier (see resources/sql/upgrade362.sql).

Kebab

seems like cloudflare successfully stopping the bots? got the $4.95 deal

benj0x

@VirMach said:

@benj0x said:
Bad gateway. (HTTP 502)

Just refresh, a few of these will appear here and then.

Okay, worked great after I stopped trying refreshing on the 502 Bad Gateway page and just went from the order site directly again.

Now I'm seeing an HTTP 403 before I was being redirected to PayPal.

But apparently my billing agreement worked fine, since my credit card was simply charged and the order seems to be processed.

Great!

VirMach

@Kebab said: seems like cloudflare successfully stopping the bots? got the $4.95 deal

It's mainly just the indexing that WHMCS refused to help with (I figured they knew more, I guess not) so I just did some basic indexing. Oh, and the 48 core server helped.

chocolateshirt

Thank you @Virmach, I got this one..

Thank you @FAT32 for your notification. Thank you @ddhhz for your image.

AlwaysSkint

LA - meh.

tenpera

Please stand by, while we are checking your browser...