Maximum MySQL Database Size? Optimizer

Maximum MySQL Database Size?

1. Kích cở Mysql Table phục thuộc vào kích cở file mà hệ điều hành cho phép

2. Mysql database size không giới hạn mà chỉ phục thuộc vào Table size và System files của hệ điều hành

3. Enable tính năng LFS (Large File Support) để hệ điều hành hỗ trợ kích thước file lớn nhất

3.1 tuning mysql

- innodb_flush_method=O_DIRECT This will prevent the operating system from caching data that is already cached.
- innodb_buffer_pool_size ---> 70% ram ---> dữ liệu sẽ cache trên ram nhiều hơn thay vì trong disk
- innodb_log_file_size=4G, ---> 4G max, số này càng cao thì ghi càng nhanh, nhưng crash recovery thì càng chậm
- max_connections --> tăng giảm theo lượng sử dụng và kết nối
- skip-name-resolve
- query_cache_size ---> https://www.percona.com/blog/2014/01...-installation/
- max_connections nên nhiều hơn 10 --> 20 % Max Client ---> https://training.bitrix24.com/support/training/course/?COURSE_ID=22&LESSON_ID=1273&LESSON_PATH=3893.4278 .1273

- Các thông số cấu hình cho InnoDB
https://training.bitrix24.com/support/training/course/?COURSE_ID=22&LESSON_ID=1274&LESSON_PATH=3893.4278 .1274


- Enable Cache trong Mysql
-----------------
http://quantrinet.com/forum/showthread.php?t=9678

query_cache_size: kích thước cache (bytes), không nên đặt lớn quá query_cache_type: ON hoặc OFF
query_cache_limit: giới hạn dung lượng query tối đa được cache

Điều chỉnh lại theo ý bạn
query_cache_limit = 3M
query_cache_size = 128M
---------------------


4. Nên chọn Myisam or InnoDB

InnoDB thiên về row-level locking còn MyISAM thiên về table locking: Tức là khi hệ thống của bạn phải thực hiện nhiều các thao tác insert/update thì InnoDB là tốt hơn, còn nếu hệ thống của bạn thực hiện các thao tác select là chủ yếu thì dùng MyISAM là lựu chọn tốt hơn.

- MyISAM hỗ trợ " full-text searches" trong hi InnoDB thì ko, nhưng MyISAM chỉ chạy tốt với các database nhỏ, Database dung lượng lớn thì nên dùng InnoDB



+ Large file support enabled in the kernel

+ A filesystem that supports large file sizes (many Linux-based filesystems do (ext3, reiserfs >= 3.6, etc))

+ Large file support within the libraries or applications utilized

- Kiểm tra Kernal (OS) đã hỗ trợ LFS kết quả ra 64 là OK

#getconf FILESIZEBITS Python-2.6.tar.bz2
64

Thí dụ:
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB


- Database ISAM không hỗ trợ LFS

http://www.elevatesoft.com/manual?ac...e_file_support


- Tăng Max_Data_Length

http://jeremy.zawodny.com/blog/archives/000796.html

MySQL LogoWhile developing myScoop, and other experimental projects, I have been thinking about the limitations of a MySQL database, particularly it’s maximum file size. I was amazed that after some research I really have absolutely nothing to worry about. It seems my 112mb database is a “new born baby” in the terms of how big it can still grow. Here are the estimated maximum file sizes per operating system:


Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB


File type: ext3, ext4, zfs... support max file size ---> https://access.redhat.com/solutions/1532

This information was taken directly off MySQL.com but this is not necessarily the maximum limitations of your database. A number of methods can help to increase your max file size:

LFS (Large File Support) in Linux

To support files larger than 2GiB on 32-bit Linux systems you would have to use LFS. Although I’m sure the latest operating systems would come out with this already enabled. The standard max file size limitations without LFS enabled are 2^31 bytes(2GiB), but enabling LFS can enable your maximum file size to reach 2^63 bytes (9 223 372 036 854 775 808 bytes). Crazy, I know!

Using the “Alter Table” command
This will come in handy when using the MyISAM storage engine. The simple “Alter Table” in the mysql prompt command can extend your database capacity dramatically.
Example: “alter table ‘weather’ max_rows = 200000000000″
Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

Most of this researched information is very old so I decided to run a little check (which I should have done right in the beginning), and by doing so on my local machine, I literally nearly fell of my chair:

mysql> show table status like ‘blog_hits’ \G
*************************** 1. row ***************************
Name: blog_hits
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 15497
Avg_row_length: 247
Data_length: 3835852
Max_data_length: 281474976710655
Index_length: 366592
Data_free: 0
Auto_increment: 15509
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

This is telling me my max database size can be as much as 281474976710655 bytes which If I’m not mistaken, equates to 256 terabytes. With this being said, I think we are going to run into system limitations rather than MySQL limitations, so therefore, revert back to the table at the top of this post.

https://dev.mysql.com/doc/refman/5.0...ize-limit.html