Category: MySQL

MySQL related posts

Misimproving performance problems with INSERT DELAYED

MySQL

INSERT DELAYED may come in handy when using MyISAM tables. It may in particular be useful for log tables, where one is required to issue frequent INSERTs on one hand, but does not usually want or need to wait for DB response on the other hand.

It may even offer some performance boost, by aggregating such frequent INSERTs in a single thread.

But it is NOT a performance solution.

That is, in a case I’ve seen, database performance was poor. INSERTs were taking a very long time. Lot’s of locks were involved. The solution offered was to change all slow INSERTs to INSERT DELAYED. Voila! All INSERT queries now completed in no time.

But the database performance remained poor. Just as poor as before, with the additional headache: nobody knew what caused the low performance.

Using INSERT DELAYED to improve overall INSERT performance is like sweeping the dust under the carpet. It’s still there, only you can’t actually see it. When your queries are slow to return, you know which queries or which parts of your application are the immediate suspects. When everything happens in the background you lose that feeling.

The slow query log, fortunately, still provides with the necessary information, and all the other metrics are just as before. Good. But it now takes a deeper level of analysis to find a problem that was previously in plain sight.

So: use INSERT DELAYED carefully, don’t just throw it at your slow queries like a magic potion.

mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports

MySQL

Revision 76 of mycheckpoint comes with quite a few improvements, including:

  • OS monitoring (CPU, load average, memory)
  • Auto-deploy
  • Improved charting
  • Brief HTML reports
  • 24/7 charts

OS Monitoring

When monitoring the local machine, mycheckpoint now monitors CPU utilization, load average, memory and swap space.

This only applies to the Linux operating system; there is currently no plan to work this out for other operating systems.

Examples:

mysql> SELECT os_cpu_utilization_percent FROM sv_report_chart_sample;

mycheckpoint-chart-cpu-sample
mysql> SELECT ts, os_loadavg FROM mycheckpoint.sv_report_sample;
+---------------------+------------+
| 2009-12-27 11:45:01 |       1.78 |
| 2009-12-27 11:50:01 |       2.48 |
| 2009-12-27 11:55:01 |       2.35 |
...
+---------------------+------------+
mysql> SELECT report FROM mycheckpoint.sv_report_human_sample ORDER BY id DESC LIMIT 1 \G
*************************** 1. row ***************************
report:
Report period: 2009-12-27 13:20:01 to 2009-12-27 13:25:01. Period is 5 minutes (0.08 hours)
Uptime: 100.0% (Up: 334 days, 06:37:28 hours)

OS:
 Load average: 1.67
 CPU utilization: 25.2%
 Memory: 7486.4MB used out of 7985.6484MB (Active: 6685.8906MB)
 Swap: 3835.2MB used out of 8189.3750MB
...

Auto-deploy

mycheckpoint now has a version recognition mechanism. There is no need to call mycheckpoint with the “deploy” argument on first install or after upgrade. mycheckpoint will recognize a change of version and will auto-deploy before moving on to monitoring your system.

Continue reading » “mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports”

Passwords which are bad for your health

MySQL

I’ve seen some passwords to take a few years from my life.

I mean, we all know about dictionary words, right? And we’ve all seen Spaceballs, right? But choosing 12345 as your password is not the only careless option: there are many more! The more I get familiar with user’s password, the more I see how so much alike they all are.

Let’s review some of the commonly used bad password practices:

  • Empty passwords. Need I say more? Apparently yes. So what if “there’s only access through firewall from our company’s IP”?
  • Dictionary passwords: real English words like ‘falcon‘ or ‘tiger‘. Don’t use these! These are the easiest to attack.
  • Well known words: how about ‘Gandalf‘? It’s not dictionary, but it’s popular enough to appear in any respectable list. For that matter, look at how well filtered passwords are on RedHat: you can’t choose a password which is a common first or last name in the US, Italy, or even Israel; which is great!
  • Common substitues: enough with ‘1nsi9ht‘ and ‘@dm1n‘! These are almost as easy to break as dictionary words; it’s just a matter of a few more combinations per word.
  • Keyboard clustered: say No! to ‘1qa2ws‘. Don’t use ‘$rty&*io‘. They seems to be random at first sight, but look for them on the keyboard: it’s just your common “how shall I create a password that’s so easy to remember I will never forget it?”. Now REPLACE(“remember”, “break”) and REPLACE(“never forget”, “always regret”).
  • Children’s names, birth dates, 123456, your car’s license plate number, your Yahoo! mail password, etc. etc. etc.

There are many guidelines for choosing strong passwords. And everyone seems to know about it. But I’m still surprised when I find out the MySQL root password is ‘zxcvbn‘ or ‘pa55wd‘.

MySQL allows for any character in your password, so you may use punctuations, spaces, and other symbols. This is stronger than plain characters and digits.

Continue reading » “Passwords which are bad for your health”

New and noteworthy in mycheckpoint (rev. 57)

