Planet MySQL

[NewsBot]

Drizzle Query logging
21 July 2009, 11:49 pm

Currently Drizzle offers three (3) separate query logging plugins. These plugins offer an extensible means of gathering all or selected queries and provide the foundation for a query analyser tool. Additional filtering includes selecting queries by execution time, result size, rows processed and by any given regular expression via PCRE.

During this tutorial I’ll be stepping though the various logging_query parameters which log SQL in a CSV format.

Confirm Logging Plugins

You can view the current ACTIVE plugins in Drizzle with the following SQL.

drizzle> select version();

+--------------+

| version()    |

+--------------+

| 2009.07.1097 |

+--------------+

drizzle> select * from information_schema.plugins where plugin_name like 'logging%';

+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+

| PLUGIN_NAME     | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_AUTHOR                        | PLUGIN_DESCRIPTION              | PLUGIN_LICENSE |

+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+

| logging_gearman | 0.1            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a Gearman server | GPL            |

| logging_query   | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a CSV file       | GPL            |

| logging_syslog  | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log to syslog                   | GPL            |

+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+

3 rows in set (0.01 sec)

Logging all queries

You can define the following configuration variables to enable query logging.

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/general.csv

You can confirm the settings with the following SHOW VARIABLES.

drizzle> show global variables like 'logging_query%';

+---------------------------------------+------------------------------+

| Variable_name                         | Value                        |

+---------------------------------------+------------------------------+

| logging_query_enable                  | ON                           |

| logging_query_filename                | /var/log/drizzle/general.csv |

| logging_query_pcre                    |                              |

| logging_query_threshold_big_examined  | 0                            |

| logging_query_threshold_big_resultset | 0                            |

| logging_query_threshold_slow          | 0                            |

+---------------------------------------+------------------------------+

This command showing queries to be logged.

$ cat /var/log/drizzle/general.csv

1248214561824590,1,1,"","select @@version_comment limit 1","Query",1248214561824590,1240,1240,1,00,0

1248214582588346,1,3,"","show global variables like 'logging_query%'","Query",1248214582588346,1958,1706,6,62,0

Unfortunately the log does not yet provide a header. You need to turn the source code to get a better description of the columns.

snprintf(msgbuf, MAX_MSG_LEN,

"%"PRIu64",%"PRIu64",%"PRIu64",\"%.*s\",\"%s\",\"%.*s\","

"%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64

"%"PRIu32",%"PRIu32"\n",

t_mark,

session->thread_id,

session->query_id,

// dont need to quote the db name, always CSV safe

dbl, dbs,

// do need to quote the query

quotify((unsigned char *)session->query,

session->query_length, qs, sizeof(qs)),

// command_name is defined in drizzled/sql_parse.cc

// dont need to quote the command name, always CSV safe

(int)command_name[session->command].length,

command_name[session->command].str,

// counters are at end, to make it easier to add more

(t_mark - session->connect_utime),

(t_mark - session->start_utime),

(t_mark - session->utime_after_lock),

session->sent_row_count,

session->examined_row_count,

session->tmp_table,

session->total_warn_count);

The important parts of this information include:

getmicrotime - 1248214561824590

Session Id - 1

Query Id - 1

Schema

The Query: “show global variables like ‘logging_query%’”

The Query type “Query”

Time session connected - 1248214582588346

The total execution time - 1958

The execution time after necessary locks - 1706

The number of rows returned - 6

The number of rows examined - 6

The number of temporary tables used - 2

The total warning count - 0

I also found what I believe is a formatting problem logged as Bug #402831.

You can enable logging dynamically.

drizzle> select now();

+---------------------+

| now()               |

+---------------------+

| 2009-07-22 02:14:31 |

+---------------------+

1 row in set (0 sec)

drizzle> set global logging_query_enable=true;

Query OK, 0 rows affected (0 sec)

drizzle> select curdate();

+------------+

| curdate()  |

+------------+

| 2009-07-22 |

+------------+

1 row in set (0 sec)

drizzle> set global logging_query_enable=false;

Query OK, 0 rows affected (0 sec)

drizzle> select now();

+---------------------+

| now()               |

+---------------------+

| 2009-07-22 02:14:54 |

+---------------------+

1 row in set (0 sec)

1248228876381645,4,3,"","set global logging_query_enable=true","Query",1248228876381645,761,761,0,00,0

1248228886866882,4,4,"","select curdate()","Query",1248228886866882,105,105,1,00,0

I was not able to alter the logging_query_filename dynamically. Need to confirm with the development team about this functionality for the future.

drizzle> set global logging_query_filename='/tmp/general.csv';

ERROR 1238 (HY000): Variable 'logging_query_filename' is a read only variable

Logging slow queries

If you just wanted to emulate the MySQL slow query log, with a long_query_time of 1 second, you could use the following.

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/slow.csv

logging_query_threshold_slow=1000000

Drizzle supports the ability to set a threshold in microseconds.

NOTE: I wanted to demonstrate this using the popular MySQL SLEEP() function, only to find this is currently not available in Drizzle.   This is an ideal example of a simple UDF that can be written and added to Drizzle.  One day if I ever have the time.

Here is some sample output using queries > 1 second.

