Databases Crash

**PuppyF4RT** · 03-11-11, 04:19 PM

YOUR FUCKING DATABASE SUCKS ASS! I have been fighting this database tooth and nail... right when I think everything is starting to look good performance issues rear their head and laugh at me. Man, I'm feeling really deflated and facing heavy combat fatigue after an entire month of working on the system. I've worked 8 hours a day and then I go home and put a few extra hours in at night.

RAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Ok, now that I got that out. Does anyone... anyone have Oracle Enterprise Database performance tuning knowledge? At this point I'm willing to fucking pay hard cash for it... I'm out of ideas.

**Bunni** · 03-11-11, 07:14 PM

must be oracle...

edit ah ya... as your last sentence explains.... Ya, but id professionally recommend mysql, as if the act of oracle buying mysql wasnt enough of a hint..... Couldnt have been a crash if your looking for tuning, was it a table lock?

**PuppyF4RT** · 03-12-11, 12:07 PM

MySQL supposedly can't handle the complex loads we put on the systems. This particular database is 12TB of data (used space) with 6 import engines running 24/7. That's the part MySQL could handle, then we have our application layer with high availability real-time reporting on dynamic fields (probably about 100-200 fields per record, getting 5million records per day per import engine). The data is loaded into about 15 tablespaces. Everything is very well indexed and uses Oracle's Best Practices in schema design. We have archive logging enabled so that the system never goes down (RMAN Backup every night, full on Sunday and incrementals every other day). 1TB redo logs so that log switches are not frequent. All data is heavily partitioned by YYYY_DDD_S so that the queries are fast. I do admit to being completely ignorant of MySQL, I've just been told that it can't handle our loads. Hell, Standard Oracle Databases can't handle the load because they don't support partitioning.

Either way I finally got it around 2:00am last night. After rebuilding the database it turns out that while the indexes were valid and being used for the tables/columns that were being hit the hardest they have yet to be rebuilt since I added a year's worth of partitions. I rebuilt indexes to include the partitions that I recently added and importation/correlation are running smoothly now. I'm so glad that it worked because after that I was seriously at the end of my rope.

**Warprosper** · 03-14-11, 02:01 PM

12 TB.... Damn.... You work for a Medical Company or something?

**PuppyF4RT** · 03-14-11, 02:11 PM

Ever heard of Mid-Continent Communications? The database I'm working with contains every detail of voice calls made on their network for the last 6 months. Yeah, it's pretty big

**PuppyF4RT** · 03-14-11, 02:14 PM

Turns out the redo logs were still giving me hell because they were all writing to the same device causing I/O contention.

The SHMMAX kernel parameter needed an extra GB to breath

The memory stack ulimit had to be manually set in /etc/security/limits.conf

More indexes require a rebuild (The last ones, FINALLY!!!)

DBMS statistics need to be gathered.

Once I've done all of that this system should be ready for the heavy loads and complex queries that it was designed for... still a long road ahead /cry

**Rawr** · 03-15-11, 04:02 PM

Man, and I thought building my database for the school's maac program and then having to use broken ODBC drivers to set up forms and make it pretty for use in access was hard....