Showing entries 31 to 40 of 73
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: mysql performance (reset)
A Visual Guide to the MySQL Performance Schema

If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or sizing, which is great for performance.

I categorize the performance_schema …

[Read more]
Hands on with JetProfiler

Jet Profiler is a great MySQL  profiler. As the world of operating systems is getting more and more mixed, Jet Profiler offers a huge advantage: It is a Java based application and therefore able to run on Windows, Max OS X as well as on Linux.

Most of the time Java based applications tend to be very slow, however Jet Profiler shows that Java based applications can be fast and responsive as well. Even after hours of recording the UI stays responsive. To give you a working example, I had it recording on a server and real world load.

A nice feature of Jet Profiler is the setting for the polling interval. If you have a very, very busy server the polls could interfere with the performance, so you can define the polling interval, which I did. However, instead of starting with a …

[Read more]
Optimize MySQL COUNT (*) query

There is not magic solution to count table contents especially if you have a client that has a table with 40+ million records and you need to do the filtering using the 'OR' SQL statement.

Original query was the following:

SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2') OR field2 IN ('val3','val4');

First benchmark before doing optimization showed me results after 4 minutes.

My

This Week in Website Performance

This Week in Website Performance is a weekly feature of the Monitis.com blog. It summarizes recent articles about website performance. Why? Because your friends at Monitis.com care.

NoSQL or Traditional Database: From an APM Perspective There Isn’t Really Much Difference

Author: Michael Kopp.

If your application is executing more statements or downloading more data than is necessary, no amount of backend tuning will have your site running at the highest level of performance. This article serves as a reminder that the application accessing the data can be prime …

[Read more]
It’s the Hardware, idiot! Increasing MySQL Performance

MySQL performance can be increased in two ways, software optimization and hardware upgrades. While the previous articles have covered much of the software side of performance optimization, we are now going to focus on the hardware aspect. Does hardware help boost performance? Like software optimization, hardware upgrades for MySQL systems are based upon set goals for an organisation. The question is not what hardware would work best; rather a question of what hardware will help the organisation achieve an X goal. The answer is yes, hardware does boost performance, but there are a few caveats to this. There are cases such as organizations’ demands are fulfilled even with crappy hardware, and all they require is a performance boost on the software side. However, an organisation might have a goal plan for a major performance increase down the road, and even …

[Read more]
Interesting behavior of a MySQL benchmark on EC2

I had to benchmark an EC2 instance to see whether a database could be safely moved to it. It is a good practice, which helps avoiding surprises when an instance or its storage are allocated in a noisy neighborhood, where the neighbors use so much resources that it affects the performance of our MySQL database. It is understandable that one can never get very reliable results on EC2, this is a shared environment after all, and that some fluctuations should be expected, however it is still good to know the numbers. I started my benchmarks and everything seemed fine at first, but then sometimes statistics I was getting started looking quite odd.

I was running the benchmarks on a High-CPU Extra Large Instance and couldn’t see any reliability in the results at all. I mean, in one moment I was getting poor throughput and horrible response times only to see it improve a lot a few minutes later. I ruled out a possibility that it could be …

[Read more]
Join Optimizations in MySQL 5.6 and MariaDB 5.5

This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 has introduced some additional optimizations which we will also look at, in this post.

Now let me briefly explain these optimizations.

Batched Key Access

Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins are read, and then for each of these rows an index lookup is performed on the second table. This means many point queries, say for example if table1 yields 1000 …

[Read more]
Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at.

Index Condition Pushdown

Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as:

KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) …
[Read more]
Difference between myisam_sort_buffer_size and sort_buffer_size

MySQL has two confusingly identical by the first look variables myisam_sort_buffer_size and sort_buffer_size. Thing is that those two confusingly similar variables has absolutely different meanings.

sort_buffer_size is a per-connection variable and do not belongs to any specific storage engine. It doesn’t matter do you use MyISAM or InnoDB – MySQL will allocate sort_buffer_size for every sort (required most of the times for ORDER BY and GROUP BY queries) so increasing it’s value might help speeding up those queries however I would not recommend to change it from the default value unless you are absolutely sure about all the drawbacks. Value for out-of-the-box MySQL-5.1.41 installation on Ubuntu is 2Mb and it’s recommended to keep it that way.

On …

[Read more]
Why is stock MySQL slow?

“I’ve installed MySQL and it doesn’t work fast enough for me”. MySQL server is heart of database driven application (if it uses MySQL as database of course!) and any slowness related to running queries is affecting all application layers.

MySQL server tuning and query slowness hunting are always step by step process and without knowing all the data (SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, SHOW TABLE STATUS LIKE ‘tablename’, EXPLAIN details for slow query is just some of the required information) it would be generally a blind guess. But there are still few things which is related to newly installed MySQL server.

If you are using stock MySQL you might need to check memory pool size which MySQL used to load index data to avoid slow IO requests and increase queries speed. Connect to MySQL and fire two queries:

SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE …
[Read more]
Showing entries 31 to 40 of 73
« 10 Newer Entries | 10 Older Entries »