Memory Problems
Hi List, We have a mysql-Server with 8G of Ram. But mysql doesn't use this ram. But we get following error: May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Got error 12 from storage engine May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Sort aborted I have set the sort_buffer_size to 1G but even this doesn't help. Any hints ? Should we try a 64Bit-OS ? Regards Christoph Kernel is a 2.6.18-3-686-bigmem from debian-etch. mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 103352 | | Aborted_connects | 3 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 2985193088 | | Bytes_sent | 3725769917 | | Com_admin_commands | 5377515| | Com_alter_db | 0 | | Com_alter_table| 8 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 5434526| | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql| 0 | | Com_delete | 154176 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 2 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 473672 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 2 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 14627137 | | Com_set_option | 450| | Com_show_binlog_events | 0 | | Com_show_binlogs | 13 | | Com_show_charsets | 112| | Com_show_collations| 112| | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 42 | | Com_show_databases | 10 | | Com_show_errors| 0 | | Com_show_fields| 111| | Com_show_grants| 46 | | Com_show_innodb_status | 0 | | Com_show_keys | 56 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status| 4930 | | Com_show_storage_engines | 7 | | Com_show_tables| 250| | Com_show_variables | 4708 | | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 1 | | Com_unlock_tables | 0 | | Com_update | 1088621| | Com_update_multi | 0 | | Connections| 61722 | | Created_tmp_disk_tables| 6036 | | Created_tmp_files | 8 | | Created_tmp_tables | 287594 | | Delayed_errors | 0 | | Delayed_insert_threads | 0
Re: Memory Problems
Christoph Klünter a écrit : Hi List, We have a mysql-Server with 8G of Ram. But mysql doesn't use this ram. But we get following error: May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Got error 12 from storage engine May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Sort aborted I have set the sort_buffer_size to 1G but even this doesn't help. Any hints ? Should we try a 64Bit-OS ? Regards Christoph Kernel is a 2.6.18-3-686-bigmem from debian-etch. mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 103352 | | Aborted_connects | 3 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 2985193088 | | Bytes_sent | 3725769917 | | Com_admin_commands | 5377515| | Com_alter_db | 0 | | Com_alter_table| 8 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 5434526| | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql| 0 | | Com_delete | 154176 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 2 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 473672 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 2 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 14627137 | | Com_set_option | 450| | Com_show_binlog_events | 0 | | Com_show_binlogs | 13 | | Com_show_charsets | 112| | Com_show_collations| 112| | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 42 | | Com_show_databases | 10 | | Com_show_errors| 0 | | Com_show_fields| 111| | Com_show_grants| 46 | | Com_show_innodb_status | 0 | | Com_show_keys | 56 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status| 4930 | | Com_show_storage_engines | 7 | | Com_show_tables| 250| | Com_show_variables | 4708 | | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 1 | | Com_unlock_tables | 0 | | Com_update | 1088621| | Com_update_multi | 0 | | Connections| 61722 | | Created_tmp_disk_tables| 6036 | | Created_tmp_files
Re: Memory Problems
On 5/15/07, Christoph Klünter [EMAIL PROTECTED] wrote: I have set the sort_buffer_size to 1G but even this doesn't help. Any hints ? Should we try a 64Bit-OS ? setting sort_buffer_size to 1GB is not recommended. it is a thread specific configuration parameter which means each thread will be eligible to get a sort_buffer_size of 1GB. On a 32 bit system, you can push to a approx 3.2 GB usage for the database by doing kernel level hacks mentioned at http://www.puschitz.com/TuningLinuxForOracle.shtml#GrowingTheOracleSGATo2.7GBInx86RHEL2.1WithoutVLMor by using VLM also. But if you are using a mysql 32bit build, then you will not be able to use more than 4GB theoretically. Using 64bit OS and 64bit build of mysql will enable you to use memory greater than 4GB effectively. ~Alex
Re: Memory Problems
On 5/15/07, Mathieu Bruneau [EMAIL PROTECTED] wrote: Hi, yeah, apparenlty you're running into the 32 bits memory liimt. Note thta some memory is allocated for the OS so you don't even have the full 4GB of ram you can technically adressesed. The 64 bits os would increase this limit to 64gb++ (on 64 bits hardware) The the OP: be advised that on a typical x86 system, even with 4GB of memory, the OS is designed not to be able to allocate more than 4GB to an individual process. I know this is counterintuitive, but this is the situation.
Re: Memory Problems
I think you may be able to get around this by using multiple key buffers? (MySQL 4.1 or later) -Micah On 05/15/2007 01:24 AM, Christoph Klünter wrote: Hi List, We have a mysql-Server with 8G of Ram. But mysql doesn't use this ram. But we get following error: May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Got error 12 from storage engine May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: Sort aborted I have set the sort_buffer_size to 1G but even this doesn't help. Any hints ? Should we try a 64Bit-OS ? Regards Christoph Kernel is a 2.6.18-3-686-bigmem from debian-etch. mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 103352 | | Aborted_connects | 3 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 2985193088 | | Bytes_sent | 3725769917 | | Com_admin_commands | 5377515| | Com_alter_db | 0 | | Com_alter_table| 8 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 5434526| | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql| 0 | | Com_delete | 154176 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 2 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 473672 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 2 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 14627137 | | Com_set_option | 450| | Com_show_binlog_events | 0 | | Com_show_binlogs | 13 | | Com_show_charsets | 112| | Com_show_collations| 112| | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 42 | | Com_show_databases | 10 | | Com_show_errors| 0 | | Com_show_fields| 111| | Com_show_grants| 46 | | Com_show_innodb_status | 0 | | Com_show_keys | 56 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status| 4930 | | Com_show_storage_engines | 7 | | Com_show_tables| 250| | Com_show_variables | 4708 | | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_truncate | 1 | | Com_unlock_tables | 0 | | Com_update | 1088621| | Com_update_multi | 0 | | Connections| 61722 | | Created_tmp_disk_tables| 6036 | |
Re: Memory Problems
On 5/15/07, Micah Stevens [EMAIL PROTECTED] wrote: I think you may be able to get around this by using multiple key buffers? (MySQL 4.1 or later) key buffers caches only index data and they dont help with sorting like sort_buffer. they dont impact innodb engine. even while using multiple key buffers, the memory allocated will belong to the same process and the limitation is at the process level. ~Alex
Memory problems?
Gurus, We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard. I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2478 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.31 mysqld 2507 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.50 mysqld 2508 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2509 mysql 17 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2510 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2511 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2547 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.58 mysqld 2548 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.27 mysqld 2549 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2550 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.16 mysqld 2758 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2760 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:02.13 mysqld 2835 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:57.11 mysqld 2933 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2947 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2948 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2949 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2950 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2962 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2963 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2964 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2965 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld My question: 1) I see 20 processes in using the 'top' command but I have only 10 open connections (I am using connection pooling). Does it mean that processes shown in the top command and open connections are not related? Can somebody please clarify? 2) At the begining of the server startup, I have noticed that each mysql process under top command begin taking approx 10-12 MB. This kept increasing. The current memory occupied by one mysql process now is 20m (as you can see above). This memory only increases over the period, but I have never seen it coming down throughout the application use. Why is this so? Is there any problem with the application or the way mysql is configured? Any pointers? Thanks in advance, Rohit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory problems?
Thanks for your quick response. Another question: So, what you are saying is that sum of the memory (RES - 20m) of all the mysql processes shown with the 'top' command is not total memory occupied by mysql server? Like 22 threads*22m = 440MB? Also, any idea why this keeps increasing? Thanks, Rohit - Original Message - From: Lars Heidieker [EMAIL PROTECTED] To: Rohit Peyyeti [EMAIL PROTECTED] Sent: Wednesday, February 01, 2006 4:37 PM Subject: Re: Memory problems? All these processes share the same address space (linux way of doing threads) therefor their sizes dont add up. It is correct one thread per connection plus a few from mysql (eg innodb has a few maintenance threads ) On 1 Feb 2006, at 12:00, Rohit Peyyeti wrote: Gurus, We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard. I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2478 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.31 mysqld 2507 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.50 mysqld 2508 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2509 mysql 17 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2510 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2511 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2547 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.58 mysqld 2548 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.27 mysqld 2549 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2550 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.16 mysqld 2758 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2760 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:02.13 mysqld 2835 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:57.11 mysqld 2933 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2947 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2948 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2949 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2950 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2962 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2963 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2964 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2965 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld My question: 1) I see 20 processes in using the 'top' command but I have only 10 open connections (I am using connection pooling). Does it mean that processes shown in the top command and open connections are not related? Can somebody please clarify? 2) At the begining of the server startup, I have noticed that each mysql process under top command begin taking approx 10-12 MB. This kept increasing. The current memory occupied by one mysql process now is 20m (as you can see above). This memory only increases over the period, but I have never seen it coming down throughout the application use. Why is this so? Is there any problem with the application or the way mysql is configured? Any pointers? Thanks in advance, Rohit -- 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: Memory problems?
Rohit Peyyeti wrote: Thanks for your quick response. Another question: So, what you are saying is that sum of the memory (RES - 20m) of all the mysql processes shown with the 'top' command is not total memory occupied by mysql server? Like 22 threads*22m = 440MB? Also, any idea why this keeps increasing? Thanks, Rohit - Original Message - From: Lars Heidieker [EMAIL PROTECTED] To: Rohit Peyyeti [EMAIL PROTECTED] Sent: Wednesday, February 01, 2006 4:37 PM Subject: Re: Memory problems? All these processes share the same address space (linux way of doing threads) therefor their sizes dont add up. It is correct one thread per connection plus a few from mysql (eg innodb has a few maintenance threads ) On 1 Feb 2006, at 12:00, Rohit Peyyeti wrote: Gurus, We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard. I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2478 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.31 mysqld 2507 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.50 mysqld 2508 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2509 mysql 17 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2510 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2511 mysql 20 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2547 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.58 mysqld 2548 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.27 mysqld 2549 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2550 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.16 mysqld 2758 mysql 15 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2760 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:02.13 mysqld 2835 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:57.11 mysqld 2933 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2947 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2948 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2949 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2950 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2962 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2963 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2964 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld 2965 mysql 16 0 35036 20m 3616 S 0.0 2.1 0:00.00 mysqld My question: 1) I see 20 processes in using the 'top' command but I have only 10 open connections (I am using connection pooling). Does it mean that processes shown in the top command and open connections are not related? Can somebody please clarify? 2) At the begining of the server startup, I have noticed that each mysql process under top command begin taking approx 10-12 MB. This kept increasing. The current memory occupied by one mysql process now is 20m (as you can see above). This memory only increases over the period, but I have never seen it coming down throughout the application use. Why is this so? Is there any problem with the application or the way mysql is configured? Any pointers? Thanks in advance, Rohit There is queries going on, so some data will be cached in memory. If memory serves me correctly, Linux won't clear out memory until it has to, so unless you are running low, that memory may stay in use until the Kernel cleans house. Unless you are into swap really badly, I wouldn't worry too much. As an aside, your memory of 1GB seems a bit low to me. If I was you, and I had the money, I would toss in another 1GB. Take a look at your in use numbers. You may see that if you add up all the memory consumption of the system, that this will be greater than what is being displayed as in use. As the other respondent said, this is how Linux works. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out Of Memory problems: One MySQL user, 86 minutes sleeping
Hello.
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
These are the threads of the same mysqld process which is using only
72m of virtual memory. May be you should find out other process
which is consuming memory. If you are sure that MySQL is guilty
than send to the list the output of 'SHOW VARIABLES' statement.
thomas Armstrong wrote:
Hi.
Using MySQL 4.1.9 on Linux FedoraCore2 (kernel 2.6.9),
I'm suffering several memory problems ('Out Of Memory'
problem) on my server.
Playing around with my server:
SHOW PROCESSLIST
Id | User | Host | db | Command | Time | State | Info
20138 | user1 | localhost | user1_db | Sleep | 5295 | NULL
(why is this user1 sleeping for 86 minutes?)
SHOW STATUS:
Qcache_free_blocks =0956
Qcache_free_memory =0911676280
Qcache_hits =09762140
Qcache_inserts =09109122
Qcache_lowmem_prunes =0912575
Qcache_not_cached =09897
Qcache_queries_in_cache =09644
Qcache_total_blocks =091582
-
[]# top
-
top - 12:55:40 up 1 day, 2:18, 2 users, load average: 0.39, 0.35, 0.29
Tasks: 88 total, 2 running, 86 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% s=
i
Mem:508072k total, 499980k used, 8092k free,69828k buffers
Swap: 1084376k total, 2176k used, 1082200k free, 116264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
1980 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.24 mysqld
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1985 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1986 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.21 mysqld
1987 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.13 mysqld
1988 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1989 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.06 mysqld
14337 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
17323 apache15 0 42056 30m 16m S 0.0 6.1 0:02.44 httpd
2316 ogo 16 0 36092 28m 23m S 0.0 5.7 0:01.25 ogo-webui-1.0a
11228 apache16 0 36600 24m 18m S 0.0 4.9 1:51.20 httpd
14276 apache15 0 34408 24m 16m S 0.0 4.8 0:58.17 httpd
15737 apache15 0 34120 23m 16m S 0.0 4.8 0:32.92 httpd
-
[]# more /etc/my.conf
[mysqld]
datadir=3D/var/lib/mysql
socket=3D/var/lib/mysql/mysql.sock
query-cache-size=3D20M
query-cache-type=3D1
default-character-set=3Dutf8
# Slow queries log
log-slow-queries =3D /var/log/mysql/slow-queries.log
long_query_time =3D 5
log-long-format
[mysql.server]
user=3Dmysql
basedir=3D/var/lib
[safe_mysqld]
err-log=3D/var/log/mysqld.log
pid-file=3D/var/run/mysqld/mysqld.pid
---
[]# free -m
--
total used free sharedbuffers cached
Mem: 496487 8 0 68113
-/+ buffers/cache:305190
Swap: 1058 2 1056
---
I'm trying to find out the reason of my memory problems. I suspect this
sleeping user is to blame. Any suggestion?
Thank you very much.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out Of Memory problems: One MySQL user, 86 minutes sleeping
Hi.
Using MySQL 4.1.9 on Linux FedoraCore2 (kernel 2.6.9),
I'm suffering several memory problems ('Out Of Memory'
problem) on my server.
Playing around with my server:
SHOW PROCESSLIST
Id | User | Host | db | Command | Time | State | Info
20138 | user1 | localhost | user1_db | Sleep | 5295 | NULL
(why is this user1 sleeping for 86 minutes?)
SHOW STATUS:
Qcache_free_blocks 56
Qcache_free_memory 11676280
Qcache_hits 762140
Qcache_inserts 109122
Qcache_lowmem_prunes12575
Qcache_not_cached 897
Qcache_queries_in_cache 644
Qcache_total_blocks 1582
-
[]# top
-
top - 12:55:40 up 1 day, 2:18, 2 users, load average: 0.39, 0.35, 0.29
Tasks: 88 total, 2 running, 86 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
Mem:508072k total, 499980k used, 8092k free,69828k buffers
Swap: 1084376k total, 2176k used, 1082200k free, 116264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
1980 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.24 mysqld
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1985 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1986 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.21 mysqld
1987 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.13 mysqld
1988 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1989 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.06 mysqld
14337 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
17323 apache15 0 42056 30m 16m S 0.0 6.1 0:02.44 httpd
2316 ogo 16 0 36092 28m 23m S 0.0 5.7 0:01.25 ogo-webui-1.0a
11228 apache16 0 36600 24m 18m S 0.0 4.9 1:51.20 httpd
14276 apache15 0 34408 24m 16m S 0.0 4.8 0:58.17 httpd
15737 apache15 0 34120 23m 16m S 0.0 4.8 0:32.92 httpd
-
[]# more /etc/my.conf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query-cache-size=20M
query-cache-type=1
default-character-set=utf8
# Slow queries log
log-slow-queries = /var/log/mysql/slow-queries.log
long_query_time = 5
log-long-format
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
---
[]# free -m
--
total used free sharedbuffers cached
Mem: 496487 8 0 68113
-/+ buffers/cache:305190
Swap: 1058 2 1056
---
I'm trying to find out the reason of my memory problems. I suspect this
sleeping user is to blame. Any suggestion?
Thank you very much.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory problems
When I run top after my server has been running for a few days, Mysql is using 60 or 70MB of memory. When I restart mysql, it goes back to 3000. Any idea where I should start to look for a problem? What causes this? I am running RH 7.3 and php/Mysql combo. I don't expect anyone to fix this, just perhaps suggest where I can look. BTW, I searched for this and got a reference to the perl-DBD-Mysql module, which I do not have installed. D - PresenterNet The Interactive Presenters' Network Doug Wolfgram - CEO 949.248.0439 http://www.presenternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory problems
In the last episode (Oct 05), Doug Wolfgram said: When I run top after my server has been running for a few days, Mysql is using 60 or 70MB of memory. When I restart mysql, it goes back to 3000. Any idea where I should start to look for a problem? What causes this? What are your mysql memory settings? 70MB may be low for all we know. It's definitely low for my machines :) And what units are 3000 in? KB can't be right, since mysql will always take more than 3MB of memory. Important settings include: key_buffer sort_buffer_size read_buffer_size read_rnd_buffer_size myisam_sort_buffer_size thread_cache query_cache_size -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory problems
In the last episode (Oct 05), Doug Wolfgram said: At 07:35 PM 10/5/2004, you wrote: In the last episode (Oct 05), Doug Wolfgram said: When I run top after my server has been running for a few days, Mysql is using 60 or 70MB of memory. When I restart mysql, it goes back to 3000. Any idea where I should start to look for a problem? What causes this? What are your mysql memory settings? 70MB may be low for all we know. It's definitely low for my machines :) And what units are 3000 in? KB can't be right, since mysql will always take more than 3MB of memory. Important settings include: When I first start mysql, RSS and Size are 3033. That's K. (for each process) I am talking about process use, not total memory use. Each process that shows in top is what I'm referring to. Does that make more sense? Since you mention multiple processes, you must be using Linux, which implements threads by forking separate processes with shared address space. If you have 50 processes each with 20MB rss, it's still only using 20MB of memory. You probably should read the MySQL manual chapter titled How MySQL uses memory to learn which variables affect memory usage: http://dev.mysql.com/doc/mysql/en/Memory_use.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory problems
hi, I'm running a mysql-server (4.0.18) on windows XP. Connected to this server were max. 100 clients ( Also XP). After a while ( maybe 8 hours ) I get error messages on the client and also in the server log: Error5: out of memory (needed bytes ) Does anyone has an idea what could cause that? Who is missing the memory? The client on its own pc or the cleint on the server? Configuration: sortbuffer: 16k, readbuffer:8k, Keybuffer: 32 MB, max 100 connections. best regards thorsten Guddack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 31, 2004, at 1:09 AM, Adam Goldstein wrote: On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote: On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... SNIP SNIP Our switch to innodb was fairly smooth, but one table is unable to be converted due to a FullText Index. I see Innodb has only one drawback :) However, the results so far are very worth it. We are still having some overloads, but, they are certainly not mysql's fault. Apache/php is taking up too much load and memory at a certain point, but the G5 doesn't break a sweat. We still have some configuring to due, as we started with 6 x 2G ibdata files, which mysteriously are only 1G on disk. my.cnf settings below. We also kept some ram in the MyIsam portion of the config for the one remaining (large/important) MyIsam table. Do the settings look kosher? One test of the speed difference has so far registered a 5-10X speed increase (max). These also depend on time of day and filesystem deletes of multiple files for each, there is a backlog of perhaps another million items left to archive that this is working on, so we'll have this script as a working test for a few more days, as we can only run this during low load hours. Before Innodb: START (07:00:00) Done. 2279 archived. STOP (07:50:07): 3005.91sec START (20:00:00) Done. 5603 archived. STOP (20:50:16): 3015.15sec START (22:00:00) Done. 7265 archived. STOP (22:50:04): 3002.85sec After Innodb: START (18:00:00) Done. 16092 archived. STOP (18:50:03): 3002.25sec START (19:00:00) Done. 19683 archived. STOP (19:50:03): 3002.38sec START (22:00:00) Done. 25370 archived. STOP (22:50:04): 3003.6sec Under a simultaneous user/high load situation, would you suggest running with pconnects in php/mysql, and with persistent connections in apache? We have been seeing 300-400 outbound mysql connections from the main app server (via netstat -n -t|grep -c :3306 , which include mostly TIME_WAIT) , 300-450 apache processesoutbound *:80 connections on the primary app server (we are researching/pricing 2-4 frontend 1U servers now.. roughly 2Gram/2Ghz+/gigabit boxes, either P4/Athlon/Athlon64 or Xserves). We are still getting some odd results in stats, such as the same high 'change db' and 'connection' rates. relevant(?) innodb status; Per second averages calculated from the last 53 seconds (now, during low hours. I am not sure how many of these stats would change during high use hours, I will check tomorrow.) 5266530 OS file reads, 2492377 OS file writes, 448439 OS fsyncs 34.68 reads/s, 18790 avg bytes/read, 14.81 writes/s, 1.74 fsyncs/s Ibuf for space 0: size 1, free list len 249, seg size 251, 970319 inserts, 970319 merged recs, 189753 merges Hash table size 4980539, used cells 2737132, node heap has 3893 buffer(s) 9649.16 hash searches/s, 1424.65 non-hash searches/s Total memory allocated 1654471880; in additional pool allocated 2385280 Buffer pool size 76800 Free buffers 124 Database pages 72783 Modified db pages 9798 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 8644115, created 38059, written 2814095 39.87 reads/s, 0.21 creates/s, 17.83 writes/s Buffer pool hit rate 1000 / 1000 Number of rows inserted 2353929, updated 1543175, deleted 1191888, read 547022884 6.98 inserts/s, 0.47 updates/s, 5.94 deletes/s, 17275.54 reads/s relevant status; | Aborted_clients | 3088 | | Aborted_connects | 1 | | Bytes_received | 2788318966 | | Bytes_sent | 1674966066 | | Com_change_db| 5245603| | Com_delete | 1091654| | Com_insert | 1933786| | Com_insert_select| 440592 | | Com_lock_tables | 82167 | | Com_select | 5133100| | Com_unlock_tables| 82172 | | Com_update | 1525300| | Connections | 788173 | | Created_tmp_disk_tables | 350| | Created_tmp_tables | 96399 | | Created_tmp_files| 27 | | Flush_commands | 1 | | Handler_commit | 82157 | | Handler_delete | 0 | | Handler_read_first | 38191 | | Handler_read_key | 1081224301 | | Handler_read_next| 3683264158 | | Handler_read_rnd | 70681449 | | Handler_read_rnd_next| 1174208910 | | Handler_rollback | 729518 | | Handler_update | 55200716 | | Handler_write| 70961992 | |
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... It seems you can (or are forced to) pretty much have daily down time... All you need is some time to bring the machine down for a quick bounce... First edit the my.cnf file to add the InnoDB settings... You have a setting in your current my.cnf disabling InnoDB, that needs to go away, my InnoDB settings are below, you probably don't need a 40Gig disk space for InnoDB, but you should take 4 x data size at least (InnoDB keeps data times 2 so it can roll back transactions, and then there are index etc...). So Decide how big you want your space to be and make the appropriate changes in my.cnf to make it happen... Whatever you go with you are stuck with as a minimmum, it's hard to go smaller once it is live. After you bounce MySQL and InnoDB is then an option you will still need some memory for MyISAM until you change the tables... So maybe set the sort/read/join buffers to 2M if they aren't already, Key Buffer to say 250M and the InnoDB numbers close to mine. After you have moved the tables just drop the Key Buffer down to 16M or so. Oh yes, and as I will say below, drop that query cache, 64M is plenty for you I expect, the rest is being wasted. Once this is all setup go ahead and bounce the server so the new settings take place. So now you have InnoDB available... All you need now it to change the data... The command is: ALTER TABLE xxx TYPE=InnoDB; Once again, do NOT change the mysql database, it MUST stay as MyISAM, and doesn't affect your performance at any rate. Once this is done you can go ahead and drop the Key Buffer right down to something tiny, 16M or so is what we have, and bounce MySQL again and you are all set. 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). we have an average of ~15-20%, with times sustaining 30+% Errmm... The stats you included says that the Query Cache is WAY out of control. There's like a gigabyte of unused space cache there. 3) Give lots of memory to InnoDB, I'll share my settings below. Thank You! 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. ok 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. The slow log has helped us a lot in the past... with the current slow log settings, only about 0.1% are slow queries. 3K out of 4million in the past 18hours. Currently the time appears to be set at 2 (From show variables: slow_launch_time 2 ). 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. Great tool.. only recently started using it. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join buffers.. I'd up the sort/read/join buffers to maybe 10MBytes, or even 20Mbytes, if you need to drop Key buffer to 1500M to give you the space for the others. We got OKish results on MyISAM with the larger sort/read/join buffers - InnoDB made all the difference though. I've only gone as high as 6M on those before. Before giving you our settings I do want to point out one thing... We haven't fine tuned the memory settings since we did the G5 switch. At the time I was bringing the machines up they needed to be up quickly, and when it didn't work correctly with my original settings I had to make adjustments to get it to run at all.
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote: On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... SNIP SNIP Unfortunately, while we would love to have similar down times to that, we obviously have overloads on a daily basis, bringing the machine down, or bogging it out, for vast periods of the day... This is only recently (past 2-3 months) before that, it bogged out onlya couple time a day, and the slowness was, at least, functional- though, thats a very relative term, especially when dealing with users who may have shorter attention spans. How are you spanning your queries over multiple DB servers? and are all writes being done on one master server, or have you found a way to do 2 way replication? Currently we have only the main appserver apache2/php/thttpd, and are trying a secondary apache2/php server (dual PIII/850/2G) which doesn't appear to be handling the load well at all... We're looking, obviously, at adding several front end appservers, though we want to ensure the G5 will be able to handle it's job if there are 5-6+ frontends on it. Here's a few stats from today. If you see anything oddball, please let me know. I see the Qcache_free_memory is awfully large, I assume that means it's being tremendously wasted. The stats say that the Query Cache is WAY big, but I predicted that. As for the slowness, I can't address the application side, but MySQL shouldn't be the cause of problems with this sort of hardware and server load. On the rest of the stats my comments are the number of queries aren't that high, this server should be more than enough properly tuned Number of connections seems high, that's an Application thing, I can only assume it's efficient in how it manages it's database connections etc? My stats show 47K connections on an uptime of a week, yours is at 242K connections on less than a day. My server typically has a little less than 400 connections actually present at any time and if I have 10 threads running (including replication etc) I start to get nervous and look for a problem (and pagers go off if we get over 20 threads running for more than 30 seconds). The number of times your connections change database seems high too, there are as many change database commands as selects. Again this is likely an Application thing, but might be an area that could be improved. SNIP While we prepare for the innodb switch, I would like to see if there is some idea as to why we are seeing so many DB changes and connections. Apache is set to allow unlimited persistant connections (prefork mpm... worker is still apparently unable to be used with php) and php.ini is set to allow unlimited persistent connections. I am unsure, yet, what calls are made exactly in the client's code, but, I do know his mysql module uses pconnects. Is there a timeout setting I should be looking for or something else? I will include some more important settings to apache/php/sysctrl below. The G5 has no odd settings, though it is OSX not OSXS (10.3.2). I have felt that the app server is a primary cause of the slowdowns... every page has debug info which report the timing of every sql call on the page, if the debug bit is set in the url when requested, and it doesn't match the page load times all the time. The pages may load slowly (time to data starting to be sent to browser, not time browser takes to completely receive it and images, etc) but the sql info says the queries took no time to complete. The overall generation time of the page is also calculated/reported for every page, and show the same discrepancies at times. In general, however, the sql times added together form 85-95% of the total page generation time, and that time is roughly the correct amount of time it takes for the page to start rendering. But the larger select queries which form the big/heavy portions of the pages (item lists within their categories) can have just plain wild times, with seemingly no reason. The same query done over and over will sometimes jump from 1sec to 5, 30,60 sec. and then back down again. The mysql server may not appear heavily loaded at these times, either- they will appear mostly normal. While we have nice SystemStats on our front end boxes (load/process counts/bandwidth/file sizes, etc) we do not, yet, have anything like it on the G5. The same stats calls do not run ont he G5, or, their output is unrecognized. I am trying to find a good stats grapher for OSX, particularly a non-console app (I use SystemStats2 from
Re: Memory Problems on a G5/OSX/MySql4.0.17
I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). 3) Give lots of memory to InnoDB, I'll share my settings below. 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join buffers.. I'd up the sort/read/join buffers to maybe 10MBytes, or even 20Mbytes, if you need to drop Key buffer to 1500M to give you the space for the others. We got OKish results on MyISAM with the larger sort/read/join buffers - InnoDB made all the difference though. Before giving you our settings I do want to point out one
RE: Memory Problems on a G5/OSX/MySql4.0.17
So should we always use InnoDB over BerkeleyBD? I was under the impression Berkeley was faster and better at handling transactions. Dan -Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 11:01 AM To: [EMAIL PROTECTED] Subject: Re: Memory Problems on a G5/OSX/MySql4.0.17 I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). 3) Give lots of memory to InnoDB, I'll share my settings below. 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join
Re: Memory Problems on a G5/OSX/MySql4.0.17
On 1/28/04 10:29 AM, stairwaymail-mysql at yahoo dot com wrote: So should we always use InnoDB over BerkeleyBD? I was under the impression Berkeley was faster and better at handling transactions. Dan Eermm... That's outside my scope of expertise, my experiences have been exclusively with InnoDB and before that MyISAM, and we don't do transactions. The point I was making by mentioning the transaction side of things was in response to the earlier comments that InnoDB might help the person out if they do transactions. Most mention of InnoDB comes into play when people want transactions, but it turns out InnoDB is much better at large databases than MyISAM in many many situations. All I was saying is that InnoDB isn't JUST about transactions. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
Raid 5 is just as common as any other raid in software, and on my other boxes it does not present any problem at all... I have seen excellent tests with raid5 in software, and many contest that software raid 5 on a high powered system is faster than hardware raid 5 using the same disks-- I haven't seen proof of this, however.I have seen the CPU's used in many raid5 hardware cards and they are surprisingly slow (avg 33mhz). The record sizes for our database are completely random, and therefore would likely require a multitude of disk reads, which would then be likely to need waits on spindles, etc (I am not aware of anyone syncing spindles anymore, or if it would have any effect if we did). We are almost ready to switch to Gbit enet, however, I am unsure it will help either... according to my graphs, internal traffic (to/from the mysql/G5 server) is only an average of ~1.3Mbs 1.0 Mbs, with peaks to 5.7Mbs/5.0Mbs (I dunno is the below graph will make it through the list...). This graph is from the Apache/php server. -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 28, 2004, at 11:33 AM, Brent Baisley wrote: The split setup may be faster because you don't have contention for resources. Depending on how much data is being moved over the network connection, making it Gb ethernet may speed things up more. In a RAID, ideally the strip size would match the record size in your database. So one record equals one read. Stripe sizes that are too small require multiple reads per record, stripe sizes that are too large require extraneous data to be read. Read ahead often doesn't work that well with databases since the access is totally random. Unless you are accessing the database in the same order the records were written. Did you have a software based RAID 5 setup on the Linux box? I never heard of implementing RAID 5 in software. I'm not sure what the CPU overhead would be on that, especially with 8 disks. So what exactly is your current setup (computers, disks, ram, software, database locations, etc)? On Jan 27, 2004, at 10:48 PM, Adam Goldstein wrote: I have had linux on soft-raid5 (6x18G, 8x9G, 4x18G) systems, and the load was even higher... The explanation for this could be that at high IO rates the data is not 100% synced across the spindles, and therefore smaller files (ie files smaller than the chunk size on each physical disk) must wait to be passed under the heads on all the disks... While larger chunk sizes may help this, I'm not sure. A large ram buffer and read ahead on a dedicated raid system is more likely to work in that case, but, that would require either yet another fileserver (fairly expensive), or a hw dedicated Raid server (much more expensive), like the Xraid, which did not produce any real difference in the mysql bench results previously posted here. In fact, going by those simple benchmarks alone, my box already beat the Xserve/Xraid system in most of the tests. Of course, the validity or relativity of those tests to a real world, heavily used server may be in question. :) I also am having trouble finding relative bench data to other good power systems (ie. I would like to see how this stacks up against an 8+G dual/quad xeon or sparc, etc) I will ensure his nightly optimize/repair scripts feature the flush. But, none of this yet explains why testing from the linux box using the remote G5/mysql server (over only 100Mbit switch) gives better results than testing directly on the server. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). we have an average of ~15-20%, with times sustaining 30+% 3) Give lots of memory to InnoDB, I'll share my settings below. Thank You! 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. ok 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. The slow log has helped us a lot in the past... with the current slow log settings, only about 0.1% are slow queries. 3K out of 4million in the past 18hours. Currently the time appears to be set at 2 (From show variables: slow_launch_time 2 ). 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the
Re: Memory Problems on G5/OSX/MySql4.0.17
I don't think there would be any benefit to using InnoDB, at least not from a transaction support view. After your nightly optimize/repair are you also doing a flush? That may help. I haven't seen any direct comparisons between HFS+ and file systems supported by Linux. I would believe that Linux would be faster since Linux tends to be geared towards performance first rather than usability. But you shouldn't rely on disk caching only. The disks still need to be read in order to fill the cache, so you want to get the best disk performance you can. Based on your other email, it looks like you are using individual disks for storing your data. While I understand what you were trying to do by separating your data onto different disks, you would get far better performance by combining your disks in a RAID, even a software RAID. If you are using software based RAID, you would need to choose between mirroring or striping. Both will give you better read speeds, mirroring will slow down writes. If you are striping, the more drives you use the better performance you'll get, although I wouldn't put more than 4 drives on a single SCSI card. I think you can use Apple's RAID software for your SCSI disk, but SoftRAID (softraid.com) would give you more options. Moving to RAID should improve things across the board and will give the best bang for your buck (SoftRAID is $129). Personally, I think you should always use some form of RAID on all servers. On Jan 26, 2004, at 5:41 PM, Adam Goldstein wrote: I have added these settings to my newer my.cnf, including replacing the key_buffer=1600M with this 768M... It was a touch late today to see if it has a big effect during the heavy load period (~3am to 4pm EST, site has mostly european users) I did not have any of these settings explicitly set in my latest my.cnf trialsm, except key_buffer, and I ommitted the innodb ones, as we are not (currently) using innodb... would there be any benefit? transactions are not a priority, so says my client, so he does not use them. I see the query_cache_size is rather large here, but I am unsure what the default size would be. I do not know, yet, how large I can/should make either setting, but, it does appear to work without malloc/memory errors appearing in the log. Note: while it bitched in the logs about the malloc setting, the server did not crash, but, kept running. Obviously with an undetermined amount of cache. I cannot seem to find any good way to know how much ram (cache/buffer/other) mysql uses, as the top output from osx is not very appealing... not that linux top tells me much more either. On average, on the old system (all on one box) mysql was said to be using about 350MB avg in top... except after the nightly optimize/repair script which left it using 1.2G of ram for hours, and making all queries rather slow. Also- a more G5 specific question: as MySql is supposed to gain much from the OS disk caching, how does OSX/HFS+ compare to other *nIX filesystems... such as Linux 2.4 w/reiserfs? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
I have managed to get what looks like 2G for the process, but, it does not want to do a key_buffer of that size I gave it a Key_buffer of 768M and a query cache of 1024M, and it seems happier.. though, not noticeably faster. [mysqld] key_buffer = 768M max_allowed_packet = 8M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 512M thread_cache = 8 thread_concurrency = 8 max_connections = 1000 skip-name-resolve skip-bdb skip-innodb skip-locking ft_min_word_len= 2 join_buffer_size = 3M query_cache_size=1024M bulk_insert_buffer_size=256M tmp_table_size =128M sort_buffer =8M read_rnd_buffer_size=8M record_buffer=32M open_files_limit=15000 tmpdir = /tmp/ log-bin = /var/log/mysql/raptor-bin.log Benchmarks are just plain weird. Here is from the linux server to the G5: alter-table: Total time: 11 wallclock secs ( 0.03 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.05 CPU) ATIS: Failed (output/ATIS-mysql_fast-Linux_2.4.22_21mdkenterprise_i686-cmp-mysql- fink-64) big-tables: Total time: 15 wallclock secs ( 4.31 usr 2.79 sys + 0.00 cusr 0.00 csys = 7.10 CPU) connect: Total time: 324 wallclock secs (46.64 usr 30.27 sys + 0.00 cusr 0.00 csys = 76.91 CPU) create: Total time: 105 wallclock secs ( 2.04 usr 1.10 sys + 0.00 cusr 0.00 csys = 3.14 CPU) insert: Total time: 1237 wallclock secs (295.16 usr 73.22 sys + 0.00 cusr 0.00 csys = 368.38 CPU) select: Total time: 134 wallclock secs (32.39 usr 6.77 sys + 0.00 cusr 0.00 csys = 39.16 CPU) wisconsin: Failed (output/wisconsin-mysql_fast-Linux_2.4.22_21mdkenterprise_i686-cmp- mysql-fink-64) and here is on the G5 locally: alter-table: Total time: 38 wallclock secs ( 0.07 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.12 CPU) ATIS: Total time: 20 wallclock secs ( 7.90 usr 7.77 sys + 0.00 cusr 0.00 csys = 15.67 CPU) big-tables: Total time: 27 wallclock secs ( 6.49 usr 16.10 sys + 0.00 cusr 0.00 csys = 22.59 CPU) connect: Total time: 167 wallclock secs (39.79 usr 52.78 sys + 0.00 cusr 0.00 csys = 92.57 CPU) create: Total time: 106 wallclock secs ( 6.12 usr 2.94 sys + 0.00 cusr 0.00 csys = 9.06 CPU) insert: Total time: 1257 wallclock secs (388.48 usr 311.51 sys + 0.00 cusr 0.00 csys = 699.99 CPU) select: Total time: 132 wallclock secs (40.22 usr 27.92 sys + 0.00 cusr 0.00 csys = 68.14 CPU) wisconsin: Total time: 5 wallclock secs ( 1.89 usr 1.65 sys + 0.00 cusr 0.00 csys = 3.54 CPU) Some of the strangeness is due to it being a live server, tested during low use hours. How accurate are these bench marks, and do they represent the overall strength of the mysql server to handle large loads? I can't get a good idea a to how many queries/sec it should be able to handle, considering I can't tell how complex the queries are. All I can say is the site serves 12mil pages/month (~100mil hits/mo), 80% concentrated into 13H of the day, with perhaps 40% in just 4-5hours... About 1million hits to the heaviest sql page/month, broken up into the above portions. Of course, that is also not including the amount of people potentially trying to access the site during this time, which by eyeball estimates on the graphs suggest easily 25-150% more, plus the amount more that would come if the site could handle them and they were happy. (We all know web users are a fickle bunch, and will drop a slow loading site like a hot potato.) -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 26, 2004, at 12:05 PM, Brad Eacker wrote: Adam, Off the wall question, but is White Wolf Networks related in any way to White Wolf Publishing? You may be hitting an OSX limit. While you can install more than 2GB on a system, I don't think any one process is allowed to allocated more than 2GB of RAM to itself. It's not a 64-bit OS yet. You should be able to search the Apple website for this limit. Could you take a look at the ulimit man page to see if it will allow greater than a signed 32 bit value (2G). If it does not then there is still a 32 bit limitation on process size due to this basic constraint. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
I have had linux on soft-raid5 (6x18G, 8x9G, 4x18G) systems, and the load was even higher... The explanation for this could be that at high IO rates the data is not 100% synced across the spindles, and therefore smaller files (ie files smaller than the chunk size on each physical disk) must wait to be passed under the heads on all the disks... While larger chunk sizes may help this, I'm not sure. A large ram buffer and read ahead on a dedicated raid system is more likely to work in that case, but, that would require either yet another fileserver (fairly expensive), or a hw dedicated Raid server (much more expensive), like the Xraid, which did not produce any real difference in the mysql bench results previously posted here. In fact, going by those simple benchmarks alone, my box already beat the Xserve/Xraid system in most of the tests. Of course, the validity or relativity of those tests to a real world, heavily used server may be in question. :) I also am having trouble finding relative bench data to other good power systems (ie. I would like to see how this stacks up against an 8+G dual/quad xeon or sparc, etc) I will ensure his nightly optimize/repair scripts feature the flush. But, none of this yet explains why testing from the linux box using the remote G5/mysql server (over only 100Mbit switch) gives better results than testing directly on the server. -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 27, 2004, at 9:45 AM, Brent Baisley wrote: I don't think there would be any benefit to using InnoDB, at least not from a transaction support view. After your nightly optimize/repair are you also doing a flush? That may help. I haven't seen any direct comparisons between HFS+ and file systems supported by Linux. I would believe that Linux would be faster since Linux tends to be geared towards performance first rather than usability. But you shouldn't rely on disk caching only. The disks still need to be read in order to fill the cache, so you want to get the best disk performance you can. Based on your other email, it looks like you are using individual disks for storing your data. While I understand what you were trying to do by separating your data onto different disks, you would get far better performance by combining your disks in a RAID, even a software RAID. If you are using software based RAID, you would need to choose between mirroring or striping. Both will give you better read speeds, mirroring will slow down writes. If you are striping, the more drives you use the better performance you'll get, although I wouldn't put more than 4 drives on a single SCSI card. I think you can use Apple's RAID software for your SCSI disk, but SoftRAID (softraid.com) would give you more options. Moving to RAID should improve things across the board and will give the best bang for your buck (SoftRAID is $129). Personally, I think you should always use some form of RAID on all servers. On Jan 26, 2004, at 5:41 PM, Adam Goldstein wrote: I have added these settings to my newer my.cnf, including replacing the key_buffer=1600M with this 768M... It was a touch late today to see if it has a big effect during the heavy load period (~3am to 4pm EST, site has mostly european users) I did not have any of these settings explicitly set in my latest my.cnf trialsm, except key_buffer, and I ommitted the innodb ones, as we are not (currently) using innodb... would there be any benefit? transactions are not a priority, so says my client, so he does not use them. I see the query_cache_size is rather large here, but I am unsure what the default size would be. I do not know, yet, how large I can/should make either setting, but, it does appear to work without malloc/memory errors appearing in the log. Note: while it bitched in the logs about the malloc setting, the server did not crash, but, kept running. Obviously with an undetermined amount of cache. I cannot seem to find any good way to know how much ram (cache/buffer/other) mysql uses, as the top output from osx is not very appealing... not that linux top tells me much more either. On average, on the old system (all on one box) mysql was said to be using about 350MB avg in top... except after the nightly optimize/repair script which left it using 1.2G of ram for hours, and making all queries rather slow. Also- a more G5 specific question: as MySql is supposed to gain much from the OS disk caching, how does OSX/HFS+ compare to other *nIX filesystems... such as Linux 2.4 w/reiserfs? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory Problems on G5/OSX/MySql4.0.17
I cannot seem to allocate any large amounts of memory to Mysql on our system... Can anyone suggest any settings/changes/etc to get this running to the best of it's ability? Dual 2Ghz G5, 4G ram, OSX 10.3.2, 73G-10Krpm Sata Raptor drives Using both the 'Complete Mysql4.0.15 and Standard binary package 4.0.17 I cannot seem to get the daemon to accept using a large Key_buffer setting, such as 1900M or 2600M (though 1600M seemed to work the first startup). I get this error: Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:40:16 mysqld started *** malloc: vm_allocate(size=2597892096) failed (error code=3) *** malloc[14345]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:43:00 mysqld started *** malloc: vm_allocate(size=1984614400) failed (error code=3) *** malloc[14378]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 This is a dedicated mysql backend server using MyISAM tables (currently) and we need it to run a fairly heavy load. This is only the relevant conf data: [mysqld] skip-locking key_buffer = 1990M max_allowed_packet = 8M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M join_buffer_size = 3M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 max_connections = 1200 skip-name-resolve skip-bdb skip-innodb ft_min_word_len = 2 tmpdir = /tmp/ log-bin = /var/log/mysql/raptor-bin.log These are the largest tables in the db (other clipped): Raptor: ../data root# ls -lhS /Library/MySQL/var/db1/ total 9177432 -rw-rw 1 mysql mysql975M 21 Jan 20:30 axxx_search.MYD -rw-rw 1 mysql mysql619M 21 Jan 20:30 axxx_search.MYI -rw-rw 1 mysql mysql571M 21 Jan 20:05 td_visitor_archive.MYD -rw-rw 1 mysql mysql492M 21 Jan 20:37 message.MYD -rw-rw 1 mysql mysql435M 21 Jan 20:30 axxx_description.MYD -rw-rw 1 mysql mysql412M 21 Jan 20:37 enxxx.MYD -rw-rw 1 mysql mysql336M 21 Jan 20:37 enxxx.MYI -rw-rw 1 mysql mysql200M 23 Dec 09:05 axxx_title_images.MYD -rw-rw 1 mysql mysql 97M 21 Jan 20:06 rating.MYD -rw-rw 1 mysql mysql 81M 21 Jan 20:06 rating.MYI -rw-rw 1 mysql mysql 49M 21 Jan 20:24 bxx.MYI -rw-rw 1 mysql mysql 28M 21 Jan 20:24 bxx.MYD ...clip... These are our best benchmarks: alter-table: Total time: 6 wallclock secs ( 0.03 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.07 CPU) ATIS: Total time: 21 wallclock secs (17.20 usr 3.37 sys + 0.00 cusr 0.00 csys = 20.57 CPU) big-tables: Total time: 15 wallclock secs ( 4.30 usr 3.60 sys + 0.00 cusr 0.00 csys = 7.90 CPU) connect: Total time: 4 wallclock secs ( 0.61 usr 0.29 sys + 0.00 cusr 0.00 csys = 0.90 CPU) create: Total time: 98 wallclock secs (11.63 usr 3.02 sys + 0.00 cusr 0.00 csys = 14.65 CPU) insert: Total time: 1405 wallclock secs (312.96 usr 78.26 sys + 0.00 cusr 0.00 csys = 391.22 CPU) select: Total time: 122 wallclock secs (33.21 usr 7.03 sys + 0.00 cusr 0.00 csys = 40.24 CPU) wisconsin: Total time: 8 wallclock secs ( 5.00 usr 0.49 sys + 0.00 cusr 0.00 csys = 5.49 CPU) -- Adam Goldstein White Wolf Networks http://whitewlf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
You may be hitting an OSX limit. While you can install more than 2GB on a system, I don't think any one process is allowed to allocated more than 2GB of RAM to itself. It's not a 64-bit OS yet. You should be able to search the Apple website for this limit. On Jan 26, 2004, at 6:10 AM, Adam Goldstein wrote: I cannot seem to allocate any large amounts of memory to Mysql on our system... Can anyone suggest any settings/changes/etc to get this running to the best of it's ability? Dual 2Ghz G5, 4G ram, OSX 10.3.2, 73G-10Krpm Sata Raptor drives Using both the 'Complete Mysql4.0.15 and Standard binary package 4.0.17 I cannot seem to get the daemon to accept using a large Key_buffer setting, such as 1900M or 2600M (though 1600M seemed to work the first startup). I get this error: Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:40:16 mysqld started *** malloc: vm_allocate(size=2597892096) failed (error code=3) *** malloc[14345]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:43:00 mysqld started *** malloc: vm_allocate(size=1984614400) failed (error code=3) *** malloc[14378]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 This is a dedicated mysql backend server using MyISAM tables (currently) and we need it to run a fairly heavy load. This is only the relevant conf data: [mysqld] skip-locking key_buffer = 1990M max_allowed_packet = 8M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M join_buffer_size = 3M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 max_connections = 1200 skip-name-resolve skip-bdb skip-innodb ft_min_word_len = 2 tmpdir = /tmp/ log-bin = /var/log/mysql/raptor-bin.log These are the largest tables in the db (other clipped): Raptor: ../data root# ls -lhS /Library/MySQL/var/db1/ total 9177432 -rw-rw 1 mysql mysql975M 21 Jan 20:30 axxx_search.MYD -rw-rw 1 mysql mysql619M 21 Jan 20:30 axxx_search.MYI -rw-rw 1 mysql mysql571M 21 Jan 20:05 td_visitor_archive.MYD -rw-rw 1 mysql mysql492M 21 Jan 20:37 message.MYD -rw-rw 1 mysql mysql435M 21 Jan 20:30 axxx_description.MYD -rw-rw 1 mysql mysql412M 21 Jan 20:37 enxxx.MYD -rw-rw 1 mysql mysql336M 21 Jan 20:37 enxxx.MYI -rw-rw 1 mysql mysql200M 23 Dec 09:05 axxx_title_images.MYD -rw-rw 1 mysql mysql 97M 21 Jan 20:06 rating.MYD -rw-rw 1 mysql mysql 81M 21 Jan 20:06 rating.MYI -rw-rw 1 mysql mysql 49M 21 Jan 20:24 bxx.MYI -rw-rw 1 mysql mysql 28M 21 Jan 20:24 bxx.MYD ...clip... These are our best benchmarks: alter-table: Total time: 6 wallclock secs ( 0.03 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.07 CPU) ATIS: Total time: 21 wallclock secs (17.20 usr 3.37 sys + 0.00 cusr 0.00 csys = 20.57 CPU) big-tables: Total time: 15 wallclock secs ( 4.30 usr 3.60 sys + 0.00 cusr 0.00 csys = 7.90 CPU) connect: Total time: 4 wallclock secs ( 0.61 usr 0.29 sys + 0.00 cusr 0.00 csys = 0.90 CPU) create: Total time: 98 wallclock secs (11.63 usr 3.02 sys + 0.00 cusr 0.00 csys = 14.65 CPU) insert: Total time: 1405 wallclock secs (312.96 usr 78.26 sys + 0.00 cusr 0.00 csys = 391.22 CPU) select: Total time: 122 wallclock secs (33.21 usr 7.03 sys + 0.00 cusr 0.00 csys = 40.24 CPU) wisconsin: Total time: 8 wallclock secs ( 5.00 usr 0.49 sys + 0.00 cusr 0.00 csys = 5.49 CPU) -- Adam Goldstein White Wolf Networks http://whitewlf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
Others on this list have claimed to be able to set over 3G, and my failure is with even less than 2G (though, I am unsure if there is a combination of other memory settings working together to create an 2GB situation combined) Even at 1.6G, which seems to work (though, -not- why we got 4G of expensive ram), does anyone have any advice for optimizing the settings? Or are they pretty optimized as it is? (according to benchmarks, anyways) -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 26, 2004, at 10:13 AM, Brent Baisley wrote: You may be hitting an OSX limit. While you can install more than 2GB on a system, I don't think any one process is allowed to allocated more than 2GB of RAM to itself. It's not a 64-bit OS yet. You should be able to search the Apple website for this limit. On Jan 26, 2004, at 6:10 AM, Adam Goldstein wrote: I cannot seem to allocate any large amounts of memory to Mysql on our system... Can anyone suggest any settings/changes/etc to get this running to the best of it's ability? Dual 2Ghz G5, 4G ram, OSX 10.3.2, 73G-10Krpm Sata Raptor drives Using both the 'Complete Mysql4.0.15 and Standard binary package 4.0.17 I cannot seem to get the daemon to accept using a large Key_buffer setting, such as 1900M or 2600M (though 1600M seemed to work the first startup). I get this error: Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:40:16 mysqld started *** malloc: vm_allocate(size=2597892096) failed (error code=3) *** malloc[14345]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 040126 05:43:00 mysqld started *** malloc: vm_allocate(size=1984614400) failed (error code=3) *** malloc[14378]: error: Can't allocate region /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.17-standard-log' socket: '/tmp/mysql.sock' port: 3306 This is a dedicated mysql backend server using MyISAM tables (currently) and we need it to run a fairly heavy load. This is only the relevant conf data: [mysqld] skip-locking key_buffer = 1990M max_allowed_packet = 8M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M join_buffer_size = 3M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 8 max_connections = 1200 skip-name-resolve skip-bdb skip-innodb ft_min_word_len = 2 tmpdir = /tmp/ log-bin = /var/log/mysql/raptor-bin.log These are the largest tables in the db (other clipped): Raptor: ../data root# ls -lhS /Library/MySQL/var/db1/ total 9177432 -rw-rw 1 mysql mysql975M 21 Jan 20:30 axxx_search.MYD -rw-rw 1 mysql mysql619M 21 Jan 20:30 axxx_search.MYI -rw-rw 1 mysql mysql571M 21 Jan 20:05 td_visitor_archive.MYD -rw-rw 1 mysql mysql492M 21 Jan 20:37 message.MYD -rw-rw 1 mysql mysql435M 21 Jan 20:30 axxx_description.MYD -rw-rw 1 mysql mysql412M 21 Jan 20:37 enxxx.MYD -rw-rw 1 mysql mysql336M 21 Jan 20:37 enxxx.MYI -rw-rw 1 mysql mysql200M 23 Dec 09:05 axxx_title_images.MYD -rw-rw 1 mysql mysql 97M 21 Jan 20:06 rating.MYD -rw-rw 1 mysql mysql 81M 21 Jan 20:06 rating.MYI -rw-rw 1 mysql mysql 49M 21 Jan 20:24 bxx.MYI -rw-rw 1 mysql mysql 28M 21 Jan 20:24 bxx.MYD ...clip... These are our best benchmarks: alter-table: Total time: 6 wallclock secs ( 0.03 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.07 CPU) ATIS: Total time: 21 wallclock secs (17.20 usr 3.37 sys + 0.00 cusr 0.00 csys = 20.57 CPU) big-tables: Total time: 15 wallclock secs ( 4.30 usr 3.60 sys + 0.00 cusr 0.00 csys = 7.90 CPU) connect: Total time: 4 wallclock secs ( 0.61 usr 0.29 sys + 0.00 cusr 0.00 csys = 0.90 CPU) create: Total time: 98 wallclock secs (11.63 usr 3.02 sys + 0.00 cusr 0.00 csys = 14.65 CPU) insert: Total time: 1405 wallclock secs (312.96 usr 78.26 sys + 0.00 cusr 0.00 csys = 391.22 CPU) select: Total time: 122 wallclock secs (33.21 usr 7.03 sys + 0.00 cusr 0.00 csys = 40.24 CPU) wisconsin: Total time: 8 wallclock secs ( 5.00 usr 0.49 sys + 0.00 cusr 0.00 csys = 5.49 CPU) -- Adam Goldstein White Wolf Networks http://whitewlf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Memory Problems on G5/OSX/MySql4.0.17
2GB was the per-process memory limit in Mac OS X 10.2 and earlier. 10.3 increased this to 4GB per-process. I've gotten MySQL running with 3GB of RAM on the G5 previously. This is an excerpt from a prior email to the list from back in October when I was first testing MySQL on the G5: query_cache_size=1024M bulk_insert_buffer_size=256M tmp_table_size=128M sort_buffer=8M read_rnd_buffer_size=8M key_buffer=768M record_buffer=32M myisam_sort_buffer_size=512M innodb_buffer_pool_size=1024M innodb_additional_mem_pool_size=32M However, for some reason, when I swapped the values key_buffer and query_cache_size to try and give key_buffer 1GB, it failed. I swapped the values back and it worked fine... odd. - Gabriel On Jan 26, 2004, at 11:16 AM, Brent Baisley wrote: Yes, MySQL is capable of using more than 2GB, but it still must obey the limits of the underlying OS. This means file sizes, memory allocation and whatever else. Have you heard of anybody allocating more the 2GB using OSX? I've heard of quite a bit more using Linux or other Unix flavors, but not OSX. As for optimizing settings, you need to profile you work load. You may actually run into I/O, CPU or Network bottleneck before you hit a memory bottleneck. You need to run things and find where the bottleneck is to optimize performance. On Jan 26, 2004, at 11:09 AM, Adam Goldstein wrote: Others on this list have claimed to be able to set over 3G, and my failure is with even less than 2G (though, I am unsure if there is a combination of other memory settings working together to create an 2GB situation combined) -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Memory Problems on G5/OSX/MySql4.0.17
Yes, I saw this port before... I am not sure why I cannot allocate more ram on this box- It is a clean 10.3 install, with 10.3.2 update. I got this box as I love OSX, and have always loved apple, but, this is not working out great. Much less powerful (and less expensive) units can do a better job of this (the entire site was run on ONE dual athlon box with 3G ram, and it seems to have made -NO- difference moving the mysql to the dedicated G5.) Obviously, there is something wrong somewhere- And, I need to find where. My client (site creator) is depending on me to help him boost the ability of the site to handle more users, but we've always been able to do it on a light budget. I need to know where to look first, as we are running out of time... His users are a fickle bunch, and will likely migrate off to other sites if this slowness continues (it has been degrading for past 3-4 months from slow at peak, to dead for all peak hours). These are example queries from the heavier pages: 1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204 LIMIT 0,1 Time: 0.0004551410675 sec / Type: Buffered 2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3, e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut, e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 14:41:59') ORDER BY date_fin ASC LIMIT 0, 80 Time: 37.60733294 sec / Type: Buffered 3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 14:41:59') Time: 0.9267110825 sec / Type: Buffered 4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query = 'language=Ecat=4204sql_log=Y', fdt_date = '2004-01-26', fdt_time = '14:42:38', fnb_seconds = 39.22 Time: 0.005410909653 sec / Type: Buffered making the page take 40 seconds to load. A few minutes later it can take only 1 second to load the same page... Strangely enough, even after shutting off apache on the primary server, it still took 33sec to execute query #2 above directly on the G5/mysql server. A few moments before shutting off apache, my client informed me a page just took 220sec to load. The apache/php server had a load 8 at that time, and there were numerous apache connections/children running at that time. Now, a few minutes later: Page generated in 33.96 seconds. 1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204 LIMIT 0,1 Time: 0.0008599758148 sec / Type: Buffered 2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3, e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut, e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 15:18:47') ORDER BY date_fin ASC LIMIT 0, 80 Time: 1.710601091 sec / Type: Buffered 3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 15:18:47') Time: 30.83186793 sec / Type: Buffered 4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query = 'language=Ecat=4204sql_log=Y', fdt_date = '2004-01-26', fdt_time = '15:19:21', fnb_seconds = 33.96 Time: 0.01498007774 sec / Type: Buffered Query 2 is fast, and 3 is slow. Completely reversed. Here is a snapshot of stats (my own script) at the time of the first, larger, slowdown (with explanations): [EMAIL PROTECTED] root]# webstat 14:54:57 up 22:48, 2 users, load average: 8.62, 7.39, 6.82 Page Connections: 408--- avg of several netstat outputs on webIP:80 (apache/php) Image Connections: 538 --- avg of several netstat outputs on imageIP:80 (thttpd) Mysql Connections: 386 --- avg of several netstat outputs on :3306 Mail Connections: 28--- avg of several netstat outputs on :25 Apache Processes: 402 --- apache child process count Mysql Processes: 0 php Processes: 2 --- number of currently running cron'd php/shell scripts procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 3 2 18 55 132600 14379 240 211 10 6 84 0 1 1 2 15 54 133000 4448 2784 1351 2753 31 27 42 0 3 1 2 9 54 133100 3332 2960 1275 3146 28 29 43 0 4 2 2 9 53 133200 2564 2892 1722 3213 25 25 50 0 We also have lots of good graph info from SystemStats php/rrd grapher, but only from the main apache server. I am adding it to the G5/sql server now. The main thing on the graph is the inverse reaction of Load, Processes, and Bandwidth usage. The bandwidth drops from ~2-3Mbits, to 50-80% less then slowly rises again, creating vivid dips. It is
Re: Memory Problems on G5/OSX/MySql4.0.17
Have you tried reworking your queries a bit? I try to avoid using IN as much as possible. What does EXPLAIN say about how the long queries are executed? If I have to match something against a lot of values, I select the values into a HEAP table and then do a join. Especially if YOU are going to be reusing the values within the current session. Are you storing images (img1, img2, img3) in the database? I would recommend against that in a high load database, it bloats the database size forcing the database to use a lot more RAM to cache the database. It also prevents you from creating a database with fixed length records. Keeping the images as files will push the loading of the images out to the file system and web server. What kind of RAID setup do you have? You just said you had 73GB 10K disks. Why didn't you go with 15k disks? Cost? On Jan 26, 2004, at 3:42 PM, Adam Goldstein wrote: Yes, I saw this port before... I am not sure why I cannot allocate more ram on this box- It is a clean 10.3 install, with 10.3.2 update. I got this box as I love OSX, and have always loved apple, but, this is not working out great. Much less powerful (and less expensive) units can do a better job of this (the entire site was run on ONE dual athlon box with 3G ram, and it seems to have made -NO- difference moving the mysql to the dedicated G5.) Obviously, there is something wrong somewhere- And, I need to find where. My client (site creator) is depending on me to help him boost the ability of the site to handle more users, but we've always been able to do it on a light budget. I need to know where to look first, as we are running out of time... His users are a fickle bunch, and will likely migrate off to other sites if this slowness continues (it has been degrading for past 3-4 months from slow at peak, to dead for all peak hours). These are example queries from the heavier pages: 1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204 LIMIT 0,1 Time: 0.0004551410675 sec / Type: Buffered 2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3, e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut, e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 14:41:59') ORDER BY date_fin ASC LIMIT 0, 80 Time: 37.60733294 sec / Type: Buffered 3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 14:41:59') Time: 0.9267110825 sec / Type: Buffered 4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query = 'language=Ecat=4204sql_log=Y', fdt_date = '2004-01-26', fdt_time = '14:42:38', fnb_seconds = 39.22 Time: 0.005410909653 sec / Type: Buffered making the page take 40 seconds to load. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on G5/OSX/MySql4.0.17
The primary server (Dual Athlon) has several U160 scsi disks, 10K and 15K rpm... Approximately half the full size images are on one 73G U160, the other half on another (about 120G of large images alone being stored... I am trying to get him to abandon/archive old/unused images). The system/logs run on a 36G 10K, Mysql used to run on another 36G 15k, and /home (with thumbnails and php files) is on another 36G 10K... There is also a 250G U133 drive for archives/backups. Apache2.0.47/PHP4.3.4 We are going to upgrade the rest of the 10Krpm drives to 15Krpm, but, that does not (yet) help the G5... it is a full tower unit at the moment, though we are now looking at replacing it with a G5 Xserve. The desktop unit can only contain 2xSATA drives internally, and we do not have an external raid/scsi/FC system to use on it.. yet. My thought when setting this up was to use more RAM cache than disk for the DB. The entire DB is about 5.5GB total, currently, and resides on it's own partition on it's own disk. The G5 is using std. HFS+ on all disks, but the Athlon/linux server is using reiserfs on most disks. I will relay the HEAP/EXPLAIN info to my client, as I do not work on that portion of the system... He does the code, I keep the systems up/running. We are trying to implement load balancing and, eventually, failover redundancy... The initial thought was the G5 and Dual Athlon being cooperative/redundant machines but, it is looking like we will need several frontends and the G5/D.Athlon be backends... All of this needs to be done in the tightest budget shortest time possible... we are looking at adding 3-5 1U frontend machines, but only if we can make sure the G5/D.Athlon boxes can handle it. Obviously there need to be some larger changes, but we want to avoid throwing hardware money at it without reason. We also have a second 'frontend' machine temporarily being used, a Dual PIII/850 w/2G ram and 4xscsi drives. It seems strangely unable to handle much user load at all Initially I tried simple DNS load balancing, but, that was quickly discarded for subdomain/site topic separation. It can handle only about 20% on the main server's userload it seems. (php files reside local on it, all images are served via main server/thttpd, some dynamic includes are done via NFS mount to main server). -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 26, 2004, at 4:39 PM, Brent Baisley wrote: Have you tried reworking your queries a bit? I try to avoid using IN as much as possible. What does EXPLAIN say about how the long queries are executed? If I have to match something against a lot of values, I select the values into a HEAP table and then do a join. Especially if YOU are going to be reusing the values within the current session. Are you storing images (img1, img2, img3) in the database? I would recommend against that in a high load database, it bloats the database size forcing the database to use a lot more RAM to cache the database. It also prevents you from creating a database with fixed length records. Keeping the images as files will push the loading of the images out to the file system and web server. What kind of RAID setup do you have? You just said you had 73GB 10K disks. Why didn't you go with 15k disks? Cost? On Jan 26, 2004, at 3:42 PM, Adam Goldstein wrote: Yes, I saw this port before... I am not sure why I cannot allocate more ram on this box- It is a clean 10.3 install, with 10.3.2 update. I got this box as I love OSX, and have always loved apple, but, this is not working out great. Much less powerful (and less expensive) units can do a better job of this (the entire site was run on ONE dual athlon box with 3G ram, and it seems to have made -NO- difference moving the mysql to the dedicated G5.) Obviously, there is something wrong somewhere- And, I need to find where. My client (site creator) is depending on me to help him boost the ability of the site to handle more users, but we've always been able to do it on a light budget. I need to know where to look first, as we are running out of time... His users are a fickle bunch, and will likely migrate off to other sites if this slowness continues (it has been degrading for past 3-4 months from slow at peak, to dead for all peak hours). These are example queries from the heavier pages: 1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204 LIMIT 0,1 Time: 0.0004551410675 sec / Type: Buffered 2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3, e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut, e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin = '2004-01-26 14:41:59') ORDER BY date_fin ASC LIMIT 0, 80 Time: 37.60733294 sec / Type: Buffered 3: SELECT COUNT(e.id_enchere) AS nbre FROM
Re: Memory Problems on G5/OSX/MySql4.0.17
I have added these settings to my newer my.cnf, including replacing the key_buffer=1600M with this 768M... It was a touch late today to see if it has a big effect during the heavy load period (~3am to 4pm EST, site has mostly european users) I did not have any of these settings explicitly set in my latest my.cnf trialsm, except key_buffer, and I ommitted the innodb ones, as we are not (currently) using innodb... would there be any benefit? transactions are not a priority, so says my client, so he does not use them. I see the query_cache_size is rather large here, but I am unsure what the default size would be. I do not know, yet, how large I can/should make either setting, but, it does appear to work without malloc/memory errors appearing in the log. Note: while it bitched in the logs about the malloc setting, the server did not crash, but, kept running. Obviously with an undetermined amount of cache. I cannot seem to find any good way to know how much ram (cache/buffer/other) mysql uses, as the top output from osx is not very appealing... not that linux top tells me much more either. On average, on the old system (all on one box) mysql was said to be using about 350MB avg in top... except after the nightly optimize/repair script which left it using 1.2G of ram for hours, and making all queries rather slow. Also- a more G5 specific question: as MySql is supposed to gain much from the OS disk caching, how does OSX/HFS+ compare to other *nIX filesystems... such as Linux 2.4 w/reiserfs? -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 26, 2004, at 11:49 AM, Gabriel Ricard wrote: 2GB was the per-process memory limit in Mac OS X 10.2 and earlier. 10.3 increased this to 4GB per-process. I've gotten MySQL running with 3GB of RAM on the G5 previously. This is an excerpt from a prior email to the list from back in October when I was first testing MySQL on the G5: query_cache_size=1024M bulk_insert_buffer_size=256M tmp_table_size=128M sort_buffer=8M read_rnd_buffer_size=8M key_buffer=768M record_buffer=32M myisam_sort_buffer_size=512M innodb_buffer_pool_size=1024M innodb_additional_mem_pool_size=32M However, for some reason, when I swapped the values key_buffer and query_cache_size to try and give key_buffer 1GB, it failed. I swapped the values back and it worked fine... odd. - Gabriel On Jan 26, 2004, at 11:16 AM, Brent Baisley wrote: Yes, MySQL is capable of using more than 2GB, but it still must obey the limits of the underlying OS. This means file sizes, memory allocation and whatever else. Have you heard of anybody allocating more the 2GB using OSX? I've heard of quite a bit more using Linux or other Unix flavors, but not OSX. As for optimizing settings, you need to profile you work load. You may actually run into I/O, CPU or Network bottleneck before you hit a memory bottleneck. You need to run things and find where the bottleneck is to optimize performance. On Jan 26, 2004, at 11:09 AM, Adam Goldstein wrote: Others on this list have claimed to be able to set over 3G, and my failure is with even less than 2G (though, I am unsure if there is a combination of other memory settings working together to create an 2GB situation combined) -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: VB6 ODBC Memory problems
My first recommendation is to switch to ADO. It is much faster. Bruce Lewis - Original Message - From: Thomas Bein [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 16, 2003 12:55 PM Subject: VB6 ODBC Memory problems Hello, I have been developing an application in VB60 (SP5) which seems to eat up my memory after running some time. The programm is just doing a SELECT in a loop. When the programm is stopped, the memory is freed. Developement environment: WinNT40 SP6a VB60 SP5 DAO 3.6 MySql 2.23.54-nt MySql-ODBC 3.51.04 (using no transactions) Are there known problems ? Thomas - 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
VB6 ODBC Memory problems
Hello, I have been developing an application in VB60 (SP5) which seems to eat up my memory after running some time. The programm is just doing a SELECT in a loop. When the programm is stopped, the memory is freed. Developement environment: WinNT40 SP6a VB60 SP5 DAO 3.6 MySql 2.23.54-nt MySql-ODBC 3.51.04 (using no transactions) Are there known problems ? Thomas - 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: Memory problems/bug ?
-Original Message- From: Nemholt, Jesper Frank Sent: viernes, 09 de marzo de 2001 19:52 To: '[EMAIL PROTECTED]' Subject: Memory problems/bug ? Hej! Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. Ran optimize on a table, and after 10 minutes : 010307 17:00:00 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 mysqld ended on Wed Mar 7 17:00:21 MET 2001 One other question about this error : If I try out the latest stable version, will I be able to use the 3.22.32 database files directly, or should I rather dump the existing and import them to the new ? I'd ofcourse still like some answers regarding my original question... -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
Memory problems/bug ?
Hej! Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. Ran optimize on a table, and after 10 minutes : 010307 17:00:00 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 mysqld ended on Wed Mar 7 17:00:21 MET 2001 I checked : iratxe.tm.es ulimit unlimited iratxe.tm.es /usr/sbin/swapon -sv Swap partition /dev/rz8b (default swap): Allocated space:48768 pages (381MB) In-use space: 316 pages ( 0%) Free space: 48452 pages ( 99%) Swap partition /dev/rzb11c: Allocated space: 256841 pages (2006MB) In-use space: 312 pages ( 0%) Free space:256529 pages ( 99%) Total swap allocation: Allocated space: 305609 pages (2387MB) Reserved space: 47242 pages ( 15%) In-use space: 628 pages ( 0%) Available space: 258367 pages ( 84%) No ulimit and more than 2 GB free swap and no active swap usage. Before the crash I'd succesfully optimized other (and smaller) tables. I tried once again on the same table, and MySQL crashed again. Same thing happened when I tried to put a new index on the table. I checked the memory status : http://www.dassic.dk/iratxe_memory.png As seen, the optimize (around 17:00) causes the UBC filesystem buffer to allocate more memory, and when it has used all the free memory, MySQL crashes. I don't understand this, since the memory used by the UBC is available for any program that might need it. The operating system will only use memory for UBC if it's not used for anything else, and release UBC memory if a application needs more. Secondly, MySQL crashed even before the machine started to swap, and as seen the active memory increases only because of the UBC, not because MySQL allocate more memory. Does MySQL 3.22.32 have a bug in this area, and if so, is it fixed in later versions ? I've compiled MySQL with large file 64-bit support, and the filsystem (AdvFS) has no problems with large files. l8r/Jspr - 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
Slackware AND mySQL 3.23.32 memory problems ....
hi! "Marcello" == Marcello Giovagnoli [EMAIL PROTECTED] writes: Marcello Hi, Marcello I'm sorry, for my previus question "To thread or not to thread " Marcello but now all it's OK. :) Marcello Looking at /etc/my.cnf, i could see two variables: Marcello set-variable= thread_cache=8 Marcello set-variable= thread_concurrency=8 # Try number of CPU's*2 Marcello this should set the edge to the maximum threads up to 8 and the mysqld Marcello running daemons are, at least, 8 !(is'nt it ?) Yes. Marcello And the process size, or thread size, decrease when the program goes down Marcello with 3.23.32. Marcello Regards Marcello marcello Regards, Monty - 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
Slackware AND mySQL 3.23.32 memory problems ....
Hi, I'm sorry, for my previus question "To thread or not to thread " but now all it's OK. :) Looking at /etc/my.cnf, i could see two variables: set-variable= thread_cache=8 set-variable= thread_concurrency=8 # Try number of CPU's*2 this should set the edge to the maximum threads up to 8 and the mysqld running daemons are, at least, 8 !(is'nt it ?) And the process size, or thread size, decrease when the program goes down with 3.23.32. Regards marcello - 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
