Various notes if I have time to write about.
I'll be giving a talk at mysqluc2007. The talk will be entitled, Federation at Flickr: Doing Billions of Queries per day. Personally I think it's a pretty catchy title, and full of info that might be helpful for many other organizations.
So, I have this really crappy laptop that looses it's harddrive every few months. I can always recover because I constantly backup the laptop. But, restoring messes things up with Yahoo! Music, and Yahoo! music doesn't inform the user what's going on.
set background=dark
autocmd BufEnter *.pl,*.cgi,*.pm,*.html,*.inc,*.php,*.gne set formatoptions=croql comments=n:#
autocmd BufEnter *.pl,*.pm set comments=n:# cinoptions=>4,(0,)5,*15
autocmd BufEnter *.java set fo=crql cin ai
if !exists("autocommands_filetypetabs")
let autocommands_filetypetabs = 1
augroup filetypetabs
"autocmd FileReadPost,BufReadPost *.css set tabstop=2
"autocmd FileReadPost,BufReadPost *.css set shiftwidth=2
"autocmd FileReadPost,BufReadPost *.xsl set tabstop=2
"autocmd FileReadPost,BufReadPost *.xsl set shiftwidth=2
augroup END
endif
au BufEnter,BufNewFile,BufReadPost,TermChanged, *.php,*.inc,*.html,*.php,*.gne,*.txt source $VIMRUNTIME/syntax/php.vim
au BufNewFile,BufReadPost,TermChanged, *.pl,*.cgi,*.pm so $VIMRUNTIME/syntax/perl.vim
set nottybuiltin
set nocompatible
set backspace=2 " backspace can join lines
set ruler " show cursor position
set laststatus=2 " show nice ruler
" set number " show number by default
set errorformat=%f:%l:%m,\"%f\"\\,\ line\ %l\:\ %m
set magic
set nobackup
set writebackup " keep a backup copy during the edit session
set incsearch " Incremental search.
set nowrap " Do not wrap lines automatically
set autoindent
set smartindent
"set list
"set listchars=tab:>-,trail:-
set joinspaces " Join adds two spaces after a period.
set noexpandtab
" softtabstop number of spaces a counts for usefull to make it look
" like u want when ts is set to 8
set softtabstop=4
set tabstop=8
set shiftwidth=8
" Line width to endless
set textwidth=0
"c auto-wrap comments
"r automatically insert the current comment leader after hitting
"o automatically insert comment leader after hitting o
"q format commens with gq
"l longlines are not broken when textwidth is reached
set formatoptions=croq cindent sw=8 ts=8
" The completion dictionary is provided by Rasmus:
" http://lerdorf.com/funclist.txt
set dictionary-=/home/dathanp/vim/funclist.txt dictionary+=/home/dathanp/vim/funclist.txt
" Use the dictionary completion
set complete-=k complete+=k
" {{{ Autocompletion using the TAB key
" This function determines, wether we are on the start of the line text (then tab indents) or
" if we want to try autocompletion
func! InsertTabWrapper()
let col = col('.') - 1
if !col || getline('.')[col - 1] !~ '\k'
return "\"
else
return "\"
endif
endfunction
" Remap the tab key to select action with InsertTabWrapper
inoremap =InsertTabWrapper()
" }}} Autocompletion using the TAB key
"*N - searches for unclosed comments
"(N - indent N characters from the live with the unclosed parentheses
")N - searches for unclosed parentheses N lines away
">N indentation
"set cinoptions=(0,)5,*15
set keywordprg=webster
set shortmess=mrnto
set showmatch
set nostartofline
set showcmd
set showmatch
if has("terminfo")
set t_Co=8
set t_Sf=[3%p1%dm
set t_Sb=[4%p1%dm
else
set t_Co=8
set t_Sf=[3%dm
set t_Sb=[4%dm
endif
set showmode
set statusline=%1*[%Y]%F\%(\ %m%r%H%)%=%3P<%l,%c>
map :so $VIMRUNTIME/syntax/php.vim
map :set syntax=php
if has("syntax")
source $VIMRUNTIME/syntax/syntax.vim
hi User1 term=inverse,bold cterm=bold ctermfg=yellow ctermbg=blue
highlight Comment term=bold ctermbg=4 cterm=bold ctermfg=7
endif
How innodb handles auto increment over there states that "When accessing the auto-increment counter InnoDB uses a special table level lock AUTO-INC lock which it keeps to the end of the current SQL statement, not to the end of the transaction."
4.1+ innodb_safe_binlog - sync's the binlog on ever update to innodb to make sure that if a crash occurs the chances on loosing slave events is very small.
thread_cache: - this keeps open treads open for frequest connections.
I reduced thread_cache to 45 from 300 and turned off innodb_safe_binlog to see if thread contention sould stop. I know I could mess with innodb_concurrent_threads but this is a different issue outside of that thread scope.
Update 2
Haha I'm such a n00b. Basically the issue was there wasn't a key on a table that just grew very large but still in memory. So disk io was low but CPU was very very high!! The reason even though a table with 150K rows is in memory processing all these rows is CPU IO intensive at high concurrency. Adding a index is all that was needed to fix the porblem.
Note: use top, vmstat, iostat -x 5
