PHPList 3.3.1 - Big database usage, how to clean?

ℹ️ Support
1

Hi there,

We are running PHPList since around 2 years, so not that long. At the moment it is version 3.3.1.
Unfortunately the system already uses more than 800 MBs (out of 1,024 total).

Where can I check for reasons? It seems, that PHPList brings up more error messages, the bigger the database is?

Thanks!
Simeon

2

@sostberg The size of the database will depend on the number of campaigns you send, the number of subscribers you have, etc. You can use phpmyadmin to see what the largest tables are.

3

@duncanc
Thanks! The largest is “user_user_history” with 517MB, then followed by “usermessage” with 195MB and “listuser” with 106MB and “eventlog” with 20MB.

In the last months we sent out maybe 30 mails to a list of around 15,000 recipients each. (the list of recipients doesn’t change much).

4

@sostberg You can reduce the database size by deleting old rows from the user history table. You will not then have the complete history for some subscribers but that usually isn’t going to be a problem.

phplist doesn’t provide a way to do this so you will have to run a query in phpmyadmin. Depending on how far back the records go, perhaps deleting records that are more than 12 months old will reclaim some space. You can see how many records will be deleted with this query

select count(*) from phplist_user_user_history where date < current_date() - interval 12 month

Then to do the actual deletion

delete from phplist_user_user_history
where date < current_date() - interval 12 month
5

Hi Duncan,

Please excuse my late reply!
This worked, the database is now much smaller. However, I still have problems. Since I did that, the system doesn’t really send all emails (only parts of it), and it displays strange figures, for example: Of 10,692 emails in the list 336,884 were sent as html. And it only sends random shares of all emails, something between 80 and close to 100 percent, but never all. Plus, it uses a lot a database space for sending. 200MBs for just 3 messages of 6,000 subscribers each.

Do you have an idea, what to do?

Thanks,
Simeon

6

That suggest that there are errors when trying to send. Look at the event log page to see whether anything has been reported there.

7

Hi Duncan,

That was the hint, thanks a million! (why didn’t I check the log myself?)
The log included a lot of “Could not instantiate mail function” errors. I checked the config and found an error.

Thanks!