?
Solved

Any way of increasing a mysql view performance when you can't use the merge algorithm.

Posted on 2011-09-29
10
Medium Priority
?
407 Views
Last Modified: 2012-05-12
Long story short, I created a view that utilizes a UNION ALL and does some math operations.  I am aware that this means I cannot use the Merge algorithm for the view, but the performance is pretty bad using the temptable algorithm.  

I really need to use this view because it makes the coding so much easier for our developers.  Are there any suggestions on improving the performance or possibly an alternative to using the temptable algorithm?  I would be open to switching database platforms (postgre maybe?) or change the server configuration, adding resources to the server, etc.

Any ideas?
0
Comment
Question by:gr1z
  • 5
  • 5
10 Comments
 
LVL 1

Expert Comment

by:Adrianc
ID: 36815397
Hi,

first I need to know:

- how big are your tables and your indexes (tables used in your query)
- hardware specs (processor, ram)
- operating sistem
- platform (32bit or 64bit)
- contents of your mysql configuration file (my.cnf)

With this info I will provide a solution for sure.

There a are 3 possible solutions:

1. mysql configuration changes (easy solution)
2. increase Memory (RAM) (medium solution)
3. change platform (hard way)

Please provide more information of what data form which tables do you want to query, maybe I will give you alternative solutions.

Thanks.
0
 

Author Comment

by:gr1z
ID: 36815876
1. how big are the tables - approx 100,000 rows

2. hardware specs - dedicated mysql server, rackspace cloud server with 1 gb ram, not sure how they rate the processor on those.

3. OS - Ubuntu

4. platform - 64 bit

5. contents of my.cnf - attached

thanks!


my.txt
0
 
LVL 1

Expert Comment

by:Adrianc
ID: 36816333
Ok,

- 1 GB ram is not enough for a mysql server (it is not enough to run decent even a desktop computer with Windows 7)
- key_buffer            = 16M it is very small - try increasing step by step that value and check for speed improve.

Here are some values from a running mysql server with 96 GB ram.

key_buffer_size = 8G
query_cache_size = 1G
query_cache_limit = 88G
join_buffer_size = 32M
tmp_table_size = 16G
max_heap_table_size = 2G
thread_cache_size = 16
table_cache = 204800
innodb_buffer_pool_size = 1G
sort_buffer_size = 16M
thread_concurrency = 16

This server have 100 GB of data ( and growing ) that needs to be extracted every second and it is lighting fast.

You cand tune your mysql server with this little tool written in perl:

http://mysqltuner.pl/mysqltuner.pl

Thanks.

Let me know the results of mysqltunner.pl and if there is a speed increase after changing key_buffer to 64M (for example)

Remember that I don't have any server with 1 GB ram - even my desktop computer have 4 GB ram.

Let's not forget that your queryes must use indexes a lot if you want speed, if your queries is not optimised they will be slow on any hardware so please paste those queries here and some table info.

Thanks.


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:gr1z
ID: 36816405
thanks, i will give these a try and report back.  i think part of the problem is when you use a view with a UNION it has to is the temptable algorithm, and this does not allow indexes.  
0
 
LVL 1

Expert Comment

by:Adrianc
ID: 36816675
That is why I want to see exactly what you want to do, maybe we can find another way, a better way.

0
 

Author Comment

by:gr1z
ID: 36816716
I basically have 2 tables.  1 table is for hours (billable time) and one tables is for expenses and etc.  i am joining these tables together so it is easier to grab a log of all the relevant hours / expenses for a certain project, employee, etc.

here is my query:


(
		SELECT
		_utf8 'time' AS `type`,
		`logHours`.`hourId` AS `typeId`,
		`logHours`.`proId` AS `proId`,
		`logHours`.`loginId` AS `loginId`,
		`logHours`.`coId` AS `coId`,
		`logHours`.`coName` AS `coName`,
		`logHours`.`proName` AS `proName`,
		`logHours`.`date` AS `thisDate`,
		`logHours`.`item` AS `item`,
		`logHours`.`amount` AS `quantity`,
		`logHours`.`loginBillable` AS `rate`,
		(
			`logHours`.`loginBillable` * `logHours`.`amount`
		)AS `total`,
		`logHours`.`RealParentId` AS `RealParentId`,
		`logHours`.`proParentId` AS `proParentId`,
		`logHours`.`isBillable` AS `isBillable`,
		`logHours`.`timesheet` AS `timesheet`,
		`logHours`.`notes` AS `notes`,
		`logHours`.`ParentName` AS `parentName`
FROM
		`time`.`logHours`
	)
UNION ALL
	(
		SELECT
		_utf8 'expense' AS `type`,
		`logExpense`.`expId` AS `typeId`,
		`logExpense`.`proId` AS `proID`,
		`logExpense`.`expLoginId` AS `loginId`,
		`logExpense`.`coId` AS `coId`,
		`logExpense`.`coName` AS `coName`,
		`logExpense`.`proName` AS `proName`,
		`logExpense`.`date` AS `thisDate`,
		`logExpense`.`item` AS `item`,
		`logExpense`.`expQuantity` AS `quantity`,
		`logExpense`.`expRate` AS `rate`,
		(
			`logExpense`.`expRate` * `logExpense`.`expQuantity`
		)AS `total`,
		`logExpense`.`RealParentId` AS `RealParentId`,
		`logExpense`.`proParentId` AS `proParentId`,
		`logExpense`.`isBillable` AS `isBillable`,
		`logExpense`.`timesheet` AS `timesheet`,
		`logExpense`.`notes` AS `notes`,
		`logExpense`.`ParentName` AS `parentName`
FROM
		`time`.`logExpense`
	)

Open in new window

0
 
LVL 1

Accepted Solution

by:
Adrianc earned 2000 total points
ID: 36816796
Hmmm.....

Please try without UNION and see the results.

Make two separate queries and join the results programmatically, I think it will be faster because there is no need for creating a temp table.



0
 

Author Comment

by:gr1z
ID: 36823849
Not sure I follow what you mean by join programmatically?  

do you mean have a separate view for each table and then do the union on them in my scripting language (like php or coldfusion)?

if this isnt what you mean can you provide a quick example?

thanks for the help
0
 

Author Comment

by:gr1z
ID: 36830432
some quick testing, making two separate views and then doing the union on those in coldfusion makes quite a difference in speed.

quick question,

if i do a query like this:

SELECT * FROM test_hours WHERE loginId = 1 AND proId = 641 AND isBillable = 'yes' AND thisDate = '04/09/11' AND type = 'time'
UNION ALL
SELECT * FROM test_exp WHERE loginId = 1 AND proId = 641 AND isBillable = 'yes' AND thisDate = '04/09/11' AND type = 'time'

Open in new window


is there a better way to specify the where clause so I only have to define it once?  not a huge deal just cleans up the code a little bit.

also if this example isn't what you were suggesting please let me know, thanks!
0
 
LVL 1

Expert Comment

by:Adrianc
ID: 36889968
Hi, by joining them programmatically what I meant is:

PHP code:

$res = mysql_query("SELECT query number 1");
while ($row = mysql_fetch_assoc($res))
{
    //do the calculations here and store them in an array
}

$res = mysql_query("SELECT query number 2");
while ($row = mysql_fetch_assoc($res))
{
    //do the calculations here and store them in the same array and build your report
}

Open in new window



Of course this is just an ideea, maybe it is too hard to implement in your project OR maybe there will be a decrease of performance because we all know that PHP is not good for heavy calculations and memory allocation.

Do some tests and let me know.

If you are using UNION keep the where clause in the subquery.




0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question