Tag Info

Hot answers tagged

22

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


12

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


10

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


9

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


8

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

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

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


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

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

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


6

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


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

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

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


5

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


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

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


5

The explanation here is related to the same situation found in the other question... the expression here is at least potentially ambiguous, so it can't reasonably be validated at define time. It could refer to something that's not yet defined. MySQL validates the syntax of stored procedures at define-time, but it doesn't validate anything that's ...


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

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

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

Your list of characters that must be supported clearly indicates you need nothing more than plain ascii. If you want to stored this as text, then this ascii is your most compact way. But here are a few clarifications: VARCHAR(10) does not "need" 80 bits. It may need 80 bits, if all characters are used, under ascii character set. If you only store 3 ...


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


4

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


4

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


4

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


4

That's a fairly serious defect. http://bugs.mysql.com/bug.php?id=55477 The lexer, encountering ! considers that the next character might be a continuation of the != operator, =, and if that happens, everything is fine. Otherwise, if the next character is of the same class (of characters which might be part of a logical operator, such as <) but doesn't ...



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