Memory Usage Doubts

Discussion of non-phpBB related topics with other phpBB.com users.
Forum rules
General Discussion is a bonus forum for discussion of non-phpBB related topics with other phpBB.com users. All site rules apply.
Post Reply
User avatar
Tarantino
Registered User
Posts: 709
Joined: Sat Feb 18, 2012 1:51 pm
Contact:

Memory Usage Doubts

Post by Tarantino » Mon Feb 04, 2019 6:44 pm

Hi there,

I am using the last phpBB 3.2.5 version with PHP 7.2.14 with MySQL(i) 5.7.21-log.
I am using phpBB Native Search and my Database have 1.21Gb.
PHP INFO: https://i.imgur.com/XiFaRiI.png

I've a pretty good plan at OVH (or thats what I think) with a private database server with 1Gb RAM. But it seems I am using more then 1Gb RAM and the forum is being too much slow lately. I've talk with some friends and they've some websites (Wordpress) and some of them forums with (SMF and Xenforo), one of the Xenforo is pretty big (Bigger then mine with more users logged in at the same time), and they said to me that even for 1Gb database the ram would never be used to cache the entire database size. And the one with the bigger xenforo forum said to me that his forum doesnt reach 100Mb of usage.

So whats wrong with this?

My database server has some variables that I can change (I've put bold the values I am using):
max_connections - 100 or 200.
max_user_connections - 50, 100, 150 or 200.
Temporary file folder - tmpdir (hdd) or dev/shm (ram)
wait_timeout - 60, 600, 3600, 7200
max_allowed_packet - 1M, 2M, 4M, 8M, 16M, 32M
innodb_buffer_pool_size - 64M, 128M, 256M, 512M, 1024M, 2048M
interactive_timeout - 60, 600, 3600, 7200
autocommit - OFF or ON
event_scheduler - OFF or ON

Any idea how to solve this? Maybe should I try a different server, but which one? I've tried some and almost all of them didnt work out well with this huge database, I wasnt able to run some SQL commands to the posts or users table without crashing the database :/

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50250
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Memory Usage Doubts

Post by stevemaury » Mon Feb 04, 2019 8:21 pm

A link to your board would help. What extensions do you have installed?

Realize, though, that server configuration is generally beyond the scope of this forum.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
Tarantino
Registered User
Posts: 709
Joined: Sat Feb 18, 2012 1:51 pm
Contact:

Re: Memory Usage Doubts

Post by Tarantino » Tue Feb 05, 2019 12:27 am

I have those:

Code: Select all

Advertisement Management	2.0.1
Auto Groups	2.0.1
Board Announcements	1.1.0
Board Rules	2.1.1
cBB Blockgets	2.0.0
cBB Chat	1.1.3
cBB QuickMod	1.1.0
Change Post Time	1.0.1
Cron Status	3.2.0
Error pages	3.1.2
External Links Open in New Window	1.0.6
Favicon Notifications	1.0.0
Forumhulp Helper	3.1.15
Forum Icons	1.0.3
Genders	1.1.1
HTML email	3.1.7
LMDI Delete Re:	1.0.7
Mark Edited Posts Unread	1.0.1
New user default settings	2.1.0-RC1
No Notice Delete PM	1.0.0
phpBB 3.2 PM Search	1.0.0
phpBB Gallery	3.2.2
phpBB Gallery Add-on: ACP Cleanup	1.2.0
phpBB Gallery Add-on: ACP Import	1.2.0
phpBB Gallery Add-on: Exif	1.2.0
phpBB Media Embed PlugIn	1.0.1 
Posting Template	1.0.0
Precise Similar Topics	1.5.1
Quick Reply On Last Page Only	1.0.1
Quick Title Edition	2.0.0-RC1
Read other's topics Permission	1.0.5
Search user topics	1.0.2
Simple Spoiler	1.3.0
Subject Check	1.0.4
Topic Description	1.0.4 
Topic Index	1.0.4
Upload Extensions	3.2.0-RC
Upload image	3.1.3
Username Colour Changer	1.0.3
Web Push Notifications
It was working fine with the Search as MySQL, after changing to Native, the database pass from 500 to 1200mb. And after some days we notice the slowness. But I prefer to use native since MySQL is kinda limited on the search. And dont provides all results.

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 3065
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Memory Usage Doubts

Post by thecoalman » Tue Feb 05, 2019 1:01 am

Tarantino wrote:
Tue Feb 05, 2019 12:27 am
the database pass from 500 to 1200mb.
For clarification are you referring to the table size or the RAM usage. If I recall correctly the table size of Native vs. MySQL is more than double.

Where are you getting this statistic from?

User avatar
AmigoJack
Registered User
Posts: 5523
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Memory Usage Doubts

Post by AmigoJack » Tue Feb 05, 2019 8:36 am

Tarantino wrote:
Mon Feb 04, 2019 6:44 pm
whats wrong with this?
Nothing - everything works as intended. phpBB's native search requires keywords which want to be found to be indexed, hence stored somewhere. That's why it needs a rather "big" database table.

MySQL Fulltext works by hugely increasing table indices, effectively also growing the database size. But does this more efficiently.

Ask your xenForo guy which search system is used there (most likely it also sticks to a DBMS solution instead of providing its own, or it doesn't even have one, which would mean slow searches once the board grows).

Also there are different ways to count the size of a database, i.e. most people forgot the size of the indices or the storage overhead.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
Tarantino
Registered User
Posts: 709
Joined: Sat Feb 18, 2012 1:51 pm
Contact:

Re: Memory Usage Doubts

Post by Tarantino » Tue Feb 05, 2019 10:31 am

I know thats the database is bigger because of the search engine, thats not a problem.
The problem is that the forum itself its using a lot of RAM. Dunno if its related to the search engine. How can this use 1Gb Ram?
I am using the tempdir, so it will not put the database cache to the ram, so the innodb_buffer of 64M would be use on hdd and not ram, so how the ram is still being used a lot? Doesnt make sense to me. When my friend get values of 60Mb with a bigger board :(
Something is not right. How can I debug the ram usage? Any idea?

User avatar
Tarantino
Registered User
Posts: 709
Joined: Sat Feb 18, 2012 1:51 pm
Contact:

Re: Memory Usage Doubts

Post by Tarantino » Tue Feb 05, 2019 10:33 am

thecoalman wrote:
Tue Feb 05, 2019 1:01 am
Tarantino wrote:
Tue Feb 05, 2019 12:27 am
the database pass from 500 to 1200mb.
For clarification are you referring to the table size or the RAM usage. If I recall correctly the table size of Native vs. MySQL is more than double.

Where are you getting this statistic from?
Database Size. But the Ram usage had reach 14 times a day the 1Gb RAM usage. And this is a RAM that is used only for this database. Its a private database instance with 1gb ram only for the database.

The database size I can get from phpmyadmin or ACP itself. About RAM usage, from OVH Admin Panel. There is an information that goes red when it bypass the limit of 1Gb Ram. He does accepts the database to use more then the 1gb ram but he shows some warnings.

User avatar
Toxyy
Registered User
Posts: 687
Joined: Mon Oct 24, 2016 3:22 pm
Location: Namek
Contact:

Re: Memory Usage Doubts

Post by Toxyy » Tue Feb 05, 2019 11:24 am

Tarantino wrote:
Tue Feb 05, 2019 10:31 am
How can I debug the ram usage? Any idea?
https://sourceforge.net/projects/qcachegrindwin/

and you'll need to enable this and some other settings in php ini, you can google to find some default settings:

https://xdebug.org/docs/all_settings#pr ... le_trigger

Use in your url like mysite.com/forums/viewtopic.php?XDEBUG_PROFILE& . . .

Open the file in qcachegrind:
Image

see if any extensions you have enabled are bringing you down somewhere, use the name of the folder they're held in as keywords.
I am a web developer/administrator, specializing in forums. If you have work you need done or are too lazy to do, pm me!

My extensions:
[3.2][BETA] Anonymous Posts || [3.2][RC] Show User Activity || [3.2][DEV] User Delete Topics

User avatar
AmigoJack
Registered User
Posts: 5523
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Memory Usage Doubts

Post by AmigoJack » Tue Feb 05, 2019 11:24 am

You haven't even stated which DBMS you use. If it has a RAM usage that doesn't fit your needs then ask for help where that software is maintained - phpBB is not a DBMS and has no influence on its RAM usage.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
Tarantino
Registered User
Posts: 709
Joined: Sat Feb 18, 2012 1:51 pm
Contact:

Re: Memory Usage Doubts

Post by Tarantino » Tue Feb 05, 2019 12:04 pm

@AmigoJack I told on the first post. I'm using MySQL.

Thnks Toxyy. But I dont have php.ini access. In any case I'll take a look at it.
Im on a shared service, but with a private database.
Maybe I can test it locally with xampp to see if I can found out an culprit on any extension.

User avatar
AmigoJack
Registered User
Posts: 5523
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Memory Usage Doubts

Post by AmigoJack » Tue Feb 05, 2019 12:30 pm

Tarantino wrote:
Tue Feb 05, 2019 12:04 pm
I told on the first post
"MySQL(i)" is a driver/interface - it could still mean you use MariaDB or other MySQL compatible ones. https://forums.mysql.com/list.php?24 looks good for asking your RAM questions, or even requesting support.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: Memory Usage Doubts

Post by A_Jelly_Doughnut » Tue Feb 05, 2019 1:00 pm

1 GB of RAM is really not much for an SQL server, especially one where the database is over 1GB. By default, MySQL will try to hold the entire database in RAM for the best performance. It will also frequently be creating join products when a query asks to join two tables, which will consume further RAM.

But with the database being larger than 1GB, it cannot keep your database entirely in RAM, so it will have to swap certain tables to disk in order to fulfill requests. Thus your slow down. The chances of such slow downs grow as your traffic grows, because different users will be doing different operations which require different tables to be loaded, and to create more Join products in memory.

First tip: The MySQL fulltext index is most likely still present. You should have an option to delete that in the phpBB admin panel (Maintenance -> Search Index). I don't have a fulltext mysql install handy to look at, but that's my recollection.

Secondly: You may be able to optimize MySQL configuration for a constrained memory environment, but this is unlikely to provide a great deal of relief.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

Post Reply

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 16 guests