By Justin Silverton

In a recent announcement:

“MySQL has today refined its licensing scheme from “GPLv2 or later” to “GPLv2 only“, in order to make it an option, not an obligation for the company to move to GPLv3.

Specifically, this means that copyright notice in the MySQL source code files will change from referring to “either version 2 of the License, or (at your option) any later version” to “version 2” only, in the MySQL 5.0 and MySQL 5.1 code bases”

Mysql is one of the best examples of a successful open source project and it is used by many businesses and people around the Internet.

If the FSF wants free software to be widely supported and used by the masses (which seems to be one of their main goals), they need to start looking more at the needs of businesses (which may actually get us better driver support for linux).

Although the FSF is still in the drafting process of the GPLV3,   there are still some big changes that will most likely make it to the final version:

• GPL software can is not compatible with digital rights managements (DRM)
• Patents cannot be used to make free software non-free
• If your work communicates with an online service, it must be possible for modified versions to communicate with the same online service in the same way such that the service cannot distinguish

more information can be found here

A more restrictive license is not the answer.  This will make businesses think twice before deciding to use or contribute to free software. 

No comments

By Justin Silverton

Mysql 5.1 beta has recently been released.  It is only a beta (and should not be used in a production environment), but it does show us some of the new features that will appear in future, stable, releases:

Partitioning: This capability enables distributing portions of individual tables across a filesystem, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table. Syntactically, this implements a number of new extensions to the CREATE TABLE, ALTER TABLE, and EXPLAIN ... SELECT statements. As of MySQL 5.1.6, queries against partitioned tables can take advantage of partition pruning. In some cases, this can result in query execution that is an order of magnitude faster than the same query against a non-partitioned version of the same table.

 

Row-based replication: Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication. As of MySQL 5.1.5, another basis for replication is available. This is called row-based replication. Instead of sending SQL statements to the slave, the master writes events to its binary log that indicate how individual table rows are effected. As of MySQL 5.1.8, a third option is available: mixed. This will use statement-based replication by default, and only switch to row-based replication in particular cases.

 

Plugin API: MySQL 5.1 adds support for a very flexible plugin API that enables loading and unloading of various components at runtime, without restarting the server. Although the work on this is not finished yet, plugin full-text parsers are a first step in this direction. This allows users to implement their own input filter on the indexed text, enabling full-text search capability on arbitrary data such as PDF files or other document formats. A pre-parser full-text plugin performs the actual parsing and extraction of the text and hands it over to the built-in MySQL full-text search.

 

Event scheduler: MySQL Events are tasks that run according to a schedule. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

No comments

By Justin Silverton

Mysql is the most popular open source database server right now.  It is used by large enteprise level companies and small, single websites.  There are countless numbers of books on the subject and many people claim it is better than the rest (microsoft, postgres)…but is it actually better?  My goal here is to find out, based on Internet research, if mysql actually does outperform the other commercial and open source database systems.

Mysql 5.0 vs. Microsoft SQL 2005

Features

• Mysql 5.X now offers support for cursors, complete views, and stored procedures.  However, Foreign Key support is still in its early stages.
• SQL 2005 has native support for xml, multi-dimensional data querying, and Visual Studio .net integration.

Cost

• Microsoft provides a free license for development use.  $1400 for a commercial license.
• Mysql is free ($0) for commercial and non-commerical use.  It is also possible to purchase a commerical license (to get around the GPL license) for $400.

Performance

• Mysql: MyISAM database table type uses less space and memory.  Innodb and NDB clusters also now use 20% less space (new to 5.0).
• SQL 2005: needs more disk storage and memory requirements.

Replication

• Mysql: One way replication using a binary log, which can easily be replicated to multile machines.
• SQL 2005: Multiple forms of replication (snapshot,transactional, and merge), which are more complex and offers a  greater degrees of flexibility.

 Recovery

• SQL 2005: Very robust.  There are multiple failsafes in place to prevent data loss.  New features in this version also allow rapid restoration and data protection.
• Mysql: Falls very short in this respect.  An unexpected shutdown of your server can cause data loss.

Reference: http://www.tometasoftware.com/MySQL-5-vs-Microsoft-SQL-Server-2005.asp

