Slow Searches when querying the database and custom fields

M

mike-gcs

Member
We have a need to search for a person in the list and noted that some searches take a very long time. The CPU during those searches is less than 2/16 for average usage. Is there a way to speed up?

Server Specs are 16vCPU and 32GB RAM on SSDs. Current RAM is sitting at 10GB out of 32GB.

There 5 million records.


Here are some screenshots

1739573159940.png

1739573132398.png

1739573044620.png
 
mike-gcs said:
We have a need to search for a person in the list and noted that some searches take a very long time. The CPU during those searches is less than 2/16 for average usage. Is there a way to speed up?

Server Specs are 16vCPU and 32GB RAM on SSDs. Current RAM is sitting at 10GB out of 32GB.

There 5 million records.


Here are some screenshots

View attachment 21843

View attachment 21842

View attachment 21841
Click to expand...
Hi Mike,


Is your mysql tuned for your resources?
 
dreyerretief said:
Hi Mike,


Is your mysql tuned for your resources?
Click to expand...
Yes. I have gone through and made sure that all steps were followed.

Overall, the rest of the system works as intended. We easily send upwards of 500,000 messages a day. Based on performance, we can hit up to 17 million a day. The only issue is the searches from the List Subscribers page. Any query will take about 10 minutes or just not work. All other site tools and functions seem to work properly.
 
Hello,
If the first numeric column is the aprox numbers of rows in that table (if not, any value from the numeric columns is huge) , I can read there something like 18 billions rows? That's more than huge... There is no way on earth to read fast from that table. You should start thinking organising the data, delete old one, etc. The list subscribers table is also huge. Please read this article: https://www.mailwizz.com/blog/mailwizz-for-high-volume-senders/

Cosmin
 
Rows is the last column.
The names in order are:
  • Table
  • Engine
  • Collation
  • Data Length
  • Index Length
  • Data Free
  • Auto Increment
  • Rows
It is about 19.5GB with 265,923,010 rows

List is 5,460,674 with 46 custom fields. I'm working with them to review if all the custom fields are needed. They do have a lot of variables in their emails.
 
You must log in or register to reply here.
Back
Top