Author Topic: file lock issues...  (Read 11382 times)

auto

  • Posts: 49
    • View Profile
file lock issues...
« 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?
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
innodb seemed to help...
« Reply #1 on: July 22, 2007, 03:05:17 pm »
I am hoping that switching from MyISAM to InnoDB resolves this problem.
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
file lock issues...
« Reply #2 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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

DW

  • Administrator
  • Posts: 3782
    • View Profile
    • http://listmailpro.com
file lock issues...
« Reply #3 on: July 22, 2007, 06:59:12 pm »
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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

auto

  • Posts: 49
    • View Profile
file lock issues...
« Reply #4 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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
more info
« Reply #5 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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
file lock issues...
« Reply #6 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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
file lock issues...
« Reply #7 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
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

DW

  • Administrator
  • Posts: 3782
    • View Profile
    • http://listmailpro.com
file lock issues...
« Reply #8 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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

auto

  • Posts: 49
    • View Profile
file lock issues...
« Reply #9 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.
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs

auto

  • Posts: 49
    • View Profile
wrapping into a transaction
« Reply #10 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.
$5,620 in 24 hours to a small email list of 500?  http://eCa.sh/5620in24hrs