Dataprix

View more Databases news and analysis from Computerworld.com

In the previous installment of our series covering the most relevant features of SQL Server 2005 Express Edition, we started an overview of Service Broker full dialog-level security. In this article, we will conclude this subject by describing the remaining prerequisites and a method to invoke a secure dialog.

Join MSAS Architect Bill Pearson as he leads the hands-on creation of a simple bar chart based upon an Analysis Service data source.

Today we announce release 1.0.2-2 of our XtraDB storage engine.

Here is a list of enhancements:

• split-buffer_pool_mutex

The patch splits global InnoDB buffer_pool mutex into several and eliminates waitings on flush IO and mutex when there is no enough free buffers. It helps if you have performance drops when data does not fit in memory.

• innodb_extra_undoslots

InnoDB has a concurrent transaction limit of 1024 because in the standard InnoDB the number of undo slots is fixed value. This patch expands the maximum number of undo slots to 4072 and allows better utilizing modern hardware. (Thank SmugMug for this feature!)

ATTENTION: If the option was enabled and the expanded slots are used, you cannot use the datafile with system tablespace for the software which assume 1024 slots (e.g. the other mysqld or innodb hotbackup). See documentation for details.

Percona XtraDB 1.0.2-2 available :

• in source code from Launchpad, the version 1.0.2-2 you can get as bzr branch lp:percona-xtradb -r tag:release-1.0.2-2 percona-xtradb-1.0.2-2
• as source code in tar.gz
• as binaries, percona xtradb is compiled in into MySQL-5.1.30 RPM
• as separated shared library, to use as drop-in plugin for existing MySQL-5.1.30 installation

XtraDB is compatible with existing InnoDB tables (Warning: unless you used innodb_extra_undoslots ) and we are going to keep compatibility in further releases. We are open for features requests for new engine and ready to accept community patches. You can monitor Percona’s current tasks and further plans on the Percona XtraDB Launchpad project. You can also request features and report bugs there. Also we have setup two maillists for General discussions and for Development related questions.

Entry posted by EvgenyS | No comment

Add to: | | | |

One of our customers gave me a chance to run some benchmarks on 24-core (intel cpu based) server, and I could not miss it and ran few CPU-bound tasks there.

The goal of benchmarks was investigation of InnoDB-plugin and XtraDB scalability in CPU-bound load.

CPU specification:

PLAIN TEXT CODE:

1. processor       : 23
2. vendor_id       : GenuineIntel
3. cpu family      : 6
4. model           : 29
5. model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
6. stepping        : 1
7. cpu MHz         : 2394.011
8. cache size      : 12288 KB
9. physical id     : 3
10. siblings        : 6
11. core id         : 5
12. cpu cores       : 6
13. fpu             : yes
14. fpu_exception   : yes
15. cpuid level     : 11
16. wp              : yes
17. flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
18. bogomips        : 4788.59
19. clflush size    : 64
20. cache_alignment : 64
21. address sizes   : 40 bits physical, 48 bits virtual
22. power management:

I tested MySQL-5.1.30 with InnoDB plugin, Xtradb-1.0.2-1, and XtraDB-1.0.2-2 (rel2). XtraDB-rel2 has not been released yet, we still are doing internal QA for, but it will be ready soon. Main difference XtraDB-rel2 it contains additional scalability fixes for buffer_pool (split_buffer_pool_mutex patch).

my.cnf is

PLAIN TEXT CODE:

1. [mysqld]
2. user=root
3.  
4. binlog_cache_size=1M
5. default_table_type=MYISAM
6. ft_min_word_len=4
7.  
8. innodb_additional_mem_pool_size=16M
9. innodb_buffer_pool_size=15G
10. innodb_data_file_path=ibdata1:10M:autoextend
11. innodb_file_per_table=1
12. innodb_flush_log_at_trx_commit=2
13. innodb_log_buffer_size=8M
14. innodb_log_files_in_group=2
15. innodb_log_file_size=512M
16. innodb_status_file=0
17. innodb_thread_concurrency=0
18.  
19. innodb_io_capacity=1000
20. innodb_write_io_threads = 16
21. innodb_read_io_threads = 16
22.  
23.  
24. join_buffer_size=1M
25. max_allowed_packet=1M
26. max_connections=3000
27. max_heap_table_size=64M
28. max_prepared_stmt_count=1000000
29. query_cache_size=0
30. skip_grant_tables
31. skip_locking
32. sort_buffer_size=64K
33. table_cache=2048
34. thread_cache_size=16
35. thread_concurrency=16
36. thread_stack=196K
37. tmp_table_size=64M
38. transaction_isolation=REPEATABLE-READ
39. local-infile=1

At first I tried sysbench oltp read-only with 10mil rows (the datasize is about 2.5GB), uniform distribution.
The results you can see there: (Results are removed for additional checking)

In the next run I tested sysbench oltp read-write load, and the results are:

