Following my recent blogg on the performance of MariaDB and PBXT on Windows showed that these new kids still has some work to do. Another question I was thinking testing was if the overhead of Transactions really matters, they do have a performance advantage also (like: You don't have to persist until the transaction is done), and so does row-level locking. And what about Linux? We could see that MariaDB on Windows was less than optimal, maybe this was a Windows problem. And finally, the Windows box I was testing on was a real old clunky box.
Hardware setup
So now, here are the results on a Linux box with a 4 Core AMD CPU and 4Gb of memory. Still not an exceptional box, but a decent one.
The first test on Linux
Again, all engines have the default settings, no tuning has been done at all. This time, with some more concurrent threads running (10 therads in total, as before, but now I have 4 running concurrently at full speed), row-level locking and increased concurrency support in PBXT and InnoDB pays off, they are both about the same speed, running in at 9216 and 8928 Operations per second respectively. Then we have MyISAM at 5615 Operations ser second, but I was expecting more here, and this is what I got, sonsistently over several runs. Last in, way behind after InnoDB, PBXT and MyISAM are already drinking beers at the local Storage Engine Pub, Maria gets in at 2285 Operations per second. So all in all, faster than in the case of my Windows test, but with a lot more Iron, and Maria way, way behind again.
Adding more threads
Wanting to try a bit more, I decided to change my test setup slightly. I insert 10000 rows per thread, instead of 1000, and run 40 simultaneous threads, to see what happens. And the results are pretty consistent with what I got before, maybe PBXT shines a bit more, at 11950 Operations / second, InnoDB comes in at 9796, MyISAM at 7665 and finally Maria, again at a sluggish 2092 Operations per second.
At this point, someone might say that Maria is still beta. But it is claimed to be RC quality, so it should be as close to GA as PBXT, but I don't really see that.
Thread impact and Transactions
Before I close the lid on this very simplistic benchmark, two more things. First, is it so that multiple-threads really cause less contention in InnoDB and PBXT than in the table-level locking Maria and MyISAM? Let's try with just 1 thread running, inserting 10000 rows. This time, MyISAM comes out best at 4468 Operations per second followed by PBXT (2170), InnoDB (2137) and last, as usual I was about to say, Maria at 1910. Now, this time Maria isn't THAT far behind, but note that Maria is not transactional, which PBXT and InnoDB is.
Lastly, let's see what good transactions can do, performance-wise. In this case, I will only persist (commit) every 100 Operations with PBXT and InnoDB. I am inserting 10000 rows in 10 threads each, and each thread is doing a commit every 100 rows then. For both PBXT and InnoDB this meant huge performance boosts, turing in at 28264 and 25773 Rows per second respectively, with MyISAM at 5540 and Maria at 2063.
Array inserts should help, but some simple tests shows that the relation stays the same, with PBXT and InnoDB on top, followed by MyISAM and Maria far behind (maybe even more behind).
Conclusion
Whereas PBXT seems good and ready for prime time, at least from a performance POV, and at least with this simple load, Maria is not there yet. This is a shame, I was hoping for Maria to be the crash-proof MyISAM we always wanted. Also, it seems that increased number of concurrent threads really do need more database concurrency (no big surprise there) in terms of row-level locking. And that transactions also may improve performance is also clear, at least to me.
And before I close, don't take this test too seriously, it is a very simplistic test, but Maria is so far behind even in this simple test (and data loads, as this is what this test is doing), is nearkly always important at some point (mysqldump recovery springs to mind :-).
/Karlsson
Thursday, January 7, 2010
Tuesday, January 5, 2010
New kids on the block doin a stupid benchmark on Windows
There are now some interesting Storage Engines out there, beyond the usual MyISAM, InnoDB, NDB etc. The question is then, how do they perform? And as usual, I was a bit curious on the feasibility of these new guys on Windows?
I decided to try out MariaDB first, based on MySQL 5.1.39, and this also includes PBXT, which was convenient, now I got a chance to try, with one installation, InnoDB, MyISAM, Maria and PBXT. The MariaDB Windows download was no fun at all, though. This includes among many other things, the text setup (300Mb just that), pdb files for libraries and executables, embedded versions and debug and optimized libraries, adding up to a whopping 869 Mb on disk (but you have to exclude my current data which amounts to 94 Mb). Standard 5.1.39 is 134 Mb on disk, excluding data.
What is more annoying is that there is no installer, no MSI no nuthin', just a zip. Which is sort of fine for me, but goes to show that this really isn't even close to RC.
As for Maria with this build, if you do this:
SELECT engine, transactions, xa FROM INFORMATION_SCHEMA.ENGINES;
What is returned is this:
+------------+--------------+------+
| engine | transactions | xa |
+------------+--------------+------+
| CSV | NO | NO |
| MRG_MYISAM | NO | NO |
| PBXT | YES | NO |
| MARIA | YES | NO |
| BLACKHOLE | NO | NO |
| MyISAM | NO | NO |
| ARCHIVE | NO | NO |
| MEMORY | NO | NO |
| InnoDB | YES | YES |
+------------+--------------+------+
9 rows in set (0.00 sec)
Note here that Maria claims to be transactional, which isn't really true. Looking at the Maris docs, this is because they plan to make it transactional.
Benchmark setup
Now, for the benchmark, I decided for something really simple. No engines has been tuned at all, i.e. no engine specific parameters are set. The tables for the different engines were created like this:
create table t1_maria(c1 int not null primary key auto_increment, c2 char(100)) engine=maria;
create table t1_pbxt(c1 int not null primary key auto_increment, c2 char(100)) engine=pbxt;
create table t1_innodb(c1 int not null primary key auto_increment, c2 char(100)) engine=innodb;
create table t1_myisam(c1 int not null primary key auto_increment, c2 char(100)) engine=myisam;
As you can see, again no engine special settings, just out of the box stuff. What I do in the actual benchmark is this:
Start 10 threads, each thread executes 1000 operations like this:
INSERT INTO t1_maria VALUES(NULL, 'xxxxxxxxxx');
Just changing the table name. This isn't anything complex, hardly scientific, but there applications out there that does something like this, no more, no less.
The hardware used was my crappy old Lenovo Laptop running Windows XP. This is not the hottest computer since Osborne-1, but all Storage Engines shared the same hardware.
Expectations
OK, what was I expecting here? MyISAM was expected to be the fastest, no surprise, there are just too many shortcuts taken for it not to be. But if I had increased the # of threads, performance would slow down, due to concurrency issues, but not even that might be true, as the table was all clean now, and MyISAM has some tricks to deal with that. InnoDB was expected to be slower, and I was expected the usual InnoDB slowdown in some threads (I always see a few InnoDB operations being slow, although performance is good overall). As for Maris, I expected something along the lines of in the middle between InnoDB and MyISAM. Maria being crash proof would add to the overhead, but on the other hand, no transaction support and MyISAM heritage would speed it up. PBXT I didn't know what to expect from, but as this should be the kind of thing that PBXT is expected to excel at, but on the other hand being transactional (and this time it really IS transactional :-), I was expecting InnoDB'ish performance, maybe even better at this PBXT oriented load.
So the results then?
MyISAM fast as expected (but not crashproof), performed on average 8553 operations / s.
InnoDB on the other end of the scale, transactional and crash proof and all that, achieved 105 Operations / s. Not bad for an untuned InnoDB. Also, note that autocommit was used all over the place, had pumped some more data per transaction or relaxed the durability, things would sure be better.
PBXT performed real well, despite not being tuned at all. 181 Operations per second, and pretty consistent throughput across the threads.
So what about Maria then? Well, I don't know, apparently performance will be addressed in a later release (personally, I think performance first and foremost is a result of design, but what do I know) according to the docs. Also, this is a beta release (but PBXT is RC I think, and Monty et all claims that Maria 1.5 "we think it's good enough for RC"). In any case, I got 20 Operations per second from Maria. Less than 1/5 of InnoDB, MyISAM being more than 4000 as fast as Maria. Hmmm, I was a bit disappointed here, I was expecting more. And where is the Windows installer?
Conclusion
PBXT looks promising to me. I've always liked the Primebase focus on smart technology and thinking outside-the-box. Here it seems it may have paid off (I write "may have", as this benchmark is far from conclusive). InnoDB works as expected (here I was using the built-in InnoDB by the way, not the plug-in). MyISAM is fast, but takes too many shortcuts for my taste. And Maria seems not ready for prime time. I admit that it wasn't tuned, but none of the other engines were tuned either, and the difference was just too big, 1/5 of InnoDB performance without transaction support. And the lack of a Windows installer is another sign of this. Too bad.
/Karlsson
I decided to try out MariaDB first, based on MySQL 5.1.39, and this also includes PBXT, which was convenient, now I got a chance to try, with one installation, InnoDB, MyISAM, Maria and PBXT. The MariaDB Windows download was no fun at all, though. This includes among many other things, the text setup (300Mb just that), pdb files for libraries and executables, embedded versions and debug and optimized libraries, adding up to a whopping 869 Mb on disk (but you have to exclude my current data which amounts to 94 Mb). Standard 5.1.39 is 134 Mb on disk, excluding data.
What is more annoying is that there is no installer, no MSI no nuthin', just a zip. Which is sort of fine for me, but goes to show that this really isn't even close to RC.
As for Maria with this build, if you do this:
SELECT engine, transactions, xa FROM INFORMATION_SCHEMA.ENGINES;
What is returned is this:
+------------+--------------+------+
| engine | transactions | xa |
+------------+--------------+------+
| CSV | NO | NO |
| MRG_MYISAM | NO | NO |
| PBXT | YES | NO |
| MARIA | YES | NO |
| BLACKHOLE | NO | NO |
| MyISAM | NO | NO |
| ARCHIVE | NO | NO |
| MEMORY | NO | NO |
| InnoDB | YES | YES |
+------------+--------------+------+
9 rows in set (0.00 sec)
Note here that Maria claims to be transactional, which isn't really true. Looking at the Maris docs, this is because they plan to make it transactional.
Benchmark setup
Now, for the benchmark, I decided for something really simple. No engines has been tuned at all, i.e. no engine specific parameters are set. The tables for the different engines were created like this:
create table t1_maria(c1 int not null primary key auto_increment, c2 char(100)) engine=maria;
create table t1_pbxt(c1 int not null primary key auto_increment, c2 char(100)) engine=pbxt;
create table t1_innodb(c1 int not null primary key auto_increment, c2 char(100)) engine=innodb;
create table t1_myisam(c1 int not null primary key auto_increment, c2 char(100)) engine=myisam;
As you can see, again no engine special settings, just out of the box stuff. What I do in the actual benchmark is this:
Start 10 threads, each thread executes 1000 operations like this:
INSERT INTO t1_maria VALUES(NULL, 'xxxxxxxxxx');
Just changing the table name. This isn't anything complex, hardly scientific, but there applications out there that does something like this, no more, no less.
The hardware used was my crappy old Lenovo Laptop running Windows XP. This is not the hottest computer since Osborne-1, but all Storage Engines shared the same hardware.
Expectations
OK, what was I expecting here? MyISAM was expected to be the fastest, no surprise, there are just too many shortcuts taken for it not to be. But if I had increased the # of threads, performance would slow down, due to concurrency issues, but not even that might be true, as the table was all clean now, and MyISAM has some tricks to deal with that. InnoDB was expected to be slower, and I was expected the usual InnoDB slowdown in some threads (I always see a few InnoDB operations being slow, although performance is good overall). As for Maris, I expected something along the lines of in the middle between InnoDB and MyISAM. Maria being crash proof would add to the overhead, but on the other hand, no transaction support and MyISAM heritage would speed it up. PBXT I didn't know what to expect from, but as this should be the kind of thing that PBXT is expected to excel at, but on the other hand being transactional (and this time it really IS transactional :-), I was expecting InnoDB'ish performance, maybe even better at this PBXT oriented load.
So the results then?
MyISAM fast as expected (but not crashproof), performed on average 8553 operations / s.
InnoDB on the other end of the scale, transactional and crash proof and all that, achieved 105 Operations / s. Not bad for an untuned InnoDB. Also, note that autocommit was used all over the place, had pumped some more data per transaction or relaxed the durability, things would sure be better.
PBXT performed real well, despite not being tuned at all. 181 Operations per second, and pretty consistent throughput across the threads.
So what about Maria then? Well, I don't know, apparently performance will be addressed in a later release (personally, I think performance first and foremost is a result of design, but what do I know) according to the docs. Also, this is a beta release (but PBXT is RC I think, and Monty et all claims that Maria 1.5 "we think it's good enough for RC"). In any case, I got 20 Operations per second from Maria. Less than 1/5 of InnoDB, MyISAM being more than 4000 as fast as Maria. Hmmm, I was a bit disappointed here, I was expecting more. And where is the Windows installer?
Conclusion
PBXT looks promising to me. I've always liked the Primebase focus on smart technology and thinking outside-the-box. Here it seems it may have paid off (I write "may have", as this benchmark is far from conclusive). InnoDB works as expected (here I was using the built-in InnoDB by the way, not the plug-in). MyISAM is fast, but takes too many shortcuts for my taste. And Maria seems not ready for prime time. I admit that it wasn't tuned, but none of the other engines were tuned either, and the difference was just too big, 1/5 of InnoDB performance without transaction support. And the lack of a Windows installer is another sign of this. Too bad.
/Karlsson
Monday, January 4, 2010
An example why Open Source rocks... Take that, Apple!
My colleague Luca Olivari wrote about the excellent site GetApp.com, where you can find loads of good Open SOurce applications. Where Luca goes wrong though is when he compares this to AppStore for iPhone. Apple does encourage iPhone application development, but it is not Open Source, which is why one crucial Web component is missing: a Flash plug-in. Yes, for those of you without an iPhone, you can NOT view Flash-based pages with an iPhone. The reason? Apple will not allow one, at least not yet, and Adobe has not announced a player for the iPhone.
Although the Flash player in and of itself is not Open Source, this still shows what an active eco-system can do for you, it will encourage and promote more and innovative development. Looking at the competing Android platform (I'm on an Acer Liquid myself), a Flash player has been announced, but is not yet released. The eco-system around Android is much younger than that around iPhone, but it is just as large and is truly innovative. If we exclude that fact that tethering applications has been banned from Market (the Android app site), as a result of some telco companies realizing that this was not "in their best interest" (not that this will stop tethering in the end anyway, and excellent PdaNet, which I use right now, i close to as good, except that it is wired only (Bluetooth enabled PdaNet will be there with Android 2.0 though)).
All in all, comparing AppStore and Market will show you just what a true live Open Source environment can do in terms of eco-systems. That Android is less mature in terms of usability and partly in terms of features compared to an iPhone is true, but it is close enough, fully Open Source, intergrates with all the neat Google things (GPS with Google maps is just great, for example) and is about 1/2 the price (at least here in sweden) (I got mine on an auction, and paid even less than my old clunky Nokia E66 even).
So, all in all, if you want a true Open Source phone and is willing to accept that some assemply is required, then go for an Android. Take that, Apple!
/Karlsson
BTW. If you are wondering why I am not blogging as much on databases here as I used to, this is because the heated tone on the MySQL blog-front right now. Me being an evil person and all that.
Although the Flash player in and of itself is not Open Source, this still shows what an active eco-system can do for you, it will encourage and promote more and innovative development. Looking at the competing Android platform (I'm on an Acer Liquid myself), a Flash player has been announced, but is not yet released. The eco-system around Android is much younger than that around iPhone, but it is just as large and is truly innovative. If we exclude that fact that tethering applications has been banned from Market (the Android app site), as a result of some telco companies realizing that this was not "in their best interest" (not that this will stop tethering in the end anyway, and excellent PdaNet, which I use right now, i close to as good, except that it is wired only (Bluetooth enabled PdaNet will be there with Android 2.0 though)).
All in all, comparing AppStore and Market will show you just what a true live Open Source environment can do in terms of eco-systems. That Android is less mature in terms of usability and partly in terms of features compared to an iPhone is true, but it is close enough, fully Open Source, intergrates with all the neat Google things (GPS with Google maps is just great, for example) and is about 1/2 the price (at least here in sweden) (I got mine on an auction, and paid even less than my old clunky Nokia E66 even).
So, all in all, if you want a true Open Source phone and is willing to accept that some assemply is required, then go for an Android. Take that, Apple!
/Karlsson
BTW. If you are wondering why I am not blogging as much on databases here as I used to, this is because the heated tone on the MySQL blog-front right now. Me being an evil person and all that.
Friday, November 13, 2009
InnoDB Plugin (With some Windows focus) - Part 2
In the last post on this subject, I discussed the benefits of the InnoDB plugin for operational things, such as creating and dropping indexes, and how much faster and non-obtrusive on other operations this is with the plug-in version of InnoDB, compared to the built-in InnoDB.
This time, I will discuss another benefit of the plugin, which is the operational metadata views provided by the plug-in. A way overdue feature of InnoDB is the ability to inspect locks and lock waiters, to be able to effectively manage lock concurrency issues. In any database using even a moderately complex schema, and having a reasonable amount of writes, concurrenctý issues will often happen. And the issue is that if you cannot monitor them properly, then you may not know what is happening. Users have sessions waiting for locks, then tires of waiting and abandons the operations, sometimes leaving locks of their own around!
Another issue is that end-users cannot usually tell the differece between a straight performace problem, such as a too small cache, a slow disk or a bad network, and a concurrency issue, i.e. waiting for a lock. And the difference in how we find and fix these issues is distinctly different!
OK, so now let's have a look in practice on hwo this works. To begin with, we need to configure the InnoDB plugin monitoring INFORMATION_SCHEMA tables, and here I will install all of them, not only the ones related to locking. The setting needed in the [mysql] section in the my.ini file, to support the InnoDB plugin and all the corresponding monitoring tables are then:
ignore_builtin_innodb
plugin_load="innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll"
Note that the "plugin_load" parameter must be on a single line! Now, restart the MySQL server and we can start to have some fun!
First create an InnoDB table for our testing:
CREATE TABLE locktest (
c1 int(11) NOT NULL AUTO_INCREMENT,
c2 char(10) DEFAULT NULL,
PRIMARY KEY (c1),
KEY c2 (c2)
) ENGINE=InnoDB;
And insert some data:
INSERT INTO locktest VALUES(1, 1);
INSERT INTO locktest VALUES(2, 2);
INSERT INTO locktest VALUES(10, 16);
INSERT INTO locktest VALUES(11, 16);
INSERT INTO locktest VALUES(15, 18);
And we are set to do some testing. First, open two sessions on the database where the testing table was created, and then, in the first session, do a SELECT .. FOR UPDATE in a transaction:
BEGIN WORK;
SELECT * FROM locktest WHERE c1 > 8 FOR UPDATE;
Now, in the other session, lets try to update one of the locked rows:
UPDATE locktest SET c2 = 17 WHERE c1 = 11;
And then, in a third session, lets see what we have in the lock and lock_wait tables:
use information_schema
SELECT lock_mode, lock_type, lock_table, lock_data FROM innodb_locks;
+-----------+-----------+-------------------------+-----------+
| lock_mode | lock_type | lock_table | lock_data |
+-----------+-----------+-------------------------+-----------+
| X | RECORD | `plugintest`.`locktest` | 11 |
| X | RECORD | `plugintest`.`locktest` | 11 |
+-----------+-----------+-------------------------+-----------+
2 rows in set (0.00 sec)
and:
SELECT * FROM innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6007 | 6007:0:16396:12 | 6001 | 6001:0:16396:12 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
As we can see, InnoDB will only show locks that are waited on, but that is cool. Look at the lock_data column in the innodb_locks table, you see the actual data being waied on there. The lock_mode column is X (the two rows are for the table and index respectively). In your case, you may see X,GAP for the index lock. That is because you have index gap locking on, or in other words, you have not set innodb_locks_unsafe_for_binlog.
All in all, tracking down concurrency issues with these tables is A LOT easier than the old "show innodb status" way of dealing with things.
/Karlsson
This time, I will discuss another benefit of the plugin, which is the operational metadata views provided by the plug-in. A way overdue feature of InnoDB is the ability to inspect locks and lock waiters, to be able to effectively manage lock concurrency issues. In any database using even a moderately complex schema, and having a reasonable amount of writes, concurrenctý issues will often happen. And the issue is that if you cannot monitor them properly, then you may not know what is happening. Users have sessions waiting for locks, then tires of waiting and abandons the operations, sometimes leaving locks of their own around!
Another issue is that end-users cannot usually tell the differece between a straight performace problem, such as a too small cache, a slow disk or a bad network, and a concurrency issue, i.e. waiting for a lock. And the difference in how we find and fix these issues is distinctly different!
OK, so now let's have a look in practice on hwo this works. To begin with, we need to configure the InnoDB plugin monitoring INFORMATION_SCHEMA tables, and here I will install all of them, not only the ones related to locking. The setting needed in the [mysql] section in the my.ini file, to support the InnoDB plugin and all the corresponding monitoring tables are then:
ignore_builtin_innodb
plugin_load="innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll"
Note that the "plugin_load" parameter must be on a single line! Now, restart the MySQL server and we can start to have some fun!
First create an InnoDB table for our testing:
CREATE TABLE locktest (
c1 int(11) NOT NULL AUTO_INCREMENT,
c2 char(10) DEFAULT NULL,
PRIMARY KEY (c1),
KEY c2 (c2)
) ENGINE=InnoDB;
And insert some data:
INSERT INTO locktest VALUES(1, 1);
INSERT INTO locktest VALUES(2, 2);
INSERT INTO locktest VALUES(10, 16);
INSERT INTO locktest VALUES(11, 16);
INSERT INTO locktest VALUES(15, 18);
And we are set to do some testing. First, open two sessions on the database where the testing table was created, and then, in the first session, do a SELECT .. FOR UPDATE in a transaction:
BEGIN WORK;
SELECT * FROM locktest WHERE c1 > 8 FOR UPDATE;
Now, in the other session, lets try to update one of the locked rows:
UPDATE locktest SET c2 = 17 WHERE c1 = 11;
And then, in a third session, lets see what we have in the lock and lock_wait tables:
use information_schema
SELECT lock_mode, lock_type, lock_table, lock_data FROM innodb_locks;
+-----------+-----------+-------------------------+-----------+
| lock_mode | lock_type | lock_table | lock_data |
+-----------+-----------+-------------------------+-----------+
| X | RECORD | `plugintest`.`locktest` | 11 |
| X | RECORD | `plugintest`.`locktest` | 11 |
+-----------+-----------+-------------------------+-----------+
2 rows in set (0.00 sec)
and:
SELECT * FROM innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6007 | 6007:0:16396:12 | 6001 | 6001:0:16396:12 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
As we can see, InnoDB will only show locks that are waited on, but that is cool. Look at the lock_data column in the innodb_locks table, you see the actual data being waied on there. The lock_mode column is X (the two rows are for the table and index respectively). In your case, you may see X,GAP for the index lock. That is because you have index gap locking on, or in other words, you have not set innodb_locks_unsafe_for_binlog.
All in all, tracking down concurrency issues with these tables is A LOT easier than the old "show innodb status" way of dealing with things.
/Karlsson
Friday, October 30, 2009
InnoDB Plugin (With some Windows focus) - Part 1
So InnoDB is now, from 5.1.38, distributed with MySQL. It is still not enabled by default though. What this means is that there are 2 InnoDB implementations that comes with your MySQL installation. The old-school standard one, which is compiled in with the MySQL binary and is enabled by default, and one plugin.
The plugin comes as a DLL, and to load it, you have to do two things:
- Disable the builtin InnoDB Engine.
- Load the plugin.
To do this, on windows with a standard configuration, you edit the my.ini file and add the following two lines:
ignore_builtin_innodb
plugin_load=innodb=ha_innodb_plugin.dll
And that's about it, if you restart MySQL now, the plugin version of InnoDB will be used. So, what is new in the plugin then? Many things, actually, some rather cool features, a bunch of performance and scalability improvements and some enhancements to the operational aspect.
I will write a few blogposts on the subject on the InnoDB plugin, and I will use Windows for what I am doing, mainly as I have noticed there is little in the way of documentation, blogs etc on this same subject with a Windows focus.
And let me tell you that although the performance and scalability improvements are what is most talked about, this by far is not the only changes. Even if you have OK performance today, use Windows and have little need for more scalability, there is stuff here for you. But before I go into today's subject, let me tell you one important thing: The InnoDB plugin, as it stands here, is not considered GA yet. This is planned for December this year, but right now, it's considered Beta, despite the fact that MySQL 5.1.38 is GA. So be a bit careful here. And that said, the plugin in and of itself actually is GA, so I don't think we'll see many issues here.
Now, today's lesson: Fast index creation
This is a feature that is waaay overdue, if you ask me, but it's available now. What it means, is that in most cases, creating or dropping an indexes does not mean that the table needs to be rewritten anymore! This is a major advantage when it comes to managing a MySQL installation. To me, this is reason alone to leave MyISAM / Maria and whathaveyou, and go InnoDB instead!
The interesting thing right now, in 5.1.38 and up, is that there are 2 InnoDB engines, and we can compare them, old and new, by just flipping a few parameters and restarting MySQL! So lets do that.
I create an InnoDB table:
CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
c2 VARCHAR(1024)) ENGINE=InnoDB;
The next step is to insert some data. I insert some semi random data in the c2 column, 300 bytes long. It's not terribly random actually, but I wanted a simple test and go on and insert some 311296 rows (just some arbitrary number).
I then want to create an index on the 10 first bytes of the c2 column. This is what happened with the InnoDB plugin being used:
mysql> create index ix1 on t1(c2(10));
Query OK, 0 rows affected (21.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
And then I drop the index:
mysql> drop index ix1 on t1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
At this point, I should say one thing. Before I created the index, I restarted MySQL, to make sure that I could "benchmark" this properly, without data in the cache.
OK, now I flip the configuration back to using the built-in InnoDB Engine, restart MySQL and run the same simple test again, and this is what happened:
mysql> create index ix1 on t1(c2(10));
Query OK, 311296 rows affected (1 min 6.28 sec)
Records: 311296 Duplicates: 0 Warnings: 0
mysql> drop index ix1 on t1;
Query OK, 311296 rows affected (43.27 sec)
Records: 311296 Duplicates: 0 Warnings: 0
Quite a difference, right? Using the InnoDB plugin, index creation is 3 times as fast! And this is not that a big index! And dropping the index is more than 300 times faster! That's the kind of performance improvement I like!
/Karlsson
The plugin comes as a DLL, and to load it, you have to do two things:
- Disable the builtin InnoDB Engine.
- Load the plugin.
To do this, on windows with a standard configuration, you edit the my.ini file and add the following two lines:
ignore_builtin_innodb
plugin_load=innodb=ha_innodb_plugin.dll
And that's about it, if you restart MySQL now, the plugin version of InnoDB will be used. So, what is new in the plugin then? Many things, actually, some rather cool features, a bunch of performance and scalability improvements and some enhancements to the operational aspect.
I will write a few blogposts on the subject on the InnoDB plugin, and I will use Windows for what I am doing, mainly as I have noticed there is little in the way of documentation, blogs etc on this same subject with a Windows focus.
And let me tell you that although the performance and scalability improvements are what is most talked about, this by far is not the only changes. Even if you have OK performance today, use Windows and have little need for more scalability, there is stuff here for you. But before I go into today's subject, let me tell you one important thing: The InnoDB plugin, as it stands here, is not considered GA yet. This is planned for December this year, but right now, it's considered Beta, despite the fact that MySQL 5.1.38 is GA. So be a bit careful here. And that said, the plugin in and of itself actually is GA, so I don't think we'll see many issues here.
Now, today's lesson: Fast index creation
This is a feature that is waaay overdue, if you ask me, but it's available now. What it means, is that in most cases, creating or dropping an indexes does not mean that the table needs to be rewritten anymore! This is a major advantage when it comes to managing a MySQL installation. To me, this is reason alone to leave MyISAM / Maria and whathaveyou, and go InnoDB instead!
The interesting thing right now, in 5.1.38 and up, is that there are 2 InnoDB engines, and we can compare them, old and new, by just flipping a few parameters and restarting MySQL! So lets do that.
I create an InnoDB table:
CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
c2 VARCHAR(1024)) ENGINE=InnoDB;
The next step is to insert some data. I insert some semi random data in the c2 column, 300 bytes long. It's not terribly random actually, but I wanted a simple test and go on and insert some 311296 rows (just some arbitrary number).
I then want to create an index on the 10 first bytes of the c2 column. This is what happened with the InnoDB plugin being used:
mysql> create index ix1 on t1(c2(10));
Query OK, 0 rows affected (21.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
And then I drop the index:
mysql> drop index ix1 on t1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
At this point, I should say one thing. Before I created the index, I restarted MySQL, to make sure that I could "benchmark" this properly, without data in the cache.
OK, now I flip the configuration back to using the built-in InnoDB Engine, restart MySQL and run the same simple test again, and this is what happened:
mysql> create index ix1 on t1(c2(10));
Query OK, 311296 rows affected (1 min 6.28 sec)
Records: 311296 Duplicates: 0 Warnings: 0
mysql> drop index ix1 on t1;
Query OK, 311296 rows affected (43.27 sec)
Records: 311296 Duplicates: 0 Warnings: 0
Quite a difference, right? Using the InnoDB plugin, index creation is 3 times as fast! And this is not that a big index! And dropping the index is more than 300 times faster! That's the kind of performance improvement I like!
/Karlsson
Monday, October 12, 2009
Of UNICODE, UTF-8, Character sets part 2
Welcome to this second post in this series on UNICODE, Character sets and what have you not. In the first of these posts, I went through some of the history of character set support, and some anomalies, and finished around the mid-1990's, when we had a bunch of reasonably well stanardized 8-bit character set. And then something happens...
Jacques Chirac becomes president of France. Now wait, that wasn't it. No, what happened was the Internet, and suddenly the time of IT as isolated islands, where we could determine ourselves how we wanted our computers to operate and what character set to use. came to an end. Suddenly, a user in France could view a webpage created in Japan. And now the issue woith Character sets becomes a real problem. Luckily, stuff has been going on since the late 1980's, more specifically UNICODE. Whoa, UNICODE comes to the rescue.
The first installments of UNICODE utilized a 16-bit character format. This later sneaked into operating systems, libraries and all over the place. The char datatype in C was supposed to be substituted for the wchar_t datatype (W as in Wide). This did non become very popular, but the scheme presisted and is still in use. Windows has many hooks for this, and most Windows API functions have wchar_t counterparts, and there are even portability macros (the being one of them, working together dit datatypes such LPTSTR and TCHAR). This turned out to be more used in countries, mostly in Asia, with large character sets, which was present in the UNICODE character set. The Windows macros and types made life easier for far-east developers to make their code more portable to more ISO-8859-1 friendly languages.
What made this not so popular was that it still was a headace creating portable code, and also, the UNICODE character set was moving on, and was soon more than 16-bit. So the 2 byte datatype we used for characters (wchar_t is mapped to a short usually), wasn't enough. Now, working with 2 bytes instead of one was bad enough, but working with 4, 5 or 6 per character was just too much.
The UNICODE character set now needs 6 bytes to cover the full representation. So much for all that hard work by Microsoft and all those macros and API rewrites. Just consider looking for the end of a C-style NULL-terminated string, even with 2 fixed bytes, this is much more difficult than what it used to be. With 6 bytes even more so!
So along comes some schmes that allow you to "encode" a UNICODE character in some other, hopefully easier to manage, form. The most popular of these, by far, is UTF-8. This is a means of encoding a 6-byte long UNICODE character to a format that is a variable length. The nice thing with UTF-8 is that the first 128 positions are encoded exactly like old-style 7-bit ASCII! One byte, highest bit being 0! The Way UTF-8 works means that these 128 bytes position will never appear as part of any other UNICODE character, or in other words, the high-order bit is ALWAYS 1, except for these first characters.
So, given all this, it seems like functions like strlen will work with a UNICODE string? Well, sort of, but it will give you the length in bytes not in characters. But besides that, it will work. And so will strcpy, strcat etc.
So is all hanky-panky then? Nah! Let's look at UNICODE transformation besides UTF-8, namely UTF-16. This is still a variable length encoding, like UTF-8, and it's not that much used actually. But some people tend to THINK it is. What is being heavly used, as mentioned above in Windows, as well as in many Linux technologies though, is UCS-2. So what is that? Well, UCS-2 is based on a ISO Standard, ISO 10646. This preceeded UNICODE slightly, and was, like early UNICODE, fixed 16-bit. UCS-2 means "Universal Character Set, 2 bytes"! When UNICODE came around, the (then) fixed encoding was UTF-16. These two, UCS-2 and UTF-16 are very often confused. But whereas UCS-2 is still a fixed 16-bit character set encoding, UTF-16 has developed and is now a variable length encoding, but of course still very similar to UCS-2. Gosh, I wonder what these folks were smoking whi figured this one up.
There is one UNICODE encoding that is fixed length, and that is called UTF-32 (or, if you ask ISO, UCS-4). This is very seldom used in practice.
But UNICODE seems to persist, in particular UTF-8, which is, for example, standardized in Java. As for MySQL, it supports UTF-8 as well as classic ISO-8859-1 (which is called latin1 in MySQL, which you know by now, if you did last weeks lesson) and several other character sets. One character set not well supported though is UCS-2. You can define it as a server character set, but a UCS-2 client is not accepted. And I think this may be difficult to implement, there is just too many places where MySQL regards a character as being 8 bits. And UTF-8 is much easier, as long as we don't care about the actual length, we can treat a UTF-8 string as long ASCII string, and all special characters that we may be looking for in a UTF-8 string, such a CR/LF, ASCII NULL and semicolon, are in the 0-127 range.
Now, lets have a quick look at UTF-8 and MySQL. As UTF-8 is variable length, any string space, if defined by the # of characters, is bound to be also variable length, right? Ha ha, got you there! No, MySQL will allocate 3 bytes for each character in a UTF-8 string. What, 3 bytes, isn't UTF-8 up to 4 bytes? Yes, but MySQL only supports UNICODE "codepoints" that can be represented with up to 3 UTF-8 bytes.
OK, that's enough for now, I'll finish of this series with a post on collations, some general ideas on character set development and testing, and a few beers.
/Karlsson
Jacques Chirac becomes president of France. Now wait, that wasn't it. No, what happened was the Internet, and suddenly the time of IT as isolated islands, where we could determine ourselves how we wanted our computers to operate and what character set to use. came to an end. Suddenly, a user in France could view a webpage created in Japan. And now the issue woith Character sets becomes a real problem. Luckily, stuff has been going on since the late 1980's, more specifically UNICODE. Whoa, UNICODE comes to the rescue.
The first installments of UNICODE utilized a 16-bit character format. This later sneaked into operating systems, libraries and all over the place. The char datatype in C was supposed to be substituted for the wchar_t datatype (W as in Wide). This did non become very popular, but the scheme presisted and is still in use. Windows has many hooks for this, and most Windows API functions have wchar_t counterparts, and there are even portability macros (the being one of them, working together dit datatypes such LPTSTR and TCHAR). This turned out to be more used in countries, mostly in Asia, with large character sets, which was present in the UNICODE character set. The Windows macros and types made life easier for far-east developers to make their code more portable to more ISO-8859-1 friendly languages.
What made this not so popular was that it still was a headace creating portable code, and also, the UNICODE character set was moving on, and was soon more than 16-bit. So the 2 byte datatype we used for characters (wchar_t is mapped to a short usually), wasn't enough. Now, working with 2 bytes instead of one was bad enough, but working with 4, 5 or 6 per character was just too much.
The UNICODE character set now needs 6 bytes to cover the full representation. So much for all that hard work by Microsoft and all those macros and API rewrites. Just consider looking for the end of a C-style NULL-terminated string, even with 2 fixed bytes, this is much more difficult than what it used to be. With 6 bytes even more so!
So along comes some schmes that allow you to "encode" a UNICODE character in some other, hopefully easier to manage, form. The most popular of these, by far, is UTF-8. This is a means of encoding a 6-byte long UNICODE character to a format that is a variable length. The nice thing with UTF-8 is that the first 128 positions are encoded exactly like old-style 7-bit ASCII! One byte, highest bit being 0! The Way UTF-8 works means that these 128 bytes position will never appear as part of any other UNICODE character, or in other words, the high-order bit is ALWAYS 1, except for these first characters.
So, given all this, it seems like functions like strlen will work with a UNICODE string? Well, sort of, but it will give you the length in bytes not in characters. But besides that, it will work. And so will strcpy, strcat etc.
So is all hanky-panky then? Nah! Let's look at UNICODE transformation besides UTF-8, namely UTF-16. This is still a variable length encoding, like UTF-8, and it's not that much used actually. But some people tend to THINK it is. What is being heavly used, as mentioned above in Windows, as well as in many Linux technologies though, is UCS-2. So what is that? Well, UCS-2 is based on a ISO Standard, ISO 10646. This preceeded UNICODE slightly, and was, like early UNICODE, fixed 16-bit. UCS-2 means "Universal Character Set, 2 bytes"! When UNICODE came around, the (then) fixed encoding was UTF-16. These two, UCS-2 and UTF-16 are very often confused. But whereas UCS-2 is still a fixed 16-bit character set encoding, UTF-16 has developed and is now a variable length encoding, but of course still very similar to UCS-2. Gosh, I wonder what these folks were smoking whi figured this one up.
There is one UNICODE encoding that is fixed length, and that is called UTF-32 (or, if you ask ISO, UCS-4). This is very seldom used in practice.
But UNICODE seems to persist, in particular UTF-8, which is, for example, standardized in Java. As for MySQL, it supports UTF-8 as well as classic ISO-8859-1 (which is called latin1 in MySQL, which you know by now, if you did last weeks lesson) and several other character sets. One character set not well supported though is UCS-2. You can define it as a server character set, but a UCS-2 client is not accepted. And I think this may be difficult to implement, there is just too many places where MySQL regards a character as being 8 bits. And UTF-8 is much easier, as long as we don't care about the actual length, we can treat a UTF-8 string as long ASCII string, and all special characters that we may be looking for in a UTF-8 string, such a CR/LF, ASCII NULL and semicolon, are in the 0-127 range.
Now, lets have a quick look at UTF-8 and MySQL. As UTF-8 is variable length, any string space, if defined by the # of characters, is bound to be also variable length, right? Ha ha, got you there! No, MySQL will allocate 3 bytes for each character in a UTF-8 string. What, 3 bytes, isn't UTF-8 up to 4 bytes? Yes, but MySQL only supports UNICODE "codepoints" that can be represented with up to 3 UTF-8 bytes.
OK, that's enough for now, I'll finish of this series with a post on collations, some general ideas on character set development and testing, and a few beers.
/Karlsson
Thursday, October 8, 2009
Of UNICODE, UTF-8, Character sets part 1
Why would you care about UNICODE? Come on now, most people can read english and english can be written using only 7-bit ASCII, so who needs more? Well, I think it's safe to say that Internet (remember that? Netscape, WWW, .com booms, pet food on the net etc) changed all that. Now applications can be found and run everywhere by anyone, more or less, so even if the application speaks english, and even if the user does, you may end up with users inputing data using some other character sets.
For someone like myself, having grown up in a "beyond A-Z" part of the world (Sweden, which is one of the easy cases), I can tell you how annoying it is when I input my address on some webpage (this happens even on swedish website)s using some swedish characters (I got 2 of the 3 beyond A-Z characters in the name of the street where I live), and it comes out looking like someone just smashed a fly prominently placed in the name of my street.
For a developer, this is difficult. Having someone test it is bad enough. And then we have things like localized keyboards (I got one of them), printers, OCR software etc. With that in mind, I plan to write a few blog posts on character sets, unicode and stuff like that.
Before I start this first post though, let me tell you that although I am pretty familiar with the subject, I'm far from an expert, so you may well catch an error or two. Please help me correct them if you find any!
So, that said, where shall we start? Well, lets begin with some basics, way back in the Reagan administration. The 7-bit ASCII character set was what was used all over the place when I started in this industry. The only competition was EBCDIC, but that was IBM mainframe only. This was in the early 1980's, but even then, we needed to use Swedish characters sometimes (I am a "swedish character" myself, I guess), and in the 7-bit ASCII world, this was handled by changing some lesser used punctuation marks to the 6 swedish characters (å, ä, ö and the upper case versions Å, Ä and Ö). This was an issue as a C developer, as I was back then, as the puctuation marks changed was the pipe, backslash, and the curly and square brackets! Yikes! Usually, you could put you VT220 terminal in US ASCII mode, but when you printed, the printer was often shared with office workers, meaning that the printouts often looked like:
main(int argc, char *argvÄÅ)
ä
printf("Hello WorldÖn");
å
Well, you see what I mean, quite unreadable, looks even worse than a Python script. I was about to write that I might have gotten the above slightly wrong, as it was a long time ago since I used this, and then I decided to look it up, and when I did, I actually had it all right, which goes to show that this was something you really had to learn if you were writing code in C here in Sweden back then in the stoneages.
Now, time went on (well, actually, it didn't. 7-bit swedish ASCII is still in use out there, quite a bit in homebrew ERP systems and stuff like that), and the next step was support for all (or most) of the western world characters in one character set. And the 8-bit ASCII set was born. This was pretty good, actually, and was pioneered most in the DEC VT220 terminal and then spread. There were still some variations of the 8-bit character set, but they were much fewer. The most common, by far, is the ISO 8859-1 character set, which contains most characters used in major western world common languages.
Why do I use such weird language here, you ask "major western world common languages", why do I just not say "western world langauges". Because that would be incorrect, that's why. Take my native Sweden for example. I think most swedes will agree that 8859-1 contains all character used in the official swedish language, and that there is just one such language. And this just isn't true, I'm afraid. Neither 8859-1 or any of the other 8859 variations cover any of the special characters in the 4 (I think there are 4, where 3 are sort-of common and used) sami languages / dialects.
8859-1 has a few variations (I know, I know, this is getting boring. ALL these character sets have variations). One such is the 8859-15, which, among other things, contains the Euro symbol. 8859 also has another name, which should be well known to you MySQLers: latin-1! And what about Windows? Windows uses codepages (cp) and cp1252 is the one used by non-UNICODE Windows variations in most of the western world. And cp1252 is the same as 8859-1, right? Nope, it's not, but for our practical people, it can be trested as being so.
So what is the difference between cp1252 and ISO-8859-1 you ask? The difference lies in something that hardly anyone uses anymore, which is in the control characters. CP1252 contains only the non-printable characters as used in 7-bit ASCII in range 0-31, whereas 8859-1 and -15 also has some control characters in the range 128-159. In the latter range, CP1252 has some real characters.
This difference is due to ISO 8859-1 being so much older, from days when we actually used control characters (do you youngsters reading this even know when these are? If not, ask your grandaddy). But besides this, they are the same. This means that web-pages, which typically use 7-bit (very old pages do), 8859-1 or UTF-8 (other variations DO exist, but these are the most common ones), using 7-bit ASCII or 8859-1 can be displayed on Windows using CP1252, as 1252 just adds characters in a control characters range, and control characters aren't used on a web-page (except the basic LF, CR/LF, LF/CR and ... NO, dont get be started on THAT for gods sake!).
So along comes 8859-15, which builds on 8859-1, but adds the Euro sign, among a few other things. And as CP1252 was already in wide use, and as 8859-1 was largely compatible with CP1252 for all practical uses, and because noone in their right mind use much of control characters anymore, the committe defining 8859-15 was smart enough to put the additional characters in the same place as the existing ones in CP1252 (the Euro sign is a good example, CP1252 contains the Euro sign in the upper control characters range). HA HA HA Got you there. This is ISO, a bunch of smart people, of course they would not put the Euro sign in 8859-15 in the same place as it was in CP1252! The effect was that, I think most people who think they use 8859 actually use CP1252 (as the Euro sign is used more and more, and the 1252 encoding of it is probably more well known).
OK, so this is a mess. You understand that by now I think, it's not just me who is a mess, the whole character set thing is. Luckily UNICODE will fix that, so more on that in the next post of this subject (and if you beleive that UNICODE will fix this and stop the controversy, let me tell you about a New York Bridge that I can get you a real good deal on). And also something on collations. What are those? Any why? And what happened to the squirrel? We'll be right back, so don't touch that dial!
/Karlsson
AKA The Swedish character
For someone like myself, having grown up in a "beyond A-Z" part of the world (Sweden, which is one of the easy cases), I can tell you how annoying it is when I input my address on some webpage (this happens even on swedish website)s using some swedish characters (I got 2 of the 3 beyond A-Z characters in the name of the street where I live), and it comes out looking like someone just smashed a fly prominently placed in the name of my street.
For a developer, this is difficult. Having someone test it is bad enough. And then we have things like localized keyboards (I got one of them), printers, OCR software etc. With that in mind, I plan to write a few blog posts on character sets, unicode and stuff like that.
Before I start this first post though, let me tell you that although I am pretty familiar with the subject, I'm far from an expert, so you may well catch an error or two. Please help me correct them if you find any!
So, that said, where shall we start? Well, lets begin with some basics, way back in the Reagan administration. The 7-bit ASCII character set was what was used all over the place when I started in this industry. The only competition was EBCDIC, but that was IBM mainframe only. This was in the early 1980's, but even then, we needed to use Swedish characters sometimes (I am a "swedish character" myself, I guess), and in the 7-bit ASCII world, this was handled by changing some lesser used punctuation marks to the 6 swedish characters (å, ä, ö and the upper case versions Å, Ä and Ö). This was an issue as a C developer, as I was back then, as the puctuation marks changed was the pipe, backslash, and the curly and square brackets! Yikes! Usually, you could put you VT220 terminal in US ASCII mode, but when you printed, the printer was often shared with office workers, meaning that the printouts often looked like:
main(int argc, char *argvÄÅ)
ä
printf("Hello WorldÖn");
å
Well, you see what I mean, quite unreadable, looks even worse than a Python script. I was about to write that I might have gotten the above slightly wrong, as it was a long time ago since I used this, and then I decided to look it up, and when I did, I actually had it all right, which goes to show that this was something you really had to learn if you were writing code in C here in Sweden back then in the stoneages.
Now, time went on (well, actually, it didn't. 7-bit swedish ASCII is still in use out there, quite a bit in homebrew ERP systems and stuff like that), and the next step was support for all (or most) of the western world characters in one character set. And the 8-bit ASCII set was born. This was pretty good, actually, and was pioneered most in the DEC VT220 terminal and then spread. There were still some variations of the 8-bit character set, but they were much fewer. The most common, by far, is the ISO 8859-1 character set, which contains most characters used in major western world common languages.
Why do I use such weird language here, you ask "major western world common languages", why do I just not say "western world langauges". Because that would be incorrect, that's why. Take my native Sweden for example. I think most swedes will agree that 8859-1 contains all character used in the official swedish language, and that there is just one such language. And this just isn't true, I'm afraid. Neither 8859-1 or any of the other 8859 variations cover any of the special characters in the 4 (I think there are 4, where 3 are sort-of common and used) sami languages / dialects.
8859-1 has a few variations (I know, I know, this is getting boring. ALL these character sets have variations). One such is the 8859-15, which, among other things, contains the Euro symbol. 8859 also has another name, which should be well known to you MySQLers: latin-1! And what about Windows? Windows uses codepages (cp) and cp1252 is the one used by non-UNICODE Windows variations in most of the western world. And cp1252 is the same as 8859-1, right? Nope, it's not, but for our practical people, it can be trested as being so.
So what is the difference between cp1252 and ISO-8859-1 you ask? The difference lies in something that hardly anyone uses anymore, which is in the control characters. CP1252 contains only the non-printable characters as used in 7-bit ASCII in range 0-31, whereas 8859-1 and -15 also has some control characters in the range 128-159. In the latter range, CP1252 has some real characters.
This difference is due to ISO 8859-1 being so much older, from days when we actually used control characters (do you youngsters reading this even know when these are? If not, ask your grandaddy). But besides this, they are the same. This means that web-pages, which typically use 7-bit (very old pages do), 8859-1 or UTF-8 (other variations DO exist, but these are the most common ones), using 7-bit ASCII or 8859-1 can be displayed on Windows using CP1252, as 1252 just adds characters in a control characters range, and control characters aren't used on a web-page (except the basic LF, CR/LF, LF/CR and ... NO, dont get be started on THAT for gods sake!).
So along comes 8859-15, which builds on 8859-1, but adds the Euro sign, among a few other things. And as CP1252 was already in wide use, and as 8859-1 was largely compatible with CP1252 for all practical uses, and because noone in their right mind use much of control characters anymore, the committe defining 8859-15 was smart enough to put the additional characters in the same place as the existing ones in CP1252 (the Euro sign is a good example, CP1252 contains the Euro sign in the upper control characters range). HA HA HA Got you there. This is ISO, a bunch of smart people, of course they would not put the Euro sign in 8859-15 in the same place as it was in CP1252! The effect was that, I think most people who think they use 8859 actually use CP1252 (as the Euro sign is used more and more, and the 1252 encoding of it is probably more well known).
OK, so this is a mess. You understand that by now I think, it's not just me who is a mess, the whole character set thing is. Luckily UNICODE will fix that, so more on that in the next post of this subject (and if you beleive that UNICODE will fix this and stop the controversy, let me tell you about a New York Bridge that I can get you a real good deal on). And also something on collations. What are those? Any why? And what happened to the squirrel? We'll be right back, so don't touch that dial!
/Karlsson
AKA The Swedish character
Subscribe to:
Posts (Atom)