1248216457856195,1,43,"test","insert into numbers   select...","Query",1248216457856195,2160680,2160620,0,26214420,0

1248216462738678,1,45,"test","insert into numbers   select...","Query",1248216462738678,4530327,4530263,0,52428821,0

1248216472430813,1,47,"test","insert into numbers   select...","Query",1248216472430813,8990965,8990890,0,104857622,0

1248216473592812,1,48,"test","select @counter := count(*) from numbers","Query",1248216473592812,1152319,1152257,1,104857622,0

Logging by threshold

Drizzle Query Logging provides the ability to return results by 2 thresholds, the number of rows in the result, and the number of rows examined by the storage engine.

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/slow.csv

logging_query_threshold_big_resultset=100

1248216631322097,1,5,"test","select * from numbers limit 100","Query",1248216631322097,281,217,100,1002,0

1248216642763174,1,6,"test","select * from numbers limit 101","Query",1248216642763174,268,215,101,1012,0

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/slow.csv

logging_query_threshold_big_examined=1000

1248216785430588,1,6,"test","select * from numbers limit 1000","Query",1248216785430588,8055,7983,1000,10002,0

1248216800327928,1,7,"test","select count(*) from numbers","Query",1248216800327928,1041322,1041222,1,10485762,0

Logging by pattern

The final option is to return queries that match a given pattern via a PCRE expression.

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/slow.csv

logging_query_pcre=now

drizzle> select now();

+---------------------+

| now()               |

+---------------------+

| 2009-07-22 03:24:32 |

+---------------------+

1 row in set (0 sec)

drizzle> select curdate();

+------------+

| curdate()  |

+------------+

| 2009-07-22 |

+------------+

1 row in set (0 sec)

drizzle> select "now";

+-----+

| now |

+-----+

| now |

+-----+

1 row in set (0 sec)

drizzle> select "know how";

+----------+

| know how |

+----------+

| know how |

+----------+

1 row in set (0 sec)

1248233072792211,3,2,"","select now()","Query",1248233072792211,154,154,1,00,0

1248233085807520,3,4,"","select \"now\"","Query",1248233085807520,92,92,1,00,0

1248233096659018,3,5,"","select \"know how\"","Query",1248233096659018,75,75,1,00,0

Another example using a pattern.

/etc/drizzle/drizzled.cnf

[drizzled]

logging_query_enable=true

logging_query_filename=/var/log/drizzle/slow.csv

logging_query_pcre="[0-9][0-9][0-9]"

drizzle> select 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0 sec)

drizzle> select 11;

+----+

| 11 |

+----+

| 11 |

+----+

1 row in set (0 sec)

drizzle> select 111;

+-----+

| 111 |

+-----+

| 111 |

+-----+

1 row in set (0 sec)

drizzle> select 1111;

+------+

| 1111 |

+------+

| 1111 |

+------+

1 row in set (0 sec)

drizzle> select 11+22;

+-------+

| 11+22 |

+-------+

|    33 |

+-------+

1 row in set (0 sec)

1248233336460373,3,4,"","select 111","Query",1248233336460373,79,79,1,00,0

1248233339300429,3,5,"","select 1111","Query",1248233339300429,82,82,1,00,0

Unfortunately it seems that this variable is also not configurable dynamically at this time.

drizzle> set global logging_query_pcre="now";

ERROR 1238 (HY000): Variable 'logging_query_pcre' is a read only variable

This is definitely an improvement over current MySQL logging.  



Source: Planet MySQL

[NewsBot]

OScon so far
21 July 2009, 11:15 pm

Sunday morning, in the wee hours of the morning, while packing, I discovered that I didn't know where either my DL or my Passport is.  After spending an hour searching, I gave up, and decided to throw myself on the mercy of the TSA.  Which I did at the airport.  The TSA supervisor checked my credit cards, FlyClear card, corporate ID card, and CostCo card, and then stamped my boarding pass.  I only need to do this 3 more times on this trip,  and then do a very deep search of my room when I get back.

After I landed at SJC, I discovered that Tim Lord has shared the flight with me.  If I had known that ahead of time, I would have let him share my taxi ride from Capitol Hill neighborhood to the SEA airport.  As it was, I let him share my ride to San Jose Convention Center.  I was actually too early to check into my hotel room.  So instead I checked in my luggage, and then went off to find the Postgres Day.

Meeting up with open source community geeks, watching lightning talks, hacking, taking pictures.

I accidentally left my jacket in one of the meeting rooms, and ended up having to bid for it in an auction.  So now I owe $20 to the Postgres Foundation.  :)

Monday, I spent hacking and geek socializing, hanging out in the speaker room.  Some of the tutorials, but I didn't end up going to any of them.  Lunch was at the Good Karma cafe, which I had stumbled across the last time I was in San Jose.

Also on Monday, I hooked up MontyT with someone who may have a solution for automating the build of Drizzle on Windows machines, without manually maintaining Visual Studio Project files, or porting Autotools to Windows.

Tuesday, I went to the Gearman tutorial, and while in it, started working on http://forge.gearman.org/ and also started implementing a bunch of basic "plumbing" gearman workers that need to exist, to link together filesystem, mogilfs, couchdb, amazon web services, memcached, and Erlang.

