Slow queries / inserts InnoDB
Hi. I have developed my first application with VB.NET at work (junior developer) and basically it reads several thousands of lines from X number of files, and inserts them into a mysql database. The application consists of several steps: 1) Dump all lines with all fields into a temp table. a. This works fine. Optimal speed for the hardware we have. 2) Query temp table to obtain certain. I query by a unique ID. I insert all unique ID (isn field). If the ID matches my interests, I insert it into an ArrayList. a. Query: SELECT COUNT(SUBSTR(ISN,2)), SUBSTR(isn, 2) FROM SMS.TEMPDATADUMP WHERE error = 0 GROUP BY SUBSTR(ISN,2) HAVING COUNT(SUBSTR(ISN,2)) = 4 b. The isn is not unique per line, but per data (sms) c. Once I have all isn on an arraylist, I do the following query: i. SELECT select, my, fields,of,interest FROM SMS.TEMPDATADUMP WHERE SUBSTR(ISN, 2) = ' isn ' ORDER BY type LIMIT 1 d. To obtain some data. During the process I query around 10 times other table per ISN. e. Here is the problem. If I have a few files to process (around 3000-4000 lines in total, small array) this steps work fine, good speed. But If I have big files or a lot of files (more than 1 lines in total, big array), this steps are incredibly slow. Queries and inserts are too slow. Meaning, one-two inserts per second, while the other case inserts are around 800 per second. Our hardware is not optimized for database server, but I don’t have other choice. It is mostly a desktop computer Intel core i5, windows 32 bits, 3GB RAM, one disk 500GB 7200rpm. I have tried some optimizations commented in mysqlperformance blog without success. Any way to optimize this? Thank you very much in advance. Adrián Espinosa Moreno.
RE: mysql 5.5.24/27 massive slow down since upgrade to solaris 10 u9 with 147440-19
Are you I/O bound? XFS What elevator strategy? Compare settings. Especially innodb_flush_log_at_trx_commit . -Original Message- From: Gael Martinez [mailto:gael.marti...@gmail.com] Sent: Sunday, October 07, 2012 5:46 PM To: mysql Subject: mysql 5.5.24/27 massive slow down since upgrade to solaris 10 u9 with 147440-19 Hello For nearly 3 years, we have had a master - slaves configuration working like a charm, the slaves would be able to sync daily ... since a recent upgrade from Solaris10u6 to solaris 10 u9 with the latest CPU bundle (kernel patch 147440-19) on one of the two slaves, the slave upgrade cannot keep up anymore... it is now at over 2 days behind in replication and the delay increases... the volume of data did not increase and the second slave seems in good shape (still on u6). No configuration change was done to the mysql configuration itself... Is there a well know issue/parameter to configure with the more recent versions of solaris ? The storage is on an Hitachi san storage with 4Gig HBAs and the volume of data to replicate is about 6-8 1G binary log files a day... Another change is that the new machine global zone is using zfs for the boot disks, but the database zone itself including the data directory is sitting on ufs filesystems... Regards -- Gaël Martinez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: date-IFNULL-sum bug?
Do not use + for DATE arithmetic!
Use, for example
+ INTERVAL 1 YEAR
-Original Message-
From: h...@tbbs.net [mailto:h...@tbbs.net]
Sent: Thursday, October 04, 2012 9:35 PM
To: mysql@lists.mysql.com
Subject: date-IFNULL-sum bug?
Can anyone explain this to me?
The first one seems quite wrong; the rest make perfect sense.
mysql select ifnull(date('1900/5/3'), date('1900/01/01')) + 1;
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) + 1 |
+--+
|11900 |
+--+
1 row in set (0.00 sec)
mysql select ifnull(date('1900/5/3'), date('1900/01/01'));
+--+
| ifnull(date('1900/5/3'), date('1900/01/01')) |
+--+
| 1900-05-03 |
+--+
1 row in set (0.00 sec)
mysql select date('1900/5/3') + 1;
+--+
| date('1900/5/3') + 1 |
+--+
| 19010503 |
+--+
1 row in set (0.00 sec)
mysql select date(date('1900/5/3') + 1);
++
| date(date('1900/5/3') + 1) |
++
| 1901-05-03 |
++
1 row in set (0.00 sec)
(5.5.8 under muSoft Windows)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