Here starting 16 threads the result is dropping down with the same speed as it grew, and with 128 connections we have the same TPS as with 1 connection (and it is on 24-cores box!). XtraDB is slightly better there than InnoDB, but nothing special to be proud of. We definitely we will look how to fix it as next step, read detailed investigation what is the reason of performance drop in next post.

And last one workload I tried is TPCC-like benchmark (you can get it on https://launchpad.net/perconatools), with 100 Warehouses (about 9.5GB datasize).
The result is:

Here the result grows up to 16 connections, but after that InnoDB-plugin is dropping down. XtraDB and XtraDB-rel2 seem quite better, I guess this is mostly because fixes to rw_locks and to buffer_pool mutex (in rel2).

Conclusion: As read-only workload seems fine, read-write cases is something to worry about in 16+ cores boxes.
Intel Based 24 Core Servers are not mainstream these days but as number of cores is increasing now at the same pace as CPU frequency before we believe they are very soon to come. Also in real production there may be not a lot database fits "in-memory" cases - but on other hand 64-128GB RAM per box is not something rare already and recommendation to fit at least active dataset in memory is one we use for our customers.

Entry posted by Vadim | 3 comments

Add to: | | | |

Oracle Database 10g offered two new Flashback features that allowed an authorized user to see all versions of any changes made to one or more rows in a table. Database 11gR1 provides the ability to back out one or more independent or dependent transactions with Flashback Transaction Backout.

Comparing the performance of SQL Server 2005 to SQL Server 2000 demonstrated an improvement of 18.5% in the time to process a workload. Now that SQL Server 2008 has shipped, it's time to compare the performance of SQL Server 2005 to SQL Server 2008.

How well do you know the sorting rules of your own language? Chances are, Oracle knows them much better than you, in addition to knowing all the rules for all of the languages Oracle supports. Take a tour through Oracle's multilingual linguistic searching and sorting features.

The company wouldn't say whether it's had a patch in hand since September, as one security researcher charges.

As you may know Sun / MySQL made release of community 5.0.75 only as source code release. We made binaries with our patchset.
Patches are mostly equal to build 10
http://www.mysqlperformanceblog.com/2008/12/11/mysql-binaries-percona-build10/, only change:
it includes bugfix for https://bugs.launchpad.net/percona-patches/+bug/308849

You can download binaries (RPMS x86_64) and sources with patches here
http://www.percona.com/mysql/5.0.75-b11/

Entry posted by Vadim | 5 comments

Add to: | | | |

Paul Zikopoulos introduces you to the Data Web Services Test Client that's available in IBM Data Studio Version 1.2 or later

Join BI Architect Bill Pearson as he introduces Attribute Relationships into his extended examination of the dimensional model within the integrated Microsoft Business Intelligence solution.

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they're generally some kind of loggable event.

The next characteristic of a lot of these systems (real or planned) is the desire for "real-time" analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types of slicing and dicing.

And as a result, one of the most common questions we hear is how to build high-performance systems to do this work. Let's see some ways you can build the functionality you need and get the performance you need. Because I've built two such systems to manage online ads through Google Adwords, Yahoo, MSN and others, it's easy and familiar for me to use the example of search engine marketing. I'll do that throughout this article.

Requirements

The words "need" and "want" are different.  Do you really need atomic-level data?  Do you really need real-time reporting?  If you do, the problem is much more expensive to solve.

Start with the granularity of your data.  What data do you need to make your business run?  If you can't get access to the time of day of every click on every ad, will it hamper your ability to measure the ad's value?  Is it enough to know how many times the ad was clicked each day?  If so, you can roll all those events up into a per-day table.

Next, let's look at "real-time."  None of the big three (Google, Yahoo, MSN) provides real-time reporting last time I was involved with them (and I suspect this is still true).  It's too expensive.  Consider your user expectations.  For most applications I've been involved with, having day-old data is adequate, and users don't expect realtime.  The trick here is that when you start out, realtime is possible because your data is small.  "Hey, we do realtime reporting.  Google doesn't even do that!  We're better!" Then you get popular :)  And if you've promoted your better-ness in the meantime, you might have to do some awkward backpedaling with customers, who now expect realtime data.  The database giveth, and the database taketh away.

Finally, you should think a lot about how you need to query the data.  It is a hard question to answer, and sometimes I've seen it evolve over time, especially as the growing data size forces it to.  This goes back to what data you really need to make your business run.  Anything else is gravy.  If there are nice-to-haves, consider not building them in.  Listen to some talks by 37Signals if you need inspiration to toss things out.  Define the types of queries you absolutely have to have, if possible, and note the ways and types of aggregation (by-ad by-day, for example).

Sometimes I ask a customer "what kinds of queries do you have to run?" and they say "we can't decide, so we want to just store everything." If you can't decide yet, then don't store everything in the database. Instead, store the source data in some fashion that you can reload later, such as flat files, and build support in the database for one or two capabilities you absolutely need now; then add the rest later, reloading the data if needed.

