Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 11 1 Older Entries

Displaying posts with tag: innodb internals (reset)

Innodb Compression: When More is Less
+1 Vote Up -1Vote Down

So Vadim posted on the MySQL Performance Blog about poor benchmarks when running innodb compressed pages.  I ran some tests a few weeks ago and did not see the same results as him and checked into my previous tests and compared them to his #’s.  In a round about way verifying his thoughts on Mutex contention I found that increasing the BP sized with compressed data decreases the transactional throughput. The test was run with an uncomressed data set size of 6GB, 3.1GB compressed read-only.

TPS 2G, NOZIP 3217.19 8G, NOZIP 4479.81 8G, NOZIP, 16BP 4424.3 1G,ZIP  [Read more...]
Filtering by table is now possible with WaffleGrid
+1 Vote Up -0Vote Down

Since I have been a home recently, I put some time correcting bugs in WaffleGrid and adding new features. Thanks to gdb, I have been able to understand a silly bug that was affecting WaffleGrid with sysbench but, weird enough, not with dbt2. Everything is in the way connections are established. I will blog more about that soon.

Regarding the new features, it is now possible to choose which tables you want to push to memcached. For that purpose, two new parameters have been introduce:

innodb_memcached_table_filter_enable = 0 | 1   (default to 0)

to enable the filtering and

innodb_memcached_table_list = db1/table1,db2/table2   

to list the tables. This feature is filtering based on the space id so, innodb_file_per_table has to be set. Right now, the association table space_id is done only at startup so, the table has to exist. Also, since an alter table

  [Read more...]
You know you have really screwed up when …
+3 Vote Up -0Vote Down

You crash MySQL/Innodb and you trace the error to a function that says:

“This function checks the consistency of an index page when we do not
know the index. This is also resilient so that this should never crash
even if the page is total garbage. ”

Oops… I guess its not that resilient after all:)

Waffle: Progress and a Rearchtecture?
+1 Vote Up -0Vote Down

So I spent several hours over the last few days on the Secondary index bug. Out of frustration I decided to try and bypass the LRU concept all together and try going to a true secondary page cache. In standard Waffle a page is written to memcached only when it is expunged ( or LRU’d ) from the main buffer pool. This means anything in the BP should not be in memcached. Obviously with this approach we missed something, as Heikii pointed out in a comment to a previous post, it seems likely we are getting an old version of a page. Logically this could happen if we do not correctly expire a page on get or we bypass a push/lru leaving an old page in memcached to be retrieved later on.

So I was thinking why not bypass the LRU process? While I feel this is the most efficient way to do this, its not the only way. I modified innodb to use the default LRU code and then modified the page get to push to

  [Read more...]
Waffle: The Mystery Continues
+0 Vote Up -0Vote Down

So I spent the weekend looking at places where we may have missed something in the code for waffle. You can actually see some of the stuff I tried in the bug on launchpad about this, but the weird thing is the very last thing I tried. As I took a step back and looked at the problem ( secondary index corruption ) and our assumption that we “missed” something, I decided to find the place where pages are written to disk and to push to memcached from here as well as from the LRU. With the double write buffer enabled that place should be buf_flush_buffered_writes. By pushing to memcached here we should eliminate the page that falls through the cracks of the LRU. Basically this should help ensure memcached has an exact copy of the data that exists on disk. The result? It failed with the same secondary index failure. This means:

a.)

  [Read more...]
Waffle: limiting the space ids being pushed to memcached
+2 Vote Up -0Vote Down

If you read Yves blog post about waffle yesterday we are seeing some weird gremlins in the system and could use some scoobey doo detective work if you have some ideas. The strange thing is it only exhibits under high load. So it really seems like we may have missed some background cleanup process that accesses or removes pages from disk or the buffer pool without going through the functions we call waffle in (buf_LRU_search_and_free_block & buf_read_page_low ).

One of the idea’s I had was trying to narrow the scope of what’s being pushed and read form Memcached. Even though I am using file per table, system tablespace pages are still making it in and out of memcached. I thought if we missed something maybe it was here ( even though I could not find it in the code ). I mean cleaning up undo or internal data would seem like a logical place to

  [Read more...]
Oracle really….
+0 Vote Up -0Vote Down

Ummmmmfff…. wind out of my sails today. I believe their was an analyst who predicted this a year ago when Sun bought MySQL…. I wish I could find the article. I am going to head down to the UC floor and get a feel for what people are thinking… will post more as I get time.

How much does it cost to update an index?
+0 Vote Up -0Vote Down

I was asked today about what is the cost of adding an index on a frequently updated column ( like a timestamp, count, or weight )… typically my answer is it depends. But for this question it was narrowed down to a specific case. An update on a secondary index based on a PK lookup. I decided to try and give an exact answer. I hacked the Juice DB Benchmark to attack my medium sized table ( which magically already had a count column in it ). I then cranked up the test. A few more details Query 23 updated a column without an index, queries 21,23,24 updated the d_count column. query 21 adds 5 to the count, query 22 adds 150, query 24 subtracts 1…. here are the results:

With a solo index on d_count:

Run Number:  86  threads:  8 Length :  340 LoadType: upd
Total Test Runtime = 375.245010137558 seconds, limiting results to 300 seconds however
QNum:     21 ... QCount:  78448 ... QTime:
  [Read more...]
Waffle Grid: Async IO Concerns
+0 Vote Up -0Vote Down

Last night I was on a plane to a client site and I was reviewing the waffle grid code for inclusion of multi-gets when calling read-ahead functions (potentially in other area’s as well)…  their I noticed something that may slow down our performance in waffle grid.  You see we read from memcached in the buf_read_page_low function.  This function is responsible for checking wether a page exists in the buffer and if not making an IO request for it via the function fil_io.  What I saw in buf_read_page_low was:  we check the buffer, then check memcached, then go get the page off disk… you follow?  Alright The get page from disk part is challenge.  Let me do a quick deep dive on the internal function calls.

You see buf_read_page_low is called by a few different functions in a few different ways, the ones I am concerned with are the functions that are pasing a sync of false ( you do not want

  [Read more...]
InnoDB secondary index file structure
+0 Vote Up -0Vote Down

In my previous Post, we took a look at the file structure of the InnoDB primary key index. With the same table structure, but a slightly different dataset:

mysql> select * from test_innodb_growth limit 10;
+----+------------+--------+
| id | data       | spacer |
+----+------------+--------+
|  1 | a          | |      |
|  2 | aa         | |      |
|  3 | aaa        | |      |
|  4 | aaaa       | |      |
|  5 | aaaaa      | |      |
|  6 | aaaaaa     | |      |
|  7 | aaaaaaa    | |      |
|  8 | aaaaaaaa   | |      |
|  9 | aaaaaaaaa  | |      |
| 10 | aaaaaaaaaa | |      |
+----+------------+--------+
10 rows in set (0.00 sec)

All the rows after id = 10 have data = ‘aaaaa’. Let’s take a look at the secondary index ‘idxdata’. I recently had an argument with a client that claimed that a varchar is fully expanded in

  [Read more...]
Showing entries 1 to 10 of 11 1 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.