Problem with character set and connection collation
Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo
Optimizing table (shall I create a primary field?)
Hi.
My hosting provider recommended me to optimize my 200,000 record table in
order to save resources.
I do _always_ this query:
SELECT * FROM books WHERE isbn='foo' LIMIT 1
The primary key of this table was 'id', and 'isbn' was and INDEX field.
I've modified this:
ALTER TABLE books DROP PRIMARY KEY, ADD INDEX ('isbn')
ALTER TABLE books ADD PRIMARY KEY ('isbn')
Is this a good change? Am I going to waste less resources with 'isbn' field
as primary key?
Thank you very much.
Re: Optimizing table (shall I create a primary field?)
On Sun, Apr 27, 2008 at 3:59 AM, Charles Lambach
[EMAIL PROTECTED] wrote:
I do _always_ this query:
SELECT * FROM books WHERE isbn='foo' LIMIT 1
The primary key of this table was 'id', and 'isbn' was and INDEX field.
This sentence could have been better written. If you have a primary
key index on (id,isbn) and isbn is not indexed as the left most column
of another index then an index would not be used for the above query.
If you have a prymary key index on (id) and another index on ('isbn')
then that index would probably be used.
It would much easier to tell you whats going on if you post your DDL
(so post the output of 'SHOW CREATE TABLE books;') and your EXPLAIN
(so post the output of 'EXPLAIN SELECT * FROM books WHERE isbn='foo'
LIMIT 1;').
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Issue
I have setup a mysql50 instance on a system where mysql4.0.20 already exists. So that I can run both instances together, I setup a new user called mysql50. The mysql50 files are owned by this mysql50 user and mysql50 is in the path. I setup a link, /usr/local/mysql-5.0 which points to the mysql50 directory. I modifed the mysql.server and I put it in the mysql-5.0 directory. Inside the file is a user parameter called user. I set the user to mysql50. When I run mysql.server -start, It exists with an error. The error log sayd that I do not have permissions to the ibdata files. However, the error.log gets created by the mysql user and NOT the mysql50 user. So I think this script is trying to access the ibdata files in the mysql-4.0.20 folders and it cannot get to them since they are owned by mysql and not mysql-5.0. So I am kind of stuck at this point. My guess is somehwere else, both the user of mysql and the directory of mysql is set and since I am using different variables, it is not working. Anyway, if anyone has any suggestions, it would be appreciated. Thanks, Mark -- View this message in context: http://www.nabble.com/User-Issue-tp16926263p16926263.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump character set
Hi, I'm storing some ascii codes in a table. When I do a dump using mysqldump from the server and then restoring it on another station, the ascii codes in the table has changed. But if i'm accessing the table from another station the code is good. Even if I insert it from a station, it goes fine in the server. Anyone knows why it changes when using mysqldump? How can I prevent this problem to happen in the future? Regards, Velen
Re: User Issue
Use my.cnf to point the data directory and the user details. [mysqld] user=mysql50 datadir=/home/mysql50 Saravanan --- On Mon, 4/28/08, Mark-E [EMAIL PROTECTED] wrote: From: Mark-E [EMAIL PROTECTED] Subject: User Issue To: mysql@lists.mysql.com Date: Monday, April 28, 2008, 12:05 AM I have setup a mysql50 instance on a system where mysql4.0.20 already exists. So that I can run both instances together, I setup a new user called mysql50. The mysql50 files are owned by this mysql50 user and mysql50 is in the path. I setup a link, /usr/local/mysql-5.0 which points to the mysql50 directory. I modifed the mysql.server and I put it in the mysql-5.0 directory. Inside the file is a user parameter called user. I set the user to mysql50. When I run mysql.server -start, It exists with an error. The error log sayd that I do not have permissions to the ibdata files. However, the error.log gets created by the mysql user and NOT the mysql50 user. So I think this script is trying to access the ibdata files in the mysql-4.0.20 folders and it cannot get to them since they are owned by mysql and not mysql-5.0. So I am kind of stuck at this point. My guess is somehwere else, both the user of mysql and the directory of mysql is set and since I am using different variables, it is not working. Anyway, if anyone has any suggestions, it would be appreciated. Thanks, Mark -- View this message in context: http://www.nabble.com/User-Issue-tp16926263p16926263.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configuring a PowerEdge 2950III, suggestions?
Hi, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene
Optimal MySQL server -- opinions?
Okay, the previous subject was too narrow, what I am really looking for are opinions on general disk/memory/cpu configurations, manufacturer notwithstanding... As stated previously, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene
Improved Cacti templates for MySQL
I've created some improved templates and scripts for monitoring MySQL servers with Cacti. Overview: http://www.xaprb.com/blog/2008/04/27/improved-cacti-monitoring-templates-for-mysql/ Homepage where you can help improve and maintain it more yourself (hint, hint): http://code.google.com/p/mysql-cacti-templates/issues/list Have fun! Baron -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database cache corrupted
So anyone had ever had any problem with database cache? :) 2008/4/25 Sergio Belkin [EMAIL PROTECTED]: Hi, I am using zabbix (monitoring software) with mysql. zabbix goes zombie and complains with messages suggesting that Database cache perhaps is corrupted. How can I check and fix it? I am using Centos 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. my.cnf is as follows: [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 1024 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 128M sort_buffer_size = 8M join_buffer_size = 3M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log_slow_queries = /var/log/mysqld/slow-query-log long_query_time = 5 log_long_format tmpdir = /tmp log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log expire_logs_days = 2 server-id = 1 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 600M innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 EOF Thanks in advance! -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database cache corrupted
Can you send us the exact zabbix error? On Sun, Apr 27, 2008 at 6:18 PM, Sergio Belkin [EMAIL PROTECTED] wrote: So anyone had ever had any problem with database cache? :) 2008/4/25 Sergio Belkin [EMAIL PROTECTED]: Hi, I am using zabbix (monitoring software) with mysql. zabbix goes zombie and complains with messages suggesting that Database cache perhaps is corrupted. How can I check and fix it? I am using Centos 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. my.cnf is as follows: [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 1024 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 128M sort_buffer_size = 8M join_buffer_size = 3M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log_slow_queries = /var/log/mysqld/slow-query-log long_query_time = 5 log_long_format tmpdir = /tmp log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log expire_logs_days = 2 server-id = 1 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 600M innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 EOF Thanks in advance! -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- high performance mysql consulting. http://provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
