Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 98 Next 30 Older Entries

Displaying posts with tag: tuning (reset)

Tuning InnoDB Configuration
+2 Vote Up -0Vote Down
I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.
Impact of the number of idle connections in MySQL
+7 Vote Up -0Vote Down

Be careful with my findings, I appear to have compile in debug mode, I am redoing the benchmarks. Updated version here.

I recently had to work with many customers having large number of connections opened in MySQL and although I told them this was not optimal, I had no solid arguments to present. More than once, I heard: it is not a problem, those connections are Sleeping! In order to remedy to this situation, I decided to run a series of DBT2 benchmarks while the number of idle connections was varied from 0 to 1500. I was expecting an influence, because those idle threads are presents in internal MySQL list objects that need to be scanned and also the socket SELECT call needs to build a FD bitmap after

  [Read more...]
Wish list for MySQL thread polling events
+2 Vote Up -1Vote Down

It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).

How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it

  [Read more...]
MySQL Query Optimization – Tip # 1 – Avoid using wildcard character at the start of a LIKE pattern.
+4 Vote Up -1Vote Down
The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls. So this post is a part of that series of tips, and this is the first tip "Avoid using a wild card character at the start of a LIKE pattern".
DiskPageBufferMemory tuning and disk data statistics in MySQL Cluster 7.1.9
+8 Vote Up -0Vote Down
From MySQL Cluster 7.1.9 (not yet released) it is possible to get better stats on disk data tables. In fact, the statistics makes it possible to tune the DiskPageBufferMemory parameter (similar to innodb_bufferpool), in order to avoid disk seeks. It is much (understatement) faster to fetch data from the DiskPageBufferMemory than disk.

Here is an example/tutorial how to use this information and how to check the hit ratio of the DiskPageBufferMemory. Next time, I will explain about other counters you can get from ndbinfo.diskpagebuffer.

Finally, no more educated guesswork is needed.

Let's take an example.

I have a table t1 with 650000 record
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,

  [Read more...]
MySQL Paginated displays – How to kill performance vs How to improve performance!
+0 Vote Up -0Vote Down
Pagination is used very frequently in many websites, be it search results or most popular posts they are seen everywhere. But the way how it is typically implemented is naive and prone to performance degradation. In this article I attempt on explaining the performance implications of poorly designed pagination implementation. I have also analyzed how Google, Yahoo and Facebook handle pagination implementation. Then finally i present my suggestion which will greatly improve the performance related to pagination.
Impact of the sort buffer size in MySQL
+2 Vote Up -0Vote Down

The parameter sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way Linux allocates memory. Monty Taylor (here) have described the underlying issue in detail, but basically above 256kB the behavior changes and becomes slower. After reading a post from Ronald Bradford (here), I decide to verify and benchmark performance while varying the size of the sort_buffer. It is my understanding that the sort_buffer is used when no index are available to help the sorting so I created a MyISAM table with one char column without an index:

  [Read more...]
Tuning MySQL Server Settings
+0 Vote Up -0Vote Down
The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file. The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data. Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine. Getting started...
Cluster - spins/contentions and thread stuck in..
+6 Vote Up -0Vote Down
I get a number of question about contentions/"stuck in..". So here comes some explanation to:
  • Contention
  • Thread Stuck in
  • What you can do about it
In 99% of the cases the contentions written out in the out file of the data nodes (ndb_X_out.log) is nothing to pay attention to.

sendbufferpool waiting for lock, contentions: 6000 spins: 489200
sendbufferpool waiting for lock, contentions: 6200 spins: 494721

Each spin is read from the L1 cache (4 cycles on a Nehalem (3.2GHz), so about a nanosecond).
1 spin = 1.25E-09 seconds (1.25ns)

In the above we have:
(494721-489200)/(6200-6000)= 27 spins/contention
Time spent on a contention=27 x 1.25E-09=3.375E-08 seconds (0.03375 us)

So we don't have a problem..

Another example (here is a lock guarding a job buffer

  [Read more...]
Databases: Normalization or Denormalization. Which is the better technique?
+1 Vote Up -0Vote Down
This has really been a long debate as to which approach is more performance orientated, normalized databases or denormalized databases. So this article is a step on my part to figure out the right strategy, because neither one of these approaches can be rejected outright. I will start of by discussing the pros and cons of both the approaches. Pros and Cons of a Normalized database design. Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons: Normalized tables are usually smaller and...
dbbenchmark.com – now supporting MySQL on OSX 10.6
+2 Vote Up -0Vote Down

Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.

dbbenchmark.com – Benchmarking script now available
+0 Vote Up -0Vote Down

You can download the first release of the benchmarking script here: http://code.google.com/p/dbbenchmark/

Please read the README file or consult the Support page before running the benchmarks.

Kontrollbase – new version available with improved analytics
+1 Vote Up -0Vote Down
A new version of Kontrollbase – the enterprise monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases – is available for download. There are several upgrades to the reporting code with improved alert algorithms as well as a new script for auto-archiving of the statistics table based […]
What do MySQL Consultants do?
+4 Vote Up -0Vote Down

One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.

  • Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
  • Review server load and identify physical bottleneck
  • Look at all running processes
  • Look specifically at MySQL processes
  • Review MySQL Error Log
  • Determine MySQL version
  • Look at MySQL configuration (e.g. /etc/my.cnf)
  • Look at running MySQL Variables
  • Look at running MySQL status (x n times)
  • Look at running MySQL INNODB status (x n times) if used
  • Get Database and Schema Sizes
  • Get Database Schema
  • Review Slow Query Log
  • Capture query sample via
  •   [Read more...]
    Benchmarking MySQL ACID performance with SysBench
    +0 Vote Up -2Vote Down

    A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

    Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

    • sysbench –test=oltp
      [Read more...]
    The “Shadow Table” trick.
    +0 Vote Up -0Vote Down
    The need: Often there is a requirement where data in a particular table has to be processed, and the data processing might be slow, while the table might be a one that is used by your application extensively. For example, a logging table that logs page hits. Or there might be an archiving operation that has to be performed on a particular table. Archiving / processing / aggregating records, all these operations are slow and can really blog down a website, combine that with the added overhead if the table that needs to have these operations performed is one that...
    Performance tuning using vertical partitioning.
    +0 Vote Up -0Vote Down
    Does having small data-sets really help? Of course it does! Are memory lookups faster that disk lookups. Of course ! So many times I have seen people complain about queries taking too long now, while they were not taking that long earlier. There is one big reason for this, earlier the size of data-set was small so it could fit into memory. Now that the data-set has grown large enough that it cannot fit entirely into memory, the disk seeks really have slowed down the queries significantly. What to do now? Vertical partitioning. Divide the data-set into separate data-sets vertically....
    Tuning InnoDB Concurrency Tickets
    +2 Vote Up -0Vote Down

    InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: "The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500..."

    What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it

      [Read more...]
    Tuning your Cluster with ndbinfo (7.1) part 1 of X
    +2 Vote Up -0Vote Down
    The new ndbinfo interface in 7.1 is really useful to assist in tuning MySQL Cluster. Here is an example (more will follow):

    I started with one test where I inserted two blobs (1KB + 1KB) in one table.
    From 16 threads (colocated with one mysqld, two data nodes, separate computers) and one application driving the load I reached about 6960TPS, and the utilization of the redo buffers (controlled by the parameter RedoBuffer in config.ini) looked like:

    mysql< select * from ndbinfo.logbuffers;
    | node_id | log_type | log_id | log_part | total | used |
    | 3 | REDO | 0 | 1 | 50331648 | 196608 |
    | 3 | REDO | 0 | 2 | 50331648 | 294912

      [Read more...]
    vBulletin session table and Hash index in MEMORY Engine
    +1 Vote Up -0Vote Down
    In vBulletin Board System there is session table contains online user information and tracking, in which forum now, what the current URL now, So it is have huge concurrent update statement, By default this table is MEMORY engine because its data not important in case MySQL restart. I notice early loaded in MySQL and server, So I do full tuning for the server then analyze slow query using
    Speaking At The MySQL Users Conference
    +0 Vote Up -0Vote Down
    My proposal has been accepted, yay!

    I'll be speaking on a topic that I feel passionate about: MySQL Server Diagnostics Beyond Monitoring. MySQL has limitations when it comes to monitoring and diagnosing as it has been widely documented in several blogs.

    My goal is to share my experience from the last few years and, hopefully, learn from what others have done. If you have a pressing issue, feel free to comment on this blog and I'll do my best to include the case in my talk and/or post a reply if the time allows.

    I will also be discussing my future plans on sarsql. I've been silent about this utility mostly because I've been implementing it actively at work. I'll post a road map shortly based on my latest experience.

    I'm excited about meeting many old friends (and most now fellow MySQL alumni) and making new ones. I hope to see you there!
    Database tuning: ratio vs. rate
    +3 Vote Up -2Vote Down

    Baron makes an excellent point in Why you should ignore MySQL’s key cache hit ratio — ratio is not the same as rate. Furthermore, rate is [often] the important thing to look at.

    This is something that, at Pythian, we internalized a long time ago when thinking about MySQL tuning. In fact, mysqltuner 2.0 takes this into account, and the default configuration includes looking at both ratios and rates.

    If I told you that your database had a ratio of temporary tables written to disk of 20%, you might think “aha, my database is slow because of a lot of file I/O caused by writing temporary tables to disk!”. However, that 20% ratio may actually mean a

      [Read more...]
    Can I use latin1 to store utf8 data?
    +2 Vote Up -0Vote Down
    I've table contains text column and its charset is latin1, and i can store Arabic text ( and non English character) in this column and retrieve it, i don't know how is it? So how is that? and why I need utf8? CREATE TABLE `post` ( `postid` int(10) unsigned NOT NULL AUTO_INCREMENT, `threadid` int(10) unsigned NOT NULL DEFAULT '0', `parentid` int(10) unsigned NOT NULL DEFAULT '0', `
    Upcoming webinar – MySQL performance tuning best practices
    Employee +4 Vote Up -0Vote Down

    MySQL Cluster Performance Tuning Best Practices

    Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?

    Benefits of connection pooling

    Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL

      [Read more...]
    Ubuntu Karmic's Network Manager Issues
    +1 Vote Up -1Vote Down
    Since Ubuntu 8.04 aka Hardy Heron, I've had issues with every new release. As Ubuntu evolves into being a viable desktop OS alternative, its complexity has been growing and with the new and improved looks new challenges arise. This bug in particular has been very difficult to diagnose and I can't imagine anyone without enough Linux experience to overcome it on their own, so I decided to summarize the steps I took to fix it ... and vent my frustration at the end.

    The Symptom

    I came across the issue for the first time while trying Ubuntu's Karmic Netbook remix. After overcoming the typical Broadcom wifi driver, Network Manager would connect, but Firefox would fail to load the web pages 90% of the time. Using ping in the command line worked just fine. Maybe I needed to update the software packages to get the latest
      [Read more...]
    Kontrollbase revision 270 is available for download
    +2 Vote Up -1Vote Down
    The latest version of Kontrollbase – the opensource enterprise MySQL analytics and performance tuning application –  is available for download. Since the previous one, version 225, there have been a lot of great changes. The most significant being the development and inclusion of the Reporter CLI script – which is a combination of the client, [...]
    Getting around optimizer limitations with an IN() list
    +1 Vote Up -0Vote Down

    There was a discussion on LinkedIn one month ago that caught my eye:

    Database search by "within x number of miles" radius?

    Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
    Thankful for any tips you can throw my way..


    A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

      [Read more...]
    Effect of adaptive_flushing
    +1 Vote Up -0Vote Down

    I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.

    The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.

    Database pages      2318457
    Old database pages  855816
    Modified db pages   457902
    Log flushed up to   10026890404067
    Last checkpoint at
      [Read more...]
    My MySQL Tool Chest
    +4 Vote Up -0Vote Down
    Every time I need to install or reconfigure a new workstation, I review the set of tools I use. It's an opportunity to refresh the list, reconsider the usefulness of old tools and review new ones. During my first week at Open Market I got one of these opportunities. Here is my short list of free (as in 'beer') OSS tools and why they have a place in my tool chest.

    Testing Environments

    Virtual Box

    Of all the Virtual Machines out there, I consider Virtual Box to be the easiest to use. Since I first looking into it while I was still working at Sun/MySQL, this package has been improved constantly. It's a must have to stage High Availability scenarios or run tools that are not available in your OS of choice.

    MySQL Sandbox

    Did you compile MySQL

      [Read more...]
    InfiniDB Top 3 Tuning Parameters
    +0 Vote Up -0Vote Down

    There are a few parameters that may be worth looking at when first installing InfiniDB; NumBlocksPct for managing memory, PmMaxMemorySmallSide for optimizing join behaviors, and MaxOutstandingRequests which changes how individual queries share resources.

    The NumBlocksPct parameter manages the amount of memory on each server allocated to store data blocks and is expressed as a % of server memory. For example, to allocate 5 GB for the data buffer cache for a server with 8GB memory, set thRead More...

    Previous 30 Newer Entries Showing entries 31 to 60 of 98 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.