Jump to content
ryan

PW 3.0.15: New quick-tree, debug, view panels and more

Recommended Posts

This week's version of ProcessWire adds several new panels that provide simpler and more direct access to the page tree, page view and debug tools. New page view options also include multi-language selection and configuration support. And if that's not enough, we've also got install-time utf8mb4 support and more!

https://processwire.com/blog/posts/pw-3.0.15/

  • Like 21

Share this post


Link to post
Share on other sites

Just click the name and it's retrieving a fresh copy from github even for the same version number.

  • Like 1

Share this post


Link to post
Share on other sites

I am wondering is there a way to alter MyISAM tables to InnoDB on a working installation. Wanna try it for the increase in speed for the daily service script. Or is it a bad idea?

  • Like 1

Share this post


Link to post
Share on other sites

@Ivan,

Back in 2012, I decided to compare access times of a locally hosted site both before and after I switched it over to InnoDB. At the time I was in a dialog with Ryan about the benefits of, at least, allowing InnoDB as one of the DB engine choices during install. After all these years, I am delighted to see it finally arrive.

Here's a screenshot of the side-by-side results of running 500 requests vs a, then standard, PW install - one using MyISAM and one using InnoDB.

post-465-0-04164000-1460833530_thumb.png

In general, MyISAM was faster than InnoDB - but it also had a larger standard deviation in the delivery times. Don't worry about the "failures" reported above - remember that the default install back then would serve one of a set of random images and the test tool looks to see which responses matched the first response - a non-match counts as a "failure".

InnoDB did have a slightly higher throughput in terms of requests served per second.

  • Like 5

Share this post


Link to post
Share on other sites

I am wondering is there a way to alter MyISAM tables to InnoDB on a working installation. Wanna try it for the increase in speed for the daily service script. Or is it a bad idea?

You can alter an existing table simply by issuing

ALTER TABLE your-table-name ENGINE=InnoDB;

Though you may want to keep the original MyISAM table around.

Here's a small bootstrap script to drop into the templates folder that converts all tables, either inplace (if $keepOld near the top is set to false) or creating a copy and keeping the MyISAM tables around postfixed with "_orig". It also writes a timestamped SQL file into the templates folder that allows you to switch back to MyISAM. Small caveat: if you select to keep your old tables, those will replace the generated InnoDB tables on restore, which means that all PW changes in between are lost - this may or may not be desired.

So to use it, simply drop it in the templates folder of the PW installation in question and run it from the command line. It will check if you MySQL version is recent enough.

<?php

/**
 * Convert all tables in PW's database from MyISAM to InnoDB
 */
 
include('../../index.php');

// Set this to false if you do not want to keep your MyISAM tables around
// with an _orig postfix
$keepOld = true;

// *****************************************************************************

$sql = "select
    substring(version(), 1, 1) as vmajor,
    substring(version(), 3, 1) as vminor
";
$stmt = $database->query($sql);

if($stmt === false) die("Something went wrong determining the version number!");

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$versionOK = false;

if($row['vmajor'] > 5 || ($row['vmajor'] == 5 && $row['vminor'] >= 6))
    $versionOK = true;

if(!$versionOK) {
    die("You need MySQL 5.6 or later for InnoDB fulltext support!");
}

// Version check finished, get list of tables

$stmt = $database->prepare(
    "SELECT table_name" .
    "  FROM information_schema.tables" .
    " WHERE table_schema = '" . $config->dbName . "'" .
    "   AND lower(engine) != 'innodb'"
);

$count = 0;

if($stmt->execute()) {
    $tbls = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "Found " . count($tbls) . " tables not running on InnoDB" . PHP_EOL;
    
    if(count($tbls) == 0) {
        echo "Nothing to do!" . PHP_EOL;
        exit;
    }
    
    $ts = strftime("%Y%m%d_%H%M%S");
    $restore = fopen("restore_to_myisam_{$ts}.sql", "w+");
    echo "- writing restore commands to file restore_to_myisam_{$ts}.sql" . PHP_EOL;
    
    // Convert each table
    foreach($tbls as $tbl) {
        $tabname = $tbl['table_name'];
        echo $tabname . PHP_EOL;
        
        if($keepOld) {
            // Create a copy of each table, convert the new table,
            // copy the content and switch table names
            
            $sql = "CREATE TABLE {$tabname}_convert LIKE $tabname";
            if($database->exec($sql) === false) continue;
            echo "... Created copy of " . $tabname . PHP_EOL;
            
            $sql = "ALTER TABLE {$tabname}_convert ENGINE=InnoDB";    
            if($database->exec($sql) === false) continue;
            echo "... Changed engine to InnoDB" . PHP_EOL;
            
            $sql = "INSERT INTO {$tabname}_convert SELECT * FROM $tabname";
            if($database->exec($sql) === false) continue;
            echo "... Copied content to InnoDB table" . PHP_EOL;
            
            $sql = "RENAME TABLE $tabname TO {$tabname}_orig," .
                   " {$tabname}_convert TO $tabname";                               
            if($database->exec($sql) === false) continue;
            echo "... Switched tables" . PHP_EOL;
            
            fputs($restore, "RENAME TABLE $tabname TO {$tabname}_innodb," .
                            " {$tabname}_orig TO $tabname;" . PHP_EOL);
            fputs($restore, "DROP TABLE {$tabname}_innodb;" . PHP_EOL);
            $count++;
            
        } else {
            // Convert tables in place
            $sql = "ALTER TABLE $tabname ENGINE=InnoDB";
            if($database->exec($sql) === false) continue;
            
            echo "... " . $tabname . " converted" . PHP_EOL;
            fputs($restore, "ALTER TABLE $tabname ENGINE=MyISAM;" . PHP_EOL);
            $count++;
        }
    }
    fclose($restore);
}

