Portable table space part III or We can do it (with partition)

First of all, I have to say that as usual I did the three investigation, because I had fun.

MySQL is still fun for me, and also if I am not touching the magic topic of the moment like SSD,

replication and so on, I enjoy what I was doing, and I see how I can save my customer data, with this.

Second, I thanks again the people who helps me in understanding better MySQL, in this case Mattias Jonsson, who points me in right direction.

The Solution

The solution is a little bit cumbersome, but it works.

I have assume that:

• a customer have a data-set which contains a partitioned table
• we can have short period of read only data

To use the DISCARD tablespace on a partition the process is:

1. - LOCK the table with partition and the final table where I will put …

Portable table space part III or We can do it (with partition)

First of all, I have to say that as usual I did the three investigation, because I had fun.

MySQL is still fun for me, and also if I am not touching the magic topic of the moment like SSD,

replication and so on, I enjoy what I was doing, and I see how I can save my customer data, with this.

Second, I thanks again the people who helps me in understanding better MySQL, in this case Mattias Jonsson, who points me in right direction.

The Solution

The solution is a little bit cumbersome, but it works.

I have assume that:

• a customer have a data-set which contains a partitioned table
• we can have short period of read only data

To use the DISCARD tablespace on a partition the process is:

1. - LOCK the table with partition and the final table where I will put …

I have been doing some research lately on various mySQL related features and found myself distracted by mySQL 5.6. If everything that I am reading is correct, this should be the best mySQL version yet.

mySQL 5.6 will have multi thread replication, making time delayed slaves-an actual feature and not a byproduct of replicating high write volumes. Also, crash safe replication! When a master crashed and corrupted the binary log this was a pain in the ass to fix. In the past I had to write scripts to walk the primary key and do a checksum on each returned table chunk and pick which row was correct and which one was not.

The performance optimization for innodb that addresses some stalls is as exciting as multiple SQL threads for replication. One major change is in the stat layer, which was throttling throughput for in memory workloads at high thread concurrency. You can read the details …

In my previous post with results for Fusion-io ioDrive we saw some instability in results, I was pointed that it may be fixed in new drivers VSL 3.1.1. I am not sure if this driver is available for everyone – if you are interested, please contact your Fusion-io support representative. I installed new drivers and firmware, and in fact, the result improved.

Information about driver and firmware: Firmware v6.0.0, rev 107006. Fusion-io driver version: 3.1.1 build 172.

Actually an upgrade was not flawless, after a firmware upgrade I had to perform low-level formatting, which erase all data. So if you want to do the same – make sure you copy your data.

So there are results for driver 3.1 (with comparison to previous driver 2.3)

Random writes:
…

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :

1 – Catch the query (if you can) !

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

•  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

…

With summer solstice approaching, we're taking Tungsten to the land of midnight sun, Finland. 

On Tuesday, May 29th, Linas Virbalas (Sr. Software Engineer) will be talking about and demonstrating Tungsten capabilities at The Helsinki MySQL User Group. Thank you, Solinor Oy, for sponsoring light snacks, sauna and refreshments!

The following day, May 30th, Linas will be presenting at 3rd

No space left on device – this can happen to anyone. Sooner or later you may face the situation where a database either has already or is only minutes away from running out of disk space. What many people do in such cases, they just start looking for semi-random things to remove – perhaps a backup, a few older log files, or pretty much anything that seems redundant. However this means acting under a lot of stress and without much thinking, so it would be great if there was a possibility to avoid that. Often there is. Or what if there isn’t anything to remove?

While xfs is usually the recommended filesystem for a MySQL data partition on Linux, the extended filesystem family continues to be very popular as it is used as default in all major Linux distributions. There is a feature specific to ext3 and ext4 that can help the goal of resolving the full disk situation.

…

The Helsinki MySQL User Group will meet at the usual place on May 29th. Click here for details and to RSVP. Linas Varbalas will talk about Tungsten and maybe dare a live demo!

Linas is in town for the OUGF Harmony conference 2012. The conference might of course be of some interest to user group members too. Due to the conference we also have other famous MySQLrs in town, Sheeri Kabral of OurSQLcast fame has also confirmed she will attend the user group (and maybe have OurSQLcast CD's with her?)

One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to degrade.

The first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many cases. Whatever powerful and well tuned system you have if you put too heavy of concurrent load on it the response times will increase and user experience will suffer.

So what you can do to prevent this problem from happening ? The answer is easy. Throttle the side load so it does not consume too much system resources. Here are some specific techniques to use.

Do push concurrency too high Many developers will test script with …

The tools we’ve redesigned in Percona Toolkit recently have moved away from a legacy technique for operating on small numbers of rows at a time, towards a more reliable and predictable method. We call the old version “chunking” and the new version “nibbling.” Many other MySQL tools I’ve seen either operate on entire tables, or use the “chunking” technique and are exposed to the problems it creates. I’ll compare the two briefly to explain the differences.

Chunking attempts to divide a table into ranges of rows of a desired size, such as 1000 rows. It does this by examining the minimum and maximum value of the primary key (or other suitable index), estimating the number of rows in the table, and dividing one by the other to create a list of boundary values. Suppose that the minimum value is 1 and the maximum is 1000000, and there are an estimated 100000 rows in the table. The chunk boundaries will fall on intervals of 10000. …