Ok... I solved my mistery of the slow 22G table.... rebuild the kernel to support memory and now things are working.
But still quite slow. I do sum() operations over the 22G table. I'm using the latest version of mysql 4... (I WILL migrate soon, I promise), one thing I have notice, is that operations over that 22G table, seasuring the I/O with iostat never surpass the 23mb/s for reading... even if I test the IO of the array, it can easily give me 300mb/s for reads... and like 150 for random reads... first of all, is there any way to squeeze more speed out from mysql 4? No... I imagined that... So, my next step is migate to a newer version of mysql ( YEY ^_^ )... I have read a few about partitions. The specific query I'm making is a query which do a sum filtered over a date field. What would be the best approach to partition this table? Can I mix innodb with partioned tables and still have acid compliance? The split in several partitions creates more lecture threads or how parelelization over partitions works? And last, does mysql 4 support partitions? XD tia.
