Available parallelism in MySQL community edition 5.1.34?
If I have a computer with many cores and multiple disks, disjoint filesystems on those disks, and enough I/O bandwidth in the machine to keep the disks busy, can MySQL keep those disks busy if I have it working on different databases at the same time? If so, can one MySQL server process do that, or do I need multiple server processes? If one process is enough, presumably I have to say something clever in my.cnf to make it happen; what would that be? If it can be done with multiple server processes, can I get that from one MySQL installation (and if so, how) or do I need to somehow fool my package management system (RPM on RHEL) into doing multiple installations? Thanks, Mike Spreitzer
Re: Available parallelism in MySQL community edition 5.1.34?
Hi Mike, The way we do it is installing each mysql instance from a precompiled binary. So if you had an 8 core box, and you wanted to use each of the 8 cores, you could install a mysql binary installation and then make 7 copies of it. We use an internal naming convention like the following: /data01/multi_mysql/mysql_A /data01/multi_mysql/mysql_B /data01/multi_mysql/mysql_C /data01/multi_mysql/mysql_D Create a my.cnf under each of these mysql instance directories. Then all you have to do is create a mysql start up script for each of the instances in /etc/init.d. The startup script needs to the following three variables set. basedir=/data01/multi_mysql/mysql_A datadir=/data01/multi_mysql/mysql_A/data/ export MYSQL_HOME=/data01/multi_mysql/mysql_A/ The datadir variable is contained under the rest of the mysql installation. I usually put the logs under /data01/logs/mysql_A /data01/logs/mysql_B This approach is very simple, and works quite well. We use it instead of the multi_mysql startup/my.cnf approach. The approach works particularly well when you have lots of IO, as you said you did. We use this on large raid array DAS, and it works great. If there is anything unclear about my explanation. Please do let me know if you have any questions. I am happy to help. -Tom On Jun 10, 2009, at 2:19 PM, Mike Spreitzer wrote: If I have a computer with many cores and multiple disks, disjoint filesystems on those disks, and enough I/O bandwidth in the machine to keep the disks busy, can MySQL keep those disks busy if I have it working on different databases at the same time? If so, can one MySQL server process do that, or do I need multiple server processes? If one process is enough, presumably I have to say something clever in my.cnf to make it happen; what would that be? If it can be done with multiple server processes, can I get that from one MySQL installation (and if so, how) or do I need to somehow fool my package management system (RPM on RHEL) into doing multiple installations? Thanks, Mike Spreitzer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Available parallelism in MySQL community edition 5.1.34?
In the last episode (Jun 10), Mike Spreitzer said: If I have a computer with many cores and multiple disks, disjoint filesystems on those disks, and enough I/O bandwidth in the machine to keep the disks busy, can MySQL keep those disks busy if I have it working on different databases at the same time? If so, can one MySQL server process do that, or do I need multiple server processes? If one process is enough, presumably I have to say something clever in my.cnf to make it happen; what would that be? If it can be done with multiple server processes, can I get that from one MySQL installation (and if so, how) or do I need to somehow fool my package management system (RPM on RHEL) into doing multiple installations? MySQL is a threaded server, so as long as your queries are accessing different tables, each should be able to use one CPU each. Multiple queries on the same MyISAM table will queue up if there is a writer amongst them. If you are using a table engine that supports parallel access (innodb for example) then multiple queries can write to the same table without blocking readers. Also, note that MySQL doesn't do query parallelism, so one query will never use more than one CPU. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org