Performance problem with large database records

Product Support
1

Hi all !



I’ve a Mautic 1.2 instance that is registering a lot of traffic, most of them Anonymous Leads.



The table “page_hits” have more than 2 million of records and the problem comes when I try to open in Mautic the statistics page of a Landing Page, the application gets locked during several minutes and it blocks the application.



Monitoring database activity I’ve seen that when I try to open the info page of a Landing Page (not when I try to edit it) there is a query that takes these several minutes to finish because is querying that table:

Code:
SELECT h.page_id, count(distinct(h.tracking_id)) as bounces FROM page_hits h WHERE (h.tracking_id IN (SELECT b.tracking_id FROM page_hits b LEFT JOIN pages p ON b.page_id = p.id WHERE b.code = 200 GROUP BY b.tracking_id HAVING count(distinct(b.page_id)) = 1)) AND (h.page_id IN (4)) GROUP BY h.page_id

I guess that this query is doing a heavy work looking through that 2 millions of rows.

Do you think that is there any way to optimize that query?

Or perhaps, am I suffering any data inconsistency problem?

Thanks a lot!
2

Hi all !

I’ve a Mautic 1.2 instance that is registering a lot of traffic, most of them Anonymous Leads.

The table “page_hits” have more than 2 million of records and the problem comes when I try to open in Mautic the statistics page of a Landing Page, the application gets locked during several minutes and it blocks the application.

Monitoring database activity I’ve seen that when I try to open the info page of a Landing Page (not when I try to edit it) there is a query that takes these several minutes to finish because is querying that table:

SELECT h.page_id, count(distinct(h.tracking_id)) as bounces FROM page_hits h WHERE (h.tracking_id IN (SELECT b.tracking_id FROM page_hits b LEFT JOIN pages p ON b.page_id = p.id WHERE b.code = 200 GROUP BY b.tracking_id HAVING count(distinct(b.page_id)) = 1)) AND (h.page_id IN (4)) GROUP BY h.page_id

I guess that this query is doing a heavy work looking through that 2 millions of rows.

Do you think that is there any way to optimize that query?

Or perhaps, am I suffering any data inconsistency problem?

Thanks a lot!

3

Hi Seti,

Were the indexes on the page_hits table created when you upgraded to 1.2?

4

Hi Alan,

No, I updated it via php app/console doctrine:schema:update --force and probably it hasn’t worked properly

Would you tell me what tables must be indexed in version 1.2?

Is it documented somewhere?

Thanks

5

Hi Alan( @alanhartless ),

I have installed Mautic on my portal and it is collecting approx 90K - 100K anonymous leads daily. I am seeing performance issues with it. Currently it is installed on a 24GB Linode in London(Linode 24GB-RAM 24 GB- CPU 8 Cores-384 GB SSD).

Can i get some help from you or your colleagues. I am really interested in making Mautic a success for my company, however it will only happen if the performance is acceptable.

Thanks
Hemang

6

@smartive - add me to the list. Large number of emails sent, large user DB… experiencing performance issues.

7

@alanhartless @caleb.hurd , i am sure there must be a way to cluster and scale mautic horizontally. Strange that no one has asked this before and there is no documentation to do so.

8

@smartive There is no method, due to the nature of how cron/spooling works within the architecture.