In my previous post, I talked about a funny replication breakage that I experienced with MariaDB. So what about different versions of MySQL... > SELECT version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) > SELECT * FROM test_jfg; +----+--------+-------------+ | id | status
10 Older Entries »
Recently, I was discussing with one of my colleagues about how insert statement performs for MySQL partitioned tables. General prediction is that it should be slower than for non-partitioned tables, but how much that we didn’t know. So, I thought let’s test with different types of partitions (i.e range, list and hash) and also with different number of partitions and check how’s performance. As people says, MySQL 5.7 is must faster than old one, so I also tested partitions with it.
So, I took simple table with 3.2M records on Centos 6.7 VM (4 core with 2GB RAM) with default my.cnf settings and then created tables for range, list and hash partitioning with 5,25,50 and 100 partitions. i.e with 5 partition (range and list), the table structures were like
CREATE TABLE emp_range_5( id int, fname varchar (30), lname varchar (30), hired_date date not null, separated_date date not null, job_code int, store_id int ) …[Read more]
When trying out new software there are many other questions you may ask and one of those is going to be the one above. The answer requires you to have built your software to capture and record low level database metrics and often the focus of application developers is slightly different: they focus on how … Continue reading Is MySQL X faster than MySQL Y? – Ask queryprofiler
Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host. But what about when you need visibility into privileges from the other direction – which accounts can access specific data? If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question. It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer. This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut.
Things to consider
There are a few things you’ll want to consider about the implementation of the MySQL privilege system as you try to sort out who has access to certain data.
MySQL can restrict privileges based on operations – somebody who has …[Read more]
MySQL Connector/Java supports connection attributes since version 5.1.25. This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments. One useful piece of information exposed is the version and vendor of the JVM in use by the client. This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.
The metadata including the Java runtime environment version and vendor can be found in PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table. Here’s the full contents of that table for a single connection from Connector/Java:
mysql> SELECT * -> FROM PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS -> WHERE processlist_id = 31\G *************************** 1. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _runtime_version ATTR_VALUE: …[Read more]
This week Oracle will release MySQL 5.7 GA, so
it’s a perfect time to do a quick review of the current state of
Percona Server 5.6.26, MySQL 5.6.26 and MySQL-5.7.8 RC. We used
two boxes from our benchmark lab for this:
– Box 1: 16 Cores+HT (32 virt cores)/fast PCIe ssd card/RAM: 192GB
– Box 2: 24 Cores+HT(48 virt cores)/fast PCIe ssd card/RAM: 128GB
Dataset: sysbench/uniform, 32 tables with 12M rows each,
Tests: sysbench – point select, oltp read only, oltp read/write
Test sequence: start server, warmup, series of the tests (each lasts 5 minutes) from 1 to 4096 threads
Tests were run for two setups:
– CPU bound (in memory) – innodb_buffer_pool_size=100GB
– IO bound – innodb_buffer_pool_size=25GB
Update 2016-01-30: restarting the IO_THREAD might be considered useful in some situations (avoiding MDEV-9138). Look for "in contrast, if the IO thread was also stopped first" in MDEV-6589 for more information.
In a previous post, I listed some sequences of commands that you should not run on a MariaDB slave that is lagging and which is using the GTID protocol. Those are the following (do not
Indexes are separate data structures that provide alternate
pathways to finding data. They can and do generally speed up the
processing of queries and other DML commands, like the
DELETE statements. Indexes are also
called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database
maintains the integrity of indexes after you create them. The
upside of indexes is that they can improve SQL statement
performance. The downside is that they impose overhead on every
DELETE statement, because the
database maintains them by inserting, updating, or deleting items
for each related change in the tables that the indexes support.
Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they …[Read more]
In my last Booking.com Hackathon, I worked on MyISAM vs InnoDB for data loading (LOAD DATA IN FILE) and bulk index creation. My motivation was the following: knowing that some are still using MyISAM for this particular use-case, I wanted to verify/understand if/why InnoDB is slower than MyISAM. I do not yet have complete results on this specific subject but I found some interesting things that
Thank you for attending my July 22 webinar titled “Advanced Query Tuning in MySQL 5.6 and 5.7” (my slides and a replay available here). As promised here is the list of questions and my answers (thank you for your great questions).
Q: Here is the explain example:
mysql> explain extended select id, site_id from test_index_id where site_id=1 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_index_id type: ref possible_keys: key_site_id key: key_site_id key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using index
why is site_id a covered index for the query, given the fact that a) we are selecting “id”, b) key_site_id only …[Read more]
10 Older Entries »