Configure mysql to compensate for bad structure/queries

[thinkingman]

Hi all,

So I've been thrown into developer hell, with the assignment "Here's
$formerprogrammers's project, it doesn't work, fix it, oh yeah, and
there's a tight deadline"

It seems like $formerprogrammer knew just enough about php/mysql to
write a working product on a REALLY bad design. Not surprisingly our
company/client decided to jump write into the terrible design and
there's a lot of important data tied up in a terrible structure.

To give an idea, we've got roughly 4 tables of "equipment' each with
between 100,000 and 500,000 records that are between 10 and 15 fields
long. Certain fields are of variable length (model number from
different suppliers) and need to be searched on with wildcards.

My conundrum is this, the existing database design has to stay, there
are roughly 80,000 lines of just as poorly written php code that
somehow manage to execute on top of this structure and need to continue
doing that until I redesign the system. However our main issue with
this tool is speed, a cacophony of sql queries need to run to make
things work, thus bogging down mysql. I'm not too familiar with
configuring mysql, and I've done my best to make this run efficiently
by increasing key_buffer_size and innodb_buffer_pool_size however it
seems no matter how large I set these (currently both at 256M) I still
get hundreds of slow queries (over 10s) per day.

Obviously the end goal is to redesign properly, but until then is there
anything that can be done in my.cnf to handle poorly written queries
running on a lot of data?

Thanks

[Bill Karwin]

th*********@gmail.com wrote:

I've done my best to make this run efficiently
by increasing key_buffer_size and innodb_buffer_pool_size however it
seems no matter how large I set these (currently both at 256M)
So you have a mix of MyISAM tables and InnoDB tables? Are you aware
that key_buffer_size is relevant only for MyISAM, and innodb_* options
are relevant only for InnoDB tables?
Obviously the end goal is to redesign properly, but until then is there
anything that can be done in my.cnf to handle poorly written queries
running on a lot of data?

Read about the query cache, that's the only thing I can suggest without
knowing more about the project -- which I definitely do *not* want to
know any more about. ;-)

http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

Good luck with the project.

I've had assignments like that. I spent about 75% of my time keeping
the old system operating, until I could identify a few key areas where a
few well-placed shell scripts could automate some clunky tasks well
enough to give me back some of the hours of the day. Then I could make
progress redesigning the system.

Make sure your managers understand that they're asking you to split your
time between _three_ jobs, any of which can be a full-time endeavor:
1. Operator of the ugly, fragile, old system.
2. Design, implement, and test a new system that does everything the old
system does, but better.
3. Migrate all the live data from the old system to the new system,
handling any data mapping, transformations, etc. You need to automate
this with scripts and test thoroughly, so that when the day comes, you
can cut over with a few hours of downtime.

Also make sure they understand that zero downtime during the final
cutover is not a practical goal. In theory it is possible, but usually
involves so much meticulous testing that it isn't worth the additional
effort. In other words, would they like 6 hours of downtime on some
Sunday, or would they like the project to be completed 3 months later?

Regards,
Bill K.