Hot answers tagged mariadb
18
Try this:
$ ps -ef|grep [m]ysql
Identify the process id then
$ strace -cp <pid>
Leave it 10 seconds or a minute then ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seen read and write dominate.
9
There is no reason to speculate whether Oracle "will" continue developing MySQL actively. The facts are easy to find out: look at the changelog and preview release announcements. The truth is that Oracle has accelerated MySQL development faster than it's ever happened before, and the releases are really good quality too, unlike Sun's 5.1 release or MySQL ...
8
I think now is the time to play with MariaDB (just like now is the time to play with anything like this). Personally, I would hold off until I know a few more devs who have deployed MariaDB onto a live customer site before doing it myself.
Some situations could call for a change of application but something as serious as changing your database engine is not ...
6
One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3
Examples on improvements/features are "Index Condition Pushdown", "Cost-based ...
6
Here is the first thing I thought about
Setup 3 DB servers (identical HW/OS configs) each installed with
MySQL
Percona
MariaDB
Get a Fourth Server installed with MONyog (eval version lasts 30 days)
Register the 3 DB servers in MONyog
Use the Performance Metrics Charts in MONyog or Set Up Your Own Charts in MONyog
Use SysBench against all 3 DB Servers ...
5
Try escaping it with backticks?
GRANT ALL ON `my-database`.* TO `my-user`@`10.0.0.1` IDENTIFIED BY 'password';
Also, if you already have a user my-user@10.0.0.1, you don't need to provide the IDENTIFIED BY... portion of your grant statement
5
Are you sure that the tables where you are reading in are without triggers and indexes and constraints?
What hardware and OS are you running on?
How is your storage configured?
I am more familiar with oracle but 12G importing on tables without triggers, indexes and constraints should easily go with 200GB/h. One single trigger can make the process to a ...
5
This (orders_products) is a many-to-many table. I think it's common to have 2 composite indexes on such tables as it helps in many common queries.
I would definitely add two (unique) indexes, on (orders_id, products_id) and on (products_id, orders_id).
Not sure if defining them both as UNIQUE would be a further improvement in MariaDB's optimizer.
And if ...
4
Here is a VERY casual explanation...
MySQL works like this:
You send it a query that has a result set larger than its buffers, so it creates a temp table on disk to hold the data. That is an expensive process as it is usually reading from, swapping and writing to the same disk, unless purposely configured differently. Nonetheless, performance tanks. So now ...
4
Are you sure that terminating the client killed the PHP process that is running the INSERTs? if it is using a "new" connection for each call and relying on some sort of connection pool for efficiency, then is might just shuffle onto the next connection available in the pool when you kill the one currently being used.
4
Do you have any InnoDB tables with a Primary Key
containing multiple columns ?
having a wide VARCHAR ?
and a lot of non-Unique indexes ?
one or more non-Unique indexes that has a wide key ?
Any of these conditions can probably cause large BTREE nodes in your indexes to have very few leaves in each BTREE node. The cluster key in the Primary Key is also ...
4
This should be very helpful to you as this is the defacto industry benchmark for databases, TPC-C, http://www.tpc.org/tpcc/spec/tpcc_current.pdf
Even if you do not use execute this benchmark, the information contained within the test definition document should provide you with tons of insight into setting up your own benchmark for testing your database ...
4
Here is a query I wrote up to give you the most recent thursday and the ending wednesday
SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT ...
4
In general, MariaDB is a drop-in replacement for MySQL. It says so on their home page. :-) Your applications won't notice a difference.
For specialized applications and edge cases, there may be difference. But these are going to be tuned to use special features of the newer versions of MySQL.
You may also want to look at Percona Server as it tracks the ...
4
I'm pretty sure the problem is the "partial" index:
CREATE TABLE `smalltable` (
...
KEY `ix_name` (`name`(10))
)
Try running the query after adding an index on the full length of the column:
ALTER TABLE smalltable
DROP INDEX ix_name
, ADD INDEX ix_name_full (name) ;
4
If you're using MySQL with InnoDB tables, then you could take incremental backups with Percona's xtrabackup, or talk to Oracle about enterprise licensing, which would net you MySQL Enterprise Backup.
Xtrabackup is a brilliant piece of software, but it behaves very differently from traditional MySQL backups using mysqldump, and it's worth spending some time ...
3
If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE statement, your database size will reduce, but the ibdata file will remain the same (not reduce).
If you are not using innodb_file_per_table option, the only way to reclaim the space is by dumping the database and restoring from the dumpfile.
However, ...
3
It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions:
Why still using MyISAM ?
MyISAM still has ...
2
I learned something interesting...
Baron Schwartz seems very confident that MySQL will stay strong for a while. Personally, I would not jump off the MySQL train just yet, either. Since MySQL (eh, Oracle [that just doesn't roll off the tongue, and it never will]) continues to be supported, this would give others such as Monty and Percona a chance to keep ...
2
MariaDB developers claim that it's a drop-in replacement, and it's true until version 5.5.
MariaDB 5.1-5.2-5.3 can replace MySQL 5.1.
MariaDB 5.5 can replace MySQL 5.5
The small "incompatibility" issues usually don't apply, however, they are documented here:
https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/
There are also a lot of bug fixes and ...
2
What does STRAIGHT_JOIN give you if you rewrite the query:
SELECT bt.col1,bt.col2
FROM smalltable st
STRAIGHT_JOIN bigtable bt ON bt.smalltable_id = st.smalltable_id
WHERE st.name = 'some name occuring only once in st'
LIMIT 10
I'm mostly curious to see if that is a faster plan, or if MySQL optimizer is 'right' to read in the bigtable rows first.
To ...
2
I try to insert a record with null sample_id
`sample_id` int unsigned NOT NULL,
The sample_id can't be null. You will probably find NEW.sample_id = 0 inside the trigger if you don't specify a value on insert, since you have declared the column as NOT NULL... so you need to test for that instead:
IF NEW.sample_id = 0 THEN
...
...or change your ...
1
mysqldump actually has an option to run FLUSH PRIVILEGES; after the dump is completed
[root@**** ~]# mysqldump --help | grep privileges
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
flush-privileges FALSE
In light of this you would run
mysqldump --all-databases --flush-privileges > dumpfile
Running ...
1
After reading this (thanks @ypercube)
MySQL : Why are there "test" entries in mysql.db?
I removed the test entries from mysql.db and it fixed it. I then realized that all the test I'd ran were using database named like test_xxx. Creating database with a name not beginning in 'test_' was not working as expected.
1
I see two things you can do
USE A BETTER INDEX
Instead of forcing an index on bigtable, make the following index on smalltable
ALTER TABLE smalltable ADD INDEX name_smalltable_id_ndx (name,smalltable_id);
Since you posted the table structures, I can now see that smalltable is InnoDB. In light of this, there is no need for any additional index. You do ...
1
The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bud numbers opened for prior versions as well.
You can use:
CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, ...
1
You must remember that TRUNCATE TABLE is DDL not DML.
Rather than figuring out where in the plumbing of TRUNCATE TABLE it is getting stuck, you may just have to take matters into your own hands by replacing this
TRUNCATE TABLE sampledb.datatable;
with this
CREATE TABLE sampledb.datatablenew LIKE sampledb.datatable;
ALTER TABLE sampledb.datatable RENAME ...
1
Without further information it is difficult to say, and I'm not expert specifically on MySQL/MariaDB, but the general reasons for a query unexpectedly taking longer than usual to run are:
Locks on that table: the query could be sat waiting for other transactions, that hold locks on that table, to complete. In this case the statement will pause, using no ...
1
When using Apache, lower MaxClients to, say, 20. There is no advantage in having lots of Apache children stuck waiting for MySQL -- latency suffers and throughput does not improve.
Decrease @@global.wait_timeout -- why have idle connections clog the max_connections.
Clients should disconnect when they are through -- reconnecting is cheap.
If ...
1
IMHO there should be no real difference. However, let me add this:
I actually wrote something up on this a while back
How do I properly perform a MySQL bake-off?
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Query runs a long time in some newer MySQL versions
In my posts, I mentioned InnoDB 5.1 Plugin outperforming native InnoDB 5.1, 5.5, 5.6 ...
Only top voted, non community-wiki answers of a minimum length are eligible
