percona interview questions
Top percona frequently asked interview questions
I am trying to take backup from a remote machine using innobackupex.
./innobackupex --host=<mysql_server> --user=<username> <backup_dir>.
Its failing as it is unable to locate a directory in the server.
(Errcode: 2 - No such file or directory)
I am assuming it is searching for the directory in the local machine.
Please let me know how can we run the backup remotely ?
Source: (StackOverflow)
I am running a MySQL 5.5 on a x64 Windows Server and keep getting a auto increment id error which is a known error in MySQL. I would like to try out Percona Server or MariaDB as they have higher benchmarks but I can't find a drop in replacement for MySql 5.5. Can MariaDB 5.2 be used to replace MySQL 5.5 or is there a version of Percona Server that runs on Windows? Also is anyone else using either of these on Windows?
Source: (StackOverflow)
I am compiling Percona (MySQL variant) on my Raspberry Pi, which has an ARM processor.
I've encountered an issue during compilation that has been reported, but nobody is willing to fix because ARM is an unsupported platform.
https://bugs.launchpad.net/percona-server/+bug/1002848
I've managed to work around the issue and successfully compile, but my knowledge of c++ is somewhat lacking and I don't fully understand if I have actually broken something or not.
I've read through lots of the invalid conversion from const char* to char* questions on SO, which is where I got the idea for this workaround.
The error was as follows:
error: invalid conversion from 'const pthread_mutex_t*' to 'pthread_mutex_t*'
(it actually wasn't pthread_mutex_t on my platform, but the issue is the same - actual type lost to the infinite abyss that is the scrollback buffer)
The offending code was:
uint32 count(uint index) const
{
my_atomic_rwlock_rdlock(&time_collector_lock);
I changed this to:
uint32 count(uint index) const
{
my_atomic_rwlock_t dummy = time_collector_lock;
my_atomic_rwlock_rdlock(&dummy);
time_collector_lock is defined as:
private:
my_atomic_rwlock_t time_collector_lock;
Due to the fact this is supposed to be a mutex, I have a feeling I've probably made this non-thread-safe. Or is this ok?
Is there a better solution?
Source: (StackOverflow)
In the command line, this will successfully update table1:
pt-table-sync --execute h=host1,D=db1,t=table1 h=host2,D=db2
However if I want to update more than one table, I'm not sure how to write it. This only updates table1 as well and ignores the other tables:
pt-table-sync --execute h=host1,D=db1,t=table1,table2,table3 h=host2,D=db2
And this gives me an error:
pt-table-sync --execute h=host1,D=db1 --tables table1,table2,table3 h=host2,D=db2
Anyone have an example of how to list the '-tables'... so that it successfully update all the tables in the list?
Source: (StackOverflow)
We have been experiencing an occasional massive slow down in UPDATE performance across our database.
For example, the table FooTable we have about 40 columns with a varchar PK in addition there are 10 indexes. The following query took 44 seconds, while at other times it run's virtually instantly. During the slow down the load average on the server is quite low (1.5 for the 5 minute average) and IO as per vmstat is fairly reasonable as well.
Here an an example:
mysql> update FooTable set BarColumn=1349981286086 where varcharPK='e4348411-0fbb-460a-80f7-f1de304a9f7c'
Query OK, 1 row affected (44.94 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show profile for QUERY 1;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000030 |
| checking permissions | 0.000004 |
| Opening tables | 0.000007 |
| System lock | 0.000003 |
| Table lock | 0.000003 |
| init | 0.000035 |
| Updating | 44.949727 |
| end | 0.000006 |
| query end | 0.000003 |
| freeing items | 0.000115 |
| logging slow query | 0.000002 |
| logging slow query | 0.000052 |
| cleaning up | 0.000003 |
+----------------------+-----------+
13 rows in set (0.02 sec)
For what it's worth the example query above was run on a InnoDB table that was 'rebuilt' (ALTER TABLE FooTable ENGINE=InnoDB;) less then a week ago. I initially suspected that this was related to InnoDB's known performance issues with varchar/non sequential PKs, however we have other tables that use sequential PKs and have seen the same issues.
This is on a production server:
Centos 5 2.6.18-238.19.1.el5 x86_64
MySQL/Percona 5.1.57-rel12.8-log
96GB of memory with 58.8G of data spread throughout 87 tables
The relevent InnoDB settings are as follows:
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 70G
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_thread_concurrency = 0
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 64
innodb_write_io_threads = 64
optimizer_search_depth = 0
innodb_file_format = barracuda
I am not using FORMAT=COMPRESSED on this table, however I am on others.
Any suggestions on how to figure out what is going on in the Updating phase that is taking so long?
Source: (StackOverflow)
I'm not sure if stack is the right place to ask this, but I recently upgraded to Percona 5.6 from 5.5 and my memory usage has skyrocketed!
This is from PS:
mysql 4598 0.0 29.5 1583356 465312 ? Sl Oct17 9:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6
I'm on a dedicated VSS
My server only has a gig of ram...how is this only memory usage only 30% according to PS?
I have my ram set in the config to be less than this, and when I run MySQLTuner i get:
[OK] Maximum possible memory usage: 338.9M (22% of installed RAM)
So how am I using almost 500MB of physical memory and over a gig and a half of virtual?
Is this a bug in mySQL or something with my server?
Source: (StackOverflow)
Using MySQL 5.5 on Arch Linux, when I create a localhost user with a password and give it all privileges to all tables and then delete that user, I can still login as that user without typing in a password.
Steps to reproduce:
# mysql -u root -h localhost -p
Enter password: <root password>
mysql> create user 'test'@'localhost' identified by 'testing123';
mysql> grant all on *.* to 'test'@'localhost' identified by 'testing123';
mysql> select * from mysql.user where user='test';
1 row in set (0.00 sec)
mysql> exit
# mysql -u test -h localhost -p
Enter password: testing123
mysql> show databases;
mysql> exit
# mysql -u root -h localhost -p
Enter password: <root password>
mysql> delete from mysql.user where user='test';
Query OK, 1 row affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
mysql> select * from mysql.user where user='test';
Empty set (0.00 sec)
mysql> exit
# mysql -u test -h localhost
mysql> (Why?)
Not only that, but the "non-existing" test user can still exercise all of the same privileges. Major security problem. If I restart the server, it still lets me login without a password.
Source: (StackOverflow)
How do I do a CREATE INDEX with Percona's pt-online-schema-change tool? I want to do something like:
CREATE UNIQUE INDEX idx_name ON table_name (col_1, col_2, ...) USING BTREE
According to the documentation, I must use the --alter argument and then the appropriate ALTER TABLE statement, minus the preceding ALTER TABLE table_name phrase. However, CREATE INDEX does not start with ALTER TABLE, and the table name is embedded inside the CREATE INDEX statement. So how can I move forward?
Source: (StackOverflow)
I've a problem in joining new node to percona xtradbcluster.
here is my log :
I tested the test_sstuser working on honor without any problem .
but in new nodes I get these errors :
2014-05-31 04:01:40 2391 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (02dc9b38-e825-11e3-a67b-4bfddfde0c98): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():447. IST will be unavailable.
WSREP_SST: [ERROR] Error while getting data from donor node: exit codes: 1 0 (20140531 04:01:40.934)
WSREP_SST: [ERROR] Cleanup after exit with status:32 (20140531 04:01:40.936)
2014-05-31 04:01:40 2391 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.0.2' --auth 'test_sstuser:123456' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '2391' '' : 32 (Broken pipe)
2014-05-31 04:01:40 2391 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2014-05-31 04:01:40 2391 [ERROR] WSREP: SST failed: 32 (Broken pipe)
2014-05-31 04:01:40 2391 [ERROR] Aborting
2014-05-31 04:01:41 2391 [Warning] WSREP: 1.0 (test.host.com): State transfer to 0.0 (master-2.test.net) failed: -32 (Broken pipe)
2014-05-31 04:01:41 2391 [ERROR] WSREP: gcs/src/gcs_group.c:gcs_group_handle_join_msg():723: Will never receive state. Need to abort.
how can I solve this problem ?
Best regards
Ali
Source: (StackOverflow)
Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf.
However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. (query cache is disabled).
The only thing I run on the server is a Wordpress blog. My questions are:
- May I enable query cache?
- There are some Wordpress plugins that offer database cache. Is the result similar of enabling query cache?
Source: (StackOverflow)
Here's a crazy one:
mysql> CREATE FUNCTION PREG_REPLACE RETURNS STRING SONAME 'lib_mysqludf_preg.so';
ERROR 1125 (HY000): Function 'PREG_REPLACE' already exists
mysql> DROP FUNCTION preg_replace;
ERROR 1305 (42000): FUNCTION (UDF) preg_replace does not exist
Um.... that's actually quite funny....
The real problem is that the function is no longer recognized in queries. Tried re-compiling, re-installing, re-starting, etc. -- no joy. UDF is from here: http://www.mysqludf.org/lib_mysqludf_preg/index.php
This followed after switching to percona 5.5 from mysql. UDF worked fine in mysql.
Question is: How do I get the PREG UDF to work after upgrading from mysql to percona 5.5?
ANSWER: Here's the answer based on Baron's tip below:
From the mysql error.log:
120319 9:32:06 Percona XtraDB (http://www.percona.com) 1.1.8-rel24.1 started; log sequence number 1547303885
120319 9:32:06 [ERROR] Can't open shared library 'lib_mysqludf_preg.so' (errno: 0 /usr/lib/plugin/lib_mysqludf_preg.so: cannot open shared object file: No such file or directory)
Percona seems to look in a different directory than my standard MySql install.
MySql looks for all plugins in /usr/lib/mysql/plugin.
Percona was looking in /usr/lib/plugin
The solution was simple -- I just created a symlink in /usr/lib to the /usr/lib/mysql/plugin directory as follows:
me@host:/usr/lib/plugin$ sudo ln -s /usr/lib/mysql/plugin ./plugin
Viola! -- everything loads just fine now.
Source: (StackOverflow)
I can install MYSQL on Ubuntu without prompts with the code below:
dbpass="mydbpassword"
export DEBIAN_FRONTEND=noninteractive
echo mysql-server-5.1 mysql-server/root_password password $dbpass | debconf-set-selections
echo mysql-server-5.1 mysql-server/root_password_again password $dbpass | debconf-set-selections
apt-get -y install mysql-server
The part with the debconf-set-selections I got somewhere online (could be here can't remember) and it has worked ok for me thus far. I'm not that much of an expert to understand how it works but it does.
However, I want to do the same thing for Percona. I've setup the apt package manager to deal with using apt-get for percona. So now my code is the following:
dbpass="dbpass" && export dbpass
export DEBIAN_FRONTEND=noninteractive
echo percona-server-server-5.5 percona-server-server-5.5/root_password password $dbpass | debconf-set-selections
echo percona-server-server-5.5 percona-server-server-5.5/root_password_again password $dbpass | debconf-set-selections
apt-get -y install percona-server-server-5.5
However, Percona installs but without a password as defined. I know I'm missing something in the debconf bit.
I'd appreciate some guidance here.
Thanks in advance.
Source: (StackOverflow)
I've been a MySQL user for a long time now. I'd like to start using all the different variations between the most popular forks. The problem is that I can't find any good comparison between them, and I'll end up just trying all of them. My concerns are in terms of production, because I won't experiment with all these forks in production.
Have you ran any of MySQL forks in production?
What are the advantages? What are the disadvantages?
Source: (StackOverflow)
I have a problem with this query:
SELECT
uca.user_activity_id,
uca.user_call_id,
uca.call_activity_id,
uca.user_activity_token,
uc.call_group_id,
uc.user_id
FROM users_calls_activities uca
INNER JOIN users_calls_activities uca2 ON uca2.user_activity_id = uca.user_activity_is_validated_with
AND aux.user_call_id = 1744136
INNER JOIN users_calls uc ON uc.user_call_id = uca.user_call_id;
We have a cluster with percona server (5.6.29) with 5 nodes(from 0 to 4) in Azure. The difference between nodes 0-3 and 4 is that, the first ones are in a balancer and the node 4 is out of the balancer (but in the cluster)
The problem is that in four of the servers (nodes 0-3) the query is really slow (15 sec) and in the other one (node 4)the query is really fast (0,002)
Afaik, the explain plan should be the same but i execute an EXPLAIN and the result is this:
Nodes 0-3 (Slow)
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
| 1 | SIMPLE | uca2 | ref | PRIMARY,user_call_id,user_call_id_2 | user_call_id | 4 | const | 1 | Using index |
| 1 | SIMPLE | uc | ALL | PRIMARY,user_call_id | NULL | NULL | NULL | 2098152 | Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | uca | ref | user_call_id,user_call_id_2,is_validated_with | user_call_id | 4 | db.uc.user_call_id | 1 | Using where |
+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
Node 4 (Fast)
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
| 1 | SIMPLE | uca2 | ref | PRIMARY,user_call_id,user_call_id_2 | user_call_id | 4 | const | 1 | Using index |
| 1 | SIMPLE | uca | ref | user_call_id,user_call_id_2,is_validated_with | is_validated_with | 5 | db.uc2.user_activity_id | 2755595 | Using index condition |
| 1 | SIMPLE | uc | eq_ref | PRIMARY,user_call_id | PRIMARY | 4 | db.uca.user_call_id | 1 | NULL |
+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
I notice that in the slow one the index is not being used. so i checked the indexes:
Node 0:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_calls | 0 | PRIMARY | 1 | user_call_id | A | 2099153 | NULL | NULL | | BTREE | | |
| users_calls | 1 | call_group_id | 1 | call_group_id | A | 16659 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_call_begin_date | 1 | user_call_begin_date | A | 1049576 | NULL | NULL | YES | BTREE | | |
| users_calls | 1 | user_call_begin_date | 2 | user_call_end_date | A | 2099153 | NULL | NULL | YES | BTREE | | |
| users_calls | 1 | user_call_id | 1 | user_call_id | A | 2099153 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_call_id | 2 | user_id | A | 2099153 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 1 | user_id | A | 91267 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 2 | call_id | A | 2099153 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 3 | user_call_status | A | 2099153 | NULL | NULL | | BTREE | | |
| users_calls | 1 | fk_users_calls_calls | 1 | call_id | A | 23067 | NULL | NULL | | BTREE | | |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Node 4:
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users_calls | 0 | PRIMARY | 1 | user_call_id | A | 2091476 | NULL | NULL | | BTREE | | |
| users_calls | 1 | call_group_id | 1 | call_group_id | A | 26813 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_call_begin_date | 1 | user_call_begin_date | A | 1045738 | NULL | NULL | YES | BTREE | | |
| users_calls | 1 | user_call_begin_date | 2 | user_call_end_date | A | 2091476 | NULL | NULL | YES | BTREE | | |
| users_calls | 1 | user_call_id | 1 | user_call_id | A | 2091476 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_call_id | 2 | user_id | A | 2091476 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 1 | user_id | A | 53627 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 2 | call_id | A | 2091476 | NULL | NULL | | BTREE | | |
| users_calls | 1 | user_id | 3 | user_call_status | A | 2091476 | NULL | NULL | | BTREE | | |
| users_calls | 1 | fk_users_calls_calls | 1 | call_id | A | 15608 | NULL | NULL | | BTREE | | |
+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
My first question is why are the indexes different? it should be the same due to both are in the same cluster right?
Why the execute plans are different? Both are in the same cluster so, it should be the same
Should i use FORCE INDEX or STRAIGHT_JOIN?
Source: (StackOverflow)