MySQL
Translated books in Chinese
I have just received copies of my Effective MySQL series that are available in Chinese. The picture speaks more than words.
SQL, ANSI Standards, PostgreSQL and MySQL
I have recently been working with the Donors Choose Open Data Set which happens to be in PostgreSQL. Easy enough to install and load the data in PostgreSQL, however as I live and breath MySQL, lets load the data into MySQL.
Understanding when EXPLAIN is not using an index as intended
When reading a MySQL Query Execution Plan (QEP) produced by the EXPLAIN command, generally one of the first observations is to validate an index is being used per table (i.e. per row of output).
MySQL Admin 101 for System Admins – key_buffer_size
As discussed in my presentation to NYLUG , I wanted to provide system administrations with some really quick analysis and performance fixes if you had limited knowledge of MySQL. One of the most important things with MySQL is to tune memory properly.
Improving performance – A full stack problem
Improving the performance of a web system involves knowledge of how the entire technology stack operates and interacts. There are many simple and common tips that can provide immediate improvements for a website.
Correctly setting your mysql prompt using sudo
If you run multiple MySQL environments on multiple servers it’s a good habit to set your MySQL prompt to double check which server you are on. however, using the MYSQL_PS1 environment variable I found this does not work under sudo (the normal way people run sudo).
What is FTS_BEING_DELETED.ibd
I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file. schema/FTS_00000000000001bb_BEING_DELETED.ibd The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
Good Test Data
Over the years you collect datasets you have created for various types of testing, seeding databases etc. I have always thought one needs to better manage this for future re-use. Recently I wanted to do some “Big Data” playing and again that question of what datasets can I use let me to review the past collated list at Seeking public data for benchmarks .
The GRANT/REVOKE dilemma
It is common practice to grant your application the privileges of “GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO user@host”. But what if you want to ensure you cannot DELETE data from just one table?
Unexplained halts using mysql command line client
I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging. A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.
Giving thanks to MySQL authors challenge
Next week the US celebrates Thanksgiving Day. For those that are American or live here, this is a significant event. Three different experiences recently have lead me to write this request for ALL MySQL community members to give thanks to those that have contributed to the MySQL ecosystem.
Kick all the tires before you buy the product
Translating theory to practice is never easy. Morgan gives us the right steps in a play environment to move from dev.mysql.com native MySQL rpm’s to the new MySQL yum repository. I thought I would try it out.
What SQL is running in MySQL
Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.
Monitoring an online MySQL ALTER TABLE using Performance Schema
Recently a client asked me how long it would take for an ALTER TABLE to complete. Generally the answer is “it depends”. While this was running on a production system I tried with the Performance Schema in MySQL 5.