Re: Good Database Philosophy Book?
Hi, all Who can recommend me a good tools for testing performance under heavy use of mysql. With large corcurrent queries. -Ares -- Nuclear Science Technology Virtual Research Center http://nst.pku.edu.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL memory problem on AIX 4.3.2
Hi all, Now I'm facing a big problem and see if anyone know how to solve it. I'm using RS6000 AIX 4.3.2 CPU*2 1G RAM with HA. And DB is MySQL 4.0.12-max. Here is the content of my.cnf. [client] #password = your_password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 set-variable= max_connections=400 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 log-bin log_slow_queries log_warnings server-id = 1 default_character_set=gbk # Uncomment the following if you are using BDB tables skip-bdb # Uncomment the following if you are using InnoDB tables skip-innodb # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Now I have to restart mysqld one time in every day, because of cannot connect it. The error message in .err file is: 030627 11:50:56 Error: Can't create thread to kill server 030627 11:51:06 mysqld restarted /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030627 11:51:15 /home/mysql/bin/mysqld: Normal shutdown 030627 11:51:16 /home/mysql/bin/mysqld: Shutdown Complete 030627 11:51:16 mysqld ended 030627 12:05:19 mysqld started /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030628 21:09:29 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030628 21:09:29 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030628 21:09:30 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit ' to allow mysqld to use more memory or you can add more swap space 030629 22:47:33 Error: Can't create thread to kill server 030629 22:47:43 mysqld restarted /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 030629 22:47:53 /home/mysql/bin/mysqld: Normal shutdown 030629 22:47:53 /home/mysql/bin/mysqld: Shutdown Complete 030629 22:47:53 mysqld ended 030629 23:07:26 mysqld started /home/mysql/bin/mysqld: ready for connections. Version: '4.0.12-max-log' socket: '/tmp/mysql.sock' port: 3306 ulimit result is ulimit -a time(seconds)unlimited file(blocks) unlimited data(kbytes) 131072 stack(kbytes)32768 memory(kbytes) 32768 coredump(blocks) 2097151 nofiles(descriptors) 2000 show status result is mysql show status; +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 123 | | Aborted_connects | 0 | | Bytes_received | 152280| | Bytes_sent | 125496454 | | Com_admin_commands | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 184 | | Com_change_master| 0 | | Com_check| 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 52| | Com_insert_select
What does the ERROR mean?
MySQL 4.0.12 max on AIX 4.3.2 Following are part of .err log file: 030615 1:29:44 Aborted connection 163 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 167 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 166 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 165 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 170 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 169 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 168 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets)
[BUG] show full processlist on AIX
MySQL 4.0.12 max on AIX 4.3.2 HA. When I use show full processlist on localhost, all Hosts show as localhost. but when I use show full processlist from remote, all Hosts show as remote IP. as follow: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| localhost:3425 | NewProduct | Sleep | 744 | | NULL | | 20 | yuheyang| localhost:3277 | NewProduct | Sleep | 4044 | | NULL | | 21 | yuheyang| localhost:3278 | NewProduct | Sleep | 4044 | | NULL | | 22 | yuheyang| localhost:3279 | NewProduct | Sleep | 4132 | | NULL | | 23 | yuheyang| localhost:3280 | NewProduct | Sleep | 4137 | | NULL | | 24 | yuheyang| localhost:3312 | NewProduct | Sleep | 1946 | | NULL | | 25 | yuheyang| localhost:3313 | NewProduct | Sleep | 1946 | | NULL | | 26 | yuheyang| localhost:3314 | NewProduct | Sleep | 1946 | | NULL | | 27 | yuheyang| localhost:3315 | NewProduct | Sleep | 1946 | | NULL | | 28 | root| localhost | NULL | Query | 0| NULL | show full processlist | | 29 | happynessrabbit | localhost:44555 | NewProduct | Sleep | 291 | | NULL | | 30 | happynessrabbit | localhost:44556 | NewProduct | Sleep | 291 | | NULL | | 31 | happynessrabbit | localhost:44557 | NewProduct | Sleep | 291 | | NULL | | 32 | happynessrabbit | localhost:44558 | NewProduct | Sleep | 291 | | NULL | | 115 | happynessrabbit | localhost:44654 | NewProduct | Sleep | 1809 | | NULL | | 116 | happynessrabbit | localhost:44655 | NewProduct | Sleep | 1801 | | NULL | | 117 | happynessrabbit | localhost:44657 | NewProduct | Sleep | 1792 | | NULL | | 118 | happynessrabbit | localhost:44658 | NewProduct | Sleep | 1781 | | NULL | | 119 | happynessrabbit | localhost:44659 | NewProduct | Sleep | 1776 | | NULL | | 131 | happynessrabbit | localhost:44671 | NewProduct | Sleep | 1715 | | NULL | | 137 | happynessrabbit | localhost:44677 | NewProduct | Sleep | 1711 | | NULL | | 156 | happynessrabbit | localhost:44696 | NewProduct | Sleep | 1603 | | NULL | | 162 | happynessrabbit | localhost:44704 | NewProduct | Sleep | 1561 | | NULL | | 172 | gege| localhost:44721 | NULL | Sleep | 17 | | NULL | +-+-+-++-+--+---+---+ 24 rows in set (0.00 sec) on remote: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| 192.168.5.108:3425 | NewProduct | Sleep | 727 | | NULL | | 20 | yuheyang| 192.168.5.108:3277 | NewProduct | Sleep | 4027 | | NULL | | 21 | yuheyang| 192.168.5.108:3278 | NewProduct | Sleep | 4027 | | NULL | | 22 | yuheyang| 192.168.5.108:3279 | NewProduct | Sleep | 4115 | | NULL | | 23 | yuheyang| 192.168.5.108:3280 | NewProduct | Sleep | 4120 | | NULL | | 24 | yuheyang| 192.168.5.108:3312 | NewProduct | Sleep | 1929 | | NULL | | 25 | yuheyang| 192.168.5.108:3313 | NewProduct | Sleep | 1929 | | NULL | | 26 | yuheyang| 192.168.5.108:3314 | NewProduct | Sleep | 1929 | | NULL | | 27 | yuheyang| 192.168.5.108:3315 | NewProduct | Sleep | 1929 | | NULL | | 28 | root| 192.168.5.108 | NULL | Sleep | 668 | | NULL | | 29 | happynessrabbit | 192.168.5.108:44555 | NewProduct | Sleep | 274 | | NULL | | 30 | happynessrabbit | 192.168.5.108:44556 | NewProduct | Sleep | 274 | | NULL | | 31 | happynessrabbit |
Re: QUESTION ABOUT DATA AND INDEX FILE DIRECTORIES
But is it available on AIX 4.3.x ? -Ares On 02 Jun 2003 19:50 CST you wrote: DATA DIRECTORY and INDEX DIRECTORY is ignored on Windows. 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]
QUESTION ABOUT DATA AND INDEX FILE DIRECTORIES
HI, ALL Under windows XP, mysql-4.0.12, when I create a table with special data and index directories as follow: USE DATABASE test; CREAT TABLE IF NOT EXISTS test_table ( Vola INT NOT NULL, PRIMARY KEY (Vola)) DATA DIRECTORY=D:/mySQLDBs/test/data INDEX DIRECTORY=D:/mySQLDBs/test/index; It show test_table created successfully in mysql client. But really, mysqld creats table test_table with three table files test_table.frm, test_table.MYD, and test_table.MYI in D:\mySQLDBs directory. In addition, I defined datadir=d:/mySQLDBs in my.cnf. Who can tell me why this happened? And how can I put the data file and index file to where I want to allocate without links. -Ares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUESTION ABOUT DATA AND INDEX FILE DIRECTORIES
I use MySQL AB released win32 binary package. i don't know if it used --skip-symlink option. And after I added an entry skip-symlink=false in my.cnf, I can't start mysqld unfortunately. -Ares On 02 Jun 2003 14:15 CST you wrote: From the Manual: By using DATA DIRECTORY=directory or INDEX DIRECTORY=directory you can specify where the storage engine should put it's table and index files. Note that the directory should be a full path to the directory (not relative path). This only works for MyISAM tables in MySQL 4.0, when you are not using the --skip-symlink option. Make sure that option is set correctly. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Ares Liu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, 02 June, 2003 16:03 Subject: QUESTION ABOUT DATA AND INDEX FILE DIRECTORIES HI, ALL Under windows XP, mysql-4.0.12, when I create a table with special data and index directories as follow: USE DATABASE test; CREAT TABLE IF NOT EXISTS test_table ( Vola INT NOT NULL, PRIMARY KEY (Vola)) DATA DIRECTORY=D:/mySQLDBs/test/data INDEX DIRECTORY=D:/mySQLDBs/test/index; It show test_table created successfully in mysql client. But really, mysqld creats table test_table with three table files test_table.frm, test_table.MYD, and test_table.MYI in D:\mySQLDBs directory. In addition, I defined datadir=d:/mySQLDBs in my.cnf. Who can tell me why this happened? And how can I put the data file and index file to where I want to allocate without links. -Ares -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Install on a AIX RS6000
change your my.ini or my.cnf - Original Message - From: Bonnie Poole [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 22, 2003 8:53 AM Subject: MySql Install on a AIX RS6000 I am trying to install on a AIX box. I do not want to install into /usr/local. I have put everything in /sybase/mysql/mysql. I am running into alot of problems with things expected to be in /usr/local/mysql. What do I need to run in order to tell mysql everthing is in /sybase/mysql/mysql Thanks for any help. Bonnie P. Poole DiscoveryLink Solutions Development IBM Life Sciences 714-438-6361 Voice Mail - 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
Turbine vs Connector/J
As both of them type IV JDBC, which one is the better? -Ares sql,query - 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
How Huge of your mySQL database or table in your former Instance
All, Now I want to design a database which contains more than 10G data to use. I think the largest table in my db will contains more than 30 million records and the amount of this kind of table will be up to 7 or 10. In my instance there won't be so many clients connecting. normally, there are less than 20 clients concurrent querying. My hardware is dual PIII 933, 512M I plan to upgrade to 1G RAM, three SCSI 18G HD, one of them is dedicated to mySQL DB. platform RH 7.2 with XFS. So, could you give me some advice that if it is feasible ? Or show me your successful cases of using mySQL which is supporting very large DB or tables with details ? Thanks a lot! Ares - 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: How Huge of your mySQL database or table in your former Instance
Thanks very much Paul, Could you like to tell me what time spent in your querying ten thousands of records from tables one time? In my test, when I query 10,000 records from a table which contains 17 millions records total, it will cost less than 1 second of querying INDEX, or cost more than 30 seconds of querying normal COLUMNs. I want to know query speed in larger table. Regards Ares - Original Message - From: [EMAIL PROTECTED] To: Ares Liu [EMAIL PROTECTED] Sent: Monday, December 10, 2001 1:28 PM Subject: Re: How Huge of your mySQL database or table in your former Instance Now I want to design a database which contains more than 10G data to use. I think the largest table in my db will contains more than 30 million records and the amount of this kind of table will be up to 7 or 10. In my instance there won't be so many clients connecting. normally, there are less than 20 clients concurrent querying. My hardware is dual PIII 933, 512M I plan to upgrade to 1G RAM, three SCSI 18G HD, one of them is dedicated to mySQL DB. platform RH 7.2 with XFS. So, could you give me some advice that if it is feasible ? Or show me your successful cases of using mySQL which is supporting very large DB or tables with details ? I've just restructured a database which has 8 identical tables whose size varies by the hour. They often have 30-35 million rows each. In addition, there are two new tables per day, and the system has been running for over a year now. The database server is a dual PIII-1000 with 1 Gb of RAM, 18 Gb Ultra-wide SCSI disk for the system, and a RAID array with 14 * 73 Gb IBM 10,000 rpm Ultra-wide SCSI discs. The system sings. It's easy to hit disk limits with a large database, hence the RAID array above. It has 8-10 other machines on a 100 MHz ethernet lan which fire selects at the database server, and two which issue inserts into the 8 tables mentioned above. Hope this helps a little. Regards, Paul Wilson iiNet Ltd - 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
Parallel queries in PHP program
All, Can I implement parallel queries to mySQL by using sevial query sentences in a single PHP file? Who knows how to do it? including query the same table or different tables in parallel. Regards Ares - 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
large count of column in a table
Hi all, I am a new member of this list. now I have a question. on my site, I add a new lonely database to store some data, and in this db, there is a table contained four columns, in which I put more than 17 million records in each column. after do that, I found out that my site became very slow. my site is based on nuke, and not very much things in it. my site env (PIII933x2, 512M, SCSI18Gx3, RH71 with XFS, mysql3.23.36) BTW, new db query is very slow for sure. who have good idea to speed it? some detail as follow: mysql show tables; ++ | Tables_in_nudb | ++ | father_child | | function_xy| | general_info | | head_info | | interpolation | | series_b | | type | ++ 7 rows in set (0.00 sec) mysql select count(*) from function_xy; +--+ | count(*) | +--+ | 17516779 | +--+ 1 row in set (0.01 sec) mysql desc function_xy; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | no| int(10) unsigned | | PRI | NULL| auto_increment | | record_no | int(10) unsigned | | | 0 || | X | char(11) | | | || | Y | char(11) | | | || +---+--+--+-+-++ 4 rows in set (0.00 sec) mysql select * from function_xy where no=123456; ++---+-+-+ | no | record_no | X | Y | ++---+-+-+ | 123456 | 154 | 2090.66774 | 2.79507+ 1 | ++---+-+-+ 1 row in set (0.13 sec) mysql regards Ares - 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: MySQL 3.23 Manual
download a windows 3.23 version and install it. as you finished, under mysql\docs\ you will find manual in HTML fmt. - 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
