Optimizing Web Pages

With the new Web site I’m developing drawing nearer to its public debut, and with the entire backend being written from scratch by me, I’ve become concerned with optimizing the output as much as possible.

While searching for some resources the other night (I was mainly looking for a Firefox add-on that would display a page’s load time, the way Netscape used to do – the only one I found has not been updated to work with FF3, yet), I came across two interesting resources.

CCleaner – Keeping Your Computer Optimized

A few months ago, I was introduced to a great utility called CCleaner. CCleaner is a freeware application for Windows that helps you keep your computer running in its best condition. Following is a quick summary of the features offered by CCleaner.

Clean up

The main feature of CCleaner is to clean all of the unnecessary and temporary files from your computer. It analyzes all of your temporary directories, your cookies, your recycle bin, etc. and finds all of the files you no longer need.

The first time I ran the tool, it found somewhere around 10 gigs of temporary files on my computer at work.

Registry cleaning

CCleaner is also capable of backing up and cleaning up your Windows registry. Every time I run CCleaner, it seems to find unused file extensions, incorrect links, etc. in my registry, so I try to use it at least once a month.

Startup management

CCleaner also offers the ability to manage your startup processes. As we all know, all computers are prone to enabling rogue startup programs that can really slow down your computer. CCleaner allows you to remove any entries you no longer want automatically running every time you boot your computer.

In the old days, it was really easy to manage this, as all startup programs were added to your “startup” folder in the start menu. However, now it’s extremely rare that any of your startup programs actually show up in that folder. Some of them show up in the adminstrator’s startup folder and most of them are actually added to your registry.

With CCleaner, you don’t need to worry about where they’re stored, as you can view and remove all of them within this one utility.

Uninstall

As if the features mentioned above weren’t enough, CCleaner also offers you the ability to manage all of the programs you have installed on your computer from one interface.

Windows has been trying to do this for years, with the “Add/Remove Programs” utility in the control panel (in Vista, it’s called “Programs and Features”, but it’s the same thing). For the most part, it’s been successful. Unfortunately, though, Windows only seems to include those utilities that come with their own uninstallers. I’ve had quite a few experiences where Windows didn’t give me the option to uninstall some software I had installed.

Back when I was using Windows 3.1 and Windows 95, I actually bought a program called “Window Cleaner”, which was built specifically for uninstalling applications. With Windows 98 and my old copies of Windows XP, I purchased and ran Norton SystemWorks, which offered the same features. Now, though, I don’t any of those commercial applications. CCleaner does a better job of recognizing installed applications and uninstalling them.

Conclusion

CCleaner is a fantastic utility. Honestly, I don’t see any reason why any Windows user would not download and install the application. The functionality offered within CCleaner is on par or above most commercial applications available on the market; and it’s completely free.

Last year, I purchased a subscription to Norton 360 for my antivirus and optimizaton needs. This year, however, with CCleaner installed, I have no need for all of the bloat that comes with Norton. I can get antivirus functionality from many sources (including a version of Symantec I get for free from work), and I no longer have a need for all of the optimization features built into Norton.

I give this utility a 5 out of 5, and would absolutely recommend it to anyone.

MySQL Developer Training – Day 5

On day 5, we went through all of the rest of the material in the course. By the end of the day, we had been provided all of the information we should need in order to pass both MySQL Developer examinations. My notes from the final day of class follow.

Day 5

DECLARE statement

  • The declare statement defines items local to a routine
    • Local variables
    • Conditions and handlers
    • Cursors
  • Declare only allowed inside a BEGIN…END
  • Declarations must follow a specific order
    • Variables
    • Conditions – error handler – captures errors that occur in the procedure/function and determines how they should be handled
      • DECLARE condition_name CONDITION FOR condition_value;
      • SQLSTATE Condition value
        • DECLARE null_not_allowed CONDITION FOR SQLSTATE ‘23000’;
        • MySQL errors are strings, so they need to be quoted
      • Continue handler
        • DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x=1;
        • A continue handler allows the procedure to continue even if the condition is met
      • Exit handler
        • An exit handler will stop the procedure/function when the condition is met (an error is generated)
    • Cursors
      • A control structure within stored routines for record retrieval
        • One row at a time
      • A cursor is basically a pointer pointing to the current row we are fetching
      • Cursors are mostly used within loops that fetch and process the rows
      • Asensitive – MySQL decides how to process the information that’s fetched
      • Read-only
      • Non-scrolling
        • The cursor strictly moves from one row to the next
        • You cannot skip records
        • You cannot go backwards
      • Cursors must be declared before declaring handlers
      • OPEN opens a previously declared cursor
      • FETCH
        • obtains the next row using the specified open cursor, and advances the cursor pointer
        • When there is no next row, an error will result
          • You would use that error as a condition to break out of the loop
      • A cursor is destroyed automatically at the end of the BEGIN…END block
      • If you choose to close the cursor ahead of time, you can use the CLOSE command
      • Prepared statements can only have one cursor, and it is not named
    • Handlers

Triggers

  • Named database objects
  • Activated when

Creating triggers

  • CREATE TRIGGER trigger_name { BEFORE | AFTER }
    { INSERT | UPDATE | DELETE }
    ON table_name
    FOR EACH ROW
    triggered_statement
  • CREATE TRIGGER City_AD AFTER DELETE ON City
    FOR EACH ROW
    INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name);
  • OLD and NEW are keywords that can be used within triggers

Trigger error handling

MySQL handles errors during trigger execution as follows:

  • Failed BEFORE triggers
    • Operation on corresponding row is not performed
  • After trigger execution
    • BEFORE trigger events and the row operation will execute successfully in non-transactional tables
    • In transactional tables, a rollback of all changes made by the statement will occur

Disallowed statements

  • SQL prepared statements
  • Explicit or implicit COMMIT or ROLLBACK
  • Return a result set
  • FLUSH
  • Recursive – cannot modify a table that is in use (reading or writing)
    • These types of things must be taken care of in BEFORE statements, rather than AFTER statements, because the table will not yet be open at that point

Storage Engines

SQL Parser and storage engines

  • Client sends requests to the server as SQL
  • Two-tier processing
    • Upper tier includes SQL parser and optimizer
    • Lower tier comprises a set of storage engines
  • SQL itself is not engine-specific, generally
    • Some exceptions include COMMIT, ROLLBACK, CREATE TABLE (which needs an engine declaration and can allow foreign key information for transactional tables), etc.
  • Steps to parse and return
    • Parse
    • Optimize
    • Retrieve/Store
      • This is where things like ORDER BY, GROUP BY, etc. are performed
  • Storage medium
  • Transactional capabilities
    • InnoDB allows transactions while MyISAM does not
  • Locking
    • InnoDB locks on a row-by-row basis
    • MyISAM locks on a table-by-table basis
  • Backup and recovery
    • MyISAM allows you to physically copy individual tables, etc. from one place in your filesystem to another place
    • InnoDB stores its information for all databases on the server in a single file, so it is more difficult to copy individual items
  • Optimization
  • Special features
  • MySQL server operates same for all storage engines
    • SQL commands independent of engine

Available Storage Engines

  • MySQL provides and maintains several storage engines
  • Also compatible with many third party engines
  • MySQL developed:
    • MyISAM
      • Incredibly fast – even more so when you use compression, fixed-width columns and good indexing
      • Data stored in table
      • Table-level locking
      • Does not support transactions
    • Falcon
    • NDB/Cluster
    • Memory
      • Data is in memory
      • Fastest engine that exists, but nothing is stored anywhere on disk
    • Archive
      • Highly compressed
      • Only supports INSERT and queries
    • Federated
      • creates an alias of an existing database on a separate server
    • Blackhole
    • CSV
    • Example – doesn’t do anything, but is in the code to show people a template of how to create an engine
  • Third party engines
    • InnoDB
      • Transactional
      • Foreign key constraints
      • Row-level locking
      • Backups
    • solidDB
    • InfoBright BrightHouse
    • Nitro
    • PBXT

You can set the storage engine when creating a table, but you are not required to do so. If you do not specify the engine, it will be set to the database default

MyISAM Storage Engine

  • MyISAM is the MySQL default
  • Manages tables with specific characteristics
    • Represented by three files
    • Most flexible AUTO_INCREMENT
    • Fast, compressed, read-only tables save space
    • Manages contention between queries
      • By default, writing to disk takes precedence over reading from disk, but that can be overridden
      • SELECT statements can be set to a higher priority with the HIGH PRIORITY switch
      • UPDATE, DELETE and INSERT statements can be set to a lower priority with LOW PRIORITY
      • INSERT statements can also be set to an extremely low priority with DELAYED
    • Portable storage format
    • Specify number rows for a table
    • Disable updating of non-unique indexes and enable the indexes
      • DISABLE KEYS/ENABLE KEYS
      • Indexes of non-unique keys can be disabled so that the indexes are not re-created each time a row is changed. The indexes will be rebuilt when the keys are reenabled
    • Tables take up very little space
  • Three row storage formats
    • Fixed-row format
    • Dynamic row format
    • Compressed format
      • Tables must be deliberately compressed
      • Compressed tables are read-only
        • Tables must be decompressed to modify
      • Using the myisampack utility
        • Includes a mixture of “True” compression and a set of optimizations
        • Each record compressed separately with small cost to decompress
        • Must be performed on the host machine
      • Use myisamchk afterward to update the indexes
      • Always backup tables prior to running utilities
  • Table-level locking
  • Acquiring locks
  • Tables with no holes support concurrent inserts
    • If a table is completely defragmented, any number of inserts can all be performed at the same time
  • Tables with holes do not support concurrent inserts by default
  • Can change priority of statements that retrieve or modify data
  • Write request not processed until current readers finished

InnoDB Storage Engine

  • Manages tables with specific characteristics
    • Represented on disk by a .frm format file as well as data and index storage
    • Supports transactions
    • ACID compliant
      • Satisfies ACID conditions
      • General locking properties
        • Does not need to set locks to achieve consistent reads
        • Uses row-level locking per concurrency properties
        • May acquire row locks as necessary to improve concurrency
        • Deadlock is possible
      • Supports two locking modifiers
      • Repeatable Read isolation level allows modifiers
    • Auto-recovery after crash
    • MVCC and non-locking reads
    • Supports foreign keys and referential integrity
    • Supports consistent and online logical backup
  • Tablespace for storing all table contents together
  • Log files for recording transaction activity
  • Format file (.frm)
  • Logical storage area can contain multiple files
  • Table-specific file (.ibd)
    • –innodb-file-per-table
  • Manages InnoDB-specific log files
  • Log files used for auto-recovery

Memory Engine

  • Uses tables stored in memory
    • Tables are temporary
  • Must have fixed-length rows
  • Manages tables with specific characteristics
  • Formerly HEAP engine
  • Memory indexing options
    • Uses HASH indexes by default
    • BTREE is preferable for some operators
      • Ranges

