Hi , We are a company specialized in developing tools for Internet Marketing domain. We are developing a product for which we need consultancy services from professionals speacilized in Mysql server optimization and tuning. Adequate financial remuneration will not be problem for effective solution provider.
Below the nature of our application and problems faced are discussed in detail. We are developing an application that needs to use a massive back-end database. The database will contain around 75 million rows with around 80 columns per row. We would prefer to use MySQL as the database platform as it is free. The MySQL database is hosted on a dedicated server that has been purchased from a web hosting company. This database would be used both by our customers and by our own employees. The first column will contain some text which will be unique in each row. 90% of the remaining columns will containing numbers and the other columns will contain text. The second column will contain numbers and it needs to be updated on a monthly basis. But, we also need to store historical data regarding the value of the second column for each row for the last 24 months, on a rolling basis. This can either be done by adding more columns to the same table, or by putting this historical data in a separate table, depending on your recommendations. Users will make 2 types of queries on this database: i) The first type of query is what can be called a mission-critical query - these queries will be made by our customers and the results of these queries must be returned within 30 seconds at the most; otherwise, customers are not going to want to use the application. This query would basically involve asking the customer for a search string, searching the FIRST column (and ONLY the first column) of the entire database to find out each row that contains that search string (either in whole or in part) and then returning all such rows to the user sorted in descending order of the SECOND column. Only the information in the first 2 columns will be returned to the customers - the information in the other 78 columns will not be returned to the customers. Customers will also have the option of specifying negative matches - i.e. if the first column of a particular row contains any one of a list of banned words or phrases, then that row will not be returned even if it contained the primary search string. ii) The second type of queries are non-mission-critical; these would be run by our employees and it is ok if these queries take as much as 10 minutes to return results. However, the queries that our employees will run are also much more complex - they will specify multiple search criteria - for instance, "return all rows for which the 60th column has a value > 2000 and the minimum value for the columns 40, 41, ... 50 for that row is 20 and the 35th column of that row is < 5" etc. It is quite possible that as many as 20 - 30 users will be querying the database at the same time. Furthermore, there will be 5 - 6 different PHP scripts that are going to constantly update the different columns and rows of the database with the values. Now we have hired a server with the following configuration: Server: Dual Xeon 2.8 GHz Secondary Processor: Second Xeon Processor Primary HDD: 73 GB SCSI Secondary HDD: None Third HDD: None RAM: ECC Registered 1024MB RAM Number of ips: 10 IP Addresses Bandwidth: 2000 GB Bandwidth Uplink Port Speed: 100 Mbps Uplink Database: MySQL 4.1.11-standard Backup Service: Network Backup Operating System: Red Hat Enterprise Linux, Version 3 Drive Controller: SCSI Chassis Control: DRAC Card We are executing certain queries but they are taking too long a time. Could you help us in tuning the SQL queries and the MySQL database such that the query time reduces and we get a fatser and more efficient database? Do let us know what is the timeframe required for this and also please indicate your charges for the same. Please send us your profile and details of some assignments that you have carried out. Awaiting your response, Regards Suryya Ghosh