Right now, I'm sitting in the Ignite talks.  BrianA just won an Google O'Reilly Open Source Award.

Source: Planet MySQL

[NewsBot]

Don’t Fight the Query Cache
21 July 2009, 8:05 pm

In the spirit of the Good Practice/Bad Practice series, this post is – apart from my first as contributing engineer at Open Query – a basic level application tip. More advanced application developers will already be aware of the issue.

It is easy to overlook certain “non-deterministic” functions in your queries which will prevent the query cache from caching its results. Common examples include NOW(), CURDATE(), and RAND() (here is the complete list).

Obviously when RAND() is used, you usually don’t expect the same result if the query is repeated, so this isn’t a concern. But other cases, particularly date/time related queries, can actually benefit from the cache, and there is a simple workaround. When the query cache is able to satisfy a query, the overhead (in latency and CPU) of parsing and planning the query is eliminated, and more importantly, no disk I/O on data and indexes is required – reducing latency even further and improving scalability on a busy server.

(Remember that the MySQL query cache is not always helpful. If your queries don’t repeat themselves often, the cost of maintaining the query cache (hashing query text, managing cached result sets, etc) is just unwanted overhead. As always, test with your own workload.)

I’ll use an example of a query that might be run many times, by different web site users. Imagine a table recording user login events:

CREATE TABLE logins (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

user_id INT UNSIGNED,

tstamp TIMESTAMP NOT NULL

);

To find how many different users logged in today,

SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= CURDATE();

The first thing to know about this query is that it requires a full table scan, because there is no index on tstamp. Let’s add the index.

mysql> CREATE INDEX tstamp_idx ON logins (tstamp);

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= CURDATE();

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+

| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+

|  1 | SIMPLE      | logins | range | tstamp_idx    | tstamp_idx | 4       | NULL |    1 | Using where |

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+

Explain shows that the query will use the new index to select a range of rows. (In general, BTree indexes are ideal for selecting records where one or more column(s) are equal to constant(s), or a range where you want rows where column is <, <=, >, or >= a constant. This query is the latter kind.)

Because this query uses CURDATE(), it is not eligible for query caching. We can confirm this as follows:

mysql> insert into logins (user_id) values(100);

Query OK, 1 row affected (0.00 sec)

mysql> show global status like 'Qcache_inserts';

+----------------+-------+

| Variable_name  | Value |

+----------------+-------+

| Qcache_inserts | 6     |

+----------------+-------+

1 row in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= CURDATE();

+-------------------------+

| COUNT(DISTINCT user_id) |

+-------------------------+

|                       1 |

+-------------------------+

1 row in set (0.01 sec)

mysql> show global status like 'Qcache_inserts';

+----------------+-------+

| Variable_name  | Value |

+----------------+-------+

| Qcache_inserts | 6     |

+----------------+-------+

Notice that the Qcache_inserts counter did not increase; the result of running the query was not inserted into the query cache.

The workaround here is to eliminate the function call to CURDATE() by inserting today’s server date as a literal, in your application (for example, PHP):

$sql = "SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= '".date('Y-m-d')."'";

The SQL then becomes something of the form:

SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= '2009-07-12';

We can now see that the result is being cached, observing Qcache_inserts:

mysql> show global status like 'Qcache_%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

...

| Qcache_hits             | 5        |

| Qcache_inserts          | 6        |

...

mysql> SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= '2009-07-12';

+-------------------------+

| COUNT(DISTINCT user_id) |

+-------------------------+

|                       1 |

+-------------------------+

1 row in set (0.01 sec)

mysql> show global status like 'Qcache_%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

...

| Qcache_hits             | 5        |

| Qcache_inserts          | 7        |

...

We can also see from Qcache_hits that repeating the same query will use the cached result:

mysql> SELECT COUNT(DISTINCT user_id) FROM logins WHERE tstamp >= '2009-07-12';

+-------------------------+

| COUNT(DISTINCT user_id) |

+-------------------------+

|                       1 |

+-------------------------+

1 row in set (0.00 sec)

mysql> show global status like 'Qcache_%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

...

| Qcache_hits             | 6        |

| Qcache_inserts          | 7        |

...

This optimisation may be worthwhile when you have queries which are frequently repeated – such as statistics that all users see.

Another query pattern is to aggregate rows according to some function of the current moment, NOW(). For example, “how many users logged in during the past hour?” To exploit the query cache you can make the result slightly approximate, e.g. in your application take the current time and round down to the last 5 minute interval, and compare with this calculated time. This slightly out of date result is cacheable until the next interval begins (and the query text changes).

(Remember that any modifications to the tables involved in the query will invalidate the cached result, so queries on tables which are very frequently changed are not going to benefit greatly from these techniques.)

For more information on the query cache, see the fine manual.

— Toby Thain, Open Query Engineer

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to cater to all levels of expertise!



Source: Planet MySQL

[NewsBot]

MySQL Server binaries: one source to rule them all
21 July 2009, 7:00 pm





If you missed Kaj's announcement in the splashing news commotion at the latest MySQL Conference, then you may be interested to get this information again.

