Problem of performance with mailwizz

C

chriphil

Member
Hi,

I'm currently have a big problem of performance with mailwizz.
I've ~ 11 Millions of users (50% in one list, the rest dispatched in 9 lists).
Each lists have 15 fields and 2 or 3 segments.

When I try to list the campaigns or look at the detail of a campaign, it takes a very long time.
To load the dashboard it's terrible, but I already seen the thread about that.

The task for bounce is also very long to execute (and it's not an imap problem, mailboxes are empty).

I'm running mailwizz on a Xeon E3 1245v5 with 64GB of RAM, all the mysql is loaded in RAM.
The disk is an SSD drive and doesn't suffer of IO bottleneck.

The InnoDB data size is around 22GB.

I'm running mailwizz v1.3.6.5

Is there anyone who have the same problem ?

Thanks,
JC
 
The dashboard is very slow, I already know that. The main problem is not the dashboard but all the part of mailwizz.
Listing campaign, consult the detail, process the bounce background task, etc...
 
@Jean-Claude CHRISTOPHE - You can easily find what the problem is. Locate a page that is terrible slow, then enable debugging. Then refresh the page. This time you will see a lot of info in the page footer. This info contains timings as well, it says how much time it took to the app to do each action. Identify the one that takes the most time and let's see which is it.
 
Hi
Yes, you're right. (but it doesn't work for the pages who load ajax content like the dashboard ;-)).

So I just apply it and loaded the information detail of a campaign: 107 seconds of load, 16MB used.

Here are the requests who take long time (30 secs each):


begin:system.db.CDbCommand.query(SELECT COUNT(DISTINCT t.subscriber_id) as
counter FROM `mw_list_subscriber` `t` LEFT JOIN `mw_list_field_value`
`fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`)
WHERE (((t.list_id=:ycp9) AND (t.status=:ycp10) AND (
(`fieldValues434`.`field_id` = :field_id434 AND
(CAST(`fieldValues434`.`value` AS UNSIGNED) = :value434 OR
CAST(`fieldValues434`.`value` AS UNSIGNED) = :value435) ) )) AND
((t.list_id=:ycp7) AND (t.status=:ycp8))). Bound with :ycp9='15',
:ycp10='confirmed', :field_id434=434, :value434=1, :value435=5, :ycp7='15',
:ycp8='confirmed')

begin:system.db.CDbCommand.query(SELECT COUNT(DISTINCT t.subscriber_id) as
counter FROM `mw_list_subscriber` `t` LEFT JOIN `mw_list_field_value`
`fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`)
WHERE ((t.list_id=:ycp28) AND (t.status=:ycp29) AND (
(`fieldValues434`.`field_id` = :field_id434 AND
(CAST(`fieldValues434`.`value` AS UNSIGNED) = :value434 OR
CAST(`fieldValues434`.`value` AS UNSIGNED) = :value435) ) )). Bound with
:ycp28='15', :ycp29='confirmed', :field_id434=434, :value434=1,
:value435=5)

I saved the content of the debug page, I can send it to you via MP if you want.

Regards,
JC
 
As far as i can tell, these are for segments right?
how many subscribers do the segments count? But the list entirely?
 
@Jean-Claude CHRISTOPHE - Yeah. i think that's the cause, sorting 5M emails to obtain only 63K is not easy.
Can you please do me a favour and run this in mysql then post here the results:
Code: 
EXPLAIN SELECT COUNT(DISTINCT t.subscriber_id) as
counter FROM `mw_list_subscriber` `t` LEFT JOIN `mw_list_field_value`
`fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`)
WHERE (((t.list_id=15) AND (t.status='confirmed') AND (
(`fieldValues434`.`field_id` = :field_id434 AND
(CAST(`fieldValues434`.`value` AS UNSIGNED) = 1 OR
CAST(`fieldValues434`.`value` AS UNSIGNED) = 5) ) )) AND
((t.list_id=15) AND (t.status='confirmed')));
 