echo "Converted $count tables from MyISAM to InnoDB" . PHP_EOL;

Edit: incorporated @LostKobrakai's suggestion to avoid nesting if statements.

  • Like 5

Share this post


Link to post
Share on other sites

@BitPoet Just a suggestion: Maybe inverting those if statements could make this a bit less nested.

if($database->exec($sql) !== false){
  […]
}
// to
if($database->exec($sql) === false) continue; // or break;
  • Like 1

Share this post


Link to post
Share on other sites

@BitPoet Just a suggestion: Maybe inverting those if statements could make this a bit less nested.

Thanks, @LostKobrakai, that's a good suggestion that adds a lot of readablity. I've amended the script.

Share this post


Link to post
Share on other sites

Love the new panel thingy! Especially the 'side effect' that you can use it as some sort mediaquery tester while dragging the pane :-).

  • Like 6

Share this post


Link to post
Share on other sites

Next I would love to see quick search on the page tree :-) still showing the parents if any.

Share this post


Link to post
Share on other sites

@netcarver: So you say it's not worth the effort?

I like InnoDB, it has row-level locking and is ACID compliant - so I'm biased. That said, I'm not really making a call one way or the other (I know nothing about your application) I was just trying to provide the only performance data I have.

  • Like 1

Share this post


Link to post
Share on other sites

In my case, it's only been worth the effort for one site that I deal with that is doing constant imports all day. It makes a difference in the performance of other requests there. There are a lot of technical benefits to InnoDB relative to MyISAM for sure. But in the PW context (or most CMS contexts) it's hard to derive much value from those benefits until you run into specific needs or scale. In 15+ years and thousands of MyISAM tables around here, I've never experienced any kind of data corruption or loss associated with MyISAM tables. If starting something new I think InnoDB is worthwhile if it can be supported in your environment, but for most cases of existing PW installations I don't think it's worth the effort unless the circumstances mentioned in post are present. From what I understand, MyISAM's fulltext indexes may still be stronger than InnoDB's, though not positive that's still the case. Btw, PW will use InnoDB either way for cases where it makes the most difference. 

  • Like 4

Share this post


Link to post
Share on other sites

Would also be awesome for be able to utilise the PW Panels for the PageTable field. 

Share this post


Link to post
Share on other sites

I have just started playing with the page tree panel and I think I'd rather see it appear beside the current view's content, rather than on top of it. That way I could leave it open and return to what I was doing - it can be a handy reference for other tasks. It would work the same way as the side menu in the Reno theme works. I'd actually like to see it remember it's open/collapsed status also. I know you are worried about the overhead of rendering a page tree on every request, but with the new page tree from 3.0.8, isn't that less of an issue now? Now it's just rendering, rather than querying? Or do I have that wrong - I actually haven't checked the source to see what's actually going on there.

I am also not sure why it has to be so wide - currently to close it you have to move your pointer to the middle of the screen to either click the panel label or the main page to make it disappear. Any reason it can't be the width of the widest page title?

Also, I have what I think is a bug. If you are on the Setup page: http://pw.dev/processwire/setup/ and you click the sitemap icon in the breadcrumbs, it loads the Page Tree fully, not in a panel.

  • Like 1

Share this post


Link to post
Share on other sites

Ryan,

I'm just seeing this now — doooode — super awesome!

I've been head down in some major projects the past few months, so I haven't had a chance to do much more than read the weekly blog posts.
I can't wait to dive into some of these new features. Thanks for keeping the Reno Amin Theme up-to-date with all this new stuff.

  • Like 1

Share this post


Link to post
Share on other sites

Love these new additions too.

I keep thinking the new quick tree panel should open from the left side on the Reno Theme instead of the right.

The icon placement itself makes sense but I get a sense that in a left to right world, it would feel more natural on the left.

  • Like 1

Share this post


Link to post
Share on other sites

I have just started playing with the page tree panel and I think I'd rather see it appear beside the current view's content, rather than on top of it. That way I could leave it open and return to what I was doing - it can be a handy reference for other tasks.

My thoughts exactly. Currently these panels are fun, but this could increase their potential use cases exponentially :)

  • Like 1

Share this post


Link to post
Share on other sites

I had a question about turning the panels off. It seems it does not belong to News & Announcements, so moved it to a separate topic.

Edited by Ivan Gretsky
A question moved to another topic

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...