Aggregate

Aggregation is absolutely key for most people.  There are special cases, and there are ways to do general-purpose work without aggregating (see the section below on technologies), but if you're doing this with vanilla MySQL, you will need to aggregate your data.

What you want to do is aggregate in ways that optimize the most expensive things you'll do.  And then, you might super-aggregate too.  For example, if you aggregate by day and then you do a lot of queries over 365-day ranges for year-over-year analysis, aggregate again by month.  Then write your queries to use the most aggregated data possible to save work.

Avoid operations that update huge chunks of aggregated data at once.  Among other things, you'll make replication lag badly.  More about this later.

Another way to say "aggregate" is to say "pre-compute." If you have time-critical queries for your app to do its work, can you do the work ahead of time so it's ready to get when needed? This might or might not be aggregation.

Denormalize

Pre-computing and careful denormalization need to go together.  Figure out what other types of data you'll need in those aggregate tables, and include columns to support these queries. But beware of denormalizing with character data; try to make your rows fixed-length.

One reason denormalization is important is that nested-loop joins on large data sets are very expensive.  If MySQL supported sort-merge or hash joins, you'd have other possibilities, but it doesn't, so you want to build your aggregate tables to avoid joins.

Watch Data Types

Does your ad ID look like "8a4dabde-1c82-102c-ab13-0019b984eacd" and is it stored in a VARCHAR(36)?  When tables get big, every byte matters a lot.  Use the smallest data types you can, the simplest character sets you can, and watch out for NULLable columns.  Use smallint unsigned or tinyint unsigned if you can.  You can save very large amounts of space.  Choose primary keys very carefully, especially with InnoDB tables -- don't use GUIDs.  Which brings me to my next point:

Use InnoDB

Assuming that you will use the stock MySQL server, InnoDB is usually your best bet. (Actually, XtraDB might be very interesting for you, but I digress).  Due to the cost of repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables when things get big.  And even if it's read-only, there's still another reason to use InnoDB/XtraDB tables...

Optimize For I/O

It is pretty much inevitable: if you do this kind of data processing in MySQL, you're going to end up heavily I/O bound.  Listen to any of the talks at past MySQL conferences from people who have built systems like yours, and there's a fair chance they will talk about how hard they have to work on I/O capacity.

What does this have to do with InnoDB?  Data clustering. InnoDB's primary keys define the physical order rows are stored in.  That lets you choose which rows are stored close to each other, which is very beneficial in many cases.  Especially on huge tables, it lets you scan portions of a table instead of the whole table if you a) choose your aggregation to match the order of your common queries and b) choose your primary key correctly.

