ListMailPRO Email Marketing Software Forums

ListMailPRO Email Marketing Software Forums => Server Optimization, Tweaks => Topic started by: auto on July 22, 2007, 12:30:41 pm

Title: file lock issues...
Post by: auto on July 22, 2007, 12:30:41 pm
Now that I have 2 million+ records in the lm_users table, I seem to be running into problems where mysql locks the table and then it takes incredibly long to process, sometimes to the point that I have to restart mysql.

Is there any way to optimize this?  Perhaps stored procedures?
Title: innodb seemed to help...
Post by: auto on July 22, 2007, 03:05:17 pm
I am hoping that switching from MyISAM to InnoDB resolves this problem.
Title: file lock issues...
Post by: auto on July 22, 2007, 04:13:08 pm
Hello Dean,

On the bounce processing (admin.php around line 2514) it has a select statement:

select id,list,email,bounces from $utable where email like '$email'

Is there any reason you use a "like" vs. a "="?

When I changed it to "=" it seemed to massively improve performance...

Best,
Tara
Title: file lock issues...
Post by: DW on July 22, 2007, 06:59:12 pm
Hi Tara,

Optimizing the MySQL server (http://listmailpro.com/forum/index.php?topic=918.0) in general should eliminate the problems.  I use LIKE in that statement because I do not change the case on the address the user entered to subscribe.  Theoretically the user should bounce from the address we send it to so there should be little to no effect on changing this to = instead.  In fact, this may be an easy and effective improvement!

Regards
Title: file lock issues...
Post by: auto on July 22, 2007, 07:06:23 pm
Hi Dean,

I have already done all the optimizations from that link.

The problem seems to be that the size of the table lm_users is just too large.

I also have been exploring using stored procedures, but it is overly complicated because of having to switch to php mysqli commands and having stored procs not work correctly with phpMyAdmin.

Is there anything that can be done to split out lm_users into smaller tables, perhaps one table per list?  

I notice that the table names all seem to be variables in the query strings, so it makes me wonder if you've dealt with this before, or if that is just something to do with a table name prefix.

Thanks,
Tara
Title: more info
Post by: auto on July 22, 2007, 08:03:12 pm
Hi Dean,

When I swapped to innodb, it did fix the file locking issue.

However, now it is taking up to 90 seconds to process a bounce request. :(

Tara
Title: file lock issues...
Post by: auto on July 22, 2007, 08:20:48 pm
Hi Dean,

I think I've figured it out...when I switched to innodb the indexes disappeared for some reason, that is probably why it is going so dog slow...

Hopefully by re-adding them it will fix the problem.

Best,
Tara
Title: file lock issues...
Post by: auto on July 23, 2007, 09:44:44 am
Okay, after switching to InnoDB, re-adding the indexes, and figuring out how to flush the log files (they grew out of hand and ate up all available disk space) then it seems that the system is working properly again - now with 2 million records in lm_users. :D
Title: file lock issues...
Post by: DW on July 23, 2007, 02:35:15 pm
Hi Tara,

Just FYI I can only recommend sending out a maximum of about 150,000 emails per day from a single dedicated server.  You can likely send more if you contact Yahoo, which is a major source of problems lately.

http://listmailpro.com/forum/index.php?topic=1579.0
http://listmailpro.com/forum/index.php?topic=1903.0

Regards
Title: file lock issues...
Post by: auto on July 27, 2007, 10:47:11 pm
I did some postfix hacks to fix the Yahoo/Hotmail deferral problem...

I have emailed Yahoo and they aren't helpful in the least.  So I just had to hack it.
Title: wrapping into a transaction
Post by: auto on July 27, 2007, 11:45:08 pm
Now that I've understood InnoDB more...

I hacked the import function so that instead of locking tables, it wraps 100 inserts at a time into a transaction.

I replaced:

Quote

@mysql_query("lock tables $utable",$link);

if($infile || $total_sql_rows > 0){


with

Quote

  @mysql_query("set autocommit=0;begin;",$link);

  if($infile || $total_sql_rows > 0){


then added:

Quote

    if ((($xcnt) % 100) == 0){
        @mysql_query("commit;begin;",$link);
    }
   
    if($done || (($xcnt) % 1000) == 0){
       @mysql_query("commit;",$link);


in place of:

Quote

    if($done || (($xcnt) % 1000) == 0){
       @mysql_query("unlock tables",$link);


commented out:

Quote

     flush();
//     @mysql_query("lock tables $utable",$link);


and finally changed the final piece from:

Quote

@mysql_query("unlock tables",$link);


to:

Quote

@mysql_query("commit;set autocommit=1;");


This has offered at least 100x speed improvement for InnoDB.  Now I can import about 2000-3000 per second instead of 20+ seconds per 1000.