need your insight

MySQL & MariaDB Other MySQL® Questions
1

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.