Let's go back to the ad-by-day table.  If you query date ranges most of the time, you should define the primary key as (day, ad).  Don't use an auto-increment primary key, and don't put ad first.  If you put ad first, then you're going to scan the whole table to query for information about yesterday.  If you put day first, then yesterday will all be stored physically together (within the page -- the pages themselves may be widely separated, but that's another matter).

Don't Store Non-Aggregated Data

I've been talking a lot about aggregated data.  What do you do with the non-aggregated data?  My answer is usually simple: just don't store it in the database.  Instead, pre-aggregate.  Suppose your data is coming from some Apache log or similar source.  Write a script to rip through the file and parse it 10k lines at a time, aggregating as it goes.  When each chunk is done, make it write out a CSV file and import that with LOAD DATA INFILE.  Keep those big fat log files out of the database.  The database is usually the most expensive and hardest-to-scale component in your system -- don't waste resources.

Another benefit of this is the chance to parallelize.  As you know, MySQL doesn't do intra-query parallelization, so ETL jobs written to rely on SQL tend to get really bogged down.  In contrast, moving the processing outside the database lets you parallelize trivially.

If you need to analyze the non-aggregated data, you can store it on the filesystem and write custom scripts to do special-purpose tasks on it.  Storing a little meta-data about each file can help a lot.  Store the ranges of values for various attributes, for example; or the presence or absence of values.  You can put these into the database in a little meta-table.  Then your script can figure out which files it can ignore.  What we're doing here starts to look like a hillbilly version of Infobright, which I'll talk about later.

Alternately, you can store the atomic data as CSV files and use the CSV engine so you have an SQL interface to it (the meta-tables are still a valid approach here!).  This is an easy way to bypass the hard-to-scale database server for the initial insertion, because you can write CSV files with any programming language.  Naturally, CSV files don't store as compactly on disk as [Compressed] MyISAM or Archive.

These are just some ideas I'm throwing around -- the point is to think outside the box, even to think of things that seem "less advanced" than using a database.

Sharding and Partitioning

Sharding is inevitable if your write workload exceeds the capacity of a single server (or if you're using replication, the capacity of a single slave). Sharding can also help you avoid massive tables that are too big to maintain. If you know you'll get there, it can change the lifecycle of your application in advance.

What about partitioning in MySQL 5.1?  I know there are some cases when it can help a lot, and we've proven that with our customers.  But you still have to think about how to avoid enormous tables that are hard to maintain, back up, and restore.  And the partitioning functionality is not done yet and not fully integrated into the server, so I expect to find a lot more bugs and annoyances.  There are already inconvenient limitations on some key parts of partitioning, such as maintenance and repair commands, that essentially negate the benefits of partitioning for those operations. An finally, it doesn't save you from the downtime caused by ALTER TABLE -- a typical reason to think about master-master with failover and failback for maintenance. As with anything, it's a cost-benefit equation. What are your priorities? Choose the solution that meets them.

Be Careful With Data Integrity

When you're storing several levels of aggregation, and there's denormalization, you need to be scrupulous about data cleanliness, because it's really hard to fix things up later.  If your data is coming from a partner site, and you upload bad data there, you'll be getting bad data back for a long time.  And every time you have some incremental job to update the aggregates, you're exposed to that bad data again.

Any inconsistencies in the atomic data tend to get magnified as it gets aggregated, because you suddenly have a single row created from many rows, and if the many rows don't match completely, the single one doesn't know what data should live in it. And this only gets harder to resolve as you get more levels of aggregations.

Watch Out For The Long Tail

People talk about the long tail and how you can focus on optimizing the short head.  It's the classic 80-20 rule.  Maybe 80% of your ad impressions are on 20% of your ads!  Hooray!  But don't forget that if you're aggregating per-day, an ad that gets a million impressions takes one row, and an ad that gets one impression takes exactly the same: one row.  An impression per day becomes a fixed overhead of storage size.  So, you actually have as many rows as you have unique ads per day.  Viewed this way, suddenly you start to hate the ads that occasionally get an impression.  They're so wasteful!

It's easy to flip back and forth between viewpoints on this and get distracted into making a mistake.  Watch out when you do your capacity planning.  Don't get fooled into calculating the wrong thing.

Be Creative With Table Structures

Suppose you have some yes/no fact about an ad impression, such as whether it was a blue ad (whatever that means.)  You start out with this:

PLAIN TEXT SQL:

1. CREATE TABLE ads_by_day_by_blueness (
2.   day date NOT NULL,
3.   ad int UNSIGNED NOT NULL,
4.   is_blue tinyint UNSIGNED NOT NULL,
5.   clicks int UNSIGNED NOT NULL,
6.   impressions int UNSIGNED NOT NULL,
7. ....
8.   PRIMARY KEY(day, ad, is_blue)
9. );

What can we improve here? Especially assuming that there are indexes other than the primary key, we can shrink the primary key's width:

PLAIN TEXT SQL:

1. CREATE TABLE ads_by_day_by_blueness (
2.   day date NOT NULL,
3.   ad int UNSIGNED NOT NULL,
4.   clicks int UNSIGNED NOT NULL,
5.   impressions int UNSIGNED NOT NULL,
6.   blue_clicks int UNSIGNED NOT NULL,
7.   blue_impressions int UNSIGNED NOT NULL,
8. ....
9.   PRIMARY KEY(day, ad)
10. );

There are a couple of ways to handle this now. You can have the clicks column record the total, and the blue_clicks column record only blue clicks; to find out non-blue clicks you subtract one from the other. Or you can have the blue clicks and non-blue clicks stored, and to get the totals you add them.

Did this gain us anything? We dropped one column, and we just moved those other values around to store them "next, to in the same row" instead of "below, in the next row." So we're storing all the same data, right?

Logically, yes; physically, no. Those values that we pivoted up beside their neighbors will share a set of primary key columns. And not only will every index be a little narrower, the table will now contain only half as many rows. That will make the indexes less than half the size. In real life this technique often makes the table+index much less than half the size. You have to write a little more complex queries, but that's often justified by a large reduction in table size.

I sort of stumbled upon this idea one day. I have no idea what this technique might be called, so I call it dog-earing the table (somehow the image of putting columns next to each other makes me think of putting cards next to each other and shoving).

Archive

If you don't need data anymore, move it away or get rid of it.  I wrote a three-part article on data archiving on my own blog a while back.  The benefits of purging and archiving data can be dramatic.

Take It Easy On Replication

Building aggregated tables is hard work for the database server.  If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it'll be hard work there too, assuming you use statement-based replication.

You can save that work by either using MySQL 5.1's row-based replication, or in MySQL 5.0 and earlier, doing the work on a slave, then piping the results back up to the master with LOAD DATA INFILE, which kind of emulates row-based replication in a way.

When you're updating big aggregate tables, don't work with giant chunks of them at once.  If there's any possible way, do it in manageable bits.  A day at a time, for example.

There are a lot of other ways you can make replication faster.  I wrote a lot about this in our book, which is linked from the sidebar above.

Don't Assume Traditional Methods Will Save You

What you're really doing here is building a data warehouse.  So you may think you should use traditional DW methods, like star schemas.  The problem is that MySQL doesn't tend to perform well on a data warehousing workload.  The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick bad plans when you have a lot of joins between fact and dimension tables, and so on.  With careful tweaking, many of these things can be overcome, but how much time do you have?  And the gains are simply limited by some of MySQL's weaknesses in some cases.

Not only that, but star schemas are not intended to be fast. The star schema is essentially "I admit defeat and accept table scans as a fact of life." Table scans can be better than the alternative, if the alternatives are limited, but they're still not what you need unless you're okay with long queries that read a lot of rows -- MySQL can't handle too many of those at once.

Aside from star schemas, another tactic I see people try a lot is to build "flexible schemas" with tables that contain name-value pairs or something similar. The thought is that you can make the application believe it has a custom table, which is really constructed behind the scenes from the name-value tables in a complex query with many joins. I have never seen this approach scale well.

Use The Best Technologies You Can

MySQL is not the end-all and be-all.  If you're familiar with it and it can serve you reasonably well, it's fine to use it for things that it's not 100% optimal for.  But if the costs of doing that are going to outweigh the costs of using another solution, then look at other solutions.

One that holds promise is Infobright.  While I have not evaluated their technology in depth, I think it merits a good look.  I had the chance at OpenSQL Camp to talk to Alex Esterkin and see him present on it, and based on that exposure, I think they are doing a lot of things right.  When I know enough to have a real opinion (or when other Percona people get to it before I do!) you'll see results on this blog.

Another is Kickfire -- also something I have not had a chance to properly evaluate.  And there are others, and there will continue to be more. Finally, PostgreSQL is clearly better for some workloads out-of-the-box than MySQL is, especially for more complex queries. Percona is not tied to MySQL, although we're most famous for our knowledge about it.  When another tool is the right one, we use it.

Have you thought about using something besides a database?  You have your choice of buzzwords these days.  Hadoop is a big one.  But beware of falling into the trap of brute-forcing a solution that really needs to be solved with intelligent engineering, instead of massive resources.

Conclusion

This article has been an overview of some of the tactics I've used to successfully scale large click-processing and other types of event-analysis databases. In some cases I've been able to avoid sharding for a long time and run on many fewer disk drives with much less memory, or even with 10-15x fewer servers. Clever application design, and a holistic approach, are absolutely necessary. You can't look to the database to solve everything -- you have to give it all the help you can. Hopefully it's useful to you, too!

Entry posted by Baron Schwartz | 8 comments

Add to: | | | |

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as you would reach much sooner with focused approach.

The time is critical for many performance optimization tasks not only because of labor associated expenses but also because of the suffering - slow web site means your marketing budget is wasted, customer not completing purchases, users are leaving to competitors, all of this making the time truly critical matter.

So what can be the goal ? Generally I see there are 2 types of goals seen in practice. One is capacity goal this is when the system is generally overloaded so everything is slow, when you're just looking to see how you can get most out of your existing system, looking for consolidation or saving on infrastructure cost. If this is the goal you can perform general system performance evaluation and just fix the stuff which causes the most load on the system. MySQL Log analyzes with Mk-Log-Parser is a very good start for a ways to generally optimize MySQL load on the system.

Latency Goal is another breed. The system may not look loaded but some pages still may want to be loading much slower than you like. These goals are not system wise but they are much more specific to the different user interactions or even types of users. For example you may define goal also "Search pages have to have response time below 1 second in 95% cases and below 3 seconds in 99% cases". Note We're specific to the user interaction - people are used to Search taking longer time than other interactions for many applications, and also we speak about percentile response time rather than "all queries". It is surely good all search queries complete in one seconds but it is too not practical. The goal description may be more specific too - for example you may have different response time guidelines for pages which are requested for real humans vs search engine bots (which are often quite different in their access pattern) or you may define "large users" as users having more than 100.000 images uploaded and measure the response time for them specifically because this group has its own performance challenges.

Looking at Latency it is also much more practical to look from the top of the stack. If you look at MySQL log you may find some queries which are slow but it is hard to go back from them to what is really important for the user and so the business - the page response times. Furthermore. It is not enough in many cases to focus only on Server Side optimization - the Client Side Optimization is also quite important in particular for aggressive performance goals and fast back-end. This is why we added this service to Percona offerings.

If Server side or Client Side performance optimization is going to be more important for your application depends on the application performance a lot. The better your application is the more Client Side optimization you will need. For example if it takes you 30 seconds to generate the search results and 3 more seconds to load all style sheets images and render the page server side optimization is more important. If you have optimized things and now HTML takes 0.5 seconds to generates an extra 3 seconds become the main response time contributer which has the highest performance optimization potential.

But let us get back to the Server Side Optimization. Lets assume our performance goal applies to the HTML generation rather than full page load on the client. So meet our goal we should look at the pages which do not meet our goal, which is pages which take more than 1 second to generate in given example.

For goal driven performance optimization it is important there is enough instrumentation and production performance logging in place so you really can focus on hard data in your work. For small and medium size applications you can log all requests to MySQL table for larger ones you can log only small portion of them. I usually keep one table per day so it is easy to copy the data to a different box for data crunching and remove the old ones.

The log table should contain URL, IP and all the data you need to be able to repeat request if you need to. It may include cookie data, post data, logged in user information etc. But the real thing is number of times which are stored for request. wall clock time - is the real time it took to generate the page by server backend. CPU Time This is the CPU time needed to generate request (you can split it to user and system if you want) and when there come various wait times - mysql, memcache, sphinx, web services etc.

For web applications doing processing in a single thread the following simple formula applies wall_time=cpu_time+sum(wait_time)+lost_time The lost time is the time which was lost for some reason - some waits we did not profile or waits we do not have control of, for example when processing had to wait for CPU available to do processing. For multi-thread application it is a bit more complicated but you still can analyze critical path.

If you have such profiling in place all you have to do is to run the query to see what are contributing factors to the response time of the problematic pages:

PLAIN TEXT SQL:

1. mysql> SELECT count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql_time-sphinx_time-utime)/wtime) lost_ratio FROM performance_log_081221 WHERE page_type='search' AND wtime>1;
2. +----------+-----------------+------------------+------------------+------------------+------------------+
3. | count(*) | avg(wtime) | cpu_ratio | mysql_ratio | sphinx_ratio | lost_ratio |
4. +----------+-----------------+------------------+------------------+------------------+------------------+
5. | 112376 | 6.0645327150223 | 0.11126040714778 | 0.17609498370795 | 0.54612972549309 | 0.16651488365119 |
6. +----------+-----------------+------------------+------------------+------------------+------------------+
7. 1 row IN SET (2.29 sec)

