Problem with database change sqlite -> mysql
Greetings,
I am totally new to this list and relatively new to mailman3 and came here because I have a problem I would like to solve soon. I hope somebody has a clue:
I am running a really small list site with 10 or so lists, all of them have low traffic and no more than ~70 members.
The lists are running on a shared server at uberspace. I followed their guide at https://lab.uberspace.de/guide_mailman-3/
Mailman Core Version GNU Mailman 3.3.5 (Tom Sawyer) Mailman Core API Version 3.1 Mailman Core Python Version 3.8.13 (default, May 19 2022, 15:06:19) [GCC 9.3.1 20200408 (Red Hat 9.3.1-2)]
Since my migration from mm2 to mm3 I see what I consider high loads and excessive i/o and memory usage. My guess is that the main problem is the database backend for mailman, posterious and hyperkitty, which is sqlite. The database is >200MB at the moment (I have not found out why it is so huge, I tried to delete the hyperkitty archives from there and no longer archive messages, but maybe I have not managed to delete all).
I take any recommandation for this situation. My current idea is changing the database backend to something else, i.e. mysql.
I tried to follow this guide
https://www.rockandnull.com/django-dumpdata/
but the "loaddata" command fails. The stack trace is below. Unfortunatelly, I have close to no knowledge of django and don't know why the Email matching query not exists.
Will postgres make things easier?
Thanks in advance for any help.
Moritz
Here is the stacktrace:
Traceback (most recent call last): File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 173, in __get__ rel_obj = self.field.get_cached_value(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/mixins.py", line 15, in get_cached_value return instance._state.fields_cache[cache_name] KeyError: 'starting_email'
During handling of the above exception, another exception occurred:
Traceback (most recent call last): File "manage.py", line 10, in <module> execute_from_command_line(sys.argv) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line utility.execute() File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv self.execute(*args, **cmd_options) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute output = self.handle(*args, **options) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 78, in handle self.loaddata(fixture_labels) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 123, in loaddata self.load_label(fixture_label) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 190, in load_label obj.save(using=self.using) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/serializers/base.py", line 223, in save models.Model.save_base(self.object, using=using, raw=True, **kwargs) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/base.py", line 763, in save_base pre_save.send( File "/home/daneben/.local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 180, in send return [ File "/home/daneben/.local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 181, in <listcomp> (receiver, receiver(signal=self, sender=sender, **named)) File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/signals.py", line 96, in Thread_on_pre_save kwargs["instance"].on_pre_save() File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/models/thread.py", line 157, in on_pre_save self.find_starting_email() File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/models/thread.py", line 149, in find_starting_email if self.starting_email is not None: File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 187, in __get__ rel_obj = self.get_object(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 307, in get_object return super().get_object(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 154, in get_object return qs.get(self.field.get_reverse_related_filter(instance)) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get raise self.model.DoesNotExist( hyperkitty.models.email.DoesNotExist: Problem installing fixture '/home/daneben/mailman-suite/datadump.json': Email matching query does not exist.
moritz both writes:
Since my migration from mm2 to mm3 I see what I consider high loads and excessive i/o and memory usage.
This isn't informative enough to guess whether this is unusual for Mailman 3 or not.
My guess is that the main problem is the database backend for mailman, posterious and hyperkitty, which is sqlite. The database is >200MB at the moment (I have not found out why it is so huge,
I don't know how active your users are, but my mbox-format INBOX is currently just over 200MB with 1770 messages in it. I religiously prune out attachments (all of them, not just "big ones"), but it your lists are at all active, I wouldn't be surprised at that size of HyperKitty archive.
I don't know how HyperKitty handles attachments offhand, but I don't think they would be in the database itself; presumably they would be stored in separate files.
I take any recommandation for this situation. My current idea is changing the database backend to something else, i.e. mysql.
Either MySQL or PostgreSQL is a much better solution for a production server, at least from the point of view of CPU. I'm not sure about I/O or memory.
If you go the MySQL route, be aware that a while back there were some issues with record size and with Unicode. I'm not sure the versions you specify have those fixed. You can fix them yourself by tweaking the MySQL configuration; ask us if you prefer this, somebody will help (sorry, I don't remember details and need to go to a meeting).
but the "loaddata" command fails. The stack trace is below. Unfortunatelly, I have close to no knowledge of django and don't know why the Email matching query not exists.
I will come back to this after meetings if someone doesn't beat me to it.
Steve
Steve,
thank you for your comments.
Am 29.06.22 um 06:33 schrieb Stephen J. Turnbull:
Since my migration from mm2 to mm3 I see what I consider high loads and excessive i/o and memory usage.
This isn't informative enough to guess whether this is unusual for Mailman 3 or not.
I agree. As it turns out, in the first row, accessing the hyperkitty archive over http consumes a lot of ressources. My guess is that frequent loading and querying the sqlite database is the main cause.
[...] If you go the MySQL route, be aware that a while back there were some issues with record size and with Unicode. I'm not sure the versions you specify have those fixed. You can fix them yourself by tweaking the MySQL configuration; ask us if you prefer this, somebody will help (sorry, I don't remember details and need to go to a meeting).
A pointer to these fixes would help me ... as well as a hint how the "loaddata" crash could be mitigated.
Thanks again for your time.
Moritz
On 6/28/22 21:33, Stephen J. Turnbull wrote:
I don't know how HyperKitty handles attachments offhand, but I don't think they would be in the database itself; presumably they would be stored in separate files.
By default they are stored in a hyperkitty_attachment table in the database, but you can set HYPERKITTY_ATTACHMENT_FOLDER in your Django settings to store them as files.
See https://gitlab.com/mailman/hyperkitty/-/issues/413 for additional information on this.
-- Mark Sapiro mark@msapiro.net The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Hello,
my problem with "loaddata" still persists - can anybody think of why the "loaddata" command fails like this? I am trying to migrate mailman3 from sqlite to mysql.
Also, could anybody tell me which tweaks to the mysql configuration are neccessary for mailman.
Thank you.
Moritz
Am 28.06.22 um 15:58 schrieb moritz both:
Here is the stacktrace:
Traceback (most recent call last): File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 173, in __get__ rel_obj = self.field.get_cached_value(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/mixins.py", line 15, in get_cached_value return instance._state.fields_cache[cache_name] KeyError: 'starting_email'
During handling of the above exception, another exception occurred:
Traceback (most recent call last): File "manage.py", line 10, in <module> execute_from_command_line(sys.argv) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line utility.execute() File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv self.execute(*args, **cmd_options) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute output = self.handle(*args, **options) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 78, in handle self.loaddata(fixture_labels) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 123, in loaddata self.load_label(fixture_label) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 190, in load_label obj.save(using=self.using) File "/home/daneben/.local/lib/python3.8/site-packages/django/core/serializers/base.py", line 223, in save models.Model.save_base(self.object, using=using, raw=True, **kwargs) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/base.py", line 763, in save_base pre_save.send( File "/home/daneben/.local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 180, in send return [ File "/home/daneben/.local/lib/python3.8/site-packages/django/dispatch/dispatcher.py", line 181, in <listcomp> (receiver, receiver(signal=self, sender=sender, **named)) File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/signals.py", line 96, in Thread_on_pre_save kwargs["instance"].on_pre_save() File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/models/thread.py", line 157, in on_pre_save self.find_starting_email() File "/home/daneben/.local/lib/python3.8/site-packages/hyperkitty/models/thread.py", line 149, in find_starting_email if self.starting_email is not None: File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 187, in __get__ rel_obj = self.get_object(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 307, in get_object return super().get_object(instance) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/fields/related_descriptors.py", line 154, in get_object return qs.get(self.field.get_reverse_related_filter(instance)) File "/home/daneben/.local/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get raise self.model.DoesNotExist( hyperkitty.models.email.DoesNotExist: Problem installing fixture '/home/daneben/mailman-suite/datadump.json': Email matching query does not exist.
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
moritz both writes:
"/home/daneben/.local/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get raise self.model.DoesNotExist( hyperkitty.models.email.DoesNotExist: Problem installing fixture '/home/daneben/mailman-suite/datadump.json': Email matching query does not exist.
I assume you're using "python manage.py dumpdata > datadump.json" to dump and "python manage.py loaddata > datadump.json" to load the data? Are you using the right python binary, ie the one Django uses?
I guess it's possible that some data is not in the database, but retrieved from Mailman core's on the fly, in which case you need Mailman core running.
That said, this is entirely handled by Django, I think, and you might be better off asking on Django channels. I'll give you a couple of wild guesses, but unless somebody else knows more about Django than I do, it's probably fastest to ask on Django channels.
I did a quick search for "datadump.json" and brought up these references, but none seem to refer to Mailman: https://stackoverflow.com/questions/4371375/db-error-when-running-python-man... https://www.reddit.com/r/learnpython/comments/8jkre9/deserializationerror_wh... https://www.rockandnull.com/django-dumpdata/
Here's a tutorial on SQLite to MySQL migration in Django: https://newbedev.com/what-s-the-best-way-to-migrate-a-django-db-from-sqlite-...
These are tutorials on SQLite to PostgreSQL migration in Django: https://www.solution-hub.com/Blog_description/37/ https://dev.to/thepylot/how-to-migrate-data-from-sqlite-to-postgresql-in-dja... https://gist.github.com/sirodoht/f598d14e9644e2d3909629a41e3522ad https://simpleit.rocks/python/django/migrate-django-sqlite-to-postgresql/
Perhaps one of those references will help.
Steve
participants (3)
-
Mark Sapiro -
moritz both -
Stephen J. Turnbull