guys i have like 100000 rows.. is there anything i can do to make my queries faster?
SuperKman 7. Optimization http://dev.mysql.com/doc/refman/5.0/en/optimization.html

not data free - out of 3.5G assigned to mysql how much and how exactly is it used
SuperKman this will explain "Beta" http://dev.mysql.com/doc/refman/5.0/en/choosing-version.html
Phazorx are you jumping from disk space to ram?

nickg i concerned about disk usage because it impacts performance, i assume it might happen because something is pushed from cache
Phazorx are they myisam? so i want to know how is ram used by mysql and if there is enough
yup
are you using raid well some inno
raid 10
6 10k barracudas
is there only 1 partition? yes
I'm not familiar with how raid handles things, but one partition in general seems bad in my head. i doubt you can benefit from raid in db sense if it would have been split
Data needs to be split.. so that when you need to do sequential reads, those can all be sequential on the drive OS is not doing anything besides whatever mysql requres
there is not binlogging
well that's low level storage optimziation, i guess i am uspposed to rely on PERC being smart enough
The same goes for dynamic format tables. If your table has dyanmic columns; text, varchar, blob, etc… your data will get fragmneted across the drive, as well as within the table itself via pointers when you do updats/deletes/reinserts
If you stick what seems to you to be a small table that you do a full scan on the same partition that you have a large text based table.
Your data could get split so that the file system or raid card has to scan around that inserts done to the large table to select the entire contents of the little table tables which are largest in size are staticly formatted with 10M+ rows
Phazorx yes but those tables are split up amongst the rest or your data nickg by rest of data you mean system?
yes, other tables, indexes, innodb, innodb log files, innodb arch files, whatever, perhaps /var/log/messages
anything
If you update dynamic tables, those could then have internal pointers within the MySQL table, which on the flip side could also have pointers on the file system to the various points on the hard drive
I lost a hard drive.. last month. I restored the tables from mysqldumps. The restored files were sometimes gigs smaller because they were contiguous. Whereas the originals on the hard drive had become fragmented over the year(s)
Phazorx you also have to look at some queries you do. Some queries, such as counting a couple hundred index keys, may have worked fine before. But when you attempt to scale your application are no longer scalable well most pressure (i guess) is from insxert/updates on tables which have 100s selects per second
which recycles cache/indexes
in mytop i see tons of similar queries at same time often
which probably means something is holdding them?
i still tihnking learning how mysql manages given ram would be nice
Phazorx you set a size for each variable, it uses the ram according to the purpose of that variable
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html and i'm thinking of offloading system to diff drive and have /lib/mysql mounted on single raid partition
Should be every variable there you need except InnoDB which is linked. nickg, yes, but say what happens within 1.5G i dedicated for keyspace
Phazorx there is a nice section there for key_buffer_size, i presume that's what you are referring to reading, thanks
Phazorx you may benefit from setting up multiple key buffers Key_blocks_unused 0
:/
reads to requests is 0.001, writes to requsts is 0.7
i figure i need mroe ram
nickg, EricB i'd like someone to take a look at my memory management for mysql
and see if it can be done mroe efficienctly
oh mysql uses about 3.5g out of 4
which is as much as i can give it
is the box swapping? not much, 120m out of 4g used
and not changing
how much total ram in the server 4
oh, hmm http://pastebin.com/740166
i want to assign to key buffer say 2.5
myisam_sort_buffer_size is only used when you alter table/repair table/optimize table/etc but i wonder where i can cut some off
How many connections?
I also don't see your innodb settings.. nickg i recall reading about all that but apparently my common sense failed me
nickg i have 2 out of 100 tables under inno
they have like 50m to them
and 200 connections
i can cut it to 150
not less
I don't see why mysql would be using 3.5gb 200 is preffered tho
well it's more like 1.8 VM + 1.5 cached
FS i/o cached