Query about big website hosting, database size and cpu utilization

lovedigit

Core license
License owner
Joined
Mar 13, 2016
Messages
279
Points
100
Hello,

Before posting these queries to support section, I wanted to discuss them with other users who manages big chevereto websites.
Recently, I noticed that there was huge CPU usage and bandwidth utilization. I wondered if it is because of the recent spike in image uploads, and hence bigger database size. However, there are few things that doesn't make sense.

1. The database is huge, approximately 1GB in size for 500K+ images.
2. Bandwidth consumption has increased from around 500GB per month on webserver to 3TB+. Storage server is separate, and this bandwidth usage data doesn't include that.
3. CPU load has increased from 0.3 on average to 5.0 or more.

I am willing to upgrade the webserver to incorporate the additional demand if that is. However, what has me baffled is there has been no change in traffic over all. It is basically the same as it was a month ago. It doesn't make sense.

So, I would like to know if it is normal. If you have a big website, you can maybe provide answers to these questions, or maybe tally the data with yours.What is the size of your database? What is bandwidth consumption of webserver which serves your website frontend? What is average CPU load?

I would really appreciate your help. I am skeptical that something is wrong with my website. Maybe I need to reindex the database, since mysql process is the main culprit of the cpu usage spike. Even a single page load results in spike of CPU utilization. I just need to make sure that any of these are unexpected before I post it in support section.

Up until this point I had an impression that chevereto is light on resources, and only actual resource that I need to worry about is storage and bandwidth. Ofcourse I would need to scale the server based on number of visitors, but there has been no change in userbase. It is about 30K pageviews per day. Nothing out of ordinary.

Feel free to ask for any more information that you may need.
 
Last edited:

mkerala2

Network license
Beta tester
Joined
Jun 30, 2014
Messages
459
Points
105
My webserver bandwidth is as follows. I got around 2.3 million images and my DB is 4+GB.

Total Outgoing ↑1.6 TB
Total Incoming ↓2.06 TB

I was on the same boat last year. As the site grow it was getting slowed down significantly. I started tweaking, webserver, php, nginx but these all were temp solution. Long story short, I moved the DB to a separate server with 8GB RAM. Then converted the image table to innodb from ISAM and jacked up the buffer to 5GB to load it always from memory. It is now fast as lightening.

PS: Playing around with Database is risky should be done at your own risk.
 

lovedigit

Core license
License owner
Joined
Mar 13, 2016
Messages
279
Points
100
Hmm, I guess everything is normal then.
Site is not slowed down, and I am aware about database optimization. Just want to validate if this is normal and expected. The resource consumption spike had me worried, because it wasn't anything significant few months ago with the same traffic, but fewer total image index.
 

ashkir

Core license
License owner
Joined
Feb 14, 2016
Messages
363
Points
125
During my peak times my CPU is never above 0.7.

My outgoing bandwidth is 958 GB.

My CDN bandwidth is 3.9 TB for last moth (light month).

I am at 1.3 million images with an additional .3 million deleted so about 1.6 million rows in the database.

To help my server run smoother, I made all my databases InnoDB & I use MariaDB.

Also my server runs on a freaken potato!
 

lovedigit

Core license
License owner
Joined
Mar 13, 2016
Messages
279
Points
100
Yes, same; changed all the database to Innodb, along with increased database cache size and Rodolfo's new patch for index, it went down to normal again.
 

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,674
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
A little off topic? Thing with MariaDB is that while is compatible with MySQL, it has some caveats. This is because they share lot of processes names and the adapters handle both databases as MySQL. In this case, the problem was that the method used to retrieve the DB version for MariaDB returns something like this: 5-5-5.10.0.0.MariaDB-blabla. The first version correspond to the fork from MySQL.

MySQL 5.5 is really old, and it doesn't support fulltext InnoDB. All this time the thing was wrongly detecting the DB version for MariaDB.
 

mkerala2

Network license
Beta tester
Joined
Jun 30, 2014
Messages
459
Points
105
I moved away from MariaDB as it was difficult to troubleshoot. Many MSQL commands/config don't work MariaDB and have to search all over the internet to find a solution. Also MYSQL Workbench I use to manage my DB doesn't work well with MariaDB.

MariaDB used to have an edge over MySQL 5.5 in terms of performance, but that is no longer the case with MySQL 8.0 release. In fact, in many benchmark MySQL 8.0 outpaced MariaDB.
 

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,674
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
MYSQL Workbench I use to manage my DB doesn't work well with MariaDB.
Workbench is the slowest thing ever created by the human being. Tested on WIndows and Linux and is just terrible. If you want something neat and easy try Navicat.

MariaDB used to have an edge over MySQL 5.5 in terms of performance, but that is no longer the case with MySQL 8.0 release. In fact, in many benchmark MySQL 8.0 outpaced MariaDB.
It's Oracle. Of course it will always outperform.
 

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,674
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
Yes, it is very expensive and their pricing is horrible. For the same reason I use DBeaver and I hope that Navicat improves their pricing in the future.