Tag Info

Hot answers tagged

21

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.


13

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 ...


10

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 ...


9

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 bug 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, ...


7

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 ...


6

MariaDB can be just be plugged-in due to compatibility with MySQL. In addition, there are some good features for DB Administration (i.e. microsecond accuracy of query time instead of only seconds). I have done that in several Drupal site and have had no problems at all.


6

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


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

This is by design - all DBMS act this was with auto-increment columns. If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly ...


5

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 ...


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 ...


5

Because like any other optimization, it doesn't fit every workload. Galera can get overwhelmed by a high rate of transactions, or when transactions update many rows. It can also make your applications experience delays on COMMIT as the cluster is synced up. Galera doesn't update other nodes synchronously, either. It just transmits worksets synchronously. ...


4

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, ...


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 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

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

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 ...


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

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 ...


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 ...


4

The two queries are not logically equivalent. FROM contacts c LEFT JOIN `dnc_contacts` as B on c.`contact` <> B.`contact` This does not do what you think it does. This expression joins every row in c to every row in b except where contact is equal. 1000 rows in each table means ~999000 rows are being generated and compared. FROM contacts c ...


3

Postgres: The write ahead log(s), WAL; it's a complimentary component of your backup strategy. It's exactly what you want.


3

Answers are here in the MariaDB knowledge base: https://kb.askmonty.org/en/installation-issues-with-php5/ - basically, PHP has been compiled vs the MySQL 5.5 library and you get a version mismatch warning - this is not an error and you can still run your code normally though. There are a number of ways to solve that, the most obvious is using mysqlnd which ...


3

The standard streaming replication on PostgreSQL is single threaded and there is no way to change this. However the question is why would you want to? PostgreSQL's streaming replication works through the write ahead log which is kinda of like a set of instructions "change block 3525 to this", "change block 2424 to this", etc. This makes the replication ...


3

You missed one thing. query_cache_size | 0 To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0. https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html Although enabled, the query cache size is by default ...


3

In order to perform an upgrade with (almost) no downtime, you need to add some extra infrastructure for high availability. The most common way to perform that is with the help of MySQL built-in replication: Setup a slave with a clone of the data of the original master. It is technically possible to setup a master-slave replication within the same node by ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible