Today’s first session — “The Java Advantage in MySQL” by Philip Antoniades. I’ve seen Philip speak before (at the Boston MySQL Users Group) so I knew it was going to be good.

Enterprise level Java
Connector/J
Bulk of the code in Connector/J code is that it’s tested and works with most major application servers.
Pure Java
SSL

Entirely in-house code base (java/mysql)
open source, gpl
created by Mark Matthews
need java 1.4 to compile
small footprint driver

can set up config to cache prepared statements.

Profiling tools for developers:
Time executions
Time prepared statements
log prepare vs. execute occurrences (are you executing more than preparing?)

Debugging tools for developers:
Logging — StandardLogger …

Jay Pipes (co-author of Pro MySQL) is in town and will speak about “MySQL Performance Tuning Best Practices”. This is the workshop he’ll be giving at the MySQL Users Conference, so if you can’t go don’t miss this meetup! There will be FREE pizza and soda.

We will have giveaways of Pro Mysql and gift certificates for free Apress books, and other swag like T-shirts, buttons, etc. RSVP for a headcount of soda and pizza at http://mysql.meetup.com/137/events/4875276/ (you will have to register; sorry…. ) Mike Kruckenberg will be there, so if you want your copy (or the copy you win!) signed by both authors, you can.

Description of the workshop: Learn where to best focus your attention when tuning the performance of your applications and database …

Dear Sheeri,

Congratulations! You have been accepted as a presenter for the O’Reilly Open Source Convention 2006 at the Oregon Convention Center July 24, 2006 - July 28, 2006.

The following has been accepted as a 45 minute session for the event:

“So you’ve inherited a MySQL Instance on Unix”

…..

——————-

I’d totally forgotten I’d even submitted that!

Guess I’m going to Oregon in July…..

So, most of the “I want images in MySQL” conversations are terminated with “Don’t.” Some articles say “MySQL’s overhead isn’t worth it” and others that say “If you put the images in MySQL you don’t have to deal with the filesystem overhead.”

My company’s site has over 2 million images to store/retrieve, and our current setup is at its limit and doesn’t scale well — 2 NFS servers. We’d rather avoid adding another NFS server, because it involves changing where images are located whenever we scale. The problem is that with so many images, there are so many inodes that the filesystem cannot keep up. As well, if a server is rebooted or offline for more than an hour (ie, during maintenance) it is extremely slow until the cache catches up (about half an hour).

We believe the best retrieval method is to use MySQL. We will be doing our own speed testing, so I’m not relying on what folks say here. But I’m …

I hated university and graduate school. I had a liberal arts education, so I was learning theory. Now, computer science theory is boring to someone who wants to make something useful. I understand why many folks dropped out of college to go work for the internet boom.

However, in the brief 5 years of work experience I’ve had since getting my Master’s, I’ve learned that a self-taught practicum only goes so far. Whether it’s system administration or database administration, the more basic levels of problem solving do not require much insight into how the system (OS or DB) works, the deeper problems do.

Some of that you can learn by doing, but most of the concepts aren’t learnable unless you’re digging into the source code.

That being said, I found a good web page today that goes through Entity-Relationship modeling. It’s very good, comprehensive.

…

So, today I have had the following problems on various databases:

1) lack of hosts file causing intermittent connectivity
2) running out of space in innodb tablespace
3) a backup causing undue slowness
4) a data partition running out of space and corrupting the binary log, thus making the slaves not replicate
5) mysql server not starting after I added space (see 2) because I put the same logfile in twice

All in all, not bad. Only problem 4 caused me to furrow my brow to fix it, because I hadn’t seen anything like it on the list, but one of the first google hits led me to the cause. Basically the symptom was a slave server that would not start replication, with the error in mysqld.err only (not in SHOW SLAVE STATUS — that would always show NULL seconds behind). But Google immediately gave me the answer.

What a day. Luckily I slept for 11 hours …

So, I have what I consider a feature request for binlog. I’d like there to be an option to log DDL only or DML only, or both DDL and DML (current functionality).

Sure, I can take a diff of the schema, or grep for TABLE or INDEX and put that into its own logfile; however, this is something that really should be an option to mysqlbinlog.

(http://bugs.mysql.com/bug.php?id=16916)

Wow! I just stumbled across mysqldumpslow (while RTFM’ing) and what a tool!

It goes through the slow query log you give as an argument and tallies up the number of times each query appears and a lot of other summary data. Not knowing about this before, it makes it easy to prioritize which slow queries to work on first.

Tonight is the 2nd MySQL Meetup Group. The group has 114 people registered. I saw a clear need to make the group back in October, given that there were 32 people waiting for a Boston MySQL Meetup Group.

And of course, Boston being a major high-tech area, folks are driving in from a 30-mile radius (New Hampshire and places out on 495) to attend.

How much time do you spend every week typing in ‘mysqladmin -u root -p showprocesslist’ ? or ‘mysqladmin -u root -p kill 123,456,789 ? After listening to Tom Limoncelli’s video presentation of a workshop called “Time Management for System Administrators” (which was the forerunner to the O’Reilly book of the same name), I realized that I could implement some of his tips right away.

Specifically, aliasing. I’ve now aliased the following in my .bash_profile:

alias myps=’mysqladmin -u root -p processlist’
alias mystatus=’mysql -u root -p -e “show status; status;”‘
alias mykill=’mysqladmin -u root -p kill’
alias mysqlr=’mysql -u root -p’

I’d rather not put …