how to clear buffer cache
in order to run benchmarks, it is useful to be able to clear the buffer cache. i'm using innodb. is there a way to clear the buffer cache, i.e., something equivalent to oracle's alter system flush buffer pool? thanks, kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to log the execution time of each query
I would like to log the execution time for each query in my MySQL server .Is there any possible way? If you are using MySQL to serve an application, then it is advisable to do the logging through the application scripts. For in most cases, you might require more information other than time alone, to debug slowness, such as - - Which script fired the query. - Number of rows retrieved. etc. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANNOUNCE] dumpster :: dumps out all related records in a mySQL InnoDB database
Hey all. Well I just finished my first version of a little tool I have affectionately dubbed dumpster. I do use my own SQL wrapper functions, but they should map fairly cleanly to a search and replace for the stock PHP mysql_*() ones, or your own ones. Mad props to Peter Brawley [EMAIL PROTECTED] for the initial SQL statement to get the FK constraints. If someone can point me at how to get the information I need to fix that bug, that'd be swell. ÐÆ5ÏÐ -- This script attempts to generate all the SQL statements needed to archive a snapshot of a single 'thing'. For example, it can harvest all records related to a given user. (This only works for InnoDB tables that utilize proper FK constraints) Usage: ./dumpster.php --database mydb --table users --id 1 [--delete] user_1.sql Then later simply mysql --force -u root mydb user_1.sql to put the 'user' back --help, -help, -h, or -? options, to get this help. --databasethe name of the database to use. --table the name of the table to use in the database --id the ID that joins all these tables together in the database --FKonly only show the Foreign Key list and exit. --debug to turn on output debugging. --version to return the version of this file. --delete deletes the record as it is output (in 'debug' mode this outputs only, no action). KNOWN ISSUE: if a column is defined as ON DELETE SET NULL, then there's a better than average chance that it might get NULL'd by a DELETE before it, therefore we won't be able to clean up some records properly as their FK ID is now NULL. catch22. :-| There is probably a way to find out which FKs have this particular constraint action and then we could save off their PK in an array and loop through them at the end I think? http://daevid.com/examples/dumpster.tgz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysterious speedup after doing FLUSH TABLES
Hi, that variable is not set, so it should be the default value (2 * number of processors I believe). To what value should I set it? -Original Message- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 10:02 AM To: Moritz Möller Subject: Re: mysterious speedup after doing FLUSH TABLES What value do you have innodb_thread_concurency ? There is a bug with that on 64bit machines ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysterious speedup after doing FLUSH TABLES
I will try that. But the symptoms are a little bit different - the server works fine for 3-4 hours, but when it gets slow, neither cpu nor disk-io reach the limit. As far as I know a cache/memory-bottleneck should turn cpu to 100%, like you have observed. In our case I guess it's a locking issue, will grab the output of INNODB STATUS the next time it happens. Moritz -Original Message- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 11:22 AM To: Moritz Möller Subject: Re: mysterious speedup after doing FLUSH TABLES See http://lists.mysql.com/mysql/199572 As lower as you can afford... The same thing happened here... MySQL will work fine for let's say some hours then it begins struggling... then the CPU load increases and machines works like a bull in the Spanish-style bullfighting... We have it now at 2 and the highest load we've seen it's 35%... at 900 simultaneous connections... Since the implementation of 'memcached' the workload on the MySQL dropped significantly... now 900 simultaneous connections is enough to keep the website at it's best ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysterious speedup after doing FLUSH TABLES
Hi, table_cache is 8 on our systems. I quick glance at the manual tells me to increase that value (Opened_tables is 2680462406)... I will try that. Thanks ;) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 12:32 PM To: Moritz Möller Cc: 'Gabriel PREDA'; mysql@lists.mysql.com Subject: RE: mysterious speedup after doing FLUSH TABLES Hi, What about your table_cache setting ? Is show status reporting a high number for Opened_tables ? --- Regards, Jocelyn Fournier www.mesdiscussions.net I will try that. But the symptoms are a little bit different - the server works fine for 3-4 hours, but when it gets slow, neither cpu nor disk-io reach the limit. As far as I know a cache/memory-bottleneck should turn cpu to 100%, like you have observed. In our case I guess it's a locking issue, will grab the output of INNODB STATUS the next time it happens. Moritz -Original Message- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 11:22 AM To: Moritz Möller Subject: Re: mysterious speedup after doing FLUSH TABLES See http://lists.mysql.com/mysql/199572 As lower as you can afford... The same thing happened here... MySQL will work fine for let's say some hours then it begins struggling... then the CPU load increases and machines works like a bull in the Spanish-style bullfighting... We have it now at 2 and the highest load we've seen it's 35%... at 900 simultaneous connections... Since the implementation of 'memcached' the workload on the MySQL dropped significantly... now 900 simultaneous connections is enough to keep the website at it's best ! -- Gabriel PREDA Senior Web Developer -- 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]
MySQL 5.1 Reference Manual in Chinese
The Chinese translation of the MySQL Reference Manual is complete. It was done by one of our partners from Beijing, People's Republic of China, and covers MySQL 5.1. Due to problems beyond our control it's not available in CHM or PDF, but you can view it online, or download the HTML version: http://dev.mysql.com/doc/#chinese-5.1 Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead. Berlin, Germany (UTC +2:00) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime issue on MySQL 4.x
Willy wrote:
Hello,
I have a MyISAM table:
CREATE TABLE `dlr` (
`msisdn` varchar(20) NOT NULL default '',
`source` varchar(20) NOT NULL default '',
`operator_id` varchar(20) NOT NULL default '',
`sms_message` longtext NOT NULL,
`smsc_id` varchar(20) NOT NULL default '',
`sms_id` varchar(250) NOT NULL default '',
`dlr_smsc` text NOT NULL,
`dlr` tinyint(4) NOT NULL default '0',
`date_time` datetime NOT NULL default '-00-00 00:00:00'
) TYPE=MyISAM
I have a case here, on July 7 I imported some data into the table with this
query:
INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time)
VALUES
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()),
('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:, 'IM33665', MD5(RAND()),
'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()),
'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()),
'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW())...
And when I try to fetch the data using PHP with this query:
SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS
TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP
BY tanggal, operator_id ORDER BY date_time
The weird thing happened. Why does all the rows are shown or treated as July
7th data? If we see the imported data, there should be June 28, June
29.until July 7. Please help and many thanks for any reply.
Regards
Willy
Because you specified only for the month of July.
AND MONTH(date_time) = MONTH(NOW())
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to understand why Select running out of memory if table not used
It's your MySQL client that's run out of memory, not the server. I don't know how many rows MySQL is trying to return. Probably an enormous amount since you're not sepcifying a join criteria on the data table. The number of records in the event table between '2006-05-01' AND '2006-05-15' times the number of records in the data table. SELECT event.cid, event.timestamp FROM event JOIN data ON ??? WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15 - Original Message - From: Jacob, Raymond A Jr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 11, 2006 6:13 PM Subject: Trying to understand why Select running out of memory if table not used When I try to retrieve all rows in the event table that are in a particular range and then try to determine if the cid is in the data table the join takes forever. So I tried just tried adding the table to see if the where clause was slowing things down. Below is a portion on my session. mysql select cid, timestamp from event where timestamp between '2006-05-01' AND '2006-05-15'; | 7753544 | 2006-05-14 23:59:58 | | 7753545 | 2006-05-15 00:00:00 | | 7753546 | 2006-05-15 00:00:00 | +-+-+ 1336344 rows in set (32.55 sec) mysql select event.cid, event.timestamp from event, data where mysql event.timestamp between '2006-05-01' AND '2006-05-15'; mysql: Out of memory (Needed 1477684 bytes) ERROR 2008 (HY000): MySQL client ran out of memory Obviously, that is a bad idea. I just can not figure Out how to speed the select clause up. I was using the query: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; But the query never completed i.e. I aborted the query after a few hours. Thank you, Raymond -- 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: Trying to understand why Select running out of memory if table not used
The JOIN criteria was there: 'event.cid=data.cid' His query was fine: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; It may be rewritten into: SELECT event.cid, event.timestamp FROM event JOIN data ON event.cid=data.cid WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15'; ... Or ... SELECT event.cid, event.timestamp FROM event JOIN data USING(cid) WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15; Although that was not the issue... Maybe you ran out of memory into one of: max_allowed_packet net_buffer_length max_join_size Try enlarging those values... on the server also... But first try to run with '--compress' maybe this will fix... -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Self Join Performance
Interesting setup. You're using one more join than you need to. Your query should look like this: SELECT DISTINCT(fvr.DocumentID) FROM FieldValueRelation fvr INNER JOIN FieldValueRelation fvr2 ON fvr.DocumentID = fvr2.DocumentID AND fvr2.FieldValueID = '1569' WHERE fvr1.FieldValueID = '1344' Your query was so slow because you were first selecting ALL the records from FieldValueRelation, then using a join to filter out the records. You should be using a WHERE clause to filter out the first data set. - Original Message - From: Andrew Wood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 11, 2006 9:50 PM Subject: Self Join Performance Hello everyone! I've got a few questions regarding optimizing self-joins. So I've got these three tables: mysql describe FieldName; +-++--+-+-++ | Field | Type | Null | Key | Default | Extra | +-++--+-+-++ | FieldNameID | bigint(20) | NO | PRI | NULL| auto_increment | | Name| char(255) | YES | | NULL|| +-++--+-+-++ 2 rows in set (0.02 sec) mysql describe FieldValue; +--++--+-+-++ | Field| Type | Null | Key | Default | Extra | +--++--+-+-++ | FieldValueID | bigint(20) | NO | PRI | NULL| auto_increment | | FieldNameID | bigint(20) | NO | MUL | NULL|| | Value| char(255) | YES | MUL | NULL|| +--++--+-+-++ 3 rows in set (0.00 sec) mysql describe FieldValueRelation; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | FieldValueID | bigint(20) | NO | MUL | NULL| | | DocumentID | bigint(20) | NO | MUL | NULL| | | FieldSetID | tinyint(11) | YES | | NULL| | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) I've ran a script to populate the database with a decent set of randomized data (~7 million DocumentIDs). The following query may clear things up a bit: mysql select * from FieldName, FieldValue, FieldValueRelation where DocumentID = '700' and FieldValue.FieldValueID = FieldValueRelation.FieldValueID and FieldName.FieldNameID = FieldValue.FieldNameID; +-+---+--+-+--+--+++ | FieldNameID | Name | FieldValueID | FieldNameID | Value| FieldValueID | DocumentID | FieldSetID | +-+---+--+-+--+--+++ | 1 | Account | 6737063 | 1 | 88116010 | 6737063 |700 | NULL | | 2 | FirstName | 1344 | 2 | Noelle | 1344 |700 | 1 | | 3 | LastName | 1569 | 3 | Shea | 1569 |700 | 1 | +-+---+--+-+--+--+++ 3 rows in set (0.00 sec) So here's my question: I want to be able to find all of the rows in the FieldValueRelation table that have both a FirstName of 'Noelle' and a LastName of 'Shea'. I've tried a few things, all of which have less than stellar performance. I'm guesstimating that I'll have to do a self join on the FieldValueRelation table. Alas, it's pretty darn slow: SELECT DISTINCT(fvr.DocumentID) FROM FieldValueRelation fvr INNER JOIN FieldValueRelation fvr1 ON fvr.DocumentID = fvr1.DocumentID AND fvr1.FieldValueID = '1344' INNER JOIN FieldValueRelation fvr2 ON fvr.DocumentID = fvr2.DocumentID AND fvr2.FieldValueID = '1569' And when I execute it: ++ | DocumentID | ++ | 162955 | | 721704 | | 993290 | |1606157 | |2459823 | |2759626 | |3949779 | |5192230 | |5753563 | |6616602 | |700 | ++ 11 rows in set (37.33 sec) 37 seconds is a bit long for what I'd like to use this schema for. So here's the counts for the matching FieldValueIDs (if this helps): mysql select count(*) from FieldValueRelation where FieldValueID = '1569'; +--+ | count(*) | +--+ | 7753 | +--+ 1 row in set (0.05 sec) mysql select count(*) from FieldValueRelation where FieldValueID = '1344'; +--+ | count(*) | +--+ | 7357 | +--+ 1 row in set (0.01 sec) Can anyone shed any light on this? -- Cheers, -Andrew All generalizations, with the possible exception of this one, are false. - Kurt Goedel -- MySQL General Mailing List For list archives:
fulltext search optimization
Hello, I have a website with a MySQL database and I have a table with ~2 millions row (usernames, filenames; ~120MB db data file and ~230MB db index file) with I would like to search using the fulltext indeces. Unfortunately the server get loaded pretty much. It always stays on 20 load average and often gets 50-60 load average. I'm sure that this is because of slow mysql response (slow queries) to the apache web server. Can you give me some advice how to optimize the mysql server? The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD (currently I have 512MB free memory) Thanks, Svilen Spasov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 5.0.2 Beta Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 5.0.2 Beta, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. This is the last planned beta release of this branch of the driver. Please notice that version 5.0.1 wasn't released due to a last-minute packaging error. Version 5.0.2 is a release for the development branch that is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0 or MySQL-5.1 beta. It is now available in source and binary form from the Connector/J download pages at http://dev.mysql.com/downloads/connector/j/5.0.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) Please notice that the download archives are larger than before as we're now shipping the output of our JUnit release tests and resultant code coverage in the docs/release-test-output subdirectory. -Mark - From the changelog (this release includes changes and fixes from versions 3.1.13 and 3.1.14): 07-11-06 - Version 5.0.2-beta - Fixed BUG#17401 - Can't use XAConnection for local transactions when no global transaction is in progress. - Fixed BUG#18086 - Driver fails on non-ASCII platforms. The driver was assuming that the platform character set would be a superset of MySQL's latin1 when doing the handshake for authentication, and when reading error messages. We now use Cp1252 for all strings sent to the server during the handshake phase, and a hard-coded mapping of the language server variable to the character set that is used for error messages. - Fixed BUG#19169 - ConnectionProperties (and thus some subclasses) are not serializable, even though some J2EE containers expect them to be. - Fixed BUG#20242 - MysqlValidConnectionChecker for JBoss doesn't work with MySQLXADataSources. - Better caching of character set converters (per-connection) to remove a bottleneck for multibyte character sets. - Added connection/datasource property pinGlobalTxToPhysicalConnection (defaults to false). When set to true, when using XAConnections, the driver ensures that operations on a given XID are always routed to the same physical connection. This allows the XAConnection to support XA START ... JOIN after XA END has been called, and is also a workaround for transaction managers that don't maintain thread affinity for a global transaction (most either always maintain thread affinity, or have it as a configuration option). - MysqlXaConnection.recover(int flags) now allows combinations of XAResource.TMSTARTRSCAN and TMENDRSCAN. To simulate the scanning nature of the interface, we return all prepared XIDs for TMSTARTRSCAN, and no new XIDs for calls with TMNOFLAGS, or TMENDRSCAN when not in combination with TMSTARTRSCAN. This change was made for API compliance, as well as integration with IBM WebSphere's transaction manager. nn-nn-06 - Version 3.1.14 - Fixed BUG#20479 - Updatable result set throws ClassCastException when there is row data and moveToInsertRow() is called. - Fixed BUG#20485 - Updatable result set that contains a BIT column fails when server-side prepared statements are used. - Fixed BUG#16987 - Memory leak with profileSQL=true. - Fixed BUG#19726 - Connection fails to localhost when using timeout and IPv6 is configured. - Fixed BUG#16791 - NullPointerException in MysqlDataSourceFactory due to Reference containing RefAddrs with null content. - Fixed BUG#20306 - ResultSet.getShort() for UNSIGNED TINYINT returns incorrect values when using server-side prepared statements. - Fixed BUG#20687 - Can't pool server-side prepared statements, exception raised when re-using them. 05-26-05 - Version 3.1.13 - Fixed BUG#15464 - INOUT parameter does not store IN value. - Fixed BUG#14609 - Exception thrown for new decimal type when using updatable result sets. - Fixed BUG#15544, no dos character set in MySQL 4.1.0 - Fixed BUG#15383 - PreparedStatement.setObject() serializes BigInteger as object, rather than sending as numeric value (and is thus not complementary to .getObject() on an UNSIGNED LONG type). - Fixed BUG#11874 - ResultSet.getShort() for UNSIGNED TINYINT returned wrong values. - Fixed BUG#15676 - lib-nodist directory missing from package breaks out-of-box build - Fixed BUG#15854 - DBMD.getColumns() returns wrong type for BIT. - Fixed BUG#14938 - Unable to initialize character set mapping tables. Removed reliance on .properties files to hold this information, as it turns out to be too
Re: Tuning High Loads MySQL Server
Cover the basics first by looking at the SHOW STATUS results. You shold make yourself familiar with what these variables are telling you. It'll help in determining your bottleneck. http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html Right of the bat, look at the Threads_created number, if it's really high you should change the thread_cache_size variable. You can look at the Threads_connected value to get an idea how high to set it (higher). If your Opened_tables number is high, you want to increase your table_cache variable. Otherwise it's a pretty open ended question you are asking. If this is something you are serious about, you may want to buy a book like High Performance MySQL (I think that's the name). I've read chapters of it online (O'Reilly bookshelf) and there was some interesting stuff. Like how Yahoo solved one of there performance problems. - Original Message - From: Willy [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 11, 2006 10:23 PM Subject: Tuning High Loads MySQL Server Hello, I have a MySQL 4.x installed, and I have a very high requests on this server, it's about 1 - 5 requests/minutes. Any tips to tuning it? Many thanks for any replies. Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to understand why Select running out of memory if table not used
Gabriel PREDA wrote: The JOIN criteria was there: 'event.cid=data.cid' It was not there in the upper example he gave where he stated the problem. It was there in the later query he said he also tried. His query was fine: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; It may be rewritten into: SELECT event.cid, event.timestamp FROM event JOIN data ON event.cid=data.cid WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15'; ... Or ... SELECT event.cid, event.timestamp FROM event JOIN data USING(cid) WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15; Although that was not the issue... Maybe you ran out of memory into one of: max_allowed_packet net_buffer_length max_join_size Try enlarging those values... on the server also... But first try to run with '--compress' maybe this will fix... -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning High Loads MySQL Server
Willy, the docs on MySQL's site have a lot of good information: http://dev.mysql.com/doc/refman/4.1/en/optimize-overview.html Also, Jeremy Zawodny's book High Performance MySQL (O'Reilly) is an excellent guide to MySQL tuning, performance, replication - and it was written for 4.1, right up your alley. Dan On 7/11/06, Willy [EMAIL PROTECTED] wrote: Hello, I have a MySQL 4.x installed, and I have a very high requests on this server, it's about 1 - 5 requests/minutes. Any tips to tuning it? Many thanks for any replies. Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JBTF
The message contains Unicode characters and has been sent as a binary attachment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search optimization
Svilen Spasov (Ancient Media) wrote: Hello, I have a website with a MySQL database and I have a table with ~2 millions row (usernames, filenames; ~120MB db data file and ~230MB db index file) with I would like to search using the fulltext indeces. Unfortunately the server get loaded pretty much. It always stays on 20 load average and often gets 50-60 load average. I'm sure that this is because of slow mysql response (slow queries) to the apache web server. Can you give me some advice how to optimize the mysql server? The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD (currently I have 512MB free memory) Run a SHOW CREATE TABLE tablename on the table and post the results here. (We're particularly interested in how it is indexed.) Tell us what queries are being run against the table. If possible post the actual SQL queries. -J Thanks, Svilen Spasov --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]
Allow user to create databases, but limit all privileges to other databases
I would like to create a user that has the ability to create databases. I would also like the user to be able to have all privileges including grant over the databases they create. However, I do not want them to have any access to other databases on the server. I've been digging through the documentation for a while and have not been able to figure out a solution, even though it seems like this would be a pretty standard requirement in shared hosting environments. Any help would be greatly appreciated! PS. On the Databse Administration support forum there is a post regarding this exact same issue, and I was originally going to add a 'me too!' response to that post, but the registration system for the forums seems to be broken and I was never able to successfully log in to post.
Re: I don't understand why SCSI is preferred.
On 7/11/06, Brian Dunning [EMAIL PROTECTED] wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. A busy database needs really fast access, Your statement is partially correct, yes, it has faster transfer rates, but that is not only for tranfer large files, it accelerates any access to the disk, because the queue will run faster and demanding apps will have a better response time (that is all theory, of course). for making numerous fast calls all over the disk. Two different, unrelated things. SCSI also has a controller that process, queues and serves the data, this would reduce CPU time and provide faster access. It also is more fit for high demand, because of its higher spin rates, and it also runs better in a server environment where there is high load 24/7. I am more than willing to be called Wrong, slapped, and cast from a bridge. Nobody will do that, but you can jump for yourself for not googling for ide scsi sata pata performance. ;) I'm just kidding. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp?DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Allow user to create databases, but limit all privileges to other databases
I would like to create a user that has the ability to create databases. I would also like the user to be able to have all privileges including grant over the databases they create. However, I do not want them to have any access to other databases on the server. I've been digging through the documentation for a while and have not been able to figure out a solution, even though it seems like this would be a pretty standard requirement in shared hosting environments. Any help would be greatly appreciated! PS. On the Databse Administration support forum there is a post regarding this exact same issue, and I was originally going to add a 'me too!' response to that post, but the registration system for the forums seems to be broken and I was never able to successfully log in to post.
Re: I don't understand why SCSI is preferred.
At 12:42 PM 7/12/2006, you wrote: On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp?DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. That's good to hear, but MTBF is really a pie in the sky estimate. I had an expensive HP tape drive that had something like 20,000 hr MTBF. Both of my units failed at under 70 hours. HP's estimate was power on hours (unit powered on and doing nothing), and did NOT include hours when the tape was in motion. Sheesh. To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. This is far from being accurate because as we all know, the older the drive, the more likely it is to fail. (Especially after the warranty period has expired, failure rate is quite highg). I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Allow user to create databases, but limit all privileges to other databases
Isaac Davis-King wrote: I would like to create a user that has the ability to create databases. I would also like the user to be able to have all privileges including grant over the databases they create. However, I do not want them to have any access to other databases on the server. I've been digging through the documentation for a while and have not been able to figure out a solution, even though it seems like this would be a pretty standard requirement in shared hosting environments. Any help would be greatly appreciated! PS. On the Databse Administration support forum there is a post regarding this exact same issue, and I was originally going to add a 'me too!' response to that post, but the registration system for the forums seems to be broken and I was never able to successfully log in to post. You asked the same question 2 hours ago. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike By newer SATA II drivers, are you referring to SAS drives? There is a great article on Tom's hardware on SAS drives as a replacement for standard SCSI: http://www.tomshardware.com/2006/04/07/going_the_sas_storage_way/index.html My company is in the process of switching to direct attached SAS arrays for our database servers, as part of a scale-out model. We've done testing between SATA, SCSI, and SAS arrays, and the SCSI and SAS systems were very comparative. The number of disks in the array seemed to have a larger effect then the type of disk. SAS also has more fiber like features then SCSI, making it better suited for HA environments. Just something else to consider. Regards, Scott Tanner Sys Admin www.amientertainment.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On 7/12/06, mos [EMAIL PROTECTED] wrote: At 12:42 PM 7/12/2006, you wrote: On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp?DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. That's good to hear, but MTBF is really a pie in the sky estimate. I had an expensive HP tape drive that had something like 20,000 hr MTBF. Both of my units failed at under 70 hours. HP's estimate was power on hours (unit powered on and doing nothing), and did NOT include hours when the tape was in motion. Sheesh. To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. This is far from being accurate because as we all know, the older the drive, the more likely it is to fail. (Especially after the warranty period has expired, failure rate is quite highg). I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's The answer (short and based on experience) is NO! A SATA drive is no different from an IDE drive of the same type. I'm sure they'll release fast and reliable drives based on SATA with differenct mechanisms (like the one Joshua pointed), but most will be IDE like with a different interface, those high demand drives are fated to cost a lot more. to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. That is something only an ISP or corporation would give (and no one will EVER sign it, *lol*). SCSI has one more advantage I forgot to add to my previous message, they can be arranged better in RAID with hot swap. I can only tell about my company, where servers have all SCSI disks (IBM, Dell). -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Tuesday 11 July 2006 04:18 pm, Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. A busy database needs really fast access, for making numerous fast calls all over the disk. Two different, unrelated things. I am more than willing to be called Wrong, slapped, and cast from a bridge. Hmm, not sure if the question at hand is being answered. The topics I've seen so far seem to indicate why SCSI is fast. However, the original question was more along the lines of Does it matter with regards to database performance?. From what I know of MySQL, not really, because MySQL does a good amount of work in memory. The only time I'd see disk access being a factor is if you had a large mass of swap/virtual memory. Now one place where I'm sure it would matter is if you were doing a substantial amount of logging, or db dumping to disk. Then yes, you'd want a nice fast disk at that point. -- Chris White PHP Programmer/DBlowMeAway Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-workbench 1.0.6beta not working on reverse-engineer
rturnbull wrote: Hello to all, I'm having some problems with the linux copy of mysql-workbench. Great features and all, if I could get them to work. What I'm trying to do is reverse-engineer a INNODB database I have in mysql 5.0 on my local machine. I go through the steps right to the end of the process and when I'm ready to click Finish it hangs the application. I can't do anything further with the application, and I have to manually kill or repeatedly click on the close window 'x'. I have tried the reverse-engineer on the mysql database on the same box, using the same steps and application version and everything works fine, so what gives? To boot with this, there is NO LOGFILE NO ERROR MESSAGES on the console or anything. Could it be that the reverse-engineering process is still running in the background? Anyways, I thought that maybe it could be the database, which was full of records was causing the problem. So I created a mysqldump file with just table create statements, then re-imported the file back into mysql. Same result, workbench freezes. Please let me know if there is anything I can do about this, otherwise I guess I have to wait for the newest version to address this, or manually draw all the table relationships by hand YUK! Workbench's reverse engineering doesn't work also for mac os x (10.4.7). The field in localhost is unaccessible. Just one question with linux version, which package did you use? For me even after upgrading the to gtkmm2.4 , I still get errors and a segmentation fault. yvan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I don't understand why SCSI is preferred.
I've seen whitepapers from MySQL's web site, co-authored with Dell, that recommend the hardware optimization be: 1. More Memory 2. Faster Drives (15K RPM is better the 10K) 3. Faster CPU. Based on this, we're spec'ing 2950s with 16Gb, dual 2.8 dual-core Xeons, and 146Gb 15K (times 6) drives. The plan is to RAID then 2 x RAID1 for the o/s (/boot, /, /var, and some working space for dumps and restores), and 4 x RAID10 for /data. Anyone have any feedback on this? Tim -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 3:59 PM To: mysql@lists.mysql.com Subject: Re: I don't understand why SCSI is preferred. On Tuesday 11 July 2006 04:18 pm, Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. A busy database needs really fast access, for making numerous fast calls all over the disk. Two different, unrelated things. I am more than willing to be called Wrong, slapped, and cast from a bridge. Hmm, not sure if the question at hand is being answered. The topics I've seen so far seem to indicate why SCSI is fast. However, the original question was more along the lines of Does it matter with regards to database performance?. From what I know of MySQL, not really, because MySQL does a good amount of work in memory. The only time I'd see disk access being a factor is if you had a large mass of swap/virtual memory. Now one place where I'm sure it would matter is if you were doing a substantial amount of logging, or db dumping to disk. Then yes, you'd want a nice fast disk at that point. -- Chris White PHP Programmer/DBlowMeAway Interfuel -- 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: Mysql-workbench 1.0.6beta not working on reverse-engineer
Yvan, I used three different packages for Linux. 1) Was the source tar.gz 2) was the rpm which I converted to a tgz file (slackware) 3) was the compiled binary version of the workbench. Here are the filenames mysql-workbench-1.0.6beta-1.i386.rpm mysql-workbench-1.0.6beta-1.i386.tgz mysql-workbench-1.0.6beta.tar.gz So the version is 1.0.6. As for the gtkmm2.4 package, I as well used that packages and still errors occur. I have done some further looking into it and it appears to be the build that the linux packages /tar file are. I was able to get everything working with the Windows GUI-TOOLS package on my windows system. The version of the tool is different under windows than it is under linux, so I think there are modifications with reverse-engineering that might be in the Linux packages but in the Windows version. Its a big disappointment that the tool doesn't work properly under *NIX systems (Really just reverse-engineering is broken, but I haven't tried any further POWER FEATURES). Thanks Yvan wrote: rturnbull wrote: Hello to all, I'm having some problems with the linux copy of mysql-workbench. Great features and all, if I could get them to work. What I'm trying to do is reverse-engineer a INNODB database I have in mysql 5.0 on my local machine. I go through the steps right to the end of the process and when I'm ready to click Finish it hangs the application. I can't do anything further with the application, and I have to manually kill or repeatedly click on the close window 'x'. I have tried the reverse-engineer on the mysql database on the same box, using the same steps and application version and everything works fine, so what gives? To boot with this, there is NO LOGFILE NO ERROR MESSAGES on the console or anything. Could it be that the reverse-engineering process is still running in the background? Anyways, I thought that maybe it could be the database, which was full of records was causing the problem. So I created a mysqldump file with just table create statements, then re-imported the file back into mysql. Same result, workbench freezes. Please let me know if there is anything I can do about this, otherwise I guess I have to wait for the newest version to address this, or manually draw all the table relationships by hand YUK! Workbench's reverse engineering doesn't work also for mac os x (10.4.7). The field in localhost is unaccessible. Just one question with linux version, which package did you use? For me even after upgrading the to gtkmm2.4 , I still get errors and a segmentation fault. yvan -- Ryan Turnbull Network Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
This REALLY should be an academic concern. Either you have a system that can tolerate the failure of a drive, or you do not. The frequency of failure rates is pretty much irrelevant: You can train incredibly non-technical (inexpensive) people to respond to a pager and hot-swap a bad drive. If you are in the position where the typical failure-rate of a class of drive is of concern to you then either: A) You have a different problem causing all your drives to fail ultra-fast (heat, electrical noise, etc) or B) You haven't adequately designed your storage subsystem. Save yourself the headache, and just set up a RAID10 PATA/SATA array with a hot spare. Not sure if Linux/FreeBSD/et al support hot-swap of drives when using software RAID, but if it does then you don't even need to spend a few hundred bucks on a RAID controller. -JF On Jul 12, 2006, at 12:11 PM, mos wrote: At 12:42 PM 7/12/2006, you wrote: On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp? DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. That's good to hear, but MTBF is really a pie in the sky estimate. I had an expensive HP tape drive that had something like 20,000 hr MTBF. Both of my units failed at under 70 hours. HP's estimate was power on hours (unit powered on and doing nothing), and did NOT include hours when the tape was in motion. Sheesh. To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. This is far from being accurate because as we all know, the older the drive, the more likely it is to fail. (Especially after the warranty period has expired, failure rate is quite highg). I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Wednesday 12 July 2006 20:11, mos wrote: To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. I don't know much statistics, but I do know that that estimate would not just be inaccurate - it would be absurdly wrong. -- Timothy Murphy e-mail (80k only): tim /at/ birdsnest.maths.tcd.ie tel: +353-86-2336090, +353-1-2842366 s-mail: School of Mathematics, Trinity College, Dublin 2, Ireland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Jul 12, 2006, at 12:45 PM, Scott Tanner wrote: I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike By newer SATA II drivers, are you referring to SAS drives? No, typically SATA II is meant to refer to SATA w/ NCQ + doubled max throughput. My company is in the process of switching to direct attached SAS arrays for our database servers, as part of a scale-out model. We've done testing between SATA, SCSI, and SAS arrays, and the SCSI and SAS systems were very comparative. The number of disks in the array seemed to have a larger effect then the type of disk. SAS also has more fiber like features then SCSI, making it better suited for HA environments. Yeah, that's sort of the conventional-wisdom for drive arrays: More spindles == faster. It's roughly analogous to adding CPUs versus getting faster CPUs with a workload that's easily parallelizable. More spindles means more heads. More heads means more simultaneous seeks, reads, and writes. -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Jul 12, 2006, at 12:56 PM, Daniel da Veiga wrote: On 7/12/06, mos [EMAIL PROTECTED] wrote: At 12:42 PM 7/12/2006, you wrote: On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp? DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. That's good to hear, but MTBF is really a pie in the sky estimate. I had an expensive HP tape drive that had something like 20,000 hr MTBF. Both of my units failed at under 70 hours. HP's estimate was power on hours (unit powered on and doing nothing), and did NOT include hours when the tape was in motion. Sheesh. To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. This is far from being accurate because as we all know, the older the drive, the more likely it is to fail. (Especially after the warranty period has expired, failure rate is quite highg). I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's The answer (short and based on experience) is NO! A SATA drive is no different from an IDE drive of the same type. I'm sure they'll release fast and reliable drives based on SATA with differenct mechanisms (like the one Joshua pointed), but most will be IDE like with a different interface, those high demand drives are fated to cost a lot more. Rule of thumb: If you see a SATA drive that is 18GB, 36GB, 72GB, or 144GB and costs WAY more per GB than other SATA drives of more normal capacities (80GB, 100GB, 120GB, 160GB, 200GB...) then it's probably using the same physical drive as a SCSI drive but with a SATA interface tacked on instead. That is something only an ISP or corporation would give (and no one will EVER sign it, *lol*). SCSI has one more advantage I forgot to add to my previous message, they can be arranged better in RAID with hot swap. I can only tell about my company, where servers have all SCSI disks (IBM, Dell). Have you had any specific problems with SATA/PATA hot-swap? We've only had problems when we've tried to use a ThreeWare RAID card and tried to do hot-swap... -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Wednesday 12 July 2006 01:13 pm, Tim Lucia wrote: I've seen whitepapers from MySQL's web site, co-authored with Dell, that recommend the hardware optimization be: 1. More Memory That's a definite 2. Faster Drives (15K RPM is better the 10K) Well, I guess for any server really, the faster the disk writes (Though let's be honest, the faster the disk writes AND the better integrity disk). Generally this is, in my opinion more suitable for things like logging, or the times MySQL actually decides to write to the disk (here's where a MySQL person steps in and states when that is ;) ). 3. Faster CPU. As with most things these days. Better CPU means less worry about Oh, I wonder if I can do this and increases the time period between now and when you need to scale. Based on this, we're spec'ing 2950s with 16Gb, dual 2.8 dual-core Xeons, and 146Gb 15K (times 6) drives. Sounds about right. If you're on a linux system I also recommend that you turn on NPTL (Native Posix Threading Library), which is done through glibc (or by grabbing an rpm/deb/whatever with said support). As always, don't forget the SMP support in the kernel to benifit from the Dual-Core (I'm guessing you probably know this, but hey.. never hurts). The plan is to RAID then 2 x RAID1 for the o/s (/boot, /, sounds good /var, and some It's actually best to shove this on a separate disk. As the name implies, /var is for variable data. That said, you'll be chucking everything and the kitchen sink at it. Logs, spools, etc. These suckers are constantly being written to, and let's forgot the fact that some people attack servers by shoving data at it, which goes to logs.. which take up space.. you get the idea. working space for dumps and restores), and 4 x RAID10 for /data. Anyone have any feedback on this? Some people use replication servers for backups, others use the same drive. I like the idea of a separate backup replication server as if the main one goes down, I've got a real physically separated backup to work with. In the end that's what matters. -- Chris White PHP Programmer/DBloomingOnions Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Jul 12, 2006, at 12:58 PM, Chris White wrote: On Tuesday 11 July 2006 04:18 pm, Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. A busy database needs really fast access, for making numerous fast calls all over the disk. Two different, unrelated things. I am more than willing to be called Wrong, slapped, and cast from a bridge. Hmm, not sure if the question at hand is being answered. The topics I've seen so far seem to indicate why SCSI is fast. However, the original question was more along the lines of Does it matter with regards to database performance?. From what I know of MySQL, not really, because MySQL does a good amount of work in memory. The only time I'd see disk access being a factor is if you had a large mass of swap/virtual memory. Now one place where I'm sure it would matter is if you were doing a substantial amount of logging, or db dumping to disk. Then yes, you'd want a nice fast disk at that point. That's just silly. ALL databases attempt to do as MUCH AS POSSIBLE in memory. The disk is ALWAYS the enemy when it comes to a relational database. The only question is the design of the database and of the queries. If you have some leeway to muck about with the design of each then you can often find ways of making the database *do less work* (talk to the disk/ram less) which is always preferable to trying to make the disk faster. -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Wednesday 12 July 2006 01:13 pm, Tim Lucia wrote: I've seen whitepapers from MySQL's web site, co-authored with Dell, that recommend the hardware optimization be: 1. More Memory 2. Faster Drives (15K RPM is better the 10K) 3. Faster CPU. Oh wait, we forgot #4: 4. Filesystem You can have the fastest disk alive, but if your filesystem is doing sleep(1000) during every transfer (this is 1% possible, but just an example), you're data transfer is just plain going to suck. There's a couple of Filesystems out there: Ext2/3 I recommend ext3 here. It's tried and true tested throughout the business world, kind of slow at times, but mostly stable in the end. You'll generally see this as the filesystem of choice for those running *NIX type systems. XFS This one does a lot of operations in memory, and tries to write to disk as infrequently as possible, instead caching it in memory. This does wonders for transfer rates, but just remember, memory is a temporary storage. If your power goes out, kiss your data goodbye! If you still want performance, at least put your server behind a nice UPS! JFS I use this at home a lot, and it works fairly well. It seems to be a nice mix of speed and stability. When something does go wrong, fsck takes under 30 seconds on a 30GB drive. Unfortunately this doesn't have too much corporate world exposure like ext2/3. Good for when you're bored on a sunny Tuesday and want to try something new out. Fat32/NTFS Well, this is kind of a quick answer. Most will straightup go NTFS nowdays (iirc because of speed and security labels, but I haven't dealt with windows filesystems in awhile). -- Chris White PHP Programmer/DBooyah! Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I don't understand why SCSI is preferred.
-Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 12, 2006 5:15 PM To: mysql@lists.mysql.com Subject: Re: I don't understand why SCSI is preferred. On Wednesday 12 July 2006 01:13 pm, Tim Lucia wrote: I've seen whitepapers from MySQL's web site, co-authored with Dell, that recommend the hardware optimization be: 1. More Memory That's a definite 2. Faster Drives (15K RPM is better the 10K) Well, I guess for any server really, the faster the disk writes (Though let's be honest, the faster the disk writes AND the better integrity disk). Generally this is, in my opinion more suitable for things like logging, or the times MySQL actually decides to write to the disk (here's where a MySQL person steps in and states when that is ;) ). 3. Faster CPU. As with most things these days. Better CPU means less worry about Oh, I wonder if I can do this and increases the time period between now and when you need to scale. Based on this, we're spec'ing 2950s with 16Gb, dual 2.8 dual-core Xeons, and 146Gb 15K (times 6) drives. Sounds about right. If you're on a linux system I also recommend that you turn on NPTL (Native Posix Threading Library), which is done through glibc (or by grabbing an rpm/deb/whatever with said support). As always, don't forget the SMP support in the kernel to benifit from the Dual-Core (I'm guessing you probably know this, but hey.. never hurts). The plan is to RAID then 2 x RAID1 for the o/s (/boot, /, sounds good /var, and some It's actually best to shove this on a separate disk. As the name implies, /var is for variable data. That said, you'll be chucking everything and the kitchen sink at it. Logs, spools, etc. These suckers are constantly being written to, and let's forgot the fact that some people attack servers by shoving data at it, which goes to logs.. which take up space.. you get the idea. /var would be on a separate partition, on the same physical RAID set -- sorry that was obvious to *me* but I didn't say that. working space for dumps and restores), and 4 x RAID10 for /data. Anyone have any feedback on this? Some people use replication servers for backups, others use the same drive. I like the idea of a separate backup replication server as if the main one goes down, I've got a real physically separated backup to work with. In the end that's what matters. The plan is to backup the slave. I just want to reserve some space if I need to have a local dump file or something. -- Chris White PHP Programmer/DBloomingOnions Interfuel -- 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: Mysql-workbench 1.0.6beta not working on reverse-engineer
rturnbull wrote: Yvan, I used three different packages for Linux. 1) Was the source tar.gz 2) was the rpm which I converted to a tgz file (slackware) 3) was the compiled binary version of the workbench. Here are the filenames mysql-workbench-1.0.6beta-1.i386.rpm mysql-workbench-1.0.6beta-1.i386.tgz mysql-workbench-1.0.6beta.tar.gz So the version is 1.0.6. As for the gtkmm2.4 package, I as well used that packages and still errors occur. I have done some further looking into it and it appears to be the build that the linux packages /tar file are. I was able to get everything working with the Windows GUI-TOOLS package on my windows system. The version of the tool is different under windows than it is under linux, so I think there are modifications with reverse-engineering that might be in the Linux packages but in the Windows version. Its a big disappointment that the tool doesn't work properly under *NIX systems (Really just reverse-engineering is broken, but I haven't tried any further POWER FEATURES). Thanks Thanks Ryan, I also ended up by using workbench on xp and then opening the .mwb file in the other systems. Not very elegant... Thanks for your answer. yvan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-workbench 1.0.6beta not working on reverse-engineer
At 07:16 PM 7/12/2006, Yvan wrote: rturnbull wrote: Yvan, I used three different packages for Linux. 1) Was the source tar.gz 2) was the rpm which I converted to a tgz file (slackware) 3) was the compiled binary version of the workbench. Here are the filenames mysql-workbench-1.0.6beta-1.i386.rpm mysql-workbench-1.0.6beta-1.i386.tgz mysql-workbench-1.0.6beta.tar.gz So the version is 1.0.6. As for the gtkmm2.4 package, I as well used that packages and still errors occur. I have done some further looking into it and it appears to be the build that the linux packages /tar file are. I was able to get everything working with the Windows GUI-TOOLS package on my windows system. The version of the tool is different under windows than it is under linux, so I think there are modifications with reverse-engineering that might be in the Linux packages but in the Windows version. Its a big disappointment that the tool doesn't work properly under *NIX systems (Really just reverse-engineering is broken, but I haven't tried any further POWER FEATURES). Thanks Thanks Ryan, I also ended up by using workbench on xp and then opening the .mwb file in the other systems. Not very elegant... Thanks for your answer. yvan I used the Workbench under XP - once I started adding relations and foreign keys, everything fell apart. So I took the generated SQL, edited it by hand and generated the tables. It's still pretty rough. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 7/11/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