Optimization

  • Server processes queries more efficiently and performs better
  • Optimization strategies
    • Use indexing properly
      • Large tables require indexing for efficiency
      • Benefits of indexes
        • Contain sorted values
        • Use less disk I/O
        • Enforce uniqueness constraints
      • Downsides of indexing
        • Any time anything in the table is changed, the indexes have to be rebuilt
        • Can slow down some data manipulations
        • Uses additional space
      • Types of indexes
        • Three general types
          • Primary key
          • Unique
            • Allows NULLs, but other than that requires the value of the cell to be different than anything else in the column
          • Non-unique
            • Allows NULLs and allows duplicate entries
      • Primary versus unique
        • Primary cannot contain NULL
        • Only one primary key is allowed per table
      • Adding indexes
        • ALTER TABLE table_name ADD PRIMARY KEY(column_name);
        • CREATE INDEX index_name ON table_name(column_name);
      • Removing indexes
        • ALTER TABLE table_name DROP PRIMARY KEY;
        • ALTER TABLE table_name DROP INDEX index_name;
        • DROP INDEX index_name ON table_name;
        • DROP INDEX `PRIMARY` ON table_name;
          • PRIMARY must be contained within back tick quotes, because it is a name. If it is not within back ticks, it will be treated as a keyword that doesn’t belong in that syntax
          • PRIMARY is the official name of the primary key in a table
      • Using index prefixes
        • Several column types
        • Use only specified, leading part of column values
        • Can make the index work faster, because it only searches a specific portion of the column value
        • CREATE INDEX part_of_name ON customer (name(10));
        • Can be used on composite indexes, as well
          • INDEX (column_name1(15),column_name2(10))
      • You can show index information by using the SHOW INDEX command
        • Full text indexes
          • Only useful with MyISAM tables
          • Definition can be given with CREATE TABLE, ALTER TABLE, CREATE INDEX
          • Create index after table creation for large datasets
          • Use MATCH()…AGAINST() syntxt
          • SELECT title FROM books WHERE MATCH(title) AGAINST (‘prince potter’)
    • Well-written queries
      • Using EXPLAIN to determine query processing
      • Returns useful information
        • Shows if index is required
        • Shows if index is being used
        • Analyzes query rewrites
      • EXPLAIN works with SELECT queries
      • EXPLAIN for JOINs
        • Joins tend to increase amount of server processing
        • EXPLAIN can help reduce server impact
        • The type column indicates the join type
        • type column output
          • system
          • const
          • eq_ref
          • ref
          • ref_or_null
          • index_merge
          • unique_subquery
          • index_subquery
          • range
          • index
          • ALL
      • The extra column (page 21-22)
        • using index
        • where used
        • distinct
        • not exists
      • Inefficient query “extra” output
        • using filesort
        • using temporary
        • range checked for each record
      • Indicators of worst performance
        • using filesort
        • using temporary
      • Rewrite query and run EXPLAIN again
      • Efficiency principles
        • No indexed columns within an expression
        • Beneficial for joins that compare columns from two tables
        • Use same value as column data type
          • WHERE id=18 (if column is a number)
          • WHERE id=’18’ (if column includes strings)
        • Pattern matching
          • WHERE name LIKE ‘de%’
          • WHERE name >= ‘de’ AND name < ‘df’ – not as good as example above
          • WHERE name LIKE ‘%de’ – full table scan
        • Rewrite using a trigger that maintains an additional column
          • WHERE LENGTH(column)=5 – bad
          • WHERE column_length=5 – column_length is a separate column that’s been automatically generated to show the value that would be returned by LENGTH(column)
        • Reduce amount of output a query produces
          • Use the LIMIT clause
            • Reduces information going over the network
            • Allows server to terminate query processing earlier
          • Use the WHERE clause
          • More improvement with index or column
      • Techniques for updating tables
        • Use DELETE and UPDATE same as SELECT
        • Multi-row INSERT
        • INSERT INTO t (id, name) VALUES (1,’Bea’),(2,’Belle’),(3,’Bernice’);
        • Better performance with a transaction
        • LOAD DATA INFILE faster than multi-row INSERT
          • Even faster with MyISAM if you disable keys before running the file and enabling them when finished
        • Use REPLACE rather than DELETE plus INSERT
    • Generating summary tables
      • Select records to generate summaries
      • Summary table strategy
      • Several benefits to this strategy
      • Table-locking table will be available more
        • The use of the temporary table will not affect the original table from which it was derived
      • Consider making a memory table
      • CREATE TABLE ContinentGNP
        SELECT Continent, AVG(GNP) AS AvgGnp
        FROM Country GROUP BY Continent
      • Disadvantages
        • Values are only good until changed
        • Storing data twice
    • Choose best matching storage engine
      • Decide query types during table creation
      • Choose storage engine with locking level needed
      • InnoDB good for a mix of retrievals and updates
      • MyISAM table structure dependent on the higher priority between speed or disk
        • Choose fixed-length or variable-length according to needs
        • Use read-only tables if possible
      • CHAR columns take more space than VARCHAR
        • considerably faster in MyISAM, but CHAR is no different (in speed) than VARCHAR in InnoDB
      • Use Memory for temporary information

* Within a MyISAM table, you can temporarily disable all non-unique keys so that new indexes will not be built while they are turned off. Use the statement ALTER TABLE table_name DISABLE KEYS. You can turn them back on with ALTER TABLE table_name ENABLE KEYS.