Hello, Need suggestion how to set variables in db MYSQL to optimize database to work with very big table over 7gig. I read some articles about table_cashe and key_buffer variable , but I would like hear some professional opinion about that Thank you in a advance.
I would start here if you haven't read these yet: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/ and http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Your memory may become a problem very quickly depending on the usage that you expect. If at all possible use a 64 bit OS and increase the memory to as high as possible. Fast HD's make a big difference as well. The best way to make a really fast DB is to load as much as possible in RAM. With only 4GB you are limited to only a portion of the DB, so you'll need to tune parameters, table and query structures to get good results. You're most likely going to have to play with cache and buffer sizes a lot. Personally, I generally use Memory tables, and InnoDB because it supports foreign keys, and is far more stable and easier to tune than MyISAM. Once you get the general parameters set so that you have a functional database, you will need to really optimize the table structures, foreign_keys, and indexes, multi-indexes, and all of the queries that you will be using. The way that MySQL uses memory compared to MSSQL, it's trickier to get a fast database once your DB size gets very large. Also, make sure your slow query log is on as well, because this can be very useful in debugging slow indexes.
I read those blogs, but I wasnt sure which one is better to increase, table_cashe or key_buffer , or something else, like I increased table_cash but doesnt look like its increased some speed.
I got 2 most loaded tables: 1) Data 7,327.9 MiB Index 172.7 MiB Total 7,500.6 MiB 2) Type Usage Data 282.9 MiB Index 99.8 MiB Total 382.7 MiB Is that better to set key_buffer around 500 Mg and the rest of memory use for table cache ? Which one would you suggest ? Server stat: cpsrvd up interval up Server Load 5.36 (4 cpus) Memory Used 32.9 % Swap Used 0.00 % Disk /dev/md1 (/tmp) 17 % Disk /dev/md2 (/) 84 % Disk /dev/md0 (/boot) 18 %
The most important ones are table_cache , sort_buffer and key_buffer. The best thing you can do is to let phpMyAdmin suggest a table layout (be careful though as its not always correct). Now if this is a dedicated SQL server then I would recommend that you turn off hostname resolving in MySQL, this will speed up things a lot. I also recommend to usage of NULL types on empty fields as it will reduce the db size a lot. Basically configuration wise your options are limited (sort of), having a good setup (OS, kernel, sysctl etc) and a good code foundation is where the real performance is (IMHO).