Re: Newbye speed question - which setup to use for indexing
Hi, No, MySQL never uses multiple CPUs for the same query at the same time (the OS could switch the thread between CPUs over time, but that doesn't count :-)). Each connection gets one thread and one thread can only run on one CPU at a time. It would be pretty hard (if not impossible) to have multiple threads for a connection. You'd have to figure out *how* to split up the work for one query and try to get them to finish at the same time, otherwise one will be waiting for the other(s) to catch up too long. At least that's what I think with my knowledge of threading. ;-) However, for the original question about creating indexes, MySQL 4.0.13 added a new feature: Added multi-threaded MyISAM repair optimisation and myisam_repair_threads variable to enable it. And from http://www.mysql.com/doc/en/SHOW_VARIABLES.html myisam_repair_threads. If this value is greater than one, MyISAM table indexes during Repair by sorting process will be created in parallel - each index in its own thread. Note: multi-threaded repair is still *alpha* quality code. Just thinking though, I wonder how much this will help during index creation? Is that more I/O bound...? Well, since it's used for Repair by sorting (and I assume not for with keycache), maybe that is fairly CPU intensive to sort the indexes -- and only writing an index chunk every few seconds (I think). Anyway, hope that helps. Matt - Original Message - From: Ware Adams Sent: Friday, October 03, 2003 7:49 PM Subject: Re: Newbye speed question - which setup to use for indexing Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) I haven't seen any documentation, and I'm not sure I'm explaining things properly, but here's what I've observed running MySQL on a decent sized data set over almost a year on OS X: When only one query is active in MySQL (observed via show processlist, all connection IDs show 'sleep' except one) the mysqld process in top never shows more than 100% (or never more than 105-110% to be absolutely truthful) When multiple queries are active in MySQL the mysqld process frequently approaches 200% (assuming each can hit 100% when run on it's own) When a single MySQL query is active and another heavy load process is running on the machine (e.g. running rsync on a big directory) mysqld will go to 100% and the other process will approach the level it would hit without mysqld running This is on a G4 1.42GHz dual proc running OS X and hooked up to an XServe RAID. My conclusions from this were that MySQL on OS X cannot use more than one processor for a single query, but it uses multiple ones fine when it has multiple queries to process. Also, it performs fine sharing the two processors with other applications. Can MySQL use multiple processors for a single query on other OS's? It's pretty key for us as we tend to run relatively few, long duration queries as opposed to lots of quick ones. Thanks, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? Is disk I/O more important ? The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). anyone tried indexing a large? database? thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbye speed question - which setup to use for indexing
Peer, How big are the table and index files? Can your OS handle files bigger than 2/4Gb? I've got a table with 55 million rows with just 3 columns all floats. I've got three indexes with all the fields in various orders. My data file is 700Mb but my index file is over 4Gb, so yours could easily be so (as could your data file). Indexing my db takes under 2 hours on a sloow 400MHz Sun E250. I don't think the index process will use two processors, but I don't think your problem is related to processor speed. Andy -Original Message- From: Peer Reiser [mailto:[EMAIL PROTECTED] Sent: 03 October 2003 10:28 To: [EMAIL PROTECTED] Subject: Re: Newbye speed question - which setup to use for indexing Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? Is disk I/O more important ? The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). anyone tried indexing a large? database? thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Is disk I/O more important ? Frequently. open a terminal window and run top while the query is running. If mysqld is using 100% of a cpu on a single process then the query is CPU bound. If it's using signficantyly less (e.g. 30%) then it's probably disk bound. The solutions to being disk bound can be lots of things: 1) Better indexing 2) More RAM (the G5 will help here as it can go past 2 GB) 3) Faster disks, the G5's faster drives and faster bus will help In general #1 is far and away the biggest factor, you can speed up queries by a factor of thousands or more. The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). I don't know the structure, but that order of magnitude is doable in much less time. We imported 30 million records on a server running other queries in less than 3 hours. However, it was an InnoDB table and there were only numeric fields in it. You can probably improve things by tweaking your table structure and my.cnf file. There's a lot of detail in the mysql manual on the web. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbye speed question - which setup to use for indexing
Andy Eastham wrote: How big are the table and index files? Can your OS handle files bigger than 2/4Gb? Yes, OS X can deal with files larger than 4 GB. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 719,887,477 queries (417/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) I haven't seen any documentation, and I'm not sure I'm explaining things properly, but here's what I've observed running MySQL on a decent sized data set over almost a year on OS X: When only one query is active in MySQL (observed via show processlist, all connection IDs show 'sleep' except one) the mysqld process in top never shows more than 100% (or never more than 105-110% to be absolutely truthful) When multiple queries are active in MySQL the mysqld process frequently approaches 200% (assuming each can hit 100% when run on it's own) When a single MySQL query is active and another heavy load process is running on the machine (e.g. running rsync on a big directory) mysqld will go to 100% and the other process will approach the level it would hit without mysqld running This is on a G4 1.42GHz dual proc running OS X and hooked up to an XServe RAID. My conclusions from this were that MySQL on OS X cannot use more than one processor for a single query, but it uses multiple ones fine when it has multiple queries to process. Also, it performs fine sharing the two processors with other applications. Can MySQL use multiple processors for a single query on other OS's? It's pretty key for us as we tend to run relatively few, long duration queries as opposed to lots of quick ones. Thanks, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
