-
Stripping Comments so Query Cache Works in MariaDB and XtraDB
I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.
This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.
In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the SHOW PROXY PROCESSLIST command (often used in the Proxy Admin module), in large part because of this limitation.)
To enable it (in MariaDB 5.3+ and XtraDB), just add query_cache_strip_comments under the [mysqld] section in your my.cnf file and restart mysqld.
Alternatively, you can also set it dynamically:
mysql> set @@global.query_cache_strip_comments=1;
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like 'query_cache_strip_comments';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| query_cache_strip_comments | ON |
+----------------------------+-------+
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.3.3-MariaDB |
+---------------+
Fwiw, from examining the source code, both implementations seemed to differ, but the end result is the same, and it’s a welcome addition, if you ask me.
-
Forums.MySQL.Com as a gym for mental exercise
The MySQL Forums seem to be have been around forever. There are groups for announcements, performance, InnoDB, Cluster, Replication, Certification, Events, questions in German, partitioning, and just under a hundred MySQL related subjects in total. Most folks in the MySQLverse seem to regularly lurk in one or more of the forums. There area great source of information and sometimes humor. But are you using the forums to help build your MySQL mental muscle? Ask yourself how would you solve the problem or how would you guide a junior DBA or developer to fix an issue posted in a forum.
Try reading the groups that are not in your regular reading list. Is there a question in there you can not answer even after a peek at the documentation?
After running MySQL for a few years, it is easy to skip over the problems of novices found in the Newbie section. But like the old story of the Emperor’s New Clothes, a new set of eyes can point out things we do not notice. Recently someone in the Newbie group wanted to set up a round robin for inserts over eight disks in an attempt to get 8x performance gain. Hmmm, if that did scale would it scale linearly(1) or close to it? Too bad I do not have the hardware or spare cycles to give it a try.
Or question on how to remove a ‘width=xxx’ pattern from a text field. Hmm, the old GIGO rules would dictate that some sort of ETL tool or script remove the unwanted chaff before it goes into the database. Do you use regular expressions in a PHP, Perl, or Java hack? Could a column oriented editor let you WYSIWYG the data? Maybe a trigger on insert that culls the unwanted width data? Hey, does your data have anything hidden in a text filed that should not be there and how best to get rid of it?
So read through a few forums and use them to exercise you mind. And if you see something in a question that you can answer, please do so.
Nothing scales linearly that you need to scale linearly. At least not on your budget and or schedule.
-
Liveblogging: Performance is Overrated, by Mark Callaghan
Mark Callaghan speaks at the New England Database Summit about how data manageability is more important than performance.
Peak performance is thrown out, 95%-98% is important.
Variance shouldn't be large.
Data manageability is rate of interrupts per server for the operations team. Rate of server growth much bigger than rate of new hires for the systems teams. A lot of the db team is from University of Wisconsin-Madison!
Why MySQL? Because it was there when he came. Mark and ops/engineering peers made it scale 10x. He likes MySQL for OLTP, InnoDB is "An amazing piece of software."
They can get 500,000 qps using a cached workload, which is on par with memcached.
What Facebook really does is OLTP for the social graph. The workload is secondary indexes, index-only queries, small joins but most queries use one table, multi-row transactions, majority of workload does not need SQL/optimizer, they do a physical and logical backup.
Most of this does not require SQL [blogger's note - they built Cassandra]. Why is the grass greener on the other side? automated replacement of failed nodes, less downtime on schema changes and/or fewer schema changes, multi-master, better compressions, etc.
Circa 2010, 13 million queries per second, 4 ms reads, 5 ms writes, 38GB peak network per second, etc.
Why so many servers? Big data high queries per seconds. They add servers to add IOPS, so they're interested in compression and flash, so they can get more IOPS. If they do remain on disk, write-optimized dbs are interesting too. About 10 people on the db team, which is very small for a company that size.
How to scale MySQL? Fix stalls to make use of capacity, improve efficiency to use fewer queries/fewer data. Fixing stalls doesn't make MySQL faster, makes it less slow.
[blogger's note - I stopped taking notes here because this is a rehash of the "How Facebook Does MySQL" talk that has been done over and over...]
[restarted when he started talking about data manageability again]
How Facebook got it's data manageable.
pylander- sheds load during a query pileup - kills dup queries, limits # of queries from some specific accounts -- take off on Highlander: there can be only one.
dogpile - collects data during a query pileup - gets perf counters and list of running queries, generates HTML page with interesting results.
Online schema change tool, for frequent schema changes, especially adding indexes. This briefly locks the table, to setup triggers to track changes, copy data to a new table with the new desired schema, replay changes to the new table, then briefly lock the table again as you rename the new table as the target table.
Manageability is a work in progress -- working on:
- make InnoDB compression work for OLTP
- Faker - tool for prefetching for replication slaves - replay workload is: page read, do some modification, page write. bottleneck might be disk reads, work is done by a single thread, transactions on master are concurrent. Faker has multiple threads replay transactions in "fake-changes" mode, no undo, no rollback, read-only, fetches into the buffer pool the pages needed for that transaction. Captures about 70% of disk reads for replication, they're working on fixes to get it up to 80-90%.
- auto replacement - replace failed and unhealthy MySQL servers.
- Auto resharding - sharding is easy, re-sharding is hard.
open issues in manageability:
diagnose why one host is slow, others are not.
....and some more.
-
Help Set the MariaDB 5.6 Roadmap with SkySQL and Monty Program
Part of every SkySQL subscription is paid to Monty Program to fund server development and improved functionality in MariaDB® (which is then sent upstream for inclusion in the MySQL® Server).
Thus, we'd like to know what you'd like to see in the upcoming releases of the server.
Please take a couple seconds (literally) and let your vote be heard:
http://www.skysql.com/content/new-server-functionality-have-your-say
We thank you and look forward to hearing your thoughts and ideas!
-
RMOUG Training Days 2012
The Rocky Mountain Oracle User Group ( RMOUG ) Training days are almost here. This year they are going to have a MySQL Oracle ACE Director, Ronald Bradford talk on MySQL Security Essentials. I will also be having a MySQL Crash Course . I was going to talk about replication but after attending the IOUG User summit and the seeing the vast interest in MySQL from Oracle users, I decided to take a step back and do this talk 1st. I have had a similar talk at a RMOUG meetup hands on labs in the past, but it has been updated.Alex Gorbachev of Pythian also has a session, Monitoring MySQL with Oracle Cloud Control 12c. Look forward to seeing everyone and I think great things are on the horizon with RMOUG and MySQL.
|
News Feeds 
The Tremont TapHouse considers itself the the city’s first Gastropub. Their definition, "gastropub is derived from London and depicts a casual pub that serves an upscale cuisine complimented by the assortment of wine and beer the tavern offers." The food is made from local ingredients, complex in ...Friday, 22 May 2009
The Old Angle is a traditional Irish bar. They have the best selection of Irish Whisky I have ever seen. The owner Alex Gleson can desribe the taste ot any Irish whisky to you better the most most Sommeliers can descibe their wine lists. He knows every detail down to where the distelery gets their c...Thursday, 02 April 2009