Tag Info

Hot answers tagged

42

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways: Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly ...


32

INSIGHT INTO DOING BACKUPS WITH mysqldump IMHO Doing backups has become more of an art form if you know just how to approach it You have options Option 1 : mysqldump an entire mysql instance This is the easiest one, the no-brainer !!! mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz Everything ...


20

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

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling. In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an ...


12

mysqld will timeout DB Connections based on two(2) server options: interactive_timeout wait_timetout Both are 28800 seconds (8 hours) by default. You can set these options in /etc/my.cnf If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your ...


9

I do not think so. The query optimizer should be clever enough. You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case. About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it). Are there indexes on ...


9

Have a look at mysql replication master to slave . It allows you to clone the database of master to another database server with same database. That include the master and slave identities. Slave make itself the exact copy of the master database server and or its databases. There may be one-one, one-many, many-one relation among master(s) and slave(s). ...


9

Reposting my answer to a similar question regarding SQL Server: In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. So, to answer your question, ...


9

I provided links to tutorials. Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial. In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master. If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up. ...


8

You can let mysqldump create the dump in such a way that it does not create or select the database. EXAMPLE : You are dumping the database db1 and loading it into database db2 This will put in the CREATE DATABASE and the USE commands in the dump mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql This will not put in the ...


7

A few admin points first: Are you connecting to do an ftp or are you ssh'ed in and it's dying? If ssh, then be sure to use screen so that you can resume after the comcast crash. If ftp, then make sure you're compressing it/tar before the send. Also try the --opt parameter or --quick --opt This option turns on a set of additional options to make the ...


7

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup. 2) This page of the MySQL manual has instructions to convert table types. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or ...


7

Let me start by saying, I hate ALTER. It's evil, IMHO. Say, this is your current table schema - CREATE TABLE my_table_of_love ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, my_value VARCHAR(40), date_created DATE, PRIMARY KEY(id) ) ENGINE=MyISAM CHARSET=utf8; Here's the path I recommend - Create a new table object that will replace the old one: CREATE ...


7

The order of the rows in the absence of ORDER BY clause may be: different between any two storage engines; if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows". if the storage engine version is the same, but MySQL version is different, it might be ...


7

Just off the bat, newer versions of MySQL actually improve innodb performance (especially 5.5). I would highly recommend updating to this version if you're going to run InnoDB. One method you could use to hunt down why it's taking so much longer is using MySQL Profiles mysql> SET PROFILING=1; mysql> SHOW TABLES; mysql> SELECT * FROM foo; mysql> ...


7

IMHO You do not need to physically split it up. Yet, it would be nice to cache it. If the users table uses the MyISAM Storage Engine, you have a nice advantage. Since MyISAM only caches indexes, you could do two things You could create a custom key cache just to load MyISAM index for the users table only You could index the username and password to force ...


7

It is impossible. The reason? The binary logs are incompatible. To be more specific, the binary logs are not backwards compatible. Here is why: An empty binary in ... MySQL 5.0 and back is 98 MySQL 5.1 is 106 MySQL 5.5 is 107 I have made reference to this subtle difference in ServerFault ...


6

Plan A: See also Xtrabackup from Percona. This allows online backup of InnoDB, without any significant locking. Plan B: A Slave can be stopped, and you can take a consistent backup by any of several means (copy files, mysqldump, xtrabackup, etc) Plan C: LVM Snapshot. After some cryptic setup, the downtime for a backup is less than a minute, regardless of ...


6

There is a known issue in Windows, that when you push a large file to another server all the memory ends up getting allocated to the System cache instead of the user processes. You can look in the Physical Memory (MB) section of task manager to see how much memory is allocated to the system cache. This can be solved by backing up to a local disk, then ...


6

Using MySQL 5.5 out-of-the-box without proper configuration is like getting a Lamborghini and expecting topnotch performance on a gallon of regular gasoline (87 Octane). You should expect better performance with high octane gasoline in a Lamborghini. As with any database product, it is only as performance-enhanced as you actually configure it. Just like ...


6

There are several places in your code where you declare prepared statements using query strings you're crafting with CONCAT(). If any of the arguments to CONCAT() is NULL, the function's return value is also NULL... so it looks like at some point you're unintentially doing (effectively) this: mysql> SET @oops = NULL; Query OK, 0 rows affected (0.00 sec) ...


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

From the documentation: If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). MySQL cannot use an index if the ...


5

Other timeout settings that are general (not just for locks, since innodb_lock_wait_timeout only applies to InnoDB row locks) would be wait_timeout and interactive_timeout (both default to 28,800)


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

A table of a couple million rows shouldn't need to be split up. Performance tuning should be done through indexes. MySpace had hundreds of millions of accounts listed in a single table and performance on that table was just fine. (I was a DBA for MySpace at the height of their usage.) The table in that case was probably 80-90 bytes wide (maybe a little ...


5

This depends on the amount of MySQL Data and what storage engines your are using When it comes to MyISAM and InnoDB, they cache differently. I wrote a post about this back on April 14, 2011. Since your machine only has 768M of RAM you have to do a nice balancing act depending on which storage engine you are using the most. All MyISAM Please run this ...


5

Remove the HAVING COUNT(*) > 0. It's useless, no row will have a count of 0 after a group by. Change the GROUP BY to: GROUP BY client_id. Grouping by institution_id is not needed, you already have a WHERE condition that narrows it one value. As @HLGEM suggested, remove the select * and use a list of fields that you need. Right now you are repeating data ...


4

The first I thought about was what max_allowed_packet actually controls. Here is what I found: According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it: MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent ...


4

Sorry, that you had such an accident. If you have binary logs on, you could try the recovery options described here: Point in time recovery Another thing is to restore a backup, hopefully, you have one. Changes made after this, could be restored as mentioned above. Good luck! vorax



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