There was a piece of news that should be extremely important for all the users. MySQL server binaries used to be split between Enterprise and Community, and they were released with separate schedules. Not anymore. Starting from April 2009, the MySQL Community and Enterprise editions are built from the same code, and they are released with the same frequency.

There were rumors about the two editions being treated differently. Since we are talking about it, let me assure you that this is not the case. Both editions go through the same tests, and even more so now, since they come from the same tree. Until version 5.0.81, there was a separate tree for Community (with extra features), but now there is only one.  

For every bug fix release, both editions are released on the same day.

Another difference that has disappeared is the version number. Previously, even numbers were for Enterprise, odd ones for Community. Now, every version number identifies both the Community and Enterprise edition.

Enjoy the best bits from MySQL. download at will!



Source: Planet MySQL

[NewsBot]

Advanced Squid Caching in Scribd: Logged In Users and Complex URLs Handling
21 July 2009, 6:18 pm

It’s been a while since I’ve posted my first post about the way we do document pages caching in Scribd and this approach has definitely proven to be really effective since then. In the second post of this series I’d like to explain how we handle our complex document URLs and logged in users in the caching architecture.

First of all, let’s take a look at a typical Scribd’s document URL: http://www.scribd.com/doc/1/Improved-Statistical-Test.

As we can see, it consists of a document-specific part (/doc/1) and a non-unique human-readable slug part (/Improved-Statistical-Test). When a user comes to the site with a wrong slug in the document URL, we need to make sure we send the user to the correct URL with a permanent HTTP 301 redirect. So, obviously we can’t simply send our requests to the squid because it’d cause few problems:

When we change document’s title, we’d create a new cached item and would not be able to redirect users from the old URL to the new one

When we change a title, we’d pollute cache with additional document page copies.

One more problem that makes the situation even worse – we have 3 different kinds of users on the site:

Logged in users – active web site users that are logged in and should see their name at the top of the page, should see all kinds of customized parts of the page, etc (especially when a page is their own document).

Anonymous users – all users that are not logged in and visit the site with a flash-enabled browser

Bots – all kinds of crawlers that can’t read flash content and need to see a plain text document version

All three kinds of users should see their own document page versions whether the page is cached or not.

So, how do we solve these two problems? Here is how.

First of all, to fix the URLs problem we’ve decided to rewrite the URL before it goes to a squid server. We change URLs to look like this: http://www.scribd.com/doc/1?__enable_docview_caching=1. This makes the document URL dependent only on a unique document ID that never change and sends an additional parameter to the backend to signal that the page could potentially be cached. The slug is sent to backend using an HTTP-header (X-Scribd-Slug) so that backend could check the slug and return a redirect if needed.

To make sure we won’t respond with a cached page to a request with an invalid URL (invalid slug basically), we use Vary: X-Scribd-Slug HTTP header which is implemented in Squid (only late 2.6 and 2.7) and makes it check specified headers in a request before responding with a cached content. If the header of the cached content is different then the header in the request, squid proxies the resuest to backend where we could handle the cache miss as we want.

Next, to resolve the users problem we’ve created a small nginx module that looking at a request headers could tell you whether the user is a bot or not and whether he’s logged in or an anonymous visitor. This module basically exposes a $scribd_user_id variable to our configs and we could use the variable to do separate configuration for different kinds of users.

At this point we do not cache document pages for logged in users so we basically have two copies of each page in the cache: flash-enabled document page and an inline document page. We do this separation by changing our cache URLs one more time: we add a scribd_user_id=$scribd_user_id variable (anonymous = 0, bot = -1) to the cache URL: http://www.scribd.com/doc/1?__enable_docview_caching=1&scribd_user_id=0.

And last, not not least, we use two really awesome Squid features called stale-while-revalidate and stale-if-error (AFAIR, they were invented in Yahoo! and then described by their squid admin).

Option stale-while-revalidate allow us to quickly serve content from the cache while doing background re-validation requests to the Rails backend. Option stale-if-error basically allows us to serve content from the squid cache when Rails backend is down/dead/slow.

All these changes allowed us to handle more traffic with less hardware and what is even more important, they helped us improve user experience with the site: response times dropped 2-3 fold and much less people see our Ouch! pages (HTTP 50x errors when backend is dead or overloaded). Here is an example of one of our servers’ hit ratio and traffic savings daily graphs:





This it with the logged in users and complex URLs handling in Scribd caching architecture. Next post in this series will explain how we do cache invalidation in Scribd. Stay tuned.









Source: Planet MySQL

[NewsBot]

Possible Improvements to MySQL Administration
21 July 2009, 5:27 pm

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root access. To be clearer, at least that’s the case of most packaged UNIX versions of MySQL.

Using a package manager can save you a lot of time, but only if the packages cover your requirements. MySQL packages at least for RedHat work fine, but not if you intend to run multiple versions of MySQL, multiple mysqld instances or as stated above if you don’t want to manage MySQL as the root user.

Make it possible to manage multiple mysql instances the same way as single mysql instances. Currently that’s not possible and as such makes you choose one way or the other and perhaps differently on different servers. Not good.

Possible Solutions

When making changes it’s important to decide how to make that change. You can make things backwards compatible or you can from a certain software version change behaviour.  Currently MySQL 5.1 is GA which means that any changes if they were to take place would be not be until at least 6.0 as 5.4 seems pretty much on the way to be the next “GA” version shortly (end of the year)?

