Entries tagged as mysql
- June, 2011
-
mysql startup debugging tip
If you are not able to start the mysql daemon repeatedly using your linux distribution init scripts and you are ready to pull out your hair in frustration, here is a tip that might help you in finding the problem.
Try running the mysqld_safe from the command line (without using the init scripts). Try running
/usr/bin/mysqld_safe -v, which should spit out some debugging information.If that fails, try calling the
mysqlddaemon directly from the command line, with the "-v" option .mysqldis usually present under/usr/sbin/mysqld.mysqldcan be called with--print-defaultsto get the command line options it would be run with./usr/sbin/mysqld would have been started with the following arguments: --user=mysql --pid-file=/var/run/mysqld/mysqld.pid ..... --max_binlog_size=100MTry adding the
-voption to these options to get more verbose details. When run from command line, mysqld will not detach from console and will print debugging info that might be useful in finding the cause of the error.I suggest using 3 terminals to figure out what is going on
- one terminal with tail -f /var/log/mysql/mysqld.err
- one terminal with tail -f /var/log/messages
- one terminal where I ran the
mysqld_safe/mysqldcommand
The init scripts are usually good for day to day work. However, sometimes the init scripts can impede a innodb crash recovery process on a large database. Some init scripts have timeout built into them and they can kill mysql while the innodb is still trying to recover its tables.
- March, 2009
-
optmizing ORDER BY in MySQL
A few days back we were trying to optimize a sql query that was using an ORDER BY clause. When we ran an EXPLAIN on the query, we saw that the query was Using filesort and Using temporary. Since this query was run quite frequently, there was a benefit in optimizing this.
I had been under the impression that if your query uses filesort and then has to use temp tables for sorting, you cannot do much to save the query. However, that is not the case. If you have an index on the column being used to do the order by , mysql will use that index for sorting. Of course, there are caveats to this, the most important one being
The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;Hence, if you have a query which requires to be sorted, and it is being run quite frequently, it makes sense to add an index on the column on which you are doing the sort.
Defined tags for this entry: geek stuff, mysql - October, 2008
-
Replication
Check the slides at scribd, Getting Smart about the New World of PostgreSQL Replication. The first few slides are not specific to PostgreSQL and they define some common replication jargon very nicely.
Defined tags for this entry: geek stuff, mysql - September, 2008
-
innodb and blobs
Are you planning on storing blobs in Innodb tables? If yes, think again. According to the manual,
InnoDB stores the first 768 bytes of a VARBINARY, VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.
This translates into slower retrieval for blobs. However, InnoDB can skip reading blobs if they are not in select column list.
One solution for this is to keep blobs in a separate myisam table and do a join when you need the blob column.
Defined tags for this entry: mysql -
mysql bugs
If you are a heavy user of MySQL, I think you will appreciate this list of saved bug searches. For example, check out "p1_bugs_in_5.1" or "ActiveInnoDBBugs" These saved searches are also available as rss feeds.
Defined tags for this entry: mysql -
precise explanation of Seconds_Behind_Master value
Thanks to Artem Russakovskii for this nice explanation of what Seconds_Behind_Master means
In order to see if a slave is lagging, execute the 'show slave status' command and look for the Seconds_Behind_Master value. The way this value is calculated can be slightly ambiguous and unclear, so I'll explain. It is simply a difference between the 2 timestamps the time of the last received (and queued up in the relay log) query that already executed on the master and the time of the currently executing query on the slave.
As he says in the post, this value is not a precise indication of the replication delay.
Links:
Defined tags for this entry: mysql - January, 2007
-
mysql slave problem
If you are seeing the line
received 0 length packet from server, apparent master shutdownin your mysqld.err file (when your mysql server is a slave), check the server-id of the machines which are replicating from the same server. Most likely, you will find that there are boxes which have the same server-id
Page 1 of 1, totaling 7 entries