Re: [ANN] PBXT Beta Version Released!
Hi Mike, The architectures of the engines are radically different, so you can expect different performance characteristics. But the main differences and advantages will show in the future, as the engines are developed further. I spoke about the future directions of PBXT at the last user's conference, here is the presentation: http://www.primebase.org/download/pbxt-uc-2008.pdf Some difference as the moment: PBXT has foreign key support like InnoDB, which Falcon does not (yet). And PBXT can stream BLOB data to and from the database, which no other engine can do (yet). This functionality is provided by the BLOB Streaming engine from: www.blobstreaming.org . Best regards, Paul On Sep 2, 2008, at 9:12 PM, mos wrote: Paul McCullagh, What's the advantage of using PBXT over InnoDb or the new Falcon transactional engine? TIA Mike Hi All! I am pleased to announce that the Beta version of PBXT has just been released. PBXT is a fast, lightweight, transactional storage engine for MySQL. More information is available at http://www.primebase.org. You can download the source code at http://www.primebase.org/ download. I have also updated the documentation for this version (http://www.primebase.org/documentation ). Configuring and building the engine is easier than ever now. To configure PBXT all you have to do is specify the path to the MySQL source code tree (after building MySQL), for example: ./configure --with-mysql=/home/foo/mysql/mysql-5.1.26-rc The PBXT configure command will retrieve all required options from the MySQL build. For example whether to do a debug or optimized build and where to install the plugin are determined automatically, depending on how you configured MySQL. This was a source of some mistakes when building the plugin, so I think it is really cool! My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do! Let me know if you have any comments or questions, I'll be glad to help. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- 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] -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Community Server License
Hi, I would like to develop a commercial application using MySQL Community Server as database platform. Can I do this? Thanks and regards.
Large Query Question.
Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life
Re: Large Query Question.
Jim, Retrieving 100,000 rows will always take some time. Do you really need to return that many rows? Are you selecting just the columns you need? What are the slow queries? Mike At 12:05 PM 9/3/2008, Jim Leavitt wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
What are the queries? Are they straight forward selects or joins? Are the columns you select from indexed and are the indexes up-to-date? On Wed, Sep 3, 2008 at 12:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
MySQL crash (negative mmapped regions)
I've been trying to backdown a production database for some time and can't seem to get around MySQL crashing at about 1GB of backup data. (the database is about 400GB). I have a page corruption, but it isn't found when I do a CHECK TABLE on *any* of the tables. InnoDB is running in super-safe (double-write) mode. The most recent crash spit out this memory status: Memory status: Non-mmapped space allocated from system: 541413376 Number of free chunks: 7121 Number of fastbin blocks: 0 Number of mmapped regions:18 Space in mmapped regions: -2063269888 Maximum total allocated space:0 Space available in freed fastbin blocks: 0 Total allocated space: 478166624 Total free space:63246752 Top-most, releasable space: 749360 Estimated memory (with thread stack):-1327869952 --- All those negative numbers make me believe its a MySQL bug rather than a data corruption issue. The server is now running 5.0.67 (redhat x86_64) Community. The server has 16 GB of ram and 8 cores and 6 RAID 1 arrays with the InnoDB files split amongst each. Any suggestions on where to look to get this figured out? thanks in advance, LA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
That's a lot of data to return, make sure you factor in data load and transfer time. You may try breaking your query into smaller parts and recombining the results in a scripting language. If you are searching on a range (i.e. date range), break the range into smaller parts and run multiple queries. Divide and conquer, it will scale better. Brent Baisley On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Large Query Question.
Right... and perhaps try MySQL Enterprise Monitor. A trial is available from mysql.com. It may give you hints on your mysql.cnf. Kind regards, TomH -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2008 11:35 PM To: Jim Leavitt Cc: mysql@lists.mysql.com Subject: Re: Large Query Question. That's a lot of data to return, make sure you factor in data load and transfer time. You may try breaking your query into smaller parts and recombining the results in a scripting language. If you are searching on a range (i.e. date range), break the range into smaller parts and run multiple queries. Divide and conquer, it will scale better. Brent Baisley On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote: Greetings List, We have a medium-large size database application which we are trying to optimize and I have a few questions. Server Specs 1 Dual Core 2.6 Ghz 2GB Ram Database Specs 51 Tables Min 10 rows, Max 100 rows Total size approx 2GB My.cnf [mysqld] set-variable=local-infile=0 log-slow-queries=slow-queries.log datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 key_buffer = 512M max_allowed_packet=4M sort_buffer_size = 512M read_buffer_size = 512M read_rnd_buffer_size = 256M record_buffer = 256M myisam_sort_buffer_size = 512M thread_cache = 128 query_cache_limit = 1M query_cache_type = 1 query_cache_size = 32M join_buffer = 512M table_cache = 512 We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Thanks much. Jim Leavitt Developer Treefrog Interactive Inc. (www.treefrog.ca) Bringing the Internet to Life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL crash (negative mmapped regions)
How are you performing the backup? What tools are involved? Are there any ther users of the database while you are doing this? - michael dykman On Wed, Sep 3, 2008 at 4:43 PM, L'argent [EMAIL PROTECTED] wrote: I've been trying to backdown a production database for some time and can't seem to get around MySQL crashing at about 1GB of backup data. (the database is about 400GB). I have a page corruption, but it isn't found when I do a CHECK TABLE on *any* of the tables. InnoDB is running in super-safe (double-write) mode. The most recent crash spit out this memory status: Memory status: Non-mmapped space allocated from system: 541413376 Number of free chunks: 7121 Number of fastbin blocks: 0 Number of mmapped regions:18 Space in mmapped regions: -2063269888 Maximum total allocated space:0 Space available in freed fastbin blocks: 0 Total allocated space: 478166624 Total free space:63246752 Top-most, releasable space: 749360 Estimated memory (with thread stack):-1327869952 --- All those negative numbers make me believe its a MySQL bug rather than a data corruption issue. The server is now running 5.0.67 (redhat x86_64) Community. The server has 16 GB of ram and 8 cores and 6 RAID 1 arrays with the InnoDB files split amongst each. Any suggestions on where to look to get this figured out? thanks in advance, LA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Query Question.
On Wed, Sep 3, 2008 at 1:05 PM, Jim Leavitt [EMAIL PROTECTED] wrote: We are having trouble with certain queries which are returning anywhere from 10 - 30 rows. Total query time is taking approx 1 - 2 mins depending on load. Is there anything in our conf file which could improve our performance? Are there any hardware recommendations that could help us improve the speed? Would more memory help us? Any comments or recommendations are greatly appreciated. Returning 100,000 to 300,000 rows will take some time no matter how you slice it. A more common approach is to be sure that the database is organized for O(log N) retrieval, then to retrieve only the records you need (the ones you need to display, for example), then to execute a second query to get more, and then a third query, etc. O(log N) retrieval = indices for the columns and the database arranged so that equality and ordering are implemented using native data types. What is your application? Do you really need all those rows at one time?
my.cnf optimization
All, We're seeing a huge surge in our qps and I'd like to make sure we're tuned as well as we can be. I'm wondering if I've got some variables maybe set too large (is that even possible?) ? We do have a fair bit of innodb, so perhaps I should add some non-defaults there, but I'm not so sure where to start with that. Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x 280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's my my.cnf: [billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf [client] port= 3306 socket = /var/mysql/mysql.sock [mysqld] port= 3306 socket = /var/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 50M table_cache = 2048 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M thread_cache_size = 100 query_cache_size = 64M thread_concurrency = 16 skip-thread-priority max_connections = 750 old-passwords innodb_file_per_table innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-slow-queries long_query_time=2 log_queries_not_using_indexes log-bin=mysql-bin server-id = 4 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout SHOW STATUS\G output follows my sig below... My devs are adding indexes where the slow query log is pointing them, but any suggestions on how better to tune things up would be much appreciated. I'm not sure what else to tune here but we're getting bursts of 1200+ queries per second regularly and seeing things slow down significantly. Best, -- Ryan Schwartz mysql SHOW STATUS\G *** 1. row *** Variable_name: Aborted_clients Value: 1656 *** 2. row *** Variable_name: Aborted_connects Value: 3 *** 3. row *** Variable_name: Binlog_cache_disk_use Value: 276 *** 4. row *** Variable_name: Binlog_cache_use Value: 6416113 *** 5. row *** Variable_name: Bytes_received Value: 134 *** 6. row *** Variable_name: Bytes_sent Value: 70104 *** 7. row *** Variable_name: Com_admin_commands Value: 0 *** 8. row *** Variable_name: Com_alter_db Value: 0 *** 9. row *** Variable_name: Com_alter_table Value: 0 *** 10. row *** Variable_name: Com_analyze Value: 0 *** 11. row *** Variable_name: Com_backup_table Value: 0 *** 12. row *** Variable_name: Com_begin Value: 0 *** 13. row *** Variable_name: Com_call_procedure Value: 0 *** 14. row *** Variable_name: Com_change_db Value: 0 *** 15. row *** Variable_name: Com_change_master Value: 0 *** 16. row *** Variable_name: Com_check Value: 0 *** 17. row *** Variable_name: Com_checksum Value: 0 *** 18. row *** Variable_name: Com_commit Value: 0 *** 19. row *** Variable_name: Com_create_db Value: 0 *** 20. row *** Variable_name: Com_create_function Value: 0 *** 21. row *** Variable_name: Com_create_index Value: 0 *** 22. row *** Variable_name: Com_create_table Value: 0 *** 23. row *** Variable_name: Com_create_user Value: 0 *** 24. row *** Variable_name: Com_dealloc_sql Value: 0 *** 25. row *** Variable_name: Com_delete Value: 0 *** 26. row *** Variable_name: Com_delete_multi Value: 0 *** 27. row *** Variable_name: Com_do Value: 0 *** 28. row *** Variable_name: Com_drop_db Value: 0 *** 29. row *** Variable_name:
