Windows Azure v.Next–Azure Websites, Linux on Azure, Persistent VM and much more …

Building Cloud applications has never been easier! Ever! The recent news announced at MEET Windows Azure event just proved it! The most exciting, the most anticipating, the most wanted release of Windows Azure is now here! Check out the samples, get the tools and dive in the clouds!

Azure Websites

Did you want to run your Drupal site in Windows Azure? Or maybe your Joomla project, or the new Umbraco 5, don’t forget your small WordPress site. Now you can either built it from scratch, or just deploy it. How to deploy? Do you like Git, or FTP ? Whatever you like, whatever you are confortable with – Windows Azure Websites is the platform to run your site, be it small or large scale enterprise site! Here is just a screenshot showing you the sample gallery, where you can chose how to start your site, if you haven’t yet:

You say that Joomla runs on PHP and MySQL! You are correct, Windows Azure supports PHP for quite some time, actually (almost) since the beginning, but it is easier now. What about MySQL? Well have you heard of ClearDB? A company that have been providing database-as-a-service for MySQL based applications. Globally distributed, fault tolerant database as a service. They have been partnering with Microsoft to provide MySQL-as-a-service within the Windows Azure data centers. Well, ironically enough their site is down for the time I write this blog post. But, trust me, since the MySQL is running in Windows Azure, it will not be down .
Oh, you have noticed – the Windows Azure Portal – reimagined! The whole portal now runs on HTML5 with METRO style interface. I have to admit that I like it much better than the old Silverlight based portal!

Persistent VM

It is not a replacement for Windows Azure VM Role, which still is stateless. It is a whole new feature, named Persistent VM. Having said that – it means, that all change you made to your VM after you deploy it to Windows Azure, will be reliably persisted across VM reboots, healings, recycling. How cool is that? Not only that, now with the Persistent VM feature, you would get a SLA for just 1 instance! What could you use that Persistent VM for? Just imagine – SQL Server, SharePoint, Linux …

Linux

What else you could do with Windows Azure now? You can, for example  run your Linux based VM! Yes, Linux on Azure! How cool is that, ah? Currently there are 4 distros you can chose from:

• OpenSUSE 12.1
• CentOS-6.2
• Ubuntu 12.04
• SUSE Linux Enterprise Server 11 SP2

But I am sure more will come soon!

Virtual Network

Connecting your own infrastructure to the cloud has never been easier. Windows Azure Virtual Network lets you configure network topology, including configuration of IP addresses, routing tables and security policies. It uses IPSEC protocol to provide a secure connection between your corporate VPN gateway and Windows Azure.
If I were you, I would go through the new Windows Azure Fact Sheet, go for the free trial to check out the Websites, and maybe even try the Linux VMs!
As a side note, something that is really on my head for quite a few years – finally we, in Bulgaria, will officially have Windows Azure!

MySQL - my (Un)Favourite Database

In my last project I decided to go out with MySQL. Why - do not ask why ;)

Anyway.

In the process of building, debugging, developing I noticed that some of the searches does not return a correct result. A search in a VARCHAR field (for Cyrillic characters). I looked for the table encoding - it was UTF8; I looked for the column encoding - it was UTF8 (both collations utf8_general_ci); I looked for the server default encoding - UTF8; I looked for the encoding passed by the client application - again it was UTF8.

Suddenly, by incident I noticed - the encoding of the database was cp1251 (the Cyrillic one). It killed me.

A simple command solved the problem:

ALTER DATABASE {db_name} DEFATULT ENCODING utf8, DEFAULT COLLATION utf8_general_ci;

But I was inserting correct values, when I was browsing the table I could see the right values, but when I search for specific values in the STORED PROCEDURE with AD-HOC SQL built dynamically in the procedure, I could not get the results ...

No Idea ... but I begin to look more and more to the MS SQL server ... and its EXPRESS edition...

SQL, Joins, Unions and more .. continued II

Last night I decided to let MySQL think about that query ... It seemed to have completed calculations in the morning. Not surprisingly it has returned the very same data like did the MS SQL - The multiplication of the results. But it has taken 6604156 milliseconds (~6604 seconds) to obtain the result. The same result that MS SQL EXPRESS returned in 274709 milliseconds (~275 seconds).

No further comments.
Case closed.

SQL, Joins, Unions and more .. continued

Hi again.

Today I made a test - transferred the data into MS SQL Server 2005 Express SP 2.0.

Guess What ? :)

The situation is almost the same ! Wondering why the result was messed, the execution time was arround '274709' - I guess this is the "ms" - "duration" column in the MS SQL Server Profiler. Which is arround 5 minutes.
The mess I am talking about is the following:
The situation is that in of the tables I have about 120k records, which should be displayed, on the other one I have about 5k records. Insted of showing "120000" for "di_count", and "5000" for "ei_count", in the result I got '594163480' (which is the number of rows in one of the left joined tables multiplied by the number of rows in the other one) for each of them - the same figure for the two columns. What is that ?

I just want to make a select from one table, than join second table, then join third table to the FIRST one, then join the last table AGAIN to THE FIRST one. Why is it joining the result alltogether?

Anyway .. using UNION, VIEW and work with the VIEW solves the problem, but I was just wondering why this happens ...

I j ust can't help showing you these two screens:

For the MSSQL Profiler:

And for the MySQL (note: the execution is still not finished!):

I have no comment!

SQL, Joins, Unions and more

Ok, this is will be about MySQL once again.

I had that problem. Let's assume we have 3 tables:
1. smetki
2. di_invoices
3. dk_invoices
4. ei_invoices
Each of the XX_invoices has a "foreign_key" to PK of "smetki". I am using MyISAM.
If I perform the following 3 queries separately:
---------------------------------------------------------
select
s.smetka_id, s.created_on,
count(di.invoice_id) as di_count
from
smetki as s
left outer join di_invoices as di on (s.smetka_id = di.smetka_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id
-----------------------------------------------------------
select
s.smetka_id, s.created_on,
count(dk.invoice_id) as dk_count
from
smetki as s
left outer join dk_invoices as dk on (s.smetka_id = dk.smetka_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id
------------------------------------------------------------
select
s.smetka_id, s.created_on,
count(ei.invoice_id) as ei_count
from
smetki as s
left outer join ei_invoices as ei on (s.smetka_id = ei.smetki_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id
------------------------------------------------------------
I have no problem, I have proper indexes and I got the results for less then 200 ms from each query.
But I need the 3 results into 1 single resultset so I decided to make a join like that:
select
s.smetka_id, s.created_on,
count(di.invoice_id) as di_count,
count(dk.invoice_id) as dk_count,
count(ei.invoice_id) as ei_count
from
smetki as s
left outer join di_invoices as di on (s.smetka_id = di.smetka_id)
left outer join dk_cor_invoices as dk on (s.smetka_id = dk.smetka_id)
left outer join ei_invoices as ei on (s.smetka_id = ei.smetki_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id
Now I have query which executes more than 15 minutes !! I couldn't wait to the end and killed it. There must be something wrong somewhere?
I sould notice that for the given period there are only 6 rows in the "smetki" table, and different number of rows in each xx_invoices table that correspond to the whole resultset from "smetki". I have to mention that there is one intersection between two of the XX_invoices tables where there are about 120k rows for one "smetka_id", and for the same "smetka_id" there are about 5k rows in the other.
Also - there are no other queries running by the time of measuring the performance.

What could be the solution. A friend of mines helped me. Make a UNION he said. So I did it like that:

(select
s.smetka_id, s.created_on,
count(di.invoice_id) as di_count, 0 as dk_count, 0 as ei_count
from
smetki as s
left outer join di_invoices as di on (s.smetka_id = di.smetka_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id)
UNION
(select
s.smetka_id, s.created_on,
0 as di_count, count(dk.invoice_id) as dk_count, 0 as ei_count
from
smetki as s
left outer join dk_invoices as dk on (s.smetka_id = dk.smetka_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id)
UNION
(select
s.smetka_id, s.created_on, 0 as di_count, 0 as ei_count,
count(ei.invoice_id) as ei_count
from
smetki as s
left outer join ei_invoices as ei on (s.smetka_id = ei.smetki_id)
where
s.created_on between '2007-12-10' and '2007-12-15'
group by
s.smetka_id )

OK, but now I have 3 rows for each single "smetka_id" - one row for the results from each table. And then we decided to make a VIEW over that UNION.

Gues what. Now I have the simple query:

select smetka_id, sum(di_count), sum(dk_count), sum(ei_count) from v_test group by smetka_id

Which returns me the desired result for less than a second !

I hope this would help your daily work with MySQL and SQL in general.

MySQL Tips & Tricks /MAX_ROWS & AVG_ROW_LENGTH/

Hello again ;)
I ran into a tricky situation last couple of days. I have some big tables with varchar fields where I have to JOIN ON varchar fields. The problem does not concern only the JOIN - it is a general problem using varchar / text fields used to be searched one way or another.

So - the situation: We have one table with about 1M rows (relatively small amount data for each row ~90 bytes) on one hand. And another table with records to be matched - about 100k rows (relatively medium amount of data for each row ~ 500 bytes). We do a SELECT which JOINs the two tables based on VARCHAR columns. Do not ask "why?" - This is the business logic; it cannot be otherwise (at least in the beginning). If we index the columns we will got the results fast! What is fast? Less than 50 ms to get around 200 joined rows. Well, OK, you got me - it depends strongly on the hardware configuration and MySQL settings. Let's assume this is our limit. I was very happy to discover that it works that fast. The speed is critical, because we will need to join around 100k rows.

Where is the problem then? Well, it happened that the first table is populated with another several hundred thousand records and it became with about 1.9M rows. And? And, it appeared that the MySQL should copy the temp table on the hard drive. As you can guess - the result is: 204 joined rows (not 204k, but only two hundred and four) for ..... 417 seconds. What about 100k rows? Killing me.

What can we do about that? Well, I know (the business logic predicts) that the first table - that with about 2M rows would never have more than 4 million rows. I also noticed that partitioning is only partially supported in the beta (RC yet) of MySQL 5.1.x, but the recommended for production environment is 5.0.x which does not support partitioning. So, I discovered the great options MAX_ROWS and AVG_ROW_LENGTH. Which does what - it says to the MySQL engine, that this table would never have more than MAX_ROWS tows, and the average row length would be AVG_ROW_LENGTH. You can find the average row length by executing the command SHOW TABLE STATUS FROM [DB_NAME] LIKE 'TBL_NAME'. So, if you know, if your business logic predicts that you will have a large table, BUT this table will have relatively known content (like it will have not more than 4M rows), and you know the average row length, you can set the MAX_ROWS and AVG_ROW_LENGTH for that table. The AVG_ROW_LENGTH is required if you have blob/text/varchar fields.

That will cause the MySQL engine to optimize the space required for that table, also optimizing the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. Yes - it works only with MyISAM tables. But you will be surprised. After I set the MAX_ROWS and AVG_ROW_LENGTH on that table with the 2M records - I got a surprising results - 90k joined rows for ..... 47 seconds !!

I think I will use these options often in the future, what about you?