need your insight

  • Filter
  • Time
  • Show
Clear All
new posts

  • need your insight

    I am still newbie and was in hot seat in a dba interview! I was asked so many questions, some of them which I was not sure about full answer are below, please let me know your answers:

    1- Rebuilding of a very large index (MyISAM and InnoDB) takes vey long time, what you do to speed it up?

    2- In InnoDB if tmp table parameter in my.cnf is too small, what will happend? Transaction hang and server stall? how you measure and solve it?

    3- What is Un-Athunteticated users in MySQL, how do you identify them and if you see thousands of them, what does that mean and how you resolve it?

    4- How you can find offensive queries other than enable slow log query?

    5- How do you see how many users actives right now and what they are doing, group them by their actions? How you can list top 10 users?

    6- How you can find bottleneck is CPU or I/O? Then how can you see who makes the pain?

    7- Is there incremental backup in MySQL exist? How do you backup a very large database? MySQLDump takes very long time, not a good option.

    8- What is the fastest way to move a database from one server to another?

    9- Load balnacing in MySQL, how do you achive it? top 5 steps to make servers loas balance?

    10- Is there equivalent to innodb_buffer_pool_size in MyISAM?

    THANKS a lot

  • #2
    can somebody answer at least some of my questions?


    • #3
      a) create tmp table with the same structure and with additional indexes
      b) create php, perl, etc. script that: fetches some rows from main table and puts them to the tmp table
      c) add new position to the crontab, for example: 5 * * * * /usr/bin/php path/to/the/script
      d) relax and wait for a while
      e) when work is done delete table and rename tmp table

      Create "wrapper" for the mysql_query function (or other used function):

      function wrapper( $query ) { $file = fopen( 'queries.log', 'a' ) ; fwrite( $file, $query."\r\n" ) ; fclose( $file ) ; return mysql_query( $query ) ;}

      You can add timer to the function (fetch time before and after mysql_query) and 'Explain ' to the $query.


      • #4
        I'll be extra lazy and answer (10)

        key_buffer_size is the closest thing MyISAM has to innodb_buffer_pool_size. The main difference is that MyISAM only caches index pages in the key buffer, while InnoDB will cache both index and data pages in the buffer pool.