Page 1 of 1

Is switching to InnoDB safe?

PostPosted: 1:06am, Thu 15 Jan, 2015
by dancrumb
So, I'm seeing what appear to be issues with my PHPList installation (version 3.0.10) and I'm wondering if it's related to the fact that I'm using MyISAM tables for the backend database.

I'll see the mysql process pegging one of my CPUs at 100% and, when I log in and run SHOW PROCESSLIST, I see a lot of:

"Waiting for table level lock"

I know that the locking granularity of MyISAM is table at best, whereas InnoDB allows row level locking. There are a *lot* of subscribers in my instance of PHPList and I think the contention for this table is just too high.

Is it safe to switch to InnoDB? Will PHPList be OK with that? I'm guessing I'd need to set autocommit to 1, since PHPList defaults to MyISAM (and implicitly assumes that all requests are committed always).

Also, will I have problems during upgrades?

Anyone have any experience doing this? Any problems or pitfalls?

Re: Is switching to InnoDB safe?

PostPosted: 5:21pm, Fri 16 Jan, 2015
by danwaterloo
Hi dancrumb,
I don't have personal experience with this, but I don't think that the the innodb tables would be any problem. I don't think that the sql commands care what kind of tables they are. For upgrades, you can address that as it happens.

I'd first make sure you have mysqli setting in the config.php file as follows:
# select the database module to use
# anyone wanting to submit other database modules is
# very welcome!
$database_module = "";

If that doesn't work, you might want to slow down the sending a bit so your computer isn't overloaded, using the batch control settings:
# max messages to process
# if there are multiple messages in the queue, set a maximum to work on

# define the amount of emails you want to send per period. If 0, batch processing
# is disabled and messages are sent out as fast as possible

# define the length of one batch processing period, in seconds (3600 is an hour)

# to avoid overloading the server that sends your email, you can add a little delay
# between messages that will spread the load of sending
# you will need to find a good value for your own server
# value is in seconds, and you can use fractions, eg "0.5" is half a second
# (or you can play with the autothrottle below)

Lastly, backup your database, and change the tables over to innodb. Then you can try sending again and see if it's any faster. Be sure to optimize your database to clear out the uncommitted transactions. You also might want to see if you have enough ram for the mysql to run mostly in memory. If it's running out of ram, you'll be io bound on the whole system.


Re: Is switching to InnoDB safe?

PostPosted: 10:13pm, Fri 16 Jan, 2015
by dancrumb
Thanks Dan,

FWIW, the root of the issue seems to be this query:

Code: Select all
select min(date(user.modified)),max(date(user.modified)) from phplist_list list ,phplist_user_user user ,phplist_listuser listuser;

Seems like a weird query, not least because it's including a number of tables that aren't used elsewhere in the query.

Any idea what this is?