Is switching to InnoDB safe?

Once you've installed phpList... ask questions here!
Forum rules
Please help the volunteers to help you by supplying the version of phpList you are using, browser & version and if possible, a link to your phpList installation.

Is switching to InnoDB safe?

Postby dancrumb » 1:06am, Thu 15 Jan, 2015

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?
dancrumb
phpList newbie
 
Posts: 3
Joined: 12:58am, Thu 15 Jan, 2015

Re: Is switching to InnoDB safe?

Postby danwaterloo » 5:21pm, Fri 16 Jan, 2015

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 = "mysqli.inc";


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('MAX_PROCESS_MESSAGE',999);

# 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("MAILQUEUE_BATCH_SIZE",175);

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

# 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)
define('MAILQUEUE_THROTTLE',1.6);


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.

Dan
danwaterloo
PL Master
 
Posts: 276
Joined: 3:51am, Wed 30 Oct, 2013
Location: http://www.phplistsupport.com

Re: Is switching to InnoDB safe?

Postby dancrumb » 10:13pm, Fri 16 Jan, 2015

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?
dancrumb
phpList newbie
 
Posts: 3
Joined: 12:58am, Thu 15 Jan, 2015


Return to Questions and Problems after Installing

Who is online

Users browsing this forum: No registered users and 4 guests