Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts

    Question Call Centre Database slowing down

    I currently have a large database with over 40 tables, it includes a main table for storing referral info, booking tables, job notes and many others. Just recently the database as started to slow down, currently my table are all using the engine format myISAM.

    I have been reading up on this and it seems that myISAM is one of the quicker db engines, but also i have looked into using innoDB engine.

    I have a lot of updates going on at the same time from a call centre full of eager workers and i can't have them moaning that it is running slow. If i was to swap of to the innoDB engine i think it would run better than it currently is but wot do u think????

    also what sort of size to db's usually get up to before they start to slow down???? i want to exclude the possibility that SQL statements are causing the problem.

  • #2
    New Coder
    Join Date
    Aug 2005
    Location
    Edmonton, Canada
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My expertise is not database administration, but from a troubleshooting viewpoint I would try running various statistical software to isolate what is causing the bottleneck. Is the CPU slowing down, is it the hard drives, the RAM, the Internet bandwidth... I would first figure out what the bottleneck is rather then blindly moving to a different database engine. Once you know what the bottleneck is then you can determine how the bottleneck is related to and why it is affecting the speed of your database. I dunno, to me doing the proper research and troubleshooting will in the end save you more time and money, and you'll understand the stuff better.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You've got to do explains on all your queries and make sure you are utilizing indexes whenever possible. Sometimes it's not enough just to have indexes-- you've got to make sure your queries use them.

  • #4
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    wot kinkda test can i run to find out what the bottle neck is???? i haven't deveoted much of my time 2wards SQL and spent most of ma time working with php so i not got a real good clue about SQL???

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    well I'd say first off that 40 tables is most likely too many.
    then you have to determine whether or not you are running queries within a php loop. (perhaps to get a category and then within each category you are running another query to get the sub-categories).

    also use the word EXPLAIN in front of each of your queries and test them to see what indexes are being used etc.

    there are so many things that can be a factor we basically can't tell you more than the simple things. In my mind though if you are running a database application but have concentrated on php then most likely there are a number of things you can do to tune your queries.

  • #6
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    y wud 40 table be to many, i have a lot of tables that are used to store info to stop data redundancy???

    I have read on a website about some databases having 100's of tables and still working fine. wen i get into work 2day i am off to check each statement so wen i ave finisted i will post back with my results (most likly take me about a week or so)

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    well the 40 tables could be too many if you are running 40 users and have given them a single table each for instance. many people make that error instead of using one main table and a column indicating what user the data belongs to.

    if you have 40 tables and you have the same table schema across more than one table because you are splitting your data up into years or months for instance, that would be another reason you have too many tables.

    yes, there are times when a larger number of tables are fine. you pointed out that you weren't well versed in mysql, so I was mentioning it since it is a common mistake made by many newer developers.

    when checking your code, check indexes on your tables to make sure they are running efficiently. too few and your queries will run slower, too many and you slow down inserts/updates.

    also check to see if you are running queries within loops as I mentioned as those are sure performance killers.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •