# need your insight

**URL:** <https://forums.percona.com/t/need-your-insight/1171>\
**Category:** Other MySQL® Questions\
**Created:** [May 13, 2009, 8:37pm UTC](https://forums.percona.com/t/need-your-insight/1171 "2009-05-13T20:37:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![farrokhp](https://avatars.discourse-cdn.com/v4/letter/f/a183cd/32.png) [@farrokhp](https://forums.percona.com/u/farrokhp)\
**Post date:** [May 13, 2009, 8:37pm UTC](https://forums.percona.com/t/need-your-insight/1171/1 "2009-05-13T20:37:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![farrokhp](https://avatars.discourse-cdn.com/v4/letter/f/a183cd/32.png) [@farrokhp](https://forums.percona.com/u/farrokhp)\
**Post date:** [May 14, 2009, 6:19pm UTC](https://forums.percona.com/t/need-your-insight/1171/2 "2009-05-14T18:19:15Z")

</div>

can somebody answer at least some of my questions?

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [May 15, 2009, 5:27am UTC](https://forums.percona.com/t/need-your-insight/1171/3 "2009-05-15T05:27:57Z")

</div>

1. 

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

1. 

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.

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [May 15, 2009, 12:58pm UTC](https://forums.percona.com/t/need-your-insight/1171/4 "2009-05-15T12:58:31Z")

</div>

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.