Planet MySQL

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.

For example, suppose you have two transactions which run in parallel:

Transaction T1:

BEGIN; SET @t = NOW(); UPDATE xtradb_table SET a= @t WHERE id = 5; UPDATE pbxt_table SET b= @t WHERE id = 5; COMMIT;

Transaction T2:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT t1.a, t2.b FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id WHERE t1.id = 5;

In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in PBXT tables.

Naturally, it would be much better if the changes were visible no matter the storage engine used by the table. Thankfully this is possible, thanks to the introduction in MariaDB 5.3 of group commit and an enhanced storage engine API for COMMIT which allows storage engines to coordinate commit ordering and visibility with each other and with the binary log.

With these improvements, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement in MariaDB 5.3 has been enhanced to ensure consistency in-between storage engines which support the new API. Currently the storage engines which support this are XtraDB (MariaDB's enhanced version of InnoDB) and PBXT. In addition, the binary log, while not a storage engine as such, also supports the new API and can provide a binlog position consistent with storage engine transaction snapshots.

This means that with transaction isolation level at least REPEATABLE READ, the START TRANSACTION WITH CONSISTENT SNAPSHOT statement can be used to ensure that queries will see a transaction-consistent view of the database between storage engines.

No longer is it possible for a query to see the changes from some transaction T in XtraDB tables without also seeing the changes T makes to PBXT tables.

More information on this can be found in the AskMonty Knowledgebase .

PlanetMySQL Voting: Vote UP / Vote DOWN

Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th. Early bird pricing ends March 12th, 2012.
Use code PL-pod and save 10% off the early bird prices!.

The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies will be held in Buenos Aires in June. It is called the MariaDB NoSQL & Cloud Latin American Conference and we'll bring you more information as it becomes available.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

1. Nothing scales linearly that you need to scale linearly. At least not on your budget and or schedule.

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

PlanetMySQL Voting: Vote UP / Vote DOWN

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!

PlanetMySQL Voting: Vote UP / Vote DOWN

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.
PlanetMySQL Voting: Vote UP / Vote DOWN

According to VentureBeat*, games companies raised a record-breaking $1.54 billion in funding last year and social gaming accounted for over half of that. No wonder everyone wants to have a piece of that pie!

With the arrival of social network platforms, the gaming industry has seen an explosion in casual and social gaming. The social gamer represents a massive audience that cuts across all age, gender and demographic boundaries. Online social games are some of the most demanding applications in the world, with millions of users, stringent response times, complex simulation models and billing requirements. Games take years to develop for a reason ...

Online social games are data-driven applications, and databases are central to these applications. However, there is no single database architecture that will fit the different types of data that the application needs to store. A data management architecture needs to account for the diversity of data, and optimize for some of the differences in the datatypes. E.g. it is ok to lose leaderboard data during a game as it can be reconstituted, whereas billing data needs to be 100% ACID.

Therefore, with the generous contribution of Joshua Butcher, we just published a whitepaper that discusses the different types of data stored for various functions in social gaming. We will see that there cannot be a one-size-fits-all approach to database architecture, and suggest a sharding strategy based on schema partitioning.

With our new whitepaper, we’re also starting a discussion on what the database of choice might be for anyone wanting to develop online social games. With so many NoSQL databases now available, one might wonder why MySQL would be a good database choice for the gaming industry. To find out, download our whitepaper today!

If you have any questions or comments, feel free to reply to this blog below or reach out to us on Facebook, LinkedIn, Xing, Twitter or directly via these contact details.

* http://venturebeat.com/2012/01/06/deanbeat-game-companies-raised-a-record-breaking-1-55b-in-2011/
PlanetMySQL Voting: Vote UP / Vote DOWN

Some guidelines for MySQL security
PlanetMySQL Voting: Vote UP / Vote DOWN

Event scheduler in MySQL 5.1
PlanetMySQL Voting: Vote UP / Vote DOWN

Accidentially I came across the statement “SHOW GRANTS requires the SELECT privilege for the mysql database.” in MySQL  documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).

It is not quite true. Any user can “SHOW GRANTS [FOR himself]” with no privileges at all. But more important: SELECT priviege is requried on database-level,  Privilege to the privileges tables is not enough.  See

SHOW GRANTS;
/*returns

Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO ‘me’@'%’
GRANT SELECT ON `mysql`.`user` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`tables_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`procs_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`db` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`columns_priv` TO ‘me’@'%’*/

SHOW GRANTS FOR root@localhost;
/*returns

Error Code: 1044
Access denied for user ‘me’@'%’ to database ‘mysql’ */

Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths)  there is no option to specify another database for the log tables.

I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like

