Managed Hosting: 24/7 support for absolute emergencies...

Are these canned email replies to trouble tickets really necessary?

Most people would just think it's an emergency because their site is down -- it's just simply UNAVAILABLE!

But I guess if the help just isn't there, you're SOL...

Seems to me though if you're in a managed hosting situation, someone on their side should know your site is down before you call.

This automated response is to make you aware that the DBA team is one
of the few teams that does not operate 24/7. While we do
offer 24/7 support for absolute emergencies and "production down"
situations, any issue that is not deemed an emergency will be addressed
during normal business hours (M-F 8am-5pm CST). If you feel this issue
is indeed an emergency, please contact your support team as soon
as possible!

For guidance, emergencies are typically defined as:

MySQL
* Refusing Connections
* Not starting
* RHCS Cluster Failure beyond successful failover Oracle
* Node down
* Database down
* Critical errors in Alert log
* Critical errors in Listener log
* Database refusing connections
* System errors tied to an Oracle process
* Restore needed SQL Server
* Service Unavailable
* Database in suspect mode
* After restart for an unexpected Server Down (Blue screen of Death) or Instance Down
* Server refusing connections

If you are unsure then please call the support line and discuss this
with your support team.

MySQL buffer_size recommendations

To figure out the right settings for your database server, you really need a good MySQL DBA to take a look at your application's resource utilization and make decisions based on hard data.

Preliminary assessment for our current set up - after yet another crash:

My guess would be that the mysqld service crashed while writing to the table. Being as mysql is using 6G in global buffers alone and then trying to use 300 threads I bet it just ran out of RAM and crashed:

sort_buffer_size = 128M
read_buffer_size = 128M
read_rnd_buffer_size = 128M


Configured Max Per-thread Buffers : 131 G
Configured Max Global Buffers : 6 G
Configured Max Memory Limit : 138 G
Physical Memory : 7.78 G

I would recommend setting the per thread values to something reasonable to avoid outages:

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size=2M

Also:

Current key_buffer_size = 6 G
Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52
See Bug#5731, Bug#29419, Bug#29446

This particular table has frequent inserts and as such is locking frequently. If possible, consider converting this table to InnoDB. This is not possible if you are using fulltext or advisable if you use the COUNT() function against it a lot.

This was on a dedicated database server with 8GB RAM and RAID5.

This server had already been upgraded to RHE 64-bit...

Converting a MyISAM table to InnoDB - pros and cons

Notes from a MySQL DBA:

With MyISAM tables, updates/insert cause table level locking. Converting to InnoDB will be useful as this uses MVCC + row level locking.

Things that can be done:
Convert to InnoDB
Create a numeric PK (auto_Increment) and UNIQUE KEY for hashid. InnoDB require primary keys (PK) for all your tables. If you don't define one, one will be invisibly defined for you. InnoDB best likes small fast PK. A big alphanumeric key in InnoDB might present performance issues.

InnoDB requires more RAM and hard drive space than MyISAM.

There are real benefits that you will gain by going all InnoDB:

Row level locking
Online snapshots for backups and/or slave provisioning
Tables cannot get corrupted (no REPAIR TABLE)

There are some caveats about InnoDB:

No full text indexing

No GEO Indexes

No merge tables

Table space never shrinks. If your tablespace grows to a point where it fills your current space, Even deleting the data will not shrink this file. An export/Import would be your only option. Of course this can be done on a slave and this server can later be promoted to a master.

More RAM and more HD space. InnoDB is RAM hungry. Its space footprint is roughly around 3x that of MyISAM. That is why with the database your size (62M) every byte in a column counts. A varchar takes the length of the text + 1 byte (in a 32 character long string that would be 33bytes if using single byte charset (latin1)) vs. a 4byte INT size. Multiply this by 64M rows, 2G in a varchar vs. 256M for an INT, on a single field column. So an InnoDB footprint would be 2-3x that size.

Optimizing your field type is especially important when the data grows like yours. Here is a good document about this:

http://dev.mysql.com/doc/refman/5.0/en/data-size.html

MySQL recommends having 2 RAIDS to deal with high IO database servers. 1 RAID1 for OS+binarylogs and 1 RAID10 for data. If you convert your database to InnoDB on the migration server. Once you’re ready to move to the new server, you would just flip the SQL traffic to the new server.


I recommend that we convert to InnoDB in the slave and not the live server as this operation does lock your table. I would not recommend converting to InnoDB without cleaning some unwanted indexes as these take a long time to build with a dataset this large. I would recommend that we make these conversions on a slave, which would be better provisioned for these operations.

--

Limitations of MySQL on 32-bit OS -- RedHat Linux Enterprise

Most developers will never run into this situation. But if you're running a Linux server RHE 32-bit OS and have a large MySQL database, watch out!

It took a few days of crashing this database with a table that has over 14GB of data.

The index on that table had grown over 2GB and was causing MySQL server to page the data (writing temp data to the hard drive instead of RAM). Disk I/O is thousands of times slower than RAM, and being over the MySQL limit on the 32-bit OS was just a recipe for disaster.

Oh, and also, at first the techs determined that our MySQL temp directory was full... so that was another major crash.

The solution included:
- upgrade the OS to 64-bit RHE
- change the storage engine to InnoDB
- get bigger drives
- set up the drives in a RAID 10 configuration
- optimize the various indexes that are used in JOIN statements...
- make sure the temp directory partition will have plenty of room

Of course that's just the beginning -- in the right direction.

-j