Why looking only at such pages is important ? This is because if you look at all pages rather than problematic subset it may lead you away from your goal. For example it is very possible among all pages we would see CPU usage as the main factor because sphinx and MySQL respond from cache.

We however see for pages which have the problem it is Sphinx which accounts for most of the time.

Looking at the data such way we have two great benefits. First we really understand what is the bottleneck. Second we know what performance gain potential is. For example in this case we could spend a lot of time optimizing PHP code but because it takes only 10% of response time in average even speeding it up 10 times we would not get more than 10% response time reduction. At the same time if we find a way to speed up Sphinx we can reduce response time to its half.

Note in this case there is some 16% of response time which is not accounted for. Large portion probably comes from memcache accesses which are not instrumented for this application. In this case this portion is not the biggest part yet but if we'd speed up Sphinx and MySQL dramatically we would have to go and look into better instrumentation so we can look inside this black box.

Once we know it is Sphinx which causes the problem we have to go and find what queries exactly are causing it - this can be done by adding request ID as comment to Sphinx log so you can profile it carefully or you can add tracing functionality to the application. All the same. Once you found the queries causing the problem you see the ones which cause the most impact and focus on optimizing them.

There are multiple ways to optimize something, my checklist is usually get rid of it, cache it, tune it, get more hardware in this order. It is often it is possible to get rid of some queries, cache them, tune them so they are faster (often at the same time changing semantics a bit) and if nothing helps or can be done quickly we can buy more hardware, assuming application can use it.