Mysql 5.x vs. Postgres 8.x

Postgres and mysql are both open source and supported by a community of users.  Postgres is a little more difficult to find information about (most statistics are from older versions of postgres).

Postgres Advantages

• Stored procedures, Triggers, and a large set of built-in functions (including many functions for date manipulation) (many more features than mysql).
• Procedural language is pretty easy to learn if you know Oracle’s PL/SQL.

Postgres Disadvantages

• Even though it has more features, because it is not as popular or mainstreamed as mysql, postgres is at a disadvantage. 

Some Links on the subject (some have comparisons with older versions of postgres/mysql):

• http://www.wlug.org.nz/PostgresVsMysql (chart detailing supported features)
• http://www.databasejournal.com/features/mysql/article.php/3288951
• http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html

Conclusion

Even though many commercial databases have more features than mysql, I still feel that it is a viable solution for most web applications and sites.  Most scalability issues can be solved using a caching soluton called memcached (http://www.danga.com/memcached/).  Future versions of mysql should concentrate on improving Data protection and recovery.

Also, because of it’s large acceptance on the Internet and in many web communities, it makes it a more practical  solution than other open source databases such as postgres.

14 comments

Introduction

Carl Henderson from Flickr.com, a very popular photo blogging service has released a pdf (not sure exactly when this was actually released) detailing the issues they faced with having a high-traffic website.

original PDF can be downloaded here

some interesting points taken from this pdf are below.

Classes, libraries, and systems used

1) smarty for templating
2) PEAR and XML for Email parsing
3) perl for controlling
4) imagemagick for image processing
5) mysql (4.0/innoDb)
6) java, for node service
7) apache 2 and redhat linux

8) 60,000 lines of PHP code
9) 60,000 lines of templates
10) 70 custom smarty functions/modifiers
11) 25,000 DB transactions/second at peak
12) 1000 pages per second at peak

unicode support

1) UTF-8 pages
2) CJKV support

Tips: don’t use HtmlEntities(). Also, Javascript has patchy Unicode Support

Why php was used

1) Everything can be stored in the database, including smarty cache
2) a “shared nothing” approach (as long as php sessions were not used)

Mysql usage

Select’s: 44,220,588
Insert’s: 1,349,234
update’s: 1,755,503
delete’s: 318,439
13 select’s per Insert, Update, and delete

Tips: many of the tables that needed to be full-text searched were de-normalized. This does waste space, but because it allowed for little or no joins, it made searching much faster.

3 comments

By Justin Silverton

I came across the following 10 benchmark tests covering:

SQLite version 3.3.3
SQLite version 3.3.3
SQLite version 2.8.17
SQLite version 2.8.17
PostgreSQL version 8.1.2
MySQL version 5.0.18
FirebirdSQL version 1.5.2

About the hardware/database settings used:

All databases were installed with default settings.
Tests were run on 1.6GHz Sempron with 1GB of ram and 7200rpm SATA disk running Windows 2000 + SP4 with all updates applied.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t1 VALUES(1,13153,’thirteen thousand one hundred fifty three’);INSERT INTO t1 VALUES(2,75560,’seventy five thousand five hundred sixty’);… 995 lines omittedINSERT INTO t1 VALUES(998,66289,’sixty six thousand two hundred eighty nine’);INSERT INTO t1 VALUES(999,24322,’twenty four thousand three hundred twenty two’);INSERT INTO t1 VALUES(1000,94142,’ninety four thousand one hundred forty two’);

SQLite 3.3.3 (sync):
3.823
SQLite 3.3.3 (nosync):
1.668
SQLite 2.8.17 (sync):
4.245
SQLite 2.8.17 (nosync):
1.743
PostgreSQL 8.1.2:
4.922
MySQL 5.0.18 (sync):
2.647
MySQL 5.0.18 (nosync):
0.329
FirebirdSQL 1.5.2:
0.320

Test 2: 25000 INSERTs in a transaction

BEGIN;CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t2 VALUES(1,298361,’two hundred ninety eight thousand three hundred sixty one’);… 24997 lines omittedINSERT INTO t2 VALUES(24999,447847,’four hundred forty seven thousand eight hundred forty seven’);INSERT INTO t2 VALUES(25000,473330,’four hundred seventy three thousand three hundred thirty’);COMMIT;

