optimizing inserts
Where might I find information about optimizing inserts to MySQL tables. I've purchased 2 books, and so far, the only optimizations I see concern reads, not writes. The only advice I've received so far on optimizing writes is to have my application dump the records to a text file first, and do periodic LOAD DATA INFILE commands, so as to bulk insert records. Are there any websites/webpages or books that detail optimizing writes to MySQL databases (aside from more memory or faster CPU or faster hard drive). Thanx, Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Getting timing from MySQL scripts
Whenever I run a command from the MySQL prompt, The command executes and outputs the time to process the command. However, if I put the same command in a script file, and run it in batch mode from the shell prompt (or even use SOURCE from the mysql prompt), I do not get the same timing info. I want to run some benchmark tests, but I need precision greater than 1 sec (the automatic timing output from MySQL is to the hundredth of a second, which will suffice). Please help. I am not using PHP at all - just sourcing MySQL query scripts. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problem: slave can't log into master
I don't even have a master.info file to remove. The master.info file never gets written. It won't start replication at all. It just keeps writing the same error to the log file: Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I don't understand what I have missed that a connection from the slave to the master is not allowed. Again, here is what is in my.cnf on the master and the slave. Master: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- Slave: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 08, 2002 3:45 PM To: Jamie Beu Cc: [EMAIL PROTECTED] Subject: Re: Replication problem: slave can't log into master On Thu, Sep 05, 2002 at 09:52:05AM -0400, Jamie Beu wrote: I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; Perhaps the master.info file on the slave is wrong. Try removing it and restatring the slave. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 33 days, processed 671,118,015 queries (231/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication problem: slave can't log into master (update)
I don't have a master.info file in my /usr/local/mysql/data dir, only 2 directories: mysql and test, neither of which contain master.info. What is supposed to be in the master.info file? I don't recall any reference to a master.info file in the replication setup procedure described in the MySQL online manual. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 -Original Message- From: Dicky Wahyu Purnomo [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 11:02 PM To: Jamie Beu Cc: [EMAIL PROTECTED] Subject: Re: Replication problem: slave can't log into master (update) Pada Thu, 5 Sep 2002 12:35:45 -0400 Jamie Beu [EMAIL PROTECTED] menulis : I am still having problems with the slave server being able to connect to the master, but a thought occured to me while we were working another topic. Please let me know what I'm missing. I can do the following: mysql -hctiadb1 -urepl -preplctia go to your slave server : look at master info file in your mysql data directory, e.g : /usr/local/mysql/data/master.info change if necessary the replication configuration ;-) -- ---Original Message--- I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; So, I am now able to log into the master from the slave as follows: mysql -hctiadb1 -urepl -preplctia and I can access any database whenever I wish. I just can't seem to get replication to work. Please tell me what I am doing wrong or missing. The /etc/my.cnf file on the master (ctiadb1) is as follows: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- The /etc/my.cnf file on the slave (ctiadb2) is as follows: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication problem: slave can't log into master
I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; Please tell me what I am doing wrong or missing. The /etc/my.cnf file on the master (ctiadb1) is as follows: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- The /etc/my.cnf file on the slave (ctiadb2) is as follows: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Thank you. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication problem: slave can't log into master (update)
I am still having problems with the slave server being able to connect to the master, but a thought occured to me while we were working another topic. Please let me know what I'm missing. I can do the following: mysql -hctiadb1 -urepl -preplctia and can access whatever database I wish. I just can't seem to get replication to work. I even tried creating a Linux user account for repl on ctiadb1 (the master server), in order to enable file access for replication. This seemed to work for allowing load data local infile commands, but it did nothing for replication. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 ---Original Message--- I am attempting to setup MySQL replication between the ctiadb1 (master) server and the ctiadb2 (slave) server. When I look at the mysql.log file on the slave, I see the following (repeated every 60 seconds): 020904 12:00:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec 020904 12:01:30 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec I already typed the following on the Master server: grant file on *.* to 'repl'@'%' identified by 'replctia'; flush privileges; Please tell me what I am doing wrong or missing. The /etc/my.cnf file on the master (ctiadb1) is as follows: --- [mysqld] log-bin server-id=21 max_binlog_size=512M --- The /etc/my.cnf file on the slave (ctiadb2) is as follows: --- [mysqld] master-host=ctiadb1 master-user=repl master-password=replctia master-port=3306 server-id=87 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_home_dir= innodb_data_file_path=/innodb_data/idbdata1:10M;/innodb_data/idbdata2:50M;/t est/innodb_data/idbdata3:100M:autoextend:max:512M # Set buffer pool size to 50-80% of computer's memory set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=16M # Set log file size to 25% of buffer pool size set-variable = innodb_log_file_size=40M set-variable = innodb_log_buffer_size=8M # Set innodb_flush_log_at_trx_commit to 0 if you can afford losing some last transactions innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Thank you. Jamie Beu Lockheed-Martin Information Systems Software Engineer CTIA (407) 306-2484 The true soldier fights not because he hates what is in front of him, but because he loves what is behind him. G. K. Chesterton - Illustrated London News, 1/14/11 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: query problem
You cna fix this problem with the HAVING keyword in the WHERE clause.
HAVING is like WHERE, but on group-by functions, like COUNT(*).
Change the query to:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
HAVING count(correct)=4
This should work (*although I have not tried it yet*).
Jamie Beu
Lockheed-Martin Information Systems
Software Engineer
CTIA
(407) 306-2484
The true soldier fights not because he hates what is in
front of him, but because he loves what is behind him.
G. K. Chesterton - Illustrated London News, 1/14/11
-Original Message-
From: Tom Emerson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 05, 2002 11:43 AM
To: [EMAIL PROTECTED]
Subject: RE: query problem
I am by no means an SQL-expert, but I'll give this a shot... :)
-Original Message-
From: Richard Brenner [mailto:[EMAIL PROTECTED]]
Subject: query problem
I have two tables with the following structure:
Users:
| Field| Type
| id | int(10) unsigned
| name | blob
[etc]
+--+--
answers:
+--+-+
| id | int(10) unsigned|
| userid | int(10) unsigned|
| question | int(10) unsigned|
| correct | set('true','false') |
| date | date|
+--+-+
This is for a quiz. Every user has to answer 4 questions ...
I want to print out all users, that
have answered all 4 questions correct.
Can I do this with one query?
I built a couple of tables similar to what you have [trimmed to the
essentials], populated with some data, and ran the following:
SELECT id,name,userid,correct,count(correct) as numcorrect
FROM users,answers
WHEREid=userid AND correct='true'
GROUP BY id
which created:
++-++-++
| id | name| userid | correct | numcorrect |
++-++-++
| 1 | alfred | 1 | true| 3 |
| 2 | bobby | 2 | true| 4 |
| 3 | carol | 3 | true| 2 |
| 4 | diane | 4 | true| 3 |
| 5 | edward | 5 | true| 3 |
| 6 | frank | 6 | true| 3 |
| 7 | george | 7 | true| 1 |
| 8 | harry | 8 | true| 3 |
| 9 | larry | 9 | true| 1 |
| 10 | mark| 10 | true| 3 |
| 11 | nancy | 11 | true| 4 |
| 12 | oliver | 12 | true| 3 |
| 13 | paul| 13 | true| 2 |
| 14 | quentin | 14 | true| 4 |
| 15 | ralph | 15 | true| 3 |
| 16 | samuel | 16 | true| 3 |
| 17 | thomas | 17 | true| 1 |
| 18 | ursula | 18 | true| 4 |
| 19 | victor | 19 | true| 2 |
| 20 | walter | 20 | true| 3 |
| 21 | xavier | 21 | true| 4 |
| 22 | yvonne | 22 | true| 3 |
| 23 | zack| 23 | true| 2 |
| 24 | igor| 24 | true| 3 |
| 25 | jack| 25 | true| 4 |
| 26 | kristen | 26 | true| 4 |
++-++-++
unfortunately, adding and numcorrect=4 to the WHERE clause caused an error
(numcorrect undefined), but if this were put into a temporary table, it is
then trivial to
SELECT * FROM results WHERE numcorrect=4;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