So here are my thoughts on making things “better”.

Give up root earlier. MySQL startup scripts should be able to run as a non-root user and work roughly the same. MySQL uses port 3306 so does not need to run as root to bind to this port, so there’s no real need to do anything as root. If that means running 2 startup scripts rather than one then so be it. Both Oracle and Sybase do this, and DB2 if I remember correctly.

Make mysqld_safe optional. While the idea of mysqld_safe is great, it restarts your server if it crashes, and it may have been necessary in the past, I’m not so convinced that it should be part of the default startup script. Currently I believe this needs to run as root. Again make it able to run as a non-root user, but also make the startup script use it optionally. If you don’t monitor a msyqld instance extensively you may never even know mysqld has crashed, as often the startup time may be pretty quick.

Make mysqld_safe optionally notify the sysadmin or dba if there’s been a crash. This means you don’t need to monitor in more sophisticated ways if there’s a problem. An email is fine, or if not provide hooks for talking to an external program.

Make it possible to install multiple MySQL packages at the same time. You can do this with the kernel and people don’t complain. Make it the same with MySQL. The focus of this is mainly for the server side where it may be that you want to upgrade. You can’t install a MySQL 5.0 and MySQL 5.1 rpm at the same time as the package’s file locations collide. That does mean doing things differently to how things are done now but it also means it’s much easier to run versions of MySQL on the same box. Once you’ve finished with a version and are no longer using it then the package can be erased as normal. There are packaging solutions to make sure that at least from the client side a specific version is the main version used, even if you may want to use a different version for specific purposes.

Make it possible to run multiple mysqld instances at the same time. In theory this is possible now, with mysqld_multi.

However this is not the default behaviour and also requires you to change the init script. It would seem better to make the init script “multi-instance” aware and if configured appropriately this would be the default behaviour.

Also if supporting multiple instances ensure that each instances configuration files (my.cnf) are not stored in a common /etc/my.cnf file but each in a separate location. This makes it much easier move the instance together with it’s configuration file about.

Support for each instance the ability to optionally start the instance. Something like /etc/oratab comes to mind, where instance name, instance my.cnf, optional startup, optional use of mysqld_safe, and user to run under are configured.

Make it possible from the init script to start/stop all instances or an instance by name. Names are so much easier to understand than numbers!

Maintain backwareds compatibility. This keeps everyone happy and makes the transition smoother.  MySQL has a lot of history so making big changes in an incompatible way mean that people get confused and upset. If the changes suggested above can be done in a way which allows current behaviour to be continued, but the new behaviour to be “enabled” with very little effort, then people who like me have to manage several boxes can take advantage of this new functionality.

It can be argued that if you write your own scripts that none of the above is necessary. It can be argued that if you build MySQL from source then you can install things how ever you want. If can be argued that it’s not MySQL’s problem, but the sysadmin administrator. It can be argued that every site is different. However to be honest while some of the smaller details may indeed be different and the scale may change in the end, one MySQL server is pretty much the same as the others. If MySQL, or Sun, or Oracle now make it easier to manage the boxes the way we need, and to do this consistently, and cover many of the use cases we won’t all be re-inventing the wheel. That’s what it seems we all do, or if not we put up with inconsistent setups on different servers that means we spend less time on tending the database and more on doing mundane things such as get things running the way we want.

I started using MySQL on a single server with just one instance running. That was some time ago. Others are likely to follow my steps and then run into some of the issues I am mentioning, or have come across this some time ago. The ideas I mention above are I think solvable but it would be much better if we could come up with a common solution rather than me implement my own or you implement yours.

So am I the only one who thinks these changes would be helpful, and am I missing some other points? Would love to know what you think.



Source: Planet MySQL

[NewsBot]

Just how useful are binary logs for incremental backups?
21 July 2009, 4:26 pm

We’ve written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

We have 2 Servers in a Master-Slave topology.

The database size is 100 GB (same tables on each).

The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)

The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn’t take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary logs  it would take an additional 20 hours ((12 * 0.9) + (12 * 0.75) = 19h48m).

If you wanted to do something like setup a new slave with a 24-hour old backup, and apply the binary logs continuously until it catches up, you will be waiting almost 5 days until that happens (each day has 24hr-19h48m = 4h12m “free” capacity, 19h48m/4h12m = 4.7 days).

So what are the solutions?

If you are using all InnoDB tables, an XtraBackup incremental backup should be much faster than using binary logs.  You can understand Vadim’s excitement when he announced this feature a few months ago.

If you are using multiple storage engines, then your options are to either try and time delay slaves (to keep them close to up to date), or hope that it’s not often that you need to restore!  Eventually this problem should be lessened by a MySQL Server feature – parallel execution on slaves.  Lets hope that it can get enough testers so that it makes it into a new release very quickly.

Entry posted by morgan |

No comment

Add to: | | | |



Source: Planet MySQL

[NewsBot]

PBMS is transactional!
21 July 2009, 2:33 pm

The PBMS engine now has built in support for transaction so that if you reference or dereference BLOBs during a transaction the changes you made will be committed or rolled back with the transaction. Up until now PBMS had depended on the host engine to handle transactions and inform it what needed to be done in the event of a rollback.

