*** I wrote this in late September last year and it has languished in my draft folder. Man, I should pay more attention to such things!! Sorry for the delay.
A couple of people have written lately about the query cache.
Konstantin believes the query cache should be removed from the server. Mark didn’t make any recommendations one way or the other — although he did post tests that show that the performance can fluctuate quite a bit according to version. While I would absolutely say you should monitor and test your setup there are cases where it can make a dramatic difference. I just looked at a configuration on a client’s server where the query cache was enabled and serving over 3,000 queries a second from cache. Yes, from cache. In the same week I have seen another client’s configuration where the query cache was slowing things up and I recommended that the QC be turned off.
As with all configuration changes you make to a server, you should always monitor the results of the change. The results of your changes may or may not be what you expect. I for one would say the QC should stay right where it is .. turned off by default and available for your use if you so desire. Additional intelligence built into the query cache would be nice but it is useful in many cases as is.
I just wanted to let everyone know that due to the great response I have received the training class scheduled for January the 13th is full. I will planning on a training class with a different topic probably in March or April but if there is enough response I will redo the Backup and Restoration class in 2010.
On January the 13th at 8:00 pm Eastern time I will be holding a free online training session on what I consider to be the single most important topic for any database administrator – backups/restorations. While many other topics might be more sexy, nothing else matters if a database gets dropped for some reason or the server has a hardware failure. Never happened to you before? Well, stick around long enough as a database administrator and it will! And when stuff hits the fan you better be able to pull a good backup out and restore it in a timely manner. The class will run for approximately 2.5 – 3.0 hours and will cover a variety of methods used for backups and restores and the preparations needed for developing and executing a backup/restoration policy.
This will be hands-on training . . . not just a running through a slide deck. I learn by doing something and not just seeing it so I structure my training around this methodology. While training like this takes more time in both preparation and execution it is well worth it in terms of retention of material.
There are a very limited number of seats available so I need you let me know you are going to attend. Please RSVP to bmurphy AT paragon-cs.com with the subject “Backup Training Online Class”.
The next issue of Open Source Database Magazine is now available. This information-packed issue has over 60 pages of information including:
Firebird’s Road Trip and What’s New with 2.5
Coding Corner: Trees – Where’s the Performance?
PostgreSQL’s tsvector: Secret Sauce for Search Engines
The Lab: The XtraBackup Program for MySQL – Part Two
Drizzle – A Lightweight Database for the Web
Kontrollbase: Enterprise grade MySQL monitoring and analytics
Creating a Twitter Mashup with MongoDB
Introducing LucidDB
Plus the usual news and views. All of this for $4.95. It is simply the biggest and the best issue we have ever released. Ready to sign up? Head over here to register and then you can download the new issue. Curious as to what this is all about? I just posted an online addendum to the Drizzle article in our free content section that will give you a taste of what you can expect.
Thanks to the contributors. You all did a great job and I appreciate it!
If you experience any problems let me know at editor AT osdbzine.net.
when this is the my.cnf on the client’s computer that you just cracked open to do a performance review . . .
$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
I had originally posted this on the 16th of September, but I had been changing hosting providers and such and it has managed to drop through the cracks. So, if you didn’t see it before here it is..
I have long held the opinon that SSD (Solid State Disk) drives are going to be a major part of the database future. I just checked and I wrote a blog posting about them two years ago. I am not alone in this opinion. It has long been realized that both I/O access speed and throughput increases have not kept pace with the increases in CPU power and the steadily decreasing cost of RAM. Storage space has increased, but both access speed and throughput performance have only had marginal increases in performance.
Solid state disks have long held the promise of lowered access speeds, especially when it comes to random access. Even so, prices for SSD drives have been high and space small (compared to standard hard disks). That is slowly beginning to change. I suspect that within five years SSD drives in RAID arrays for database servers will be just as common as standard disk arrays are today.
Pliant Technology announced today what they call an Enterprise grade series of SSD drives that they claim has twice the throughput of the leading competitors. Computer World has a good writeup of the announcement here. Interesting technology that Pliant claims can produce a maximum of 180,000 IOPS from a single 3.5″ SSD drive. While undoubtedly expensive, for those who are I/O bound with few other options this could be a God-send.
Be prepared . . the time is coming sooner than you think that SSD drives are your main system drives and traditional hard drives are used for backup.
I wanted to play with the new version of MyQuery that was just released. In order to use it, I needed to use a ssh tunneling tool. I have used various hacks in the past but I wanted something that was at least moderately well done.
A bit of googling uncovered Bitvise’s Tunnelier. It is a free for personal use tool that is designed just for tunneling. It seems to handle it with some aplomb. Ten minutes after I downloaded it, I was online with a remote server using MyQuery.
If you are using a Windows desktop and want to use some of the various graphical client programs that don’t include port forwarding this could very easily fill your needs. I just wish I had found it a long time ago.
Anyone involved with MySQL knows that Oracle agreed to purchase Sun earlier this year. Unfortunately for Oracle things haven’t gone as well as they might have hoped. Earlier today it was announced by the EC (Europenan Commission) tasked with reviewing the merger that the EC is delaying approval of the merger and they would be performing an in depth investigation of Oracle’s role in managing the MySQL database.
Daniel Wall, lead legal counselor for Oracle, said in a prepared statement on June the 26th: “we were almost able to resolve everything before the Second Request deadline. All that’s left is one narrow issue about the way rights to Java are licensed that is never going to get in the way of the deal.”
# Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1
Server is up and running but doesn’t show the right values for some variables. What’s wrong? I had to scratch my head for a couple of minutes on this one as it wasn’t immediately obvious (to me). Nothing was logged in the error log…
Oh dear. I was hoping that by now this would be a non-issue. It seems that many of us (including the majority of my clients) are late to the party.
People…it’s now September of 2009. Next month starts the last quarter of 2009. If you have a 64-bit chip in your server for the LOVE OF GOD please run a 64-bit operating system. And easily 90% of the servers shipping have 64-bit procs. I don’t know of any shipping dedicated servers that are running 32-bit procs but I suppose there are some left somewhere in the universe.
Do you know how difficult and costly it can be to upgrade your server to a 64-bit OS? Costly in terms of money and downtime (which may as well be money). All because you didn’t realize when you bought the server that you would ever use more than four gigs of RAM. Or you thought you might have some trouble getting your application to work with the 64-bit libraries or whatever. Data sets can increase at almost exponentially rates and your server performance is almost a direct corollary to the amount of RAM you have available to utilize for the database server. What worked for you even six months ago might not be enough for you today. And without jumping through some serious hoops there is no effective way to use memory above 4-gigs on a 32-bit system.
The new Dell R710 (which is replacing the 1950) has a baseline price of around $1500. This server will support two quad core Xeons and 144GB of RAM. Yes, that is correct, 144GB of RAM. It has 18 slots for memory. This will be NORMAL SERVER in months..not years.
Please raise your right hand and solemnly swear ”I will never put anything on my database server but a 64-bit operating system.” Thank you for your cooperation and congratulations you just saved yourself both time and money. Now if someone could just order me a dell R900 please…
