The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 22, 2019, 06:33:13 PM

Login with username, password and session length


Pages: [1] 2 3
  Print  
Author Topic: MySQL super badass  (Read 7052 times)
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« on: July 13, 2010, 07:37:42 PM »

I have some specific MySQL issues that I'm really tired of dealing with and would like one of the fantastically smart and talented people here at the cache to have a look see and do a little consulting for me.  I was going to put an ad out on the interwebs somewhere but figured I would start here first.

If anyone has some hours they can sell me for taking a look at my database and my issues I would appreciate it.  You can reply here or contact me via PM or any of the popular instant messenger protocols.

MSN - kaptainkrayola@hotmail.com
ICQ 85896389

Thanks,

KK
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: July 13, 2010, 11:18:05 PM »

Wish I could help KK, have to respectfully stay clear.

Side note to any and all interested: Kaps is a good man and will be an excellent contact for now and the future.
Do you well to connect with someone such.

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #2 on: July 14, 2010, 07:13:48 AM »

Thanks for the endorsement Perks, I appreciate it.
 Grin
Logged

We can't stop here, this is bat country.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: July 14, 2010, 08:43:31 AM »

unfortunately im guessing its more of an "internal workings" type issue, and I wouldnt be of much help there. Can you post anything of what it is. you never know?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #4 on: July 14, 2010, 08:54:26 AM »

I think it's to do with a combination of the db structure (not setup how MySQL wants you to design the DB), Server settings, queries (again not taking advantage of how MySQL wants you to do things)

I the DB is designed well and the data normalized and all that stuff they tell you to do but I think the breakdown is with my lack of knowledge on how MySQL wants you to do things.  I am primarily a Microsoft SQL Server guy and the same DB done in MSSQL would be awesome and have no problems.

I've already restructured a lot of the queries to remove things that MySQL seems to dislike and that has helped but there are still some show stopper issues (like tables being read locked when another query is doing a read) that cause the associated web app to be mostly unusable to my users.



Logged

We can't stop here, this is bat country.
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« Reply #5 on: July 14, 2010, 09:15:08 AM »

Any possibilities to change to NoSQL systems? You'll get a lot more power out of crappy machines when you go with MongoDB or with Redis.
Logged

I met god and he had nothing to say to me.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: July 14, 2010, 09:21:05 AM »

Much easier instant fix for the read lock Kaps is simply to go INNODB rather than myISAM. I think the conversion is almost click and go. You'll surrender just a twitch of performance, but gain full transactional capability as well.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #7 on: July 14, 2010, 09:22:45 AM »

Longish term is to move to mongoDB but I need the issues i'm having fixed like NOW and don't have the time at the moment to dedicate to doing the conversion to MongoDB.

Speaking of that, you know of any good tools that will do all the work for me as far as data migration goes?  The DB has millions of records so part of my hesitation to migrate is figuring out how to move all of that data.
Logged

We can't stop here, this is bat country.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #8 on: July 14, 2010, 09:24:26 AM »

Much easier instant fix for the read lock Kaps is simply to go INNODB rather than myISAM. I think the conversion is almost click and go. You'll surrender just a twitch of performance, but gain full transactional capability as well.

I was thinking the same thing but InnoDB doesn't support fulltext index from what I have read and its a major component to the app.  I could move the full text to a myISAM table and relate to two but i was afraid the join to the table would cause the same locking problems.  These are the kinds of things I wanted to discuss with whoever can have a look at the DB.
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #9 on: July 14, 2010, 09:37:13 AM »

Then innoDB + Sphinx would do you ... Take a little bit but would certainly fit the bill.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #10 on: July 14, 2010, 09:42:27 AM »

Very cool - i'll read up on it.

Thanks Perk, I knew I kept you around for something.
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #11 on: July 14, 2010, 09:47:31 AM »

Phew. For a little while I thought it was that you still had the hots for me. Glad you're over it.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #12 on: July 14, 2010, 10:01:57 AM »

Yeah, I just don't talk about it in public anymore.  I keep it between me and the life size Perkiset cardboard cut out in my bathroom.
Logged

We can't stop here, this is bat country.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #13 on: July 14, 2010, 02:12:13 PM »

you can intermingle tables. So assuming you have 1 that needs fulltext, that one could stay isam, the rest can go innodb. Thats what I had to do for my inturdnet stuff.

Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #14 on: July 14, 2010, 02:35:36 PM »

and doing the JOIN to the full text table wont' lock up rows on the innodb table?  if that's the case it sounds like a pretty easy fix.
Logged

We can't stop here, this is bat country.
Pages: [1] 2 3
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!