I have implemented transaction support by adding a circular transaction log and transaction cache.  The circular transaction log is dynamic and can grow and shrink as required. The transaction records are all small (30 byte) fixed length records so the log doesn’t need to be that large to be able to handle a lot of transactions. A head and tail pointer are maintained to indicate where the head and tail of the circular log is. If the circular log becomes full then the records just overflow and are written to the end of the log. Once the transaction reader has caught up after an overflow has occurred it simply resizes the circular log to include the overflow and everything continues as normal with a larger circular log. The circular log will be reduced to its normal size once free space becomes available at the end of the log.

If anyone has any use for something like this them selves it has been written as a standalone module so it would not be to difficult to pull it out and build it into something else.



Source: Planet MySQL

[NewsBot]

451 CAOS Links 2009.07.21
21 July 2009, 12:19 pm

Microsoft contributes to Linux. Acquia raises $8m. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca

“Tracking the open source news wires, so you don’t have to.”

Microsoft contributes to Linux

Microsoft announced that it is to contribute device driver code to the Linux kernel under the GPLv2. Prompting us to publish a CAOS Theory Q&A. Answering one questioning we failed to ask, ZDnet reported that Microsoft’s Linux contributions should find their way into the 2.6.32 release.

Acquia raises $8m

Mass High Tech reported Acquia has picked up an $8m second funding round from existing investors. The funding was later confirmed by the company.

# Adobe released Flash Platform Media and Text Frameworks as open source.

# Red Hat replaced CIT in the S&P 500.  

# The recent spate of posts about licensing continued as Dirk Riehle argued that every license has its time and place and examined the intellectual rights imperative of single-vendor open source. Meanwhile Matt Asay noted that the right business strategy is openness, but defining that strategy is variegated, while Tarus Balog explained why reports on the death of the GPL are greatly exaggerated.

# Bradley Kuhn described Microsoft’s patent deal with Buffalo as free software-targeted patent aggression.

# Engine Yard launched Cloud services platform and GA of Flex, a cloud service plan for Rails apps.  

# City of Chicago selected SpringSource Hyperic HQ Enterprise to run and manage IT and Web operations.

# Take Off Technology is sponsoring two new support for Solaris/and integration with the ZFS filesystem in openQRM.

# Alfresco’s Nancy Garrity presented the case for community involvement with commercial open source.

# “I’m giving [Microsoft] its divorce papers,” says City of Edmonton CIO, according to an Information Exec report.

# Canonical has released the source code for Launchpad.

# Percona released v6 of its XtraDB storage engine for MySQL.

# HadoopDB is a new open source project combining DBMS and MapReduce technologies to target analytical workloads.

# Dr Dobbs Q&A with MySQL’s creator, Michael “Monty” Widenius.  

# Accenture announced that it is to acquire Symbian professional services operations from Nokia.



Source: Planet MySQL

[NewsBot]

Olio on 6-core Opterons (Istanbul) based Sun Systems
21 July 2009, 8:00 am

Sun is launching systems with multisocket  6-core Opterons (Istanbul) today. Last week I got access to  Sun Fire X4140 with 2 x 6-core Opterons with 36GB RAM. It is always great to see such a 1RU system packaged with so many x64 cores.

# psrinfo -vp

The physical processor has 6 virtual processors (0-5)

  x86 (chipid 0x0 AuthenticAMD family 16 model 8 step 0 clock 2600 MHz)

    Six-Core AMD Opteron(tm) Processor 8435

The physical processor has 6 virtual processors (6-11)

  x86 (chipid 0x1 AuthenticAMD family 16 model 8 step 0 clock 2600 MHz)

    Six-Core AMD Opteron(tm) Processor 8435