Code: 
+----+-------------+----------------+------+--------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+---------+--------------------------+
| id | select_type | table  | type | possible_keys  | key  | key_len | ref  | rows  | Extra  |
+----+-------------+----------------+------+--------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+---------+--------------------------+
|  1 | SIMPLE  | t  | ref  | PRIMARY,fk_list_subscriber_list1_idx,list_email,status_last_updated,list_id_status  | list_id_status  | 49  | const,const  | 5331121 | Using where; Using index |
|  1 | SIMPLE  | fieldValues434 | ref  | fk_list_field_value_list_field1_idx,fk_list_field_value_list_subscriber1_idx,field_subscriber,field_id_value | field_subscriber | 8  | const,mailwizz.t.subscriber_id |  1 | Using where  |
+----+-------------+----------------+------+--------------------------------------------------------------------------------------------------------------+------------------+---------+--------------------------------+---------+--------------------------+
2 rows in set (0.00 sec)
 
Last edited:
This looks good to me, the query uses indexes for both tables (index/where) so by definition there's not much how the query can be improved.
I am not sure why you have this issue for only 5M records, this should be def. faster.
What is the size of the mysql innodb_buffer_pool_size? This article helps you understand how much it should be: http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
Did you do any mysql optimization at all? https://tools.percona.com/ can help you generate a proper mysql.config for your database size, usage and amount of ram.
 
yes, already done !

Code: 
innodb_file_per_table  = 1
innodb_buffer_pool_size  = 25GB
innodb_buffer_pool_instances  = 25
innodb_log_buffer_size  = 16M
innodb_flush_log_at_trx_commit  = 0
 
Jean-Claude CHRISTOPHE said:
yes, already done !

Code: 
innodb_file_per_table  = 1
innodb_buffer_pool_size  = 25GB
innodb_buffer_pool_instances  = 25
innodb_log_buffer_size  = 16M
innodb_flush_log_at_trx_commit  = 0
Click to expand...

what is
innodb_log_file_size
max_heap_table_size
tmp_table_size
wait_timeout
innodb_lock_wait_timeout
?
 
Code: 
innodb_log_file_size = 50331648 (48M)
max_heap_table_size  = 536870912 (512M)
tmp_table_size = 536870912 (512M)
wait_timeout = 1800
innodb_lock_wait_timeout = 50
 
Hi,
I tried with the percona configuration but the result is worst !
I tried also with
innodb_log_file_size 1/4 of innodb_buffer_pool_size
wait_timeout = innodb_lock_wait_timeout = 1000
but the query are always very slow...

Here is the my.cnf configuration:
Code: 
[mysqld]
bind-address                    = 127.0.0.1
max_connections                 = 32

innodb_file_per_table           = 1
innodb_buffer_pool_size         = 25GB
innodb_buffer_pool_instances    = 25
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 0
innodb_lock_wait_timeout        = 1000
innodb_log_file_size            = 6GB

binlog_cache_size               = 16M
query_cache_limit               = 128MB
query_cache_size                = 128MB
query_cache_type                = 1
tmp_table_size                  = 512M
max_heap_table_size             = 512M
log-bin                         = /var/db/mysql/mysql-log/mysql-bin.log
tmpdir                          = /var/db/mysql/mysql-tmp
slow_query_log                  = 1
slow_query_log_file             = /var/db/mysql/mysql-log/log-slow-queries.log
long_query_time                 = 10
#log_queries_not_using_indexes  = 1
interactive_timeout             = 1800
wait_timeout                    = 1000
 
done ! normally when I change innodb_buffer_pool_size, I chage the innodb_buffer_pool_instances too. Anyway, I let it as it (25).
Just let the server running for a while and I will post the result this night.
Regards
JC
 
Hi,

Sorry for the delay, I was ill last week.
There are no ameliorations, the interface is unusable now :-(

Here is the request who took very long time (304 secs):

Code: 
# Time: 160628 15:31:16
# User@Host: mailwizz[mailwizz] @  [127.0.0.1]  Id:   157
# Query_time: 304.479513  Lock_time: 0.000230 Rows_sent: 1  Rows_examined: 10274614
use mailwizz;
SET timestamp=1467120676;
SELECT COUNT(DISTINCT t.subscriber_id) as counter FROM `mw_list_subscriber` `t`  LEFT JOIN `mw_list_field_value` `fieldValues434` ON (`fieldValues434`.`subscriber_id`=`t`.`subscriber_id`) WHERE ((t.list_id='15') AND (t.status='confirmed') AND ( (`fieldValues434`.`field_id` = 434 AND (CAST(`fieldValues434`.`value` AS UNSIGNED) = 0 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 3 OR CAST(`fieldValues434`.`value` AS UNSIGNED) = 4) ) ));
 
You must log in or register to reply here.
Back
Top