sorted by: recent | see : popular

Content Tagged with databases + professional

Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.

The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.

# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET insert_id=6281;
INSERT INTO mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_created_by,
 comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log,
comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by,
comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible)
VALUES (...)

The impact here, is that SELECT statements to the mt_comment table are also blocked because this table is in MyISAM. It was reviewing slow running SELECT statements that the cause of the slow inserts was easily determined.

mysql> explain SELECT comment_id
    -> FROM mt_comment
    -> WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (comment_entry_id = '276')
    -> ORDER BY comment_created_on DESC;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt_comment
         type: ref
possible_keys: mt_comment_visible,mt_comment_entry_id,mt_comment_blog_id,mt_comment_blog_stat,mt_comment_visible_date,mt_comment_entry_visible,mt_comment_blog_visible,mt_comment_blog_ip_date,mt_comment_blog_url
          key: mt_comment_entry_visible
      key_len: 6
          ref: const,const
         rows: 99
        Extra: Using where
1 row in set (0.00 sec)

CREATE TABLE `mt_comment` (
  `comment_id` int(11) NOT NULL auto_increment,
  `comment_author` varchar(100) default NULL,
  `comment_blog_id` int(11) NOT NULL default '0',
  `comment_commenter_id` int(11) default NULL,
  `comment_created_by` int(11) default NULL,
  `comment_created_on` datetime default NULL,
  `comment_email` varchar(75) default NULL,
  `comment_entry_id` int(11) NOT NULL default '0',
  `comment_ip` varchar(16) default NULL,
  `comment_junk_log` mediumtext,
  `comment_junk_score` float default NULL,
  `comment_junk_status` smallint(6) default '0',
  `comment_last_moved_on` datetime NOT NULL default '2000-01-01 00:00:00',
  `comment_modified_by` int(11) default NULL,
  `comment_modified_on` datetime default NULL,
  `comment_parent_id` int(11) default NULL,
  `comment_text` mediumtext,
  `comment_url` varchar(255) default NULL,
  `comment_visible` tinyint(4) default NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `mt_comment_commenter_id` (`comment_commenter_id`),
  KEY `mt_comment_visible` (`comment_visible`),
  KEY `mt_comment_junk_score` (`comment_junk_score`),
  KEY `mt_comment_ip` (`comment_ip`),
  KEY `mt_comment_parent_id` (`comment_parent_id`),
  KEY `mt_comment_entry_id` (`comment_entry_id`),
  KEY `mt_comment_email` (`comment_email`),
  KEY `mt_comment_last_moved_on` (`comment_last_moved_on`),
  KEY `mt_comment_created_on` (`comment_created_on`),
  KEY `mt_comment_junk_status` (`comment_junk_status`),
  KEY `mt_comment_blog_id` (`comment_blog_id`),
  KEY `mt_comment_blog_stat` (`comment_blog_id`,`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_visible_date` (`comment_visible`,`comment_created_on`),
  KEY `mt_comment_entry_visible` (`comment_entry_id`,`comment_visible`,`comment_created_on`),
  KEY `mt_comment_blog_visible` (`comment_blog_id`,`comment_visible`,`comment_created_on`,`comment_id`),
  KEY `mt_comment_blog_ip_date` (`comment_blog_id`,`comment_ip`,`comment_created_on`),
  KEY `mt_comment_junk_date` (`comment_junk_status`,`comment_created_on`),
  KEY `mt_comment_blog_url` (`comment_blog_id`,`comment_visible`,`comment_url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

As you can see, the table has 18 indexes. This means that for every row inserted, 18 separate index inserts are required.

When adding an Index to a table, first determine the usage patterns that will use the index, consolidating indexes when possible and removing obvious duplicates (in the above example, the single column comment_blog_id is a classic duplicate index).

Adding an index will generally help SELECT performance, depending on cardinality, but will always impact INSERT,UPDATE and DELETE performance.
Another down side of too many indexes is the MySQL optimizer has much more work to do to eliminate beneficial indexes for every Query Execution Plan (QEP) that is undertaken.

Indeed I have seen worse, in one case a table with ~120 columns, move then 20 single column indexes AND a 3 part primary key summing 40 bytes in InnoDB. The impact was terrible for performance, with the Index size being 3x times the data size.

---

About the Author

Ronald Bradford, Principal of 42SQL provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of specialized consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.

Pro SQL Server 2008 Relational Database Design and Implementation Pro Learn effective and scalable database design techniques in a SQL Server environment. Pro SQL Server 2008 Relational Database Design and Implementation covers everyt...

I’ve had to accept the “practice what you preach” pill recently due to a disaster at my hosting provider. See Learning from a Disaster.

While it was my own personal site on a dedicated server in question and not a business generating review I found that my MySQL Backup Strategy was incomplete ( It is also based on code 4 years old). I found that I had not tested my Disaster Recovery Plan adequately. I have used my backup and recovery approach in the past for various controlled situations and testing successfully.

So what mistakes did I make. There were two.

1. I was using a cold backup approach. That is specifically copying the entire MySQL Database in a controlled manner at the file system level. These were also copied to an alternate shared hosting server for storage. This works fine when you backup server supports a means of restoring data in this format, however if your backup shared hosting facility does not give you access to the MySQL data area, then this does not work. Not wanting to pay for two dedicated hosts this backup solution is impractical for my present hosting. Time to consider alternatives, such as being prepared with an EC2 image.

2. Recently I moved to using two MySQL instances, both 5.0 GA and 5.1 RC. The problem is I didn’t adjust my backup scripts appropriately to reflect two instances. Of course when my server was unavailable for 43 hours I was completely screwed in at least I could only throw an Site Unavailable page rather then my website. Combined with my hosting provider totally screwing DNS and admin access to manage DNS for 3-4 days more then almost 2 days of total server unavailability I also had to change support for my DNS. Would have been easier if I had a DNS DR plan.

The lesson is, no matter how trivial the information or website, if you actually want the content, make sure you test your recovery strategy ahead of a disaster.

For those that have my existing blog bookmarked, or use any RSS/Atom feeds please update your information now.

My new blog can be found at http://ronaldbradford.com/blog/

RSS 2 is http://ronaldbradford.com/blog/feed/rss2, Atom is http://ronaldbradford.com/blog/feed/atom

Ok, so before you read on please change now. While all old links will redirect I encourage you to take a few moments in your browser and RSS reader.

So what’s new. Well as Baron stated in I moved this blog to pairLite with zero downtime, and it was easy I moved my old blog site blog.arabx.com.au and my temporary staging site ronaldbradford.com and transitioning my passworded beta site all with no downtime. Even with all my testing over several weeks, and several test users, my production migration had one hitch with some internal url re-directs not working correctly, but my overall site main links were not affected. You would have had to been quick at 1:30am on Saturday to catch it.

Other features is a consolidation of information from various sites now all under my brand ronaldbradford.com and allowing me now to expand on that. I’ve also split my professional blog from my personal views, photos and comments as well as new SEO/SEM optimizations. More on these experiences later. My theme was actually inspired by some business cards I had made in Australia while home last week, quality 300GSM color cards that were designed and printed in a just a few hours and are very good quality (which was very impressive).

I’m now free to work on so many other projects including many MySQL draft posts now Version 1.0 of my new site is out. As with all release early, release often sites, expect 1.1 soon!

I’m migrating my existing WordPress run blog site at blog.arabx.com.au to a my new site ronaldbradford.com (which is not yet publically available)

As part of this process I’ll be doing a number of upgrades/changes including:

1. Update blog software to 2.5.1 from 2.0.2 (I’d previously done a 2.0.2 upgrade to 2.3.2, but not deployed)
2. Migrate to new domain
3. Upgrade existing MySQL 5.1 version from 5.1.11 to 5.1.24
4. Migrate database to using MySQL 5.1.24, from 5.0.22 (my server runs 5.0 and 5.1 instances)
5. Split my blog into Professional & Personal

Upgrading
The upgrade is straightforward, backup database, download latest wordpress software. I run full revision to older versions via directories + symlinks so my installation is more complicated, but fully recoverable. Install, and run upgrade script. That all works, but my site breaks. Suspecting is my heavily customized them, by disabling that my site is up. One to add to the TODO list.

Migrate to new domain
Dump + Reload data into new schema. Copy Wordpress install. I had to make two data changes to correctly use the new domain.


update wp_options set option_value='http://ronaldbradford.com/blog' where option_name='siteurl';
update wp_options set option_value='http://ronaldbradford.com/blog' where option_name='home';


Upgrade MySQL 5.1 version
That was also relatively straightforward. I was surprised I was running such an old 5.1.11, but I remember originally using 5.1.6 in production use before that.

Migrate database to 5.1 from 5.0
This is where my problems have begun. Wordpress does not appear to like host+port stuff. I can confirm access via MySQL client. At 9:30pm Sunday night, this may have to remain in the unresolved bin for a few days.

In review of some list posts today, I came across the Falcon Preview 6.0.5 downloads available from the MySQL Forge (even that is unclear, but the directory indicates this on the forge). The Forge Wiki Documentation indicates the 6.0.5 release features (without a download link), however the official MySQL downloads for 6.0, and the directory structure for all MySQL releases only describes 6.0.4.

Nothing in the MySQL News and Events of this new release. The documentation for 6.0.5 is also unclear as it lists this version as Not Yet Released.

This comes on the heels of “Pending General Availability/near-final release candidate” (See here and here), and the Press Releases for Sun Celebrates Third-Party MySQL Storage Engines only to be negated by Sun Microsystems Announces MySQL 5.1 which broke the plugins as they were not compatible.

I don’t wish to create confusion, but it’s unclear what is happening here, not withstanding the new preview only has 5 builds and excludes Mac OS/X which means I can’t natively test.

Jay’s opening lines regarding the final MySQL Conference keynote speaker was: “I work with a lot of data. I think peta-bytes, maybe exa-bytes”. This was relating to Jacek Becla from the Stanford Linear Accelerator Center, giving his presentation on “The Science and Fiction of Petascale Analytics”.

The goal of the Large Synoptics Survey Telescope (LSST) is the storage of 50+ PB of images and 20+ PB data.
Let’s just clarify the size. 20 PB of data = 20 years of HD Movies = 2000 years of 128kb MP3

The next database frontier is obviously building huge databases. What part will MySQL or other relational databases play? Some interesting facts were.

• The Digital Universe Created 161 Exabytes of data last year.
• Google, processes 20 petabytes of data per day.

The Operational plan for LSST Project Timeline is 10 years, only starting in 2014. The timeline:

• 2009 Choosing Technology
• 2010-2014 constructions
• 2014-2023 production

The primary goals are: Scale, parallelize, fault tolerant.

A small but committed group met at 8:30pm to hear more about our the plans from PrimeBase Technologies here at the 2008 MySQL Conference. Our discussion started in true MySQL form.

Monty Widenius presents to the group plastic cups and a bottle of Absolut Vodka.
After a shot, Paul starts with “While I can still talk”.
Monty, slams another bottle of Vodka on the table.
We all laugh.

Paul outlined some of the roadmap plans from existing the Alpha release to Beta releases.
We talked about our plans for Synchronous Replication and there was active discussion on various use cases.
There was also discussion and input on Solid State Drive (SDD) Technology which we will be testing with PBXT in the coming months.

A: Maria, an ACID, MVCC engine that plans to be the default non-transactional and default transactional engine for MySQL.

Presently development with a team of 6 people and plans of adding 2-3 developers the work on Maria should see the 1.5 release this month.

It was great to here Monty say “We have a policy of zero MySQL Bugs, like the old MySQL way.”

Maria Version History
1.0 - “Crash Safe” — part of a existing 5.1 branch
1.5 - “Concurrent insert/select” to be merged as part of formal MySQL 6.0 release
2.0 - Transactional and ACID
3.0 - High Concurrency & Online Backup
4.0 - Data Warehousing

The schedule has all of the features to be available for the next MySQL Conference Q2 2009

Some points of note:

• This is a MyISAM replacement.
• It was interesting to hear about log file size (suggesting being big like 1G), and there are not circular. New log files will be created, and only files purged only when no longer used.
• There has been a change in default page size, presently defaulting to 8K for both data and indexes.
• Maria 1.5 does not support INSERT DELAYED and FullText and GIS indexes are not crash safe.
• There are extensive tests in the MySQL Test Suite
• Will support READ COMMITTED and REPEATABLE READ (available in 1.5)
• Every part of the development and process is open an available documentation (unlike some other storage engines Monty mentions)
• Have a drop everything policy on new bugs to have Maria as stable as possible.
• Check out the blog at monty-says.blogspot.com

Also in the MySQL Press Releases today but dated for tomorrow is Sun Microsystems Announces MySQL 5.1.

I find the wording clearly a new language from my previous understanding — “pending general availability of MySQL? 5.1″.

We now see the trademark notice, obviously a Sun influence.
We now have a “pending” GA version. MySQL is obviously very keen to release MySQL 5.1 for GA. This was expected at last years’ MySQL conference. Many in the past year have expected this prior to this year’s conference (we are now being informed late Q2 2008). There was an anticipation there would be two RC versions, this is now the third. So what exactly does “pending” mean? Will 5.1.24 be renamed Production if it passes community acceptance (I say community because it’s not an Enterprise release). This would be a change from previous naming policy. What’s most likely is hopefully they release 5.1.25 as production. Comes back then to why the words “pending general availability”, and not “next release candidate” which is what it is.

Previously MySQL also made new with the initial RC status of 5.1, moving away from the previously policy.

In today’s Official MySQL Press Release, PrimeBase XT (PBXT) has been named along with three other storage engine partners in “Sun Celebrates Third-Party MySQL Storage Engines“.

This a great achievement for us to be recognized in the certified storage engine partner program, in comparison to the other companies that are much larger on the balance sheet. This continues the news this week, last week you may have read PrimeBase Technologies a MySQL Platinum Level Partner.

A noted absence from the list is Nitro, an expected absence was Solid.

Marten has opened the 2008 MySQL Conference & Expo. This time he started in his opening comments “I have more to say to more people, and given less time to say it”.

His answer to why Sun bought MySQL included slides showing “Alignment in Culture and Vision” and “What’s in it for you - Performance & Scale, Support, Marketplace”.

This year the MySQL Conference has over 2,000 people and 55 exhibitors.

What was funny, was the photo showing the burning of the IPO Prospectus. Marten mentioned now with many Sun lawyers he has to be more careful what to day. I actually have an interesting extension to this at Watching what you say

Some points of note for me:

• The Web Economy continues to have exponential growth and the need for new technology but a goal of linear growth.
• Continuing to mention becoming “Disruptive Online Innovations”
• The Online landscape consists of the Software Development Model, Business Model,Software Deployment Model and Organisational Model.
• He Reiterated the Design Priorities at MySQL. Reliability, performance, easy of Use
• MySQL Workbench is now GA. Congratulations Michael Zinner.
• On Storage Engine Update, we as in PrimeBase, PBXT and Paul McCullagh got our 20 seconds of mention as predicated. In addition to he usual suspects, but ScaleDD & Tokutek are newly mentioned and are Exhibitors this year.

As per the Platinum Partners List we have made a key achievement here at PrimeBase Technologies. I think this is a great list to make for you. There are only 18 Platinum partners, and we are the third Storage Engine provider and the first open source storage engine company.

Our News Release was where I got to make my first published quote for the company. “As a key milestone in our relationship with both the MySQL Community and MySQL, this partnership strengthens our commitment and involvement in working with the MySQL database server starting with PBXT and following with additional product developments.” said Ronald Bradford, COO of PrimeBase Technologies.

The official press release of my commencement at PrimeBase Technologies can be now found at
Ronald Bradford strengthens management team and new open source initiative.

I’m very excited to be the COO, that’s Chief Operating Officer, rather impressive if you say it out loud a few times. My close friends and MySQL colleagues in the know in New York were also most impressed I’ve made CxO status during celebrations in recent previous weeks. My title was under wraps until I’d started.

What does COO mean at PrimeBase Technologies? I’m being charged with a number of things. I will responsible for the new Business Solutions division which includes helping to define the product road map for PrimeBase XT (PBXT) and Media Blob Streaming storage engines for MySQL. I’ll be looking at product enhancements, quality control and testing and verification of these and new products. I will also have a special focus on promoting PrimeBase Technologies to the MySQL community and commercial marketplace, hoping to leverage of the good start at the MySQL Community Awards 2007 where Paul McCullagh was named “Community Code Contributor of the Year”.

The continued success of our products including PBXT and Media Blob Streaming relies greatly on the community with your input of what is needed to help us best serve your needs always welcome.

We will be Exhibiting at the 2008 MySQL Conference & Expo, Paul’s has presentations at the MySQL Conference on An Introduction to BLOB Streaming for MySQL Projectand Inside the PBXT Storage Engine and I have a presentation The top 20 design tips every data architect should know..

We are also planning a BoF session on Tuesday night.

It’s been six weeks since the completion of the acquisition of MySQL by Sun Microsystems. In this time, on the MySQL News and Press Releases there has only been one (1) Press Release here.
In the six weeks between the announcement and completion of the acquisition MySQL made ten (10) Press Releases, and the six weeks preceding this including the Christmas break there was (six) Press Releases.

On the Sun Press Releases in this period there has been twenty three (23), but just the same one MySQL specific.

The MySQL Developer Zone has picked up three articles in the past week, but nothing prior to that for a month.

So, Where’s the News of what’s happening with MySQL?

It seems our only options are the official company blog of Kaj Arno and his travels , Robin Schumacher and Giuseppe Maxia as other sources of information.

There has been some discussion recently regarding the death of MySQL Read Replication starting with Brian Aker and then Farhan, Arjen and Paul have all chimed in. Whatever you want to call it, the next generation of replication approach is clearly on the agenda of the industry leaders and pack followers. We should take a programmatic look however and ask ourselves a few questions. Such as:

1. The Use — What is/was MySQL Replication used for?
2. The Reasons — Why was it used?
3. The Problems — Why is there a need for something better, different or improved?

Read Scalability

1. The Use — Clearly the most commonly known method of MySQL Replication is for Read Scalability. The ability for many more reads to occur against the data then writes. I call it the YouTube effect, thousands/millions of reads (i.e. view a video) to a single write (i.e. upload of a video)
2. The Reasons — Database servers could not support the read/write load. Applications were poorly designed for sharding data. The database was being used for a function when it wasn’t really needed. The MySQL Server can’t scale to a massive number of cores.
3. The Problems — MySQL Replication suffers from the asynchronous condition. A successful transaction on the Master, is not confirmed with a Slave before acknowledgment to the client. Google has made inroads in their own environment to improve the asynchronous nature with the SemiSyncReplication patch, now submitted back to the community. The number one effect of asynchronous replication is lag, and again poorly developed applications simply can’t support successful scale-out. Other problems include locking contention when not using a transactional engine.

Backups

1. The Use — A Replication slave is used to perform a database backup, designed to not affect production usage.
2. The Reasons — MySQL does not support a single “unbreakable” online backup solution. Period. There are partial/incomplete implementations, dependent on storage engines and data volume. There is a Roadmap for a solution in MySQL 6.0 (finally), but it’s way too early to tell what features will there be, will you need to pay for some of them, will it just work, let alone who is going to upgrade to 6.0 for this.
3. The Problems — There exists no consistent method for backup across all storage engines. There is no compatibility for backup/point in time recovery (e.g. binary logs/redo logs) across different master/slave servers. No mirrored binary logs is also a problem, something also in the Google Patch.

Failover

1. The Use — You need Business Continuity or Revenue Continuity Solutions (RCS) as discussed by Marc Simony. You need to ensure the maximum uptime possible with a failover infrastructure when some component (software or hardware) fails and your production database is not accessible.
2. The Reasons — MySQL provides no Online DDL. In a 24×7 large volume, changing functionality application this provides an unnecessary complexity for applying changes easily.
3. The Problems — When correctly configured and applied in a controlled situation, failover can work, provided your data is not too large, and your traffic is not too much. It simply doesn’t have a guarantee to work without data loss in an uncontrolled (i.e. disaster recovery) situation.

Others

There are more reasons, something to write about at a later time. These would include.

• Testing
• Alternative data access path (e.g. Data Warehousing)
• Upgrade Path/Migration Path

Options

To handle Read Scalability. Why select something from the database (or various level of caches in the database call when it can be managed by the Application). Memcache is the obvious buzz word here. With tighter database integration (such as the patches and commands FaceBook has) this could be a winner.

Backups is a tough one, without practical storage engine agnostic consistent solutions I don’t feel the backup conundrum will be resolved anytime soon.

To handle a successful failover you need a guarantee that the data is consistent. A Synchronous Replication solution will solve this. MySQL Cluster is a synchronous solution, however it is impractical for basically every application that is already running with MySQL Replication.

What other options exist? Food for thought.

About Memcache

A little on Memcache for those still in the dark ages. Many large sites such as FaceBook,Fotolog, Wikipedia and Slashdot are strong proponents of Memcached. Created originally for Live Journal Memcached has become the defacto standard in this level of application data caching.

The description from the source.

memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.

There is a Linked In group I created some time ago but forgot to advertise that is for MySQL Speakers and Presenters.

If you a speaker or presenter of MySQL content, confirm your registration here.
You will need to have a reference to a website confirming you have been a speaker at a MySQL Event such as a User Conference, MySQL Camp or Local MySQL Users Group.

Hopefully overtime we can build a consolidated index at MySQL Forge Presently some pages exist including MySQL Conference & Expo and User Group Presentations but I’m accepting input for a model to have a central page, and link to or upload of presentation. Any input welcome.

Tonight’s Hamburg MySQL Meetup was a packed room of 60-70 people at Sun in Hamburg. The most I’ve seen @ a MySQL Meetup across three cities, countries, continents and years (my first Oct 5, 2004).

As expected most was in German. Pity, I would have liked to hear about Open Office and MySQL. Giuseppe, CCO (That’s Chief Cartoon Officer - I liked that one) gave in English a general review of MySQL. He highlighted the reasons “Why did Sun by MySQL?” including most popular, most dynamic and people with the Freedom to work anywhere.

Three points I would like to re-iterate for the community.

The MySQL Forge contains the Worklogs, i.e. what is going to be developed in current & future versions and the status of tasks in next version, say in review, in development, completed development etc. This is important because this gives exposure to the world what MySQL are indeed working on.
• The MySQL University each Thursday gives a free session that includes from an expert audio and visual slides as well as the ability to ask questions via IRC. I’ve attended one of these before and I’d like to see this continue to improve and grow.
• MySQL is open and values external contributions from the community. My new co-worker Paul creator of PBXT highlights this. I hope with additional time within the MySQL Community I can help to contribute and lobby for greater differentiation of MySQL Community and MySQL Enterprise in favor of the Community.

Giuseppe did however not talk about the MySQL SandBox as scheduled. I was even prepared having wanted to use his software previously. I’d downloaded and was all ready. I did manage to get a one-on-one lesson which I found most useful, and a few points of enhancements to consider.

I didn’t know there were so many MySQL people in the area, the following attending tonight. Lenz, Giuseppe, Kai V, Kay, Jan, Ulf W and Georg whom I’d not met before. Most I’ll see next week at the MySQL Users Conference.