SQLite 3.3.3 (sync):
0.764
SQLite 3.3.3 (nosync):
0.748
SQLite 2.8.17 (sync):
0.698
SQLite 2.8.17 (nosync):
0.663
PostgreSQL 8.1.2:
16.454
MySQL 5.0.18 (sync):
7.833
MySQL 5.0.18 (nosync):
7.038
FirebirdSQL 1.5.2:
4.280

Test 3: 25000 INSERTs into an indexed table

BEGIN;CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));CREATE INDEX i3 ON t3(c);… 24998 lines omittedINSERT INTO t3 VALUES(24999,442549,’four hundred forty two thousand five hundred forty nine’);INSERT INTO t3 VALUES(25000,423958,’four hundred twenty three thousand nine hundred fifty eight’);COMMIT;

SQLite 3.3.3 (sync):
1.778
SQLite 3.3.3 (nosync):
1.832
SQLite 2.8.17 (sync):
1.526
SQLite 2.8.17 (nosync):
1.364
PostgreSQL 8.1.2:
19.236
MySQL 5.0.18 (sync):
11.524
MySQL 5.0.18 (nosync):
12.427
FirebirdSQL 1.5.2:
6.351

Test 4: 100 SELECTs without an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b=100 AND b=200 AND b=9700 AND b=9800 AND b=9900 AND bTest 5: 100 SELECTs on a string comparison

SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%one%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%two%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%three%’;… 94 lines omittedSELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%ninety eight%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%ninety nine%’;SELECT count(*), avg(b) FROM t2 WHERE c LIKE ‘%one hundred%’;

SQLite 3.3.3 (sync):
4.853
SQLite 3.3.3 (nosync):
4.868
SQLite 2.8.17 (sync):
4.511
SQLite 2.8.17 (nosync):
4.500
PostgreSQL 8.1.2:
6.565
MySQL 5.0.18 (sync):
3.424
MySQL 5.0.18 (nosync):
2.090
FirebirdSQL 1.5.2:
5.803

Test 6: INNER JOIN without an index

SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
SQLite 3.3.3 (sync):
14.473
SQLite 3.3.3 (nosync):
14.445
SQLite 2.8.17 (sync):
47.776
SQLite 2.8.17 (nosync):
47.750
PostgreSQL 8.1.2:
0.176
MySQL 5.0.18 (sync):
3.421
MySQL 5.0.18 (nosync):
3.443
FirebirdSQL 1.5.2:
0.141

Test 7: Creating an index

CREATE INDEX i2a ON t2(a);CREATE INDEX i2b ON t2(b);
SQLite 3.3.3 (sync):
0.552
SQLite 3.3.3 (nosync):
0.526
SQLite 2.8.17 (sync):
0.650
SQLite 2.8.17 (nosync):
0.605
PostgreSQL 8.1.2:
0.276
MySQL 5.0.18 (sync):
1.159
MySQL 5.0.18 (nosync):
0.275
FirebirdSQL 1.5.2:
0.264

Test 8: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b=100 AND b=200 AND b=499700 AND b=499800 AND b=499900 AND bTest 9: 1000 UPDATEs without an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a=10 AND a=9980 AND a=9990 AND aTest 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;… 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
1.883
SQLite 3.3.3 (nosync):
1.894
SQLite 2.8.17 (sync):
1.994
SQLite 2.8.17 (nosync):
1.973
PostgreSQL 8.1.2:
23.933
MySQL 5.0.18 (sync):
16.348
MySQL 5.0.18 (nosync):
17.383
FirebirdSQL 1.5.2:
15.542

Test 9: 1000 UPDATEs without an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a=10 AND a=9980 AND a=9990 AND aTest 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;… 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
3.153
SQLite 3.3.3 (nosync):
3.088
SQLite 2.8.17 (sync):
3.993
SQLite 2.8.17 (nosync):
3.983
PostgreSQL 8.1.2:
5.740
MySQL 5.0.18 (sync):
2.718
MySQL 5.0.18 (nosync):
1.641
FirebirdSQL 1.5.2:
2.976

If you want to see some more information on the above and 10 more tests, you can go Here

6 comments