Once you've performed optimizations you can repeat analyzes again to see if performance goals are met and where is the bottleneck this time.

As a side note I should mention looking at performance statistics for the day overall is often not enough. Application performs as good as it performs during its worst times so it is very good to plot some graph over time. Sometimes an hour base may be enough but for large scale application I'd recommend to looking down to 5 minutes or even 1 minute intervals and making sure there are no hiccups.

Check the stats from the application above for example:

PLAIN TEXT SQL:

1. mysql> SELECT date_format(logged,'%H') h,count(*),avg(wtime),avg(sphinx_time/wtime) sphinx_ratio  FROM performance_log_081221 WHERE page_type='search' AND wtime>1 GROUP BY h;
2. +------+----------+-----------------+------------------+
3. | h    | count(*) | avg(wtime)      | sphinx_ratio     |
4. +------+----------+-----------------+------------------+
5. | 00   |     5851 | 3.0608555987602 | 0.49142908242509 |
6. | 01   |     6639 | 2.9099249532198 | 0.48133478800683 |
7. | 02   |     5406 | 3.3770073273647 | 0.49140835595675 |
8. | 03   |     5397 | 2.9834221059666 | 0.53178056214228 |
9. | 04   |     4820 | 3.8182240369409 | 0.53530183347988 |
10. | 05   |     3720 | 13.025273085185 | 0.61126549080115 |
11. | 06   |     1606 | 60.624889697559 | 0.89123114911947 |
12. | 07   |     2699 | 38.821067012253 | 0.90885394709571 |
13. | 08   |     2419 | 45.388828675971 |  0.9226436892381 |
14. | 09   |     4810 |  6.330725168364 | 0.60329631087965 |
15. | 10   |     5445 | 3.8355732669953 | 0.53918653169648 |
16. | 11   |     5283 | 3.0498331333457 |  0.5512679788082 |
17. | 12   |     4147 | 2.9050685487542 | 0.52802563348716 |
18. | 13   |     2313 | 3.1297905412629 | 0.47887915792732 |
19. | 14   |     4155 | 2.9788750504185 | 0.53700871350403 |
20. | 15   |     4081 | 4.4940078389087 | 0.67605124513469 |
21. | 16   |     3720 | 3.1698921914062 | 0.54566719123393 |
22. | 17   |     4210 | 2.7616731525034 | 0.47537024159769 |
23. | 18   |     6735 |  2.639767089152 |  0.5204920072653 |
24. | 19   |     5581 | 2.6058266677645 | 0.42959908812738 |
25. | 20   |     4990 | 2.4441354725308 | 0.44270882435635 |
26. | 21   |     6305 | 2.6316682707403 |  0.5236776389174 |
27. | 22   |     6774 | 2.4394227009732 | 0.53342757714496 |
28. | 23   |     5270 | 2.3949674527604 | 0.51381316608346 |
29. +------+----------+-----------------+------------------+
30. 24 rows IN SET (2.37 sec)