I decided to take the system for a test drive with Olio. Olio is a Web 2.0 toolkit consisting on a web 2.0 event calendar application  which can help stress a system. Depending on your favorite scripting language you can use either PHP, Ruby on Rails, Java as the language used to create the application. (I took the easy way out and selected Olio PHP's prebundled binary kit)

Please don't let the small 2MB kit size fool you thinking it will be a easy workload to test it out. While setting it up I figured that to generate the data population for say 5000 users you will need space with atleast 500GB disk space for the content that it generates for it. Yes I quickly had to figure out how to get a storage array for Olio with about 800GB LUN.

Olio requires a webserver, PHP (of course) and  a database for its metadata store (it has scripts for MySQL already in the kit). The system came preconfigured with Solaris 10 5/09. I downloaded MySQL 5.4.1 beta  and also the Sun WebStack kit which has Apache Httpd 2.2, PHP 5.2 (and also MySQL 5.1 which had not used since I had already downloaded MySQL 5.4 Beta). Memcached 1.2.5 is part of the WebStack download and Olio is configured to use it also by default (but can be disabled too).

Eventually everything was installed and configured in the same X4140 and using the Faban Harness on another system started executing some runs with file store and the meta store preconfigured to handle all the way upto 5000 concurrent users. The results are as follows:



Here are my observation/interpretations:

* Eventually beyond 10 cores run I find that the system memory (36GB) is not enough to sustain more concurrent users to fully utilize the remaining cores. I would probably need RAM  in the range of 48GB or more to handle more users. (PHP is not completely thread-safe and hence the web server used here spawns processes)

* This 1RU system can handle more than 3200 users  (with everything on the same system) with CPU cycles to spare is pretty impressive. It means you still have enough CPU to log into the system without seeing degraded performance.

 * Actually you can see here that SMP (or should be call these SMC - Scalable Multi Cores) type system helps when the initial cores are added  instead of using multiple single core systems (ala in Cloud).

 In an upcoming blog entries I will talk more about the individual components used.



Source: Planet MySQL

[NewsBot]

Backups, Backups, Backups (and Restore)
21 July 2009, 2:38 am

Tungsten Replicator has built-in backup and restore for MySQL!   I checked in the final touches over the weekend. Here's how to run a backup on a database and store it so you can restore it later.  If you leave off options, we use the default back-up procedure and storage that you select when setting up replication.

trepctl backup [-backup agent] [-storage agent] [-limit timeout]

And here's how to restore.  If you leave off the options, we find the latest backup in your default storage and load that.

trepctl restore [-uri backup_uri] [-limit timeout]

That's the syntax.  Now here's what happens behind the scenes.  First Tungsten Replicator has a new BackupAgent plug-in that implements backup procedures. We have a backup agent for each the following types of backups for MySQL:

Mysqldump - Probably least useful but easy to set up.

LVM snapshot to tar.gz - Scalable with miminal database down time.  Features are similar to Lenz Grimmer's excellent mylvmbackup script.

Script dump - Integrate your own script for backup and restore.  The script has to follow some very simple conventions, for which there is an example.  You can integrate practically any backup/restore package this way.

Speaking as an unbiased user of the system I love the LVM snapshots. LVM snapshot is overall the most convenient way to do backups for a wide range of databases, not just MySQL, though I have to admit I have not used either InnoDB Hot Backup or Percona's XtraBackup.   I guess now would be a good time to try them since we can integrate them through the script dump mechanism.

Meanwhile, mysqldump is far and away my least favorite mechanism, not least of all because of this unusually heinous bug, which completely breaks the mysqldump --all-databases command.  It's still not fixed as of at least MySQL 5.1.34.  (How on earth did this one get in and why didn't it get corrected instantly?)

Second, there is a new StorageAgent plug-in to handle storing and retrieving backup files.  There is one of these for each type of storage.  Currently the choice is limited to shared disk but I expect we'll have an Amazon S3 storage plug-in in the near feature.  That's just too useful to pass up for very long...Among other things we ourselves run all our company services on Amazon and I would like to use it for our own backups.

If you want to use the new backup capability you can either build Tungsten Replicator yourself using the instructions on our getting started page or wait until the next binary build comes out in a couple of weeks.   Backup and restore are documented here in the Tungsten documentation.

We went through a lot of effort to make the backup and restore processes as simple as possible.  It's down to one keyword for each operation, so I don't think it's going to get much simpler.  Please try it out and provide your feedback.  I love bug reports and want to hear what you think.



Source: Planet MySQL

[NewsBot]

OSCON Tutorial - Scaling a web application (mostly PHP/MySQL)
21 July 2009, 2:04 am

A quick blog post from the OSCON floor where I just finished a 3.5 hour tutorial on "Scaling a Web Application (mostly PHP/MySQL)". To be honest I submitted this talk as a 45 min talk and clicked the wrong button and ended up being handed a tutorial which I think turned out to be a good thing. After all, we could spend days and years talking about scaling.

One of my goals in this presentation wasn't just to talk about tips and examples, but also talk about the general business of scaling and creating scaling plans, scenario planning or capacity planning. This I believe is almost always more important than coming up with the small tips that fix a site when you have a problem!

Thanks to Ronald Bradford for showing up for the last hour of the presentation and helping out, it's good to have help and from a knowledgeable expert as well :)

Slides are now on Slideshare.

Source: Planet MySQL

[NewsBot]

I’m Offering Pro-Bono Consulting
21 July 2009, 12:10 am

I started my company about a year ago, but I’ve been doing consulting for a long time. In fact, my first job in the IT industry was working for a consulting firm. Before that, starting as far back as grade school, I was involved in a lot of volunteer civic and community service activities. I admire companies who get involved in their communities, or even outside of their communities, wherever help is needed.

As part of my business plan, I’ve put in place a policy of accepting one pro-bono consulting project per year. So far, I haven’t gotten any requests for free consulting work, so here’s my public shout out to let you know what types of services are available:

1. Speaking or Training. My specialties are things like advanced Linux administration and SQL, but I’m perfectly capable of delivering content for people who just need to know how the internet works, or want to know more about social media.Training, funny enough, has been the bulk of my business for the past year.

2. I can help with MySQL performance tuning on *nix systems, including finding hotspots related to the design of the database itself, or how your application code interacts with the database. If it happens that your MySQL server is performing poorly due to an underpowered system, I can also pinpoint which resource is dragging on the performance of your database.

3. If you just need random scripts written to perform *nix system administration tasks, I can consult with you about the requirements and write them for you. Note that while I can script in several languages, my preference for anything longer than 40 lines of code is Python.

4. I can build PC’s, install networks, set up firewalls and wireless routers, and all of the normal “office IT” functions, but note that my consulting is Linux consulting. I don’t work with Windows (well, I do, but not for free)

5. If there’s some other thing you’ve seen me blog about here, chances are I’ll be willing to perform a pro-bono consulting engagement to do it for you, or show you how to approach a problem, a large project, a migration, automation, monitoring, security or whatever.

Unless you happen to live within commuting distance to Princeton, NJ, work will be done remotely

Please email your request to jonesy at owladvisors dot com. Include your organization’s name, your contact info, and as much detail about the project and what your organization does as possible. The decision of which project to take on will be based solely on the information in your request!



Source: Planet MySQL

[NewsBot]

We don’t need any more complexity
20 July 2009, 8:14 pm

 

In thinking about the software platform topic, I found myself thinking more about how I feel that bloated, monolithic and extremely complex software is DEAD.

 

Think about it for a minute, the industry is littered with monolithic and extremely complex software products. It comes in the form of feature bloat (you frequently hear that only 20% of the features of a software product are truely valuable to a majority of the user), installation (how long is it really going to take this piece of software to install, how many disks, how many pieces need I need to install before I install this software onto the stack), user experience (dealing with 1000’s of dialog boxes, menu items, text boxes, drop down lists, “where the heck am I in this software”, etc).

 

This is just the tip of the proverbial iceberg as you know. I can sense you are shaking your head up and down as you have probably been a part of building such a solution. In addition, the industry has had a propensity to cobble  various software products together into “Suites” further exasperating the compexities of the solution. This cobbling together approach causes all kinds of additional bloat and complexities for weaving together the various products which in turn extends the release cycle from months to years. Then, you go from Suite to Platform. “We are no longer selling a “Suite” of products, we are offering you a “Platform”“. Yes, many companies have created compelling “platforms” and in a sense you can see how this has evolved. But, when taking a platform approach, some users start to object as they just don’t want to deal with all this complexity and feature boat that sometimes comes with monolithic and extremely complex software. In fact, when you add things like security, redundancy, storage, the operating system, etc underneath and on top of the platform, you get even more monolithic, bloated and extreme complexity.

 

So, what happens next,  users begin to retract, they begin to look around for more simple, flexible and adaptable solutions that truely address the direct need.  You hear users saying “All I want to do is X, I don’t need all this other stuff” and as much as the sales reps try to showcase the value of the platform, the user just migh say that they want X and stick to it, X representing the task at hand and the solution they need to get going on.  Furthermore, you might hear “Can I just buy the X feature from you”. But wait, even the X feature may be monolithic and complex, so the user / buyer continues their  search for a focused solution that is simple and gets the job done. This could come in the form of an appliance version of a focused product, a low cost open source solution or even as a SaaS solution in today’s world. You and I know this very well. Never mind the perpetual license discussion that could be brought to bear in this topic area.

 

My sense at this junction is that monolithic and extremely complex software platforms or even database solutions  will eventually die and fade and that they will quietly and quickly be displaced by emerging offerings that are simple and rapidly made available to address the requirements of the users without them having to add machine after machine just to scale. Its time to spend more time on making things simpler but with ability to support the needs of the user whether a small shop or a big shop. That’s one of the areas we are very focused on here at Infobright, making data warehousing easy yet reliable and scalable without adding significant bloat and complexities. In the business intelligence space,  a good example of simplicity is illustrated by Lyzasoft, if you haven’t checked them out, I would encourage you to give it a try. A good illustration of simplicity without complexity.

 



Source: Planet MySQL

[NewsBot]

XtraDB storage engine release 1.0.3-6
20 July 2009, 4:44 pm

Dear community,

Today we are pleased to announce release 6 of XtraDB – the result of 2 months hard work.

The release includes following new features:

MySQL 5.1.36 as a base release

New patch  innodb_recovery_patches.patch

Experimental adaptive checkpoint method estimate

innodb_stats – the implementation of the fix forMySQL Bug#30423

expand-import Support of import InnoDB / XtraDB tables from another server

split-bufpool-mutex-3 New patch to split buffer pool mutex

g-style-io-thread Google’s fixes to InnoDB IO threads

dict-size-limit Limit of internal data dictionary

Fixed bugs:

MySQL Bugs: #39793: Foreign keys not constructed when column has a ‘#’ in a comment or default value

Bug #395784 in Percona-XtraDB: “main.innodb_xtradb_bug317074 internal check fails on 5.1.36”

Bug #395778 in Percona-XtraDB: “main.innodb-analyze internal check failed on 5.1.36”

Bug #391189 in Percona-XtraDB: “main.innodb_bug36172 mysq test fails with innodb_stat.patch applied”

Bug #388884 in Percona-XtraDB: “patching fails on 5.1.35”

The builds for RedHat4,5 and Debian are located on http://www.percona.com/mysql/xtradb/5.1.36-6/

The latest source code of XtraDB, including development branch you can find on LaunchPAD.

Please report any bugs found on Bugs in Percona XtraDB Storage Engine for MySQL.

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

Entry posted by Aleksandr Kuzminsky |

No comment

Add to: | | | |



Source: Planet MySQL