UPDATE `identity`SET  `bank_account_no` =  ….. WHERE social_security_id = ….. ;

You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.

I don’t claim all applications would send such statements (but I believe that some will do).  You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don’t need to ‘ping-pong’ data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).

So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)

I posted this bug report: http://bugs.mysql.com/bug.php?id=64215

Tweet
PlanetMySQL Voting: Vote UP / Vote DOWN

I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. … Continue reading →
PlanetMySQL Voting: Vote UP / Vote DOWN

With new year many new projects, new technologies, new frameworks and new ideas are springing up at the speed of light and bloggers in the database arena are keeping up with this pace and this Log Buffer Edition is also living up to that pace and covers some of those posts in Log Buffer #257. [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

Collaborate 2012 MySQL Sessions

Please mark you calendars for the MySQL sessions at Collaborate this April in Las Vegas.

Date Session ID Session Details Track Sun. Apr. 22 4:30 pm – 5:30 pm 9390 The Essentials of Data Discovery: Do you Know Where Your Data Is? Asset Lifecycle Management Mon. Apr. 23 9:45 am – 10:45 am 826 Virtualization Boot Camp: Virtualizing Oracle On VMware – Quick Tips Database Mon. Apr. 23 12:15 pm – 12:45 pm 554 Set Up MySQL In Five Minutes Flat (2-Part Session) Database Mon. Apr. 23 1:15 pm – 2:15 pm 578 Shell Scripting for MySQL Administration Database Mon. Apr. 23 2:30 pm – 3:30 pm 681 Portable SQL between Oracle and MySQL Development

Mon. Apr. 23 3:45 pm – 4:45 pm 429 MySQL High Availability Solutions and case studies Database

Mon. Apr. 23 5:00 pm – 6:00 pm 436 Making Sense of Parent-Child Relationships – The Oracle Kind, Not the Human Kind BI/Data Warehousing/EPM

Mon. Apr. 23 5:00 pm – 6:00 pm 493 Administering MySQL for Oracle DBAs Database

Mon. Apr. 23 5:00 pm – 6:00 pm 9050 Training? Where do I even begin? Professional Development

Tue. Apr. 24 8:00 am – 9:00 am 281 Database Development Boot Camp: SQL Tips, Techniques and Traps to Avoid BI/Data Warehousing/EPM

Tue. Apr. 24 8:00 am – 9:00 am 729 Challenges of Big Databases with MySQL Database

Tue. Apr. 24 12:00 pm – 12:30 pm 389 MySQL for Oracle DBAs or How to Speak MySQL for Beginners (2-Part Session) Database

Tue. Apr. 24 12:00 pm – 12:30 pm 1554 Set Up MySQL In Five Minutes Flat (2-Part Session) Database

Tue. Apr. 24 2:00 pm – 3:00 pm 694 Security Boot Camp: Avoiding SQL Injection: Don’t Let a Stranger “Shoot You Up” Development

Tue. Apr. 24 2:00 pm – 3:00 pm 809 Demystifying MySQL for Oracle DBAs and Developers Database

Tue. Apr. 24 4:30 pm – 5:30 pm 362 Security Boot Camp: Security Around MySQL Database

Wed. Apr. 25 12:00 pm – 12:30 pm 1389 MySQL for Oracle DBAs or How to Speak MySQL for Beginners (2-Part Session) Database Thu. Apr. 26 9:45 am – 10:45 am 9395 Understanding the ROI of Archiving: Why Data Archiving is a Must do for 2012 Application Strategy and Services


PlanetMySQL Voting: Vote UP / Vote DOWN

Schooner has a blog post showing that one node of their product beats 9 nodes of Clustrix’s in throughput. But this reduces everything to a single number, and that’s not everything that matters. If you’ve looked at Vadim’s white paper about Clustrix’s (paid-for) performance evaluation with Percona, you see there is a lot of detail about how consistent the throughput and response time are.

I’d love to see that level of details in any product comparison. A single number often isn’t enough to judge how good the performance is — fast is not the only thing that matters.

I have absolutely no doubts that a single node of Schooner’s product can run like a deer. It isn’t doing any cross-node communication, after all, so it had better be faster than something that blends multiple nodes together into a virtual “single database server.” And I think if the full story were told, it would be a great knock-down drag-out fight. Give us more details, Schooner!

Further Reading:

• High Performance MySQL, Second Edition: Query Performance Optimization
• An ongoing thread of blogs on MySQL performance
• Status update on High Performance MySQL
• High Performance MySQL Third Edition pre-order available
• Sessions of interest at the Percona Performance Conference

PlanetMySQL Voting: Vote UP / Vote DOWN