Database running VERY hot
Hello all,
We had an old installation of Mailman 2.1 across three servers with over 32,000 mailing lists on them. Due to circumstances beyond my control, we had to shut them down and quickly set up Mailman 3 on a new server.
We have Mailman3 set up on a single server this time, as we were having problems with a 3-server setup and Hyperkitty and archives. Long story short, it is up and running, and our users are starting to come back.
However, we do not have very many users hitting Postorius and Hyperkitty yet, and we are seeing a distressing number of 504 gateway timeouts.
We noticed that there is a significant number of queries to the API whenever hitting Postorius's front page to load the mailing lists, and then on every load. It looks as if it makes a single call to the API to get information about a single list, and does this 10 times - once for each list - to render the page. This gets much worse when users set the view to 200 per page, as they are inclined to do with 32,000 mailing lists...
What we've done, actually, is to override the front page and point the users to a different set of static pages where we have all of our lists categorized and searchable, and when they find the list they are looking for, we have a link back to postorius. It has taken a lot of the stress off of the server.
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so. However, one of the threads is taking up about 85% of the load, another thread about 14%, and little dribbles here and there from the rest of the threads.
Here are the questions:
Has anyone run into problems like these? I would think that maybe this is an issue having so many mailing lists, but honestly there aren't that many people using the system yet. It is going to ramp up as the users realize that we are live again, but we aren't getting that much traffic.
Is there any configuration that I can set, especially with regards to the database, so that it can be used more efficiently and not throttle through one or two threads?
Please let me know what further information I can provide. Thanks in advance for any help or advice!
Silas
Hi Darren,
On 3/8/18 9:43 AM, Darren Smith wrote:
Hello all,
We had an old installation of Mailman 2.1 across three servers with over 32,000 mailing lists on them. Due to circumstances beyond my control, we had to shut them down and quickly set up Mailman 3 on a new server.
We have Mailman3 set up on a single server this time, as we were having problems with a 3-server setup and Hyperkitty and archives. Long story short, it is up and running, and our users are starting to come back.
However, we do not have very many users hitting Postorius and Hyperkitty yet, and we are seeing a distressing number of 504 gateway timeouts.
We noticed that there is a significant number of queries to the API whenever hitting Postorius's front page to load the mailing lists, and then on every load. It looks as if it makes a single call to the API to get information about a single list, and does this 10 times - once for each list - to render the page. This gets much worse when users set the view to 200 per page, as they are inclined to do with 32,000 mailing lists...
What we've done, actually, is to override the front page and point the users to a different set of static pages where we have all of our lists categorized and searchable, and when they find the list they are looking for, we have a link back to postorius. It has taken a lot of the stress off of the server.
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so. However, one of the threads is taking up about 85% of the load, another thread about 14%, and little dribbles here and there from the rest of the threads.
Here are the questions:
- Has anyone run into problems like these? I would think that maybe this is an issue having so many mailing lists, but honestly there aren't that many people using the system yet. It is going to ramp up as the users realize that we are live again, but we aren't getting that much traffic.
I can say that in general, Postorius doesn't really optimize REST API queries and so loading a single page can sometimes make things bad.
Currently, the largest installation of Mailman 3 that we know of runs at lists.fedoraproejct.org and the administrators of the list would know how well does our Web UI scale.
However, I don't think we have anyone actively working on trying to fix this particular issue. Pull Requests and fixes are welcome :)
- Is there any configuration that I can set, especially with regards to the database, so that it can be used more efficiently and not throttle through one or two threads?
There isn't any configuration that would change how databases are used in Mailman Core. Django *may* have some, but I don't know about any of them of the top of my head.
thanks, Abhilash
Abhilash,
Thanks for the response. I'm going over to lists.fedoraproject.org to see if they have done any fine tuning.
Our installation is at https://lists.rootsweb.ancestry.com, if you're interested in seeing an installation with over 32,000 mailing lists. As I mentioned, it was big enough that we have disabled the browse experience in Postorius and are hosting our own pages for browsing to a new list.
If we get time to work on this we will certainly create a pull request. :)
On Thu, Mar 8, 2018 at 11:49 AM, Abhilash Raj maxking@asynchronous.in wrote:
Hi Darren,
On 3/8/18 9:43 AM, Darren Smith wrote:
Hello all,
We had an old installation of Mailman 2.1 across three servers with over 32,000 mailing lists on them. Due to circumstances beyond my control, we had to shut them down and quickly set up Mailman 3 on a new server.
We have Mailman3 set up on a single server this time, as we were having problems with a 3-server setup and Hyperkitty and archives. Long story short, it is up and running, and our users are starting to come back.
However, we do not have very many users hitting Postorius and Hyperkitty yet, and we are seeing a distressing number of 504 gateway timeouts.
We noticed that there is a significant number of queries to the API whenever hitting Postorius's front page to load the mailing lists, and then on every load. It looks as if it makes a single call to the API to get information about a single list, and does this 10 times - once for each list - to render the page. This gets much worse when users set the view to 200 per page, as they are inclined to do with 32,000 mailing lists...
What we've done, actually, is to override the front page and point the users to a different set of static pages where we have all of our lists categorized and searchable, and when they find the list they are looking for, we have a link back to postorius. It has taken a lot of the stress off of the server.
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so. However, one of the threads is taking up about 85% of the load, another thread about 14%, and little dribbles here and there from the rest of the threads.
Here are the questions:
- Has anyone run into problems like these? I would think that maybe this is an issue having so many mailing lists, but honestly there aren't that many people using the system yet. It is going to ramp up as the users realize that we are live again, but we aren't getting that much traffic.
I can say that in general, Postorius doesn't really optimize REST API queries and so loading a single page can sometimes make things bad.
Currently, the largest installation of Mailman 3 that we know of runs at lists.fedoraproejct.org and the administrators of the list would know how well does our Web UI scale.
However, I don't think we have anyone actively working on trying to fix this particular issue. Pull Requests and fixes are welcome :)
- Is there any configuration that I can set, especially with regards to the database, so that it can be used more efficiently and not throttle through one or two threads?
There isn't any configuration that would change how databases are used in Mailman Core. Django *may* have some, but I don't know about any of them of the top of my head.
thanks, Abhilash
Abhilash,
Thanks for the response. I'm going over to lists.fedoraproject.org to see if they have done any fine tuning.> Our installation is at https://lists.rootsweb.ancestry.com, if you're interested in seeing an installation with over 32,000 mailing lists. As I mentioned, it was big enough that we have disabled the browse experience in Postorius and are hosting our own pages for browsing to a new list. Please keep us posted if you have other unique challenges for operating
On Fri, Mar 9, 2018, at 9:11 AM, Darren Smith wrote: this high number of lists, other than this particular one. We'd love to be able to support high number of lists efficiently :)
If we get time to work on this we will certainly create a pull request. :)>
On Thu, Mar 8, 2018 at 11:49 AM, Abhilash Raj maxking@asynchronous.in wrote:>> Hi Darren,
On 3/8/18 9:43 AM, Darren Smith wrote:
Hello all,
We had an old installation of Mailman 2.1 across three servers with over>>> 32,000 mailing lists on them. Due to circumstances beyond my control, we>>> had to shut them down and quickly set up Mailman 3 on a new server.>>> We have Mailman3 set up on a single server this time, as we were having>>> problems with a 3-server setup and Hyperkitty and archives. Long story>>> short, it is up and running, and our users are starting to come back.>>> However, we do not have very many users hitting Postorius and Hyperkitty>>> yet, and we are seeing a distressing number of 504 gateway timeouts.>>> We noticed that there is a significant number of queries to the API>>> whenever hitting Postorius's front page to load the mailing lists, and then>>> on every load. It looks as if it makes a single call to the API to get>>> information about a single list, and does this 10 times - once for each>>> list - to render the page. This gets much worse when users set the view to>>> 200 per page, as they are inclined to do with 32,000 mailing lists...>>> What we've done, actually, is to override the front page and point the>>> users to a different set of static pages where we have all of our lists>>> categorized and searchable, and when they find the list they are looking>>> for, we have a link back to postorius. It has taken a lot of the stress off>>> of the server.
However, I still see where the database at times can run VERY hot.>>> We have set up this server to run with PostgreSQL. When I do a ps aux |>>> grep postgres, I find a lot of processes that have been spawned to take>>> care of mailman - a dozen or so. However, one of the threads is taking up>>> about 85% of the load, another thread about 14%, and little dribbles here>>> and there from the rest of the threads.
Here are the questions:
- Has anyone run into problems like these? I would think that maybe this>>> is an issue having so many mailing lists, but honestly there aren't that>>> many people using the system yet. It is going to ramp up as the users>>> realize that we are live again, but we aren't getting that much traffic.>> I can say that in general, Postorius doesn't really optimize REST API queries and so loading a single page can sometimes make things bad.>> Currently, the largest installation of Mailman 3 that we know of runs at lists.fedoraproejct.org and the administrators of the list would know how well does our Web UI scale.>> However, I don't think we have anyone actively working on trying to fix this particular issue. Pull Requests and fixes are welcome :)
- Is there any configuration that I can set, especially with regards to the database, so that it can be used more efficiently and not throttle through one or two threads?>>
There isn't any configuration that would change how databases are used in Mailman Core. Django *may* have some, but I don't know about any of them of the top of my head.>> thanks, Abhilash
-- Abhilash Raj maxking@asynchronous.in
Well, we haven't tackled the issue if importing old archives yet - we're trying to stabilize things first, trying to get the 504 gateway timeouts to stop happening. Once that happens, we have over 40 million old emails to import. (some of the emails date back to 1987 I think.)
We'll see how it goes. :)
-Darren
On Fri, Mar 9, 2018 at 10:20 AM, Abhilash Raj maxking@asynchronous.in wrote:
On Fri, Mar 9, 2018, at 9:11 AM, Darren Smith wrote:
Abhilash,
Thanks for the response. I'm going over to lists.fedoraproject.org to see if they have done any fine tuning.
Our installation is at https://lists.rootsweb.ancestry.com, if you're interested in seeing an installation with over 32,000 mailing lists. As I mentioned, it was big enough that we have disabled the browse experience in Postorius and are hosting our own pages for browsing to a new list.
Please keep us posted if you have other unique challenges for operating this high number of lists, other than this particular one. We'd love to be able to support high number of lists efficiently :)
If we get time to work on this we will certainly create a pull request. :)
On Thu, Mar 8, 2018 at 11:49 AM, Abhilash Raj maxking@asynchronous.in wrote:
Hi Darren,
On 3/8/18 9:43 AM, Darren Smith wrote:
Hello all,
We had an old installation of Mailman 2.1 across three servers with over 32,000 mailing lists on them. Due to circumstances beyond my control, we had to shut them down and quickly set up Mailman 3 on a new server.
We have Mailman3 set up on a single server this time, as we were having problems with a 3-server setup and Hyperkitty and archives. Long story short, it is up and running, and our users are starting to come back.
However, we do not have very many users hitting Postorius and Hyperkitty yet, and we are seeing a distressing number of 504 gateway timeouts.
We noticed that there is a significant number of queries to the API whenever hitting Postorius's front page to load the mailing lists, and then on every load. It looks as if it makes a single call to the API to get information about a single list, and does this 10 times - once for each list - to render the page. This gets much worse when users set the view to 200 per page, as they are inclined to do with 32,000 mailing lists...
What we've done, actually, is to override the front page and point the users to a different set of static pages where we have all of our lists categorized and searchable, and when they find the list they are looking for, we have a link back to postorius. It has taken a lot of the stress off of the server.
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so. However, one of the threads is taking up about 85% of the load, another thread about 14%, and little dribbles here and there from the rest of the threads.
Here are the questions:
- Has anyone run into problems like these? I would think that maybe this is an issue having so many mailing lists, but honestly there aren't that many people using the system yet. It is going to ramp up as the users realize that we are live again, but we aren't getting that much traffic.
I can say that in general, Postorius doesn't really optimize REST API queries and so loading a single page can sometimes make things bad.
Currently, the largest installation of Mailman 3 that we know of runs at lists.fedoraproejct.org and the administrators of the list would know how well does our Web UI scale.
However, I don't think we have anyone actively working on trying to fix this particular issue. Pull Requests and fixes are welcome :)
- Is there any configuration that I can set, especially with regards to the database, so that it can be used more efficiently and not throttle through one or two threads?
There isn't any configuration that would change how databases are used in Mailman Core. Django *may* have some, but I don't know about any of them of the top of my head.
thanks, Abhilash
-- Abhilash Raj maxking@asynchronous.in
Hi,
I need your help: I installed mailman3 on a debian stretch environment and faced following problems.
On "mailman start" I got the error message: Starting Mailman's master runner /usr/bin/python3: can't open file '/sbin/master': [Errno 2] No such file or directory
connection to maria-db In debian stretch maria-db is running. In my mailman.cfg I set: [database] class: mailman.database.mysql.MySQLDatabase url: mysql+pmysql://#username#:#user-pawo#@localhost/#databasename#?charset=utf8&use_unicode=1
This produces as errormessage: sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysql.pmysql
On mailman start the error message is: File "/usr/local/bin/mailman", line 11, in <module> sys.exit(main()) File "/usr/local/lib/python3.5/dist-packages/mailman/bin/mailman.py", line 94, in main initialize(config_path) File "/usr/local/lib/python3.5/dist-packages/mailman/core/initialize.py", line 189, in initialize initialize_2(propagate_logs=propagate_logs) File "/usr/local/lib/python3.5/dist-packages/mailman/core/initialize.py", line 160, in initialize_2 config.db = getUtility(IDatabaseFactory, utility_name).create() File "/usr/local/lib/python3.5/dist-packages/mailman/database/factory.py", line 54, in create database.initialize() File "/usr/local/lib/python3.5/dist-packages/mailman/database/base.py", line 106, in initialize self.engine = create_engine(url, isolation_level='READ UNCOMMITTED') File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine return strategy.create(*args, **kwargs) File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/strategies.py", line 57, in create entrypoint = u._get_entrypoint() File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/url.py", line 156, in _get_entrypoint cls = registry.load(name) File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/langhelpers.py", line 221, in load (self.group, name)) sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysql.pmysql
Can You help??
Regards Wolfgang
On 03/10/2018 01:53 PM, Wolfgang Bock wrote:
Hi,
I need your help: I installed mailman3 on a debian stretch environment and faced following problems.
How? Did you install https://packages.debian.org/stretch-backports/mailman3-core?
If so, these are probably issues with the Debian package.
You may find https://gitlab.com/mailman/mailman/issues/455 to be of interest.
If you installed Mailman some other way, How?
- On "mailman start" I got the error message: Starting Mailman's master runner /usr/bin/python3: can't open file '/sbin/master': [Errno 2] No such file or directory
Where is it? What does 'which master' show?
- connection to maria-db In debian stretch maria-db is running. In my mailman.cfg I set: [database] class: mailman.database.mysql.MySQLDatabase url: mysql+pmysql://#username#:#user-pawo#@localhost/#databasename#?charset=utf8&use_unicode=1
should that be mysql+pymysql ?
-- Mark Sapiro mark@msapiro.net The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Am 10.03.2018 um 23:44 schrieb Mark Sapiro:
How? Did you install https://packages.debian.org/stretch-backports/mailman3-core? -> No, if I had known this, I wouldn't waste my time for the last 3 days in fiddeling and googleling ...
If so, these are probably issues with the Debian package.
You may findhttps://gitlab.com/mailman/mailman/issues/455 to be of interest.
If you installed Mailman some other way, How? -> From here: http://docs.mailman3.org/en/latest/prodsetup.html#installing-mailman-core including dependencies
- On "mailman start" I got the error message: Starting Mailman's master runner /usr/bin/python3: can't open file '/sbin/master': [Errno 2] No such file or directory Where is it? What does 'which master' show? -> /usr/local/bin/master -> After I set a symlink to /sbin/master another errormessage occurs
- connection to maria-db In debian stretch maria-db is running. In my mailman.cfg I set: [database] class: mailman.database.mysql.MySQLDatabase url: mysql+pmysql://#username#:#user-pawo#@localhost/#databasename#?charset=utf8&use_unicode=1 should that be mysql+pymysql ? -> Yes, according to: http://mailman.readthedocs.io/en/latest/src/mailman/docs/database.html -> Not right??
After that I decided to delete mailman via pip and took a new approach via debians apt-get ... mailman 3
So far so good, now new errormessage:
Traceback (most recent call last): File "/usr/local/bin/mailman", line 11, in <module> sys.exit(main()) File "/usr/local/lib/python3.5/dist-packages/mailman/bin/mailman.py", line 94, in main initialize(config_path) File "/usr/local/lib/python3.5/dist-packages/mailman/core/initialize.py", line 189, in initialize initialize_2(propagate_logs=propagate_logs) File "/usr/local/lib/python3.5/dist-packages/mailman/core/initialize.py", line 160, in initialize_2 config.db = getUtility(IDatabaseFactory, utility_name).create() File "/usr/local/lib/python3.5/dist-packages/mailman/database/factory.py", line 52, in create database = call_name(database_class) File "/usr/local/lib/python3.5/dist-packages/mailman/utilities/modules.py", line 67, in call_name named_callable = find_name(dotted_name) File "/usr/local/lib/python3.5/dist-packages/mailman/utilities/modules.py", line 51, in find_name return getattr(sys.modules[package_path], object_name) AttributeError: module 'mailman.database.mysql' has no attribute 'MYSQLDatabase'
Another question is, where can I find the sql-command within the database mailman3 like CREATE TABLE x and TABLE y as I created a database mailman3 which is empty??
Thanks for your help in advance.
Regards Wolfgang
On 03/11/2018 03:11 PM, Wolfgang Bock wrote:
-> After I set a symlink to /sbin/master another errormessage occurs
- connection to maria-db In debian stretch maria-db is running. In my mailman.cfg I set: [database] class: mailman.database.mysql.MySQLDatabase url: mysql+pmysql://#username#:#user-pawo#@localhost/#databasename#?charset=utf8&use_unicode=1
should that be mysql+pymysql ? -> Yes, according to: http://mailman.readthedocs.io/en/latest/src/mailman/docs/database.html -> Not right??
My point is you have 'mysql+pmysql' and you should have 'mysql+pymysql'. You are missing the first 'y' in 'pymysql'.
After that I decided to delete mailman via pip and took a new approach via debians apt-get ... mailman 3
So far so good, now new errormessage:
Traceback (most recent call last): ... AttributeError: module 'mailman.database.mysql' has no attribute 'MYSQLDatabase'
Did you install the Debian dbconfig-mysql dependency? I don't know how installation of mailman3-core determines which if any of the database dependencies, but you need that for sure to support MySQL/MariaDB.
Another question is, where can I find the sql-command within the database mailman3 like CREATE TABLE x and TABLE y as I created a database mailman3 which is empty??
The database may be empty because of the above error and the fact you haven't yet done anything.
All the database access is via SQLAlchemy and models defined in Mailman's mailman/models/ directory. See http://mailman.readthedocs.io/en/latest/src/mailman/model/docs/model.html.
-- Mark Sapiro mark@msapiro.net The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Hi Mark,
thanks for your reply.
In my debian apt-get install the "mysql+pymysq" sentence was now automaticly set. My Debian dbconfig-mysql dependency refers to maria-db.
I had some problem of creating the database due to right-problems with dbconfig. So I created the database manually and set permissions to the database-user manually. The results were shown yesterday.
Now I am stopping all efforts on fiddeling with mailman3, re-setup my server in order to get rid of all that python stuff and will use mailman 2.1 which runs without problems and does all things I need. A sql-membermanagement would be useful, but in this version not available.
And wait for an official mailman upgrade within the debian stretch stable ...
Thank you for your efforts.
Regards
Wolfgang
Am 12.03.2018 um 02:24 schrieb Mark Sapiro:
On 03/11/2018 03:11 PM, Wolfgang Bock wrote:
-> After I set a symlink to /sbin/master another errormessage occurs
- connection to maria-db In debian stretch maria-db is running. In my mailman.cfg I set: [database] class: mailman.database.mysql.MySQLDatabase url: mysql+pmysql://#username#:#user-pawo#@localhost/#databasename#?charset=utf8&use_unicode=1
should that be mysql+pymysql ? -> Yes, according to: http://mailman.readthedocs.io/en/latest/src/mailman/docs/database.html -> Not right??
My point is you have 'mysql+pmysql' and you should have 'mysql+pymysql'. You are missing the first 'y' in 'pymysql'.
After that I decided to delete mailman via pip and took a new approach via debians apt-get ... mailman 3
So far so good, now new errormessage:
Traceback (most recent call last): ... AttributeError: module 'mailman.database.mysql' has no attribute 'MYSQLDatabase'
Did you install the Debian dbconfig-mysql dependency? I don't know how installation of mailman3-core determines which if any of the database dependencies, but you need that for sure to support MySQL/MariaDB.
Another question is, where can I find the sql-command within the database mailman3 like CREATE TABLE x and TABLE y as I created a database mailman3 which is empty??
The database may be empty because of the above error and the fact you haven't yet done anything.
All the database access is via SQLAlchemy and models defined in Mailman's mailman/models/ directory. See http://mailman.readthedocs.io/en/latest/src/mailman/model/docs/model.html.
Greetings,
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so.
Out of the box Pg performs pretty well but still benefits from tuning.
Recommend you head over to
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and see
what makes sense for your configuration.
Anectodally, at my work we produce an app with Pg as the backend
database. Simple tuning, including turning synchronous_commit off,
yielded us 10x better performance. Granted, our app is very write-heavy
and typically Mailman and friends will be far more read-heavy, so giving
Pg enough RAM for in-memory sorts and caching will benefit you more.
--cro
Thanks for the response about how to fine tune postgres - that's some good documentation there, and I certainly appreciate what you've found in your work.
Darren
On Thu, Mar 8, 2018 at 12:28 PM, C. R. Oldham cro@ncbt.org wrote:
Greetings,
However, I still see where the database at times can run VERY hot.
We have set up this server to run with PostgreSQL. When I do a ps aux | grep postgres, I find a lot of processes that have been spawned to take care of mailman - a dozen or so.
Out of the box Pg performs pretty well but still benefits from tuning. Recommend you head over to https://wiki.postgresql.org/wi ki/Tuning_Your_PostgreSQL_Server and see what makes sense for your configuration.
Anectodally, at my work we produce an app with Pg as the backend database. Simple tuning, including turning
synchronous_commitoff, yielded us 10x better performance. Granted, our app is very write-heavy and typically Mailman and friends will be far more read-heavy, so giving Pg enough RAM for in-memory sorts and caching will benefit you more.--cro
Mailman-users mailing list mailman-users@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
Mailman-users mailing list mailman-users@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
participants (5)
-
Abhilash Raj -
C. R. Oldham -
Darren Smith -
Mark Sapiro -
Wolfgang Bock