file lock issues...

[auto]

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?

[auto]

I am hoping that switching from MyISAM to InnoDB resolves this problem.

[auto]

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

[DW]

Hi Tara,

Optimizing the MySQL server 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

[auto]

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

[auto]

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

[auto]

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

[auto]

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.

[DW]

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

[auto]

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.

[auto]

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:

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

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

with

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

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

then added:

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

in place of:

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

commented out:

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

and finally changed the final piece from:

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

to:

@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.