MySQL

Rev. 57 of mycheckpoint has been released and is available for download.

New and updated in this revision:

Remote host monitoring

It is now possible to monitor one host, while writing into another. Either or both could be remote hosts:

mycheckpoint --host=localhost --monitored-host=192.168.10.178

The above monitors the MySQL server on 192.168.10.178, and writes down to localhost (to be queried later)

mycheckpoint --monitored-host=127.0.0.1 --host=192.168.10.178

The above monitors the MySQL server on 127.0.0.1, and writes down to 192.168.10.178.

Continue reading » “New and noteworthy in mycheckpoint (rev. 57)”

In favour of a milestone based release model

MySQL

I like milestone based release models.

The advantages I find in this model are in particular beneficial for MySQL. What I find good about this model are:

  • Things are unstable for shorter periods. Even if some feature is not full stable in some milestone, the model encourages that such a feature is fixed on higher priority.
  • It is easy to create a priority ranking for new features. Moreover, priorities are expressed more by chronological time of development, less by “how many people are working on it”.
  • The model pushes towards rapid development, since you can’t release M5 before M4 is complete.

The last versions of MySQL took long time to complete. Take 5.1, for example: partitioning and event scheduling were long considered GA before row-based replication was half stable. Consider the so small but useful sub-second slow logs; the variables made dynamic in 5.1 (slow log again, for example); the new INFORMATION_SCHEMA tables.

Continue reading » “In favour of a milestone based release model”

Useful temporal functions & queries

MySQL

Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations.

There are many ways to solve such problems. I’ll present my favorites.

Querying for time difference

Given two timestamps: ts1 (older) and ts2 (newer), how much time has passed between them?

One can use TIMEDIFF() & DATEDIFF(), or compare two UNIX_TIMESTAMP() values. My personal favorite is to use TIMESTAMPDIFF(). Reason being that I’m usually interested in a specific metric, like the number of hours which have passed, or the number of days, disregarding the smaller minute/second resolution. Which allows one to:

SELECT TIMESTAMPDIFF(HOUR, ts1, ts2)

Take, for example:

SELECT TIMESTAMPDIFF(MONTH, '2008-10-07 00:00:00', '2009-12-06 00:00:00')

The function correctly identifies the number of days per month, and provides with 13, being the truncated number of full months.

Doing arithmetics

One can use TIMESTAMPADD(), or DATE_SUB(), but, again, when dealing with specific resolutions, I find “+ INTERVAL” to be the most convenient:

SELECT ts1 + INTERVAL 10 HOUR

Continue reading » “Useful temporal functions & queries”

On restoring a single table from mysqldump

MySQL

Following Restore one table from an ALL database dump and Restore a Single Table From mysqldump, I would like to add my own thoughts and comments on the subject.

I also wish to note performance issues with the two suggested solutions, and offer improvements.

Problem relevance

While the problem is interesting, I just want to note that it is relevant in very specific database dimensions. Too small – and it doesn’t matter how you solve it (e.g. just open vi/emacs and copy+paste). Too big – and it would not be worthwhile to restore from mysqldump anyway. I would suggest that the problem is interesting in the whereabouts of a few dozen GB worth of data.

Problem recap

Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?

Let’s review the two referenced solutions. I’ll be using the employees db on mysql-sandbox for testing. I’ll choose a very small table to restore: departments (only a few rows in this table).

Security based solution

Chris offers to create a special purpose account, which will only have write (CREATE, INSERT, etc.) privileges on the particular table to restore. Cool hack! But, I’m afraid, not too efficient, for two reasons: Continue reading » “On restoring a single table from mysqldump”

questions or queries?

MySQL

I’ve hit a recent change which took me by surprise.

I was used to checking for the ‘questions‘ global status variables to see the total amount of queries the server performs. So, for example, I could run com_select/questions to learn the SELECT ratio out of all queries.

Apparently, as of 5.0.72–5.0.76 & 5.1.31 this has changed. A new status variable was introduced, called ‘queries‘.

The change being? questions does not any longer indicate the number of queries the server has executed: only the number of queries requested by the client (so, calling on a stored routine only counts as 1, regardless of how many queries the routine executes). The new queries variable indicates the amount of server queries issued (see the 5.0 and 5.1 docs for details).

So, as of 5.0.72 or 5.1.31, the calculation should be com_select/com_queries (or com_select_diff/com_queries_diff) to learn the SELECT ratio of all queries. I learned this due to a bug report on mycheckpoint, which presented some 10265% SELECT ratio…

My take on this is that it could have been worked out differently: instead of changing the meaning of an existing variable, questions could have remained as it was, with the introduction of, say, client_questions, which would only indicate client number of issued queries.

I believe changing the meaning of status variables at such late versions (5.0.76 is quite late!) invites trouble: code that used to work on already then-stable versions (e.g. 5.0.51) would behave differently after upgrade. Such changes should best take place while still in BETA phase.