As you can see in this case during certain hours the average type of bad queries skyrockets and it becomes 90% or so driven by Sphinx. This tells us there is some irregular activity (cron jobs?) is happening and it affects Sphinx layer significantly.

Such goal based from top to bottom approach is especially helpful for complex applications using mutliple components (like sphinx and MySQL) or multiple MySQL Servers because in these cases you often can't easily guess the component which needs attention. Though even for less complicated single MySQL server application there is often the question if it is MySQL server causing the problem or if application code needs to be optimized.

Entry posted by peter | No comment

Add to: | | | |

An interview with Herain Oberoi, group project manager for the SQL Server Business Intelligence and Data Warehouse product.

Learn how to create right click (Short Cut) Menus in Microsoft Access 2007.

Customers are showing interest in Oracle's recently announced products for high-end data warehousing, but it will be some time before that enthusiasm makes a significant impact on the vendor's bottom line, CEO Larry Ellison said this week.

Peter said me that previous results http://www.mysqlperformanceblog.com/2008/12/18/xtradb-benchmarks-15x-gain/ are too marketing, and we should show other results also.

Here is the run for CPU Bound,or it would be more correctly to say in-cache benchmark, because there is a lot of CPU remains idle. This run is exactly the same as Disk Bound but with innodb_buffer_pool_size=8G which is more than enough to fit all data in memory.

The XtraDB is showing very modest gain of about 2.5% in this case which can be attributed to measurement error too.

The story becomes more interesting if we look at CPU usage graph. As you can see they are very different for XtraDB and Innodb storage engines.

Innodb has generally higher CPU usage and more uniform while it has a huge dip in the middle. This happens because of checkpointing activity which can basically stall processing while dirty buffers are flushed. This is serious issue in real life application even though it does not significantly affects average throughput.

Honestly I'm not overly happy how XtraDB adaptive checkpointing performs in this case - we should see how to get it to be more uniform in this case.

So how does Average CPU usage looks ? You can see XtraDB delivers 2.5% better performance with 10% lower CPU usage.... which is more than the measurement error. So we think in workload which can utilize CPU more fully XtraDB will show more gains, though not 1.5x as for IO bound workload.

One may ask why CPU is not fully used in this case ? Honestly we did not investigate it further. One could be transaction commits (the run is with innodb_flush_log_at_trx_commit=1) which causes some overhead even though system has BBU installed. Other is simply properties of TPC-C benchmark which is rather lock intensive and may not be able to use all CPUs simply due to row level locking. Third is internal Innodb contention issues which also may serialize things not allowing full CPU usage.

Enjoy.
BTW if somebody has 16 core box he can provide access to so we can do some benchmarks we would appreciate it.

Entry posted by Vadim | No comment

Add to: | | | |

I guess it is first reaction on new storage engine - show me benefits. So there is benchmark I made on one our servers. It is Dell 2950 with 8CPU cores and RAID10 on 6 disks with BBU, and 32GB RAM on board with CentOS 5.2 as OS. This is quite typical server we recommend to run MySQL on. What is important I used Noop IO scheduler, instead of default CFQ. Disclaimer: Please note you may not get similar benefits on less powerful servers, as most important fixes in XtraDB are related to multi-core and multi-disks utilization. Also results may be different if load is CPU bound.

I compared MySQL 5.1.30 trees - MySQL 5.1.30 with standard InnoDB, MySQL 5.1.30 with InnoDB-plugin-1.0.2 and MySQL 5.1.30 with XtraDB (all plugins statically compiled in MySQL)

For benchmarks I used scripts that emulate TPCC load and datasize 40W (about 4GB in size), 20 client connections. Please note I used innodb_buffer_pool_size = 2G and innodb_flush_method=O_DIRECT to emulate IO bound load.

InnoDB parameters:

PLAIN TEXT CODE:

1. innodb_additional_mem_pool_size = 16M
2. innodb_buffer_pool_size = 2G
3. innodb_data_file_path = ibdata1:10M:autoextend
4. innodb_file_io_threads = 4
5. innodb_thread_concurrency = 16
6. innodb_flush_log_at_trx_commit = 1
7. innodb_log_buffer_size = 8M
8. innodb_log_file_size = 256M
9. innodb_log_files_in_group = 3
10. innodb_max_dirty_pages_pct = 90
11. innodb_flush_method=O_DIRECT
12. innodb_file_per_table = 1

And for XtraDB I additionally used:

PLAIN TEXT CODE:

1. innodb_io_capacity = 10000
2. innodb_adaptive_checkpoint = 1
3. innodb_write_io_threads = 16
4. innodb_read_io_threads = 16

So what is in result:

Result is in NOTPM (New Order Transactions Per Minute), more is better. As you see XtraDB is somewhat 1.5x better than InnoDB in standard 5.1.30 and even more than InnoDB-plugin-1.0.2

And there is CPU utilization for all tested engines:

As you see XtraDB also utilizes CPUs better.

Finally let me show you why I took NOOP IO scheduler instead of CFQ, there are result for XtraDB with both:

4X difference is just giant one. And it is important to remember as Linux kernels 2.6.18+ (which are used on CentOS / RedHat 5.2) are coming with CFQ scheduler as default.
So echo 'noop' > /sys/block/sda/queue/scheduler should be one of first things to do on new server (sure you also need to change kernel startup parameter to make it automatic after reboot).

Entry posted by Vadim | 27 comments

Add to: | | | |

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
2. +----------------+
3. | Tables_in_test |
4. +----------------+
5. | queue          |
6. | test_innodb    |
7. +----------------+
8. 2 rows IN SET (0.00 sec)
9.  
10. mysql> SHOW CREATE TABLE test_innodb;
11. ERROR 1146 (42S02): TABLE 'test.test_innodb' doesn't exist

With more elaborate details in error log:

081217 15:59:11 [ERROR] Cannot find or open table test/test_innodb from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

I would much rather see MySQL to report some more reasonable error message, something like Storage Engine could not open table or something like it.

So what we can do is to make sure Innodb has something in its data dictionary so it allows MySQL to succeed displaying .frm file contents:

mysql> create table test_innodb(i int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> Aborted
[root@test3 test]# cp /tmp/test_innodb.frm .
cp: overwrite `./test_innodb.frm'? y
[root@test3 test]# mysql test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table test_innodb;
+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Note: I have created the table which have a different definition from original table. It is good enough to get SHOW CREATE TABLE but do not try to use it any other way, as nasty things may happen:

PLAIN TEXT SQL:

1. mysql> INSERT INTO test_innodb VALUES(1,2);
2. Query OK, 1 row affected (0.00 sec)
3.  
4. mysql> SELECT * FROM test_innodb;
5. ERROR 2013 (HY000): Lost connection TO MySQL server during query

Recovering CREATE TABLE from .frm file for MyISAM Table

We do not really need this that frequently but I decided to cover this for completeness anyway.

With MyISAM table MySQL comes with another creative error message if .frm is the only file which exists:

PLAIN TEXT SQL:

1. mysql> SHOW CREATE TABLE test_myisam;
2. ERROR 1017 (HY000): Can't find file: 'test_myisam' (errno: 2)

This is closer to the truth though file name is wrong - there should be test_myisam.MYI or test_myisam.MYD in the error message. The file with name "test_myisam" does not need to be exist.

The intuitive way to rebuild MyISAM table would be REPAIR TABLE test_myisam USE_FRM, however it does not work... just yet.

You need to create the .MYI and .MYD files for the table to make it work:

[root@test3 test]# touch test_myisam.MYI
[root@test3 test]# touch test_myisam.MYD
[root@test3 test]# chown mysql:mysql *

PLAIN TEXT SQL:

1. mysql> repair TABLE test_myisam USE_FRM;
2. +------------------+--------+----------+----------+
3. | TABLE            | Op     | Msg_type | Msg_text |
4. +------------------+--------+----------+----------+
5. | test.test_myisam | repair | STATUS   | OK       |
6. +------------------+--------+----------+----------+
7. 1 row IN SET (0.00 sec)
8.  
9. mysql> SHOW CREATE TABLE test_myisam;
10. +-------------+----------------------------------------------------------------------------------------------------------------------------+
11. | TABLE       | CREATE TABLE                                                                                                               |
12. +-------------+----------------------------------------------------------------------------------------------------------------------------+
13. | test_myisam | CREATE TABLE `test_myisam` (
14.   `a` int(11) DEFAULT NULL,
15.   `b` int(11) DEFAULT NULL
16. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
17. +-------------+----------------------------------------------------------------------------------------------------------------------------+

So it is not at all that complicated.

Entry posted by peter | No